检测CPU瓶颈

对于SQL Server的一个工作进程的状态有很多，主要状态有运行中（RUNNING)、可运行（RUNNABLE)和挂起(SUSPENED)3种。

通过查看系统监视计数器Processor:% Processor Time，可以确定CPU瓶颈。如果这个计数器的值很高。比如持续15-20分钟超80%，就意味着CPU出现了瓶颈。

检测CPU压力的另一个方法是计算可运行状态下的工作进程数量，通过执行如下的DMV查询可以得到这个信息：

SELECT COUNT(*) AS workers_waiting_for_cpu, t2.Scheduler_id 
FROM sys.dm_os_workers AS t1, sys.dm_os_schedulers AS t2
WHERE t1.state = 'RUNNABLE' AND t1.scheduler_address=t2.scheduler_address AND t2.scheduler_id < 255
GROUP BY t2.scheduler_id

也可以执行如下的查询得到工作进程在可运行状态下花费的时间：

SELECT SUM(signal_wait_time_ms) FROM sys.dm_os_wait_stats

 

下面查询是找出每次执行占用CPU最多的前100位查询：

SELECT TOP 100 total_worker_time/execution_count AS avg_cpu_cost, plan_handle, execution_count,
			(SELECT SUBSTRING(text, statement_start_offset/2+1,
				(CASE WHEN statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max), text)) * 2
				ELSE statement_end_offset END - statement_end_offset)/2)
			FROM sys.dm_exec_sql_text(sql_handle)) AS query_text
FROM sys.dm_exec_query_stats
ORDER BY avg_cpu_cost DESC

稍做修改，找出运行最频繁的查询

SELECT TOP 100 total_worker_time/execution_count AS avg_cpu_cost, plan_handle, execution_count,
			(SELECT SUBSTRING(text, statement_start_offset/2+1,
				(CASE WHEN statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max), text)) * 2
				ELSE statement_end_offset END - statement_end_offset)/2)
			FROM sys.dm_exec_sql_text(sql_handle)) AS query_text
FROM sys.dm_exec_query_stats
ORDER BY execution_count DESC

 

可以使用下列系统监视性能计数器查看编译和重编译的速度：

1. SQLServer: SQL Statistics: Batch Requests/Sec（每秒批处理请求数）
2. SQLServer: SQL Statistics: SQL Compilations/Sec（每秒SQL编译次数）
3. SQLServer: SQL Statistics: SQL Recompilations/Sec（每秒SQL重编译次数）

还可以通过下面语句得到SQL Server在优化查询计划上花费的时间：

SELECT * FROM sys.dm_exec_query_optimizer_info 
WHERE counter='optimizations' OR counter = 'elapsed time'

 

下面查询找到被编译得最多的前10位查询计划

SELECT TOP 10 plan_generation_num, execution_count,
			(SELECT SUBSTRING(text, statement_start_offset/2+1,
				(CASE WHEN statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max), text)) * 2
				ELSE statement_end_offset END - statement_end_offset)/2)
			FROM sys.dm_exec_sql_text(sql_handle)) AS query_text
FROM sys.dm_exec_query_stats
WHERE plan_generation_num > 1
ORDER BY plan_generation_num DESC