SqlServer性能监控指标（部分）
以下内容，有些细节字段后续再丰富，⽬前只是供⾃⼰整理
1.SqlServer已获得的页⾯数(缓冲池)
SELECT
cntr_value
FROM
sys.dm_os_performance_counters
WHERE
object_name = 'SQLServer:Buffer Manager'
AND counter_name = 'Databa pages'
2.SqlServer缓冲池必须的理想页⾯数
SELECT
cntr_value
FROM
sys.dm_os_performance_counters
WHERE
object_name = 'SQLServer:Buffer Manager'
AND counter_name = 'Target pages'
3.数据页在缓冲池中驻留的时间，这个时间⼀般会⼤于 300 单位 s
SELECT
cntr_value
FROM
sys.dm_os_performance_counters
WHERE
object_name = 'SQLServer:Buffer Manager'
AND counter_name = 'Page life expectancy'
4.数据库缓冲池⾼速缓冲命中率，被请求的数据在缓冲池中被找到的概率，⼀般会⼤于 98% 才算正常，否则可能是缓冲池容量太⼩
SELECT
cntr_value
FROM
sys.dm_os_performance_counters三国马超
WHERE
object_name = 'SQLServer:Buffer Manager'梦见牙疼
AND counter_name = 'Buffer cache hit ratio'
5.检查点每秒写⼊磁盘的脏页个数，如果数据过⾼，证明缺少内存容量
SELECT
表示说的词语有哪些cntr_value
FROM
sys.dm_os_performance_counters
WHERE
瑶族服饰
object_name = 'SQLServer:Buffer Manager'
AND counter_name = 'Checkpoint pages/c'
6.缓存池中每秒读写的页数
cntr_value
FROM
sys.dm_os_performance_counters
WHERE
object_name = 'SQLServer:Buffer Manager'
AND counter_name = 'Page reads/c';
SELECT
cntr_value
FROM
sys.dm_os_performance_counters
WHERE
object_name = 'SQLServer:Buffer Manager'
AND counter_name = 'Page writes/c'
7.每秒全表扫描次数
SELECT
*
FROM
sys.dm_os_performance_counters
WHERE
object_name = 'SQLServer:Access Methods'
AND counter_name = 'Full Scans/c'
8.每秒页⾯拆分次数
SELECT
*
FROM
sys.dm_os_performance_counters
WHERE
object_name = 'SQLServer:Access Methods'
AND counter_name = 'Page Splits/c'
9.等待内存授权的进程队列
千层葱油饼
SELECT
*
FROM
sys.dm_os_performance_counters
WHERE
object_name = 'SQLServer:Memory Manager'
AND counter_name = 'Memory Grants Pending' 10.每秒创建临时表和临时变量的数量
SELECT
*
FROM
sys.dm_os_performance_counters
WHERE
object_name = 'SQLServer:General Statistics'
AND counter_name = 'Temp Tables Creation Rate' 11.正在等待销毁的临时表的数量
*
八的英语单词FROM
sys.dm_os_performance_counters
WHERE
object_name = 'SQLServer:General Statistics'
AND counter_name = 'Temp Tables For Destruction' 12.每秒批处理请求次数
SELECT
*
FROM
sys.dm_os_performance_counters
WHERE
object_name = 'SQLServer:SQL Statistics'
AND counter_name = 'Batch Requests/c'
13.每秒锁等待数⽬
SELECT
cntr_value
FROM
sys.dm_os_performance_counters
WHERE
object_name = 'SQLServer:Locks'
AND instance_name = '_Total'
AND counter_name = 'Lock Waits/Sec'
14.缓存池中的页寿命
SELECT
cntr_value
FROM
sys.dm_os_performance_counters
WHERE
object_name = 'SQLServer:Buffer Manager'
AND counter_name = 'Page life expectancy'
15.数据库剩余空间
SELECT
*
FROM
master.sys.sysperfinfo
WHERE
object_name = 'SQLServer:Memory Manager'
AND counter_name = 'Free Memory (KB)'
16.当前已连接的会话数
cntr_value
FROM
sys.dm_os_performance_counters
WHERE
object_name = 'SQLServer:General Statistics'
AND counter_name = 'Ur Connections'
17.数据库服务启动的时间
SELECT
sqlrver_start_time AS start_time
FROM
sys.dm_os_sys_info
18.每秒编译执⾏数量
SELECT
cntr_value
FROM
sys.dm_os_performance_counters
WHERE
object_name = 'SQLServer:SQL Statistics'
AND counter_name = 'SQL Compilations/Sec'
19.服务总内存
SELECT
*,
CAST (
cntr_value / 1024.0 AS DECIMAL ( 20, 1 )) MemoryMB FROM
master.sys.sysperfinfo
WHERE
counter_name = 'Total Server Memory (KB)'
20.每秒重编译数量
SELECT
cntr_value
FROM
sys.dm_os_performance_counters
WHERE
object_name = 'SQLServer:SQL Statistics'
AND counter_name = 'SQL Re-Compilations/Sec'
21.缓冲池中每秒懒写⼊的个数
SELECT
cntr_value
FROM
sys.dm_os_performance_counters
WHERE
object_name = 'SQLServer:Buffer Manager'
AND counter_name = 'Lazy writes/c';
22.锁平均等待时间
SELECT
*
FROM
sys.dm_os_performance_counters
一场美丽的邂逅WHERE
object_name = 'SQLServer:Wait Statistics'
23.进程在队列中等待内存锁花费的时间
SELECT
*
FROM
sys.dm_os_performance_counters
WHERE
object_name = 'SQLServer:Wait Statistics' and
counter_name = 'Memory grant queue waits'
and instance_name = 'Average wait time (ms)'
24.计划缓冲区中不同缓冲池的命中率
SELECT
instance_name,
round(
CAST ( SUM ( CASE WHEN counter_name = 'Cache Hit Ratio' THEN cntr_value ELSE 0 END ) AS DECIMAL ( 38, 16 ) ) / SUM ( CASE WHEN counter_name = 'C 2
) AS b
FROM
sys.dm_os_performance_counters
WHERE
object_name = 'SQLServer:Plan Cache'毛泽民的子女
GROUP BY
instance_name
HAVING
SUM ( CASE WHEN counter_name = 'Cache Hit Ratio Ba' THEN cntr_value ELSE 0 END ) <>0
25.当前数据库⾼消耗的SQL
