SqlServer性能监控指标(部分)

以下内容，有些细节字段后续再丰富，目前只是供自己整理

 

1.SqlServer已获得的页面数(缓冲池)

SELECT
	cntr_value 
FROM
	sys.dm_os_performance_counters 
WHERE
	object_name = 'SQLServer:Buffer Manager' 
	AND counter_name = 'Database 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/sec'

6.缓存池中每秒读写的页数

SELECT
	cntr_value 
FROM
	sys.dm_os_performance_counters 
WHERE
	object_name = 'SQLServer:Buffer Manager' 
	AND counter_name = 'Page reads/sec';

SELECT
	cntr_value 
FROM
	sys.dm_os_performance_counters 
WHERE
	object_name = 'SQLServer:Buffer Manager' 
	AND counter_name = 'Page writes/sec'

7.每秒全表扫描次数

SELECT
	* 
FROM
	sys.dm_os_performance_counters 
WHERE
	object_name = 'SQLServer:Access Methods' 
	AND counter_name = 'Full Scans/sec'

8.每秒页面拆分次数

SELECT
	* 
FROM
	sys.dm_os_performance_counters 
WHERE
	object_name = 'SQLServer:Access Methods' 
	AND counter_name = 'Page Splits/sec'

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.正在等待销毁的临时表的数量

SELECT
	* 
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/sec'

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.当前已连接的会话数

SELECT
	cntr_value 
FROM
	sys.dm_os_performance_counters 
WHERE
	object_name = 'SQLServer:General Statistics' 
	AND counter_name = 'User Connections'

17.数据库服务启动的时间

SELECT
	sqlserver_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/sec';

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 = 'Cache Hit Ratio Base' THEN cntr_value ELSE 0 END ) * 100,
	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 Base' THEN cntr_value ELSE 0 END ) <>0

25.当前数据库高消耗的SQL

SELECT
	objectid,
	total_cpu_time,
	total_exec_count,
	num_of_statment,
	total_logical_reads / total_exec_count AS avg_logical_reads,
	total_logical_writes / total_exec_count AS avg_logical_writes,
	total_physical_reads / total_exec_count AS avg_physical_reads,
	text 
FROM
	(
	SELECT
		qs.sql_handle,
		SUM ( qs.total_worker_time ) AS total_cpu_time,
		CAST (
		SUM ( qs.execution_count ) AS DECIMAL ( 38, 16 )) AS total_exec_count,
		COUNT ( * ) AS num_of_statment,
		SUM ( total_logical_reads ) AS total_logical_reads,
		SUM ( total_logical_writes ) AS total_logical_writes,
		SUM ( total_physical_reads ) AS total_physical_reads 
	FROM
		sys.dm_exec_query_stats qs 
	GROUP BY
		qs.sql_handle 
	) dt CROSS apply sys.dm_exec_sql_text ( dt.sql_handle ) eqp 
WHERE
	objectid IS NOT NULL

26.当前数据库有阻塞和物理IO多的session

SELECT
	session_id,
	request_id,
	start_time,
	status,
	command,
	sql_handle,
	plan_handle,
	database_id,
	user_id,
	connection_id,
	blocking_session_id,
	wait_type,
	wait_time,
	wait_resource,
	open_transaction_count,
	open_resultset_count,
	transaction_id,
	cpu_time,
	total_elapsed_time,
	reads,
	writes,
	logical_reads,
	lock_timeout,
	granted_query_memory 
FROM
	sys.dm_exec_requests 
WHERE
	( logical_reads <> 0 AND reads / logical_reads >= 0.2 ) 
	OR blocking_session_id <> 0

27.死锁数

SELECT
	*
FROM
	sys.dm_os_performance_counters 
WHERE
	object_name = 'SQLServer:Locks' 
	AND counter_name = 'Number of Deadlocks/sec'
	AND instance_name = '_Total'

28.闩等待的平均时间

SELECT
	*
FROM
	sys.dm_os_performance_counters 
WHERE
	object_name = 'SQLServer:Latches' 
	AND counter_name = 'Average Latch Wait Time (ms)'

29.进程阻塞个数

SELECT
	*
FROM
	sys.dm_os_performance_counters 
WHERE
	object_name = 'SQLServer:General Statistics'
	AND counter_name = 'Processes blocked'

30.版本信息

SELECT SERVERPROPERTY('productversion'),SERVERPROPERTY('productlevel'),SERVERPROPERTY('edition')

31.数据库启动时间

SELECT sqlserver_start_time FROM sys.dm_os_sys_info

32.查询缓存的各类执行计划分别占用了多少内存