CPU相关的SQL

z

1. 查看是否有进程在等待CPU

SELECT scheduler_id, current_tasks_count, runnable_tasks_count   FROM sys.dm_os_schedulers    WHERE scheduler_id < 255 

如果 runnable_tasks_count>0 则可能存在CPU瓶颈

2. 占CPU时间最多的SQL

SELECT SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,((CASEqs.statement_end_offsetWHEN-1THENDATALENGTH(qt.TEXT)ELSEqs.statement_end_offset
END - qs.statement_start_offset)/2)+1),
      qs.execution_count,qs.total_logical_reads,qs.last_logical_reads,
      qs.total_logical_writes,qs.last_logical_writes,qs.total_worker_time,
      qs.last_worker_time, qs.total_elapsed_time/1000000 total_elapsed_time_in_S,
      qs.last_elapsed_time/1000000 last_elapsed_time_in_S,qs.last_execution_time,
      qp.query_plan
FROM sys.dm_exec_query_stats qs
     CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle)qt
     CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle)qp
ORDER BY qs.total_worker_time DESC -- CPU time

3. 当前缓存的哪些批处理或过程占用了大部分 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

4. 缓存计划所占用的 CPU 总使用率

SELECT total_cpu_time, total_execution_count, number_of_statements, s2.text
      --(SELECT SUBSTRING(s2.text, statement_start_offset / 2, ((CASE WHEN statement_end_offset = -1 THEN (LEN(CONVERT(NVARCHAR(MAX), s2.text)) * 2) ELSE statement_end_offset END) - statement_start_offset) / 2) ) AS query_text
FROM (  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 --,
                                             --MIN(statement_start_offset) AS statement_start_offset,
                                             --MAX(statement_end_offset) AS statement_end_offset
                  FROM sys.dm_exec_query_stats AS qs
                 GROUP BY qs.sql_handle
                 ORDER BY SUM(qs.total_worker_time) DESC
      ) AS stats
      CROSS APPLY sys.dm_exec_sql_text(stats.sql_handle) AS s2

5. 显示 CPU 平均占用率最高的前 50 个 SQL 语句

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

如果系统存在过多的编译和重新编译，可能会导致系统出现与 CPU 相关的性能问题。

6. 过多编译/重新编译的

select * from sys.dm_exec_query_optimizer_info where counter = 'optimizations' or counter = 'elapsed time'

7.已重新编译的前 25 个存储过程

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

8. 哪个查询占用了最多的 CPU 累计使用率

SELECT highest_cpu_queries.plan_handle, highest_cpu_queries.total_worker_time, q.dbid, q.objectid, q.number, q.encrypted, q.[text]
from ( select top 50 qs.plan_handle, qs.total_worker_time from sys.dm_exec_query_stats qs order by qs.total_worker_time desc) as highest_cpu_queries
     cross apply sys.dm_exec_sql_text(plan_handle) as q
order by highest_cpu_queries.total_worker_time desc

9. 可能占用大量 CPU 使用率的运算符

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%'