对于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
可以使用下列系统监视性能计数器查看编译和重编译的速度:
- SQLServer: SQL Statistics: Batch Requests/Sec(每秒批处理请求数)
- SQLServer: SQL Statistics: SQL Compilations/Sec(每秒SQL编译次数)
- 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