TA的每日心情 | 开心 2023-8-9 11:05 |
---|
|
楼主 |
发表于 2020-1-13 22:06:48
|
显示全部楼层
- select 1 as id,'max memory' as configname
- into #configure_parameters
- union all select 2,'procedure cache size'
- union all select 3,'max online engines'
- union all select 4,'number of user connections'
- union all select 5,'number of open objects'
- union all select 6,'number of open indexes'
- union all select 7,'number of open partitions'
- union all select 8,'number of locks'
- union all select 9,'number of open databases'
- go
- select 1 as id, 105 as confignum, null as counter1, null as counter2, "open_database_reuse_requests" as counter3, null as counter4, 0 as is_perprocess, 1 as multiplier
- into #resource_monitor_tbl
- union all select 2,107,null,null,"open_object_reuse_requests",null,0,1
- union all select 3,146,"active_procedure_cache","hwm_procedure_cache","procedure_cache_reuse_requests",null,0,1
- union all select 4,263,null,null,"open_index_reuse_requests",null,0,1
- union all select 5,266,null,null,null,null,0,1
- union all select 6,301,"active_dskbufs","hwm_dskbufs",null,null,0,1
- union all select 7,185,null,null,null,null,1,0
- union all select 8,347,null,null,"dtxp_reuse_reqs",null,0,1
- union all select 9,103,"active_connections","hwm_connections",null,null,0,1
- union all select 10,267,"active_worker_process","hwm_worker_process",null,null,0,1
- union all select 11,242,null,null,null,null,0,1
- union all select 12,116,"active_devices","hwm_devices",null,null,0,1
- union all select 13,355,null,null,null,null,0,1
- union all select 14,340,null,null,null,null,0,1
- union all select 15,341,null,null,null,null,0,1
- union all select 16,331,null,null,null,null,0,1
- union all select 17,395,null,null,null,null,0,1
- union all select 18,120,"active_remote_connections","hwm_remote_connections",null,null,0,1
- union all select 19,118,"active_remote_logins","hwm_remote_logins",null,null,0,1
- union all select 20,119,"active_remote_sites","hwm_remote_sites",null,null,0,1
- union all select 21,136,null,null,null,null,0,1
- union all select 22,186,"active_perm_cache_entries","hwm_perm_cache_entries","perm_cache_entries_reuse_requests",null,1,1
- union all select 23,137,null,null,null,null,0,1
- union all select 24,171,null,null,null,null,0,1
- union all select 25,172,null,null,null,null,0,1
- union all select 26,181,"active_sort_buffers","hwm_sort_buffers",null,null,0,1
- union all select 27,399,null,null,"heap_mem_waits",null,1,1
- union all select 28,396,"hwm_maxmem","hwm_maxmem",null,null,0,1
- union all select 29,106,null,null,null,null,0,1
- union all select 30,173,null,null,null,null,0,1
- union all select 31,277,null,null,null,null,0,1
- union all select 32,268,null,null,null,null,2,1
- union all select 33,126,"active_online_engines","hwm_online_engines",null,null,0,1
- union all select 34,156,"active_network_listeners","hwm_network_listeners",null,null,0,1
- union all select 35,150,null,null,null,null,0,1
- union all select 36,408,null,null,"open_partition_reuse_requests",null,0,1
- go
- select convert(varchar(30),c.name) as name
- ,cc.value as config_value
- ,case
- when config_admin(22, confignum, 2, 0, counter1, NULL) < 0 or (cc.value-config_admin(22, confignum, 2, 0, counter1, NULL)) < 0 then cc.value
- else cc.value-config_admin(22, confignum, 2, 0, counter1, NULL)
- end as num_free
- ,case
- when config_admin(22, confignum, 2, 0, counter1, NULL) < 0 then 0
- else config_admin(22, confignum, 2, 0, counter1, NULL)
- end as num_active
- , case
- when config_admin(22, confignum, 3, 0, counter2, NULL) < 0 then 0
- else config_admin(22, confignum, 3, 0, counter2, NULL)
- end as max_active
- ,(case
- when cc.value=0 then 0.00
- else convert(decimal(10,2), (case when config_admin(22, confignum, 2, 0, counter1, null) < 0 then 0 else config_admin(22, confignum, 2, 0, counter1, null) end) * 100.0 / cc.value)
- end) as Pct_act
- from #resource_monitor_tbl a, master..sysconfigures c,master..syscurconfigs cc ,#configure_parameters t
- where c.config=a.confignum and c.config=cc.config and c.name=t.configname
- go
复制代码 |
|