如何排查历史时段导致SQL Server CPU占用100%的查询及用户

方案1：查询查询存储（Query Store）的历史数据 #

SQL Server 2016默认开启查询存储功能，会留存长期的查询性能历史数据，是事后排查的首选方案，可直接拉取故障时间窗口的高CPU消耗查询：

SELECT 
    qt.query_sql_text,
    q.query_id,
    rs.avg_cpu_time,
    rs.max_cpu_time,
    rs.total_cpu_time,
    rs.count_executions,
    p.plan_id,
    rs.runtime_stats_start_time
FROM 
    sys.query_store_runtime_stats rs
JOIN 
    sys.query_store_plan p ON rs.plan_id = p.plan_id
JOIN 
    sys.query_store_query q ON p.query_id = q.query_id
JOIN 
    sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
WHERE 
    rs.runtime_stats_start_time BETWEEN '202X-XX-XX 06:20:00' AND '202X-XX-XX 06:40:00' -- 替换为故障当天的对应日期
ORDER BY 
    rs.total_cpu_time DESC;

查询结果按总CPU消耗倒序排列，排在前列的即为故障诱因，同时可通过执行次数、平均/最大CPU耗时判断是单次慢查询还是高频小查询累计占满CPU。

方案2：查询计划缓存的历史执行统计 #

如果手动关闭了查询存储，可查询系统动态管理视图的计划缓存数据，注意如果故障后SQL Server重启过、或者计划被缓存淘汰，数据可能丢失：

SELECT 
    SUBSTRING(qt.text, (qs.statement_start_offset/2)+1, 
    ((CASE qs.statement_end_offset
        WHEN -1 THEN DATALENGTH(qt.text)
        ELSE qs.statement_end_offset
     END - qs.statement_start_offset)/2)+1) AS query_text,
    qs.total_worker_time/1000 AS total_cpu_time_ms,
    qs.execution_count,
    qs.total_worker_time/(1000*qs.execution_count) AS avg_cpu_time_ms,
    qs.max_worker_time/1000 AS max_cpu_time_ms,
    DB_NAME(qt.dbid) AS database_name,
    SUSER_SNAME(qt.objectid) AS execute_user_name
FROM 
    sys.dm_exec_query_stats qs
CROSS APPLY 
    sys.dm_exec_sql_text(qs.sql_handle) qt
WHERE 
    qs.creation_time BETWEEN '202X-XX-XX 00:00:00' AND '202X-XX-XX 06:40:00' -- 替换为故障当天日期
ORDER BY 
    qs.total_worker_time DESC;

方案3：排查作业与系统日志 #

如果以上两个方案未查到结果，可通过以下路径排查非用户查询类的诱因：

• 查看Windows性能监视器的历史记录，确认06:30的高CPU确实是SQL Server进程占用，排除其他系统进程干扰
• 查看SQL Server代理的作业历史，确认故障时间点是否有定时作业触发，索引重建、统计信息更新、批量数据同步类的维护任务经常会短时间打满CPU
• 查看SQL Server错误日志，排查故障时间点是否有大量死锁、连接风暴、任务堆积的异常记录

排查优先级建议先查查询存储，SQL Server 2016默认查询存储数据留存期为30天，只要未手动修改配置，90%以上的场景都可以直接定位到具体查询。

内容的提问来源于stack exchange，提问作者ice7101