1、检查SQL阻塞原因
select blocking_session_id, wait_duration_ms, session_id
from sys.dm_os_waiting_tasks
where blocking_session_id is not null
2、检查前10个等待资源的SQL语句
select top 10 *
from sys.dm_os_wait_stats
order by wait_time_ms desc
3、查询显示 CPU 平均占用率最高的前50个SQL语句
total_worker_time指的是微秒，1秒=1000000微秒
select top 50 total_worker_time/execution_count as [Avg CPU Time],
(select substring(text,statement_start_offset/2,(case when
statement_end_offset = -1 then len(convert(nvarchar(max), text)) * 2
else statement_end_offset end -statement_start_offset)/2) from sys.dm_exec_sql_text(sql_handle)) as query_text, *
from sys.dm_exec_query_stats
order by [Avg CPU Time] desc
4、CPU 瓶颈通常由以下原因引起：查询计划并非最优、配置不当、设计因素不良或硬件资源不足。
下面的常用查询可帮助您确定导致CPU瓶颈的原因。
下面的查询使您能够深入了解当前缓存的哪些批处理或过程占用了大部分CPU资源。
select top 50
sum(qs.total_worker_time) as total_cpu_time,
sum(qs.execution_count) as total_execution_count,
count(*) as number_of_statements, qs.sql_handle
from sys.dm_exec_query_stats as qs
group by qs.sql_handle
order by sum(qs.total_worker_time) desc
5、下面的查询显示缓存计划所占用的CPU总使用率(带SQL文本)。
select total_cpu_time, total_execution_count, number_of_statements, qsC.text
from (
select top 50
sum(qsA.total_worker_time) as total_cpu_time,
sum(qsA.execution_count) as total_execution_count,
count(*) as number_of_statements, qsA.sql_handle
from sys.dm_exec_query_stats as qsA
group by qsA.sql_handle
order by sum(qsA.total_worker_time) desc
) as qsB
cross apply sys.dm_exec_sql_text(qsB.sql_handle) as qsC
6、下面的示例查询显示已重新编译的前 25 个存储过程。plan_generation_num 指示该查询已重新编译的次数。
select top 25 sql_text.text, sql_handle, plan_generation_num, execution_count,
dbid, objectid
from sys.dm_exec_query_stats a
cross apply sys.dm_exec_sql_text(sql_handle) as sql_text
where plan_generation_num > 1
order by plan_generation_num desc
7、效率较低的查询计划可能增大 CPU 占用率。
下面的查询显示哪个查询占用了最多的 CPU 累计使用率。
select highest_cpu_queries.plan_handle, highest_cpu_queries.total_worker_time,
qrB.dbid, qrB.objectid, qrB.number, qrB.encrypted, qrB.[text]
from
(
select top 50 qsA.plan_handle,
qsA.total_worker_time from sys.dm_exec_query_stats qsA
order by qsA.total_worker_time desc
) as highest_cpu_queries
cross apply sys.dm_exec_sql_text(plan_handle) as qrB
order by highest_cpu_queries.total_worker_time desc
8、下面的查询显示一些可能占用大量 CPU 使用率的运算符(例如’%Hash Match%’、’%Sort%’)以找出可疑对象。
select * from
sys.dm_exec_cached_plans
cross apply sys.dm_exec_query_plan(plan_handle)
where cast(query_plan as nvarchar(max)) like ‘%Sort%’
or cast(query_plan as nvarchar(max)) like ‘%Hash Match%’
9、如果已检测到效率低下并导致 CPU 占用率较高的查询计划，请对该查询中涉及的表运行 UPDATE STATISTICS
以查看该问题是否仍然存在。
select * from sys.dm_exec_query_optimizer_info
where counter = ‘optimizations’ or counter = ‘elapsed time’