SQL Server CPU飙升排查：从定位高耗查询到优化执行计划

在实际数据库运维和性能调优工作中，最令人头疼的场景之一就是：一条昨天还运行良好的 SQL 语句，今天突然变得异常缓慢，并且直接导致数据库服务器的 CPU 使用率飙升到 90% 以上。这种问题往往发生在业务高峰期，影响范围广，排查压力大。它不仅考验 DBA 或开发人员对数据库内部机制的理解，更考验一套系统化、高效的排查方法论。

本文将以一个资深数据库工程师的视角，带你走一遍完整的线上 SQL 性能突降排查流程。我们将从确认问题、定位元凶、分析根因到最终解决，覆盖从操作系统层到 SQL 语句层的全链路分析。无论你使用的是 SQL Server、MySQL 还是 Oracle，其核心排查思路是相通的。通过本文，你将掌握一套可复现、可操作的排查清单，下次再遇到类似问题，就能做到心中有数，快速响应。

1. 确认问题：真的是 SQL Server 导致的 CPU 飙升吗？

当监控告警显示数据库服务器 CPU 使用率超过 90% 时，第一步不是立刻去查 SQL，而是先确认 CPU 负载的来源。服务器上可能运行着其他进程，如防病毒软件、备份程序或其他应用服务。

1.1 使用任务管理器或性能监视器初步判断

在 Windows 服务器上，最直接的方法是打开任务管理器，切换到“进程”选项卡，按 CPU 使用率排序。观察 sqlservr.exe 进程的 CPU 占用是否持续高位（例如，持续超过 70%）。

更专业的做法是使用性能监视器（Perfmon）：

1. 运行 perfmon 打开性能监视器。
2. 添加计数器： Process -> % User Time 和 Process -> % Privileged Time ，实例选择 sqlservr 。
3. 观察 % User Time 。如果该值持续接近 100% * (CPU 核心数)，则基本可以确定是 SQL Server 的用户态代码（即你的查询）导致了高 CPU。如果 % Privileged Time 很高，则可能是驱动程序、杀毒软件或其他操作系统组件的问题。

你也可以通过 PowerShell 脚本快速收集一段时间的数据：

$serverName = $env:COMPUTERNAME
$Counters = @(
    ("\\$serverName" + "\Process(sqlservr*)\% User Time"),
    ("\\$serverName" + "\Process(sqlservr*)\% Privileged Time")
)
Get-Counter -Counter $Counters -MaxSamples 30 | ForEach {
    $_.CounterSamples | ForEach {
        [pscustomobject]@{
            TimeStamp = $_.TimeStamp
            Path = $_.Path
            Value = ([Math]::Round($_.CookedValue, 3))
        }
    }
    Start-Sleep -s 2
}

1.2 使用 SQL Server Management Studio (SSMS) 内置报表

在 SSMS 中，右键点击目标实例，选择“报表” -> “标准报表” -> “性能仪表板”。仪表板上的“系统 CPU 使用率”图表会清晰地区分 SQL Server 进程（深色部分）和系统其他进程（浅色部分）的 CPU 占用情况。这是一个非常直观的判断工具。

注意 ：不要仅凭瞬间的 CPU 峰值就下结论。需要观察一个持续的时间段（例如 1-5 分钟），确认高 CPU 是 SQL Server 进程的常态行为。

2. 定位罪魁祸首：找出消耗 CPU 最高的查询

确认是 SQL Server 的问题后，下一步就是找出具体是哪些查询在“吃”CPU。SQL Server 提供了丰富的动态管理视图（DMV）来帮助我们。

2.1 查看当前正在执行的、高 CPU 消耗的查询

以下查询可以列出当前正在执行且消耗 CPU 最高的会话和请求，并显示其正在执行的 SQL 语句片段。

SELECT TOP 10
    s.session_id,
    r.status,
    r.cpu_time AS [CPU Time (ms)],
    r.logical_reads,
    r.reads,
    r.writes,
    r.total_elapsed_time / (1000 * 60) AS [Elapsed Time (Min)],
    SUBSTRING(st.TEXT, (r.statement_start_offset / 2) + 1,
        ((CASE r.statement_end_offset
            WHEN -1 THEN DATALENGTH(st.TEXT)
            ELSE r.statement_end_offset
        END - r.statement_start_offset) / 2) + 1) AS [Executing Statement],
    COALESCE(QUOTENAME(DB_NAME(st.dbid)) + N'.' + 
             QUOTENAME(OBJECT_SCHEMA_NAME(st.objectid, st.dbid)) + N'.' + 
             QUOTENAME(OBJECT_NAME(st.objectid, st.dbid)), '') AS [Object],
    r.command,
    s.login_name,
    s.host_name,
    s.program_name,
    s.last_request_end_time,
    s.login_time,
    r.open_transaction_count
FROM sys.dm_exec_sessions AS s
JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
WHERE r.session_id != @@SPID -- 排除当前查询自身
  AND r.status = 'running' -- 只查看正在运行的
ORDER BY r.cpu_time DESC;

关键字段解释 ：

• cpu_time ：该请求已消耗的 CPU 时间（毫秒），是定位高 CPU 查询的核心指标。
• logical_reads ：逻辑读取次数，高通常意味着大量数据扫描或缺失索引。
• Executing Statement ：当前正在执行的具体 SQL 语句文本。
• Object ：语句所属的数据库对象（库.架构.表）。

2.2 查看历史累计高 CPU 消耗的查询

如果问题查询已经执行完毕，或者你想找出长期消耗 CPU 资源最多的“惯犯”，可以查询计划缓存。

SELECT TOP 10
    qs.last_execution_time AS [Last Execution Time],
    st.text AS [Batch Text],
    SUBSTRING(st.TEXT, (qs.statement_start_offset / 2) + 1,
        ((CASE qs.statement_end_offset
            WHEN -1 THEN DATALENGTH(st.TEXT)
            ELSE qs.statement_end_offset
        END - qs.statement_start_offset) / 2) + 1) AS [Statement Text],
    (qs.total_worker_time / 1000) / qs.execution_count AS [Avg CPU Time (ms)],
    (qs.total_elapsed_time / 1000) / qs.execution_count AS [Avg Elapsed Time (ms)],
    qs.total_logical_reads / qs.execution_count AS [Avg Logical Reads],
    (qs.total_worker_time / 1000) AS [Cumulative CPU Time (ms)],
    (qs.total_elapsed_time / 1000) AS [Cumulative Elapsed Time (ms)],
    qs.execution_count AS [Execution Count]
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY (qs.total_worker_time / qs.execution_count) DESC; -- 按平均CPU时间排序
-- 也可以按 ORDER BY qs.total_worker_time DESC 查看总CPU消耗最高的查询

这个查询结果非常宝贵，它能告诉你：

1. 哪条 SQL 平均每次执行最耗 CPU （ Avg CPU Time ）：这可能就是今天突然变慢的元凶。
2. 哪条 SQL 总消耗 CPU 最多 （ Cumulative CPU Time ）：这可能是系统长期的性能热点。
3. 执行频率 （ Execution Count ）：结合平均消耗，判断是单次查询变慢还是大量并发执行导致。

3. 分析根因：为什么这条 SQL 今天突然变慢了？

找到消耗 CPU 最高的 SQL 后，我们需要像侦探一样分析其执行计划，找出性能突降的根本原因。以下是几种最常见的情况及排查方法。

3.1 原因一：统计信息过时或缺失

这是导致“昨天快今天慢”的最常见原因。SQL Server 的查询优化器依赖统计信息来估算数据分布和行数，从而生成高效的执行计划。如果表的数据发生了大量增删改（例如，夜间批量作业），而统计信息没有及时更新，优化器可能会基于错误的信息选择一个非常低效的计划（例如，本应使用索引查找却选择了全表扫描）。

如何检查与修复？

1. 更新统计信息 ：对问题 SQL 涉及的表，手动更新统计信息。

   -- 更新单个表的统计信息
   UPDATE STATISTICS [YourTableName] WITH FULLSCAN;
   -- 更新当前数据库所有用户表的统计信息
   EXEC sp_updatestats;
   

   警告 ：在生产环境高峰期，对大型表执行 WITH FULLSCAN 可能会消耗大量 I/O 资源。可以考虑使用 WITH SAMPLE 或安排在低峰期进行。

2. 检查统计信息最后更新时间 ：

   SELECT 
       OBJECT_NAME(object_id) AS TableName,
       name AS StatsName,
       STATS_DATE(object_id, stats_id) AS LastUpdated,
       rows_sampled,
       rows
   FROM sys.stats
   WHERE object_id = OBJECT_ID('YourTableName')
   ORDER BY LastUpdated;
   

   如果 LastUpdated 远早于数据发生重大变化的时间，那么统计信息过时的可能性就很大。

3.2 原因二：参数嗅探（Parameter Sniffing）

参数嗅探是 SQL Server 的一个特性，优化器在第一次编译存储过程或参数化查询时，会“嗅探”传入的参数值，并基于该值生成一个“认为最优”的执行计划，然后将其缓存。问题在于，如果后续传入的参数值分布差异极大（例如，第一次传入 @UserId = 1 （返回1行），后续传入 @UserId = NULL （返回100万行）），缓存的计划对新的参数值可能就是灾难性的。

如何识别参数嗅探？ 一个典型的迹象是：同一条带参数的查询，有时快有时慢，清空计划缓存（ DBCC FREEPROCCACHE ）后可能暂时变好。

临时验证与解决方案 ：

1. 临时清空特定查询的计划缓存 ：首先，你需要找到问题查询的 plan_handle 。

   -- 查找包含特定文本的查询的计划句柄
   SELECT text, plan_handle, 'DBCC FREEPROCCACHE (0x' + CONVERT(VARCHAR(512), plan_handle, 2) + ')' AS dbcc_command
   FROM sys.dm_exec_cached_plans
   CROSS APPLY sys.dm_exec_sql_text(plan_handle)
   WHERE text LIKE '%YourProblematicQueryText%';
   

   然后执行输出的 DBCC FREEPROCCACHE 命令。如果执行后查询立即恢复正常，但过一段时间（计划被重新编译后）又变慢，那么参数嗅探的可能性极高。

2. 解决方案 ：

   • 使用 OPTION (RECOMPILE) 查询提示 ：强制语句每次执行都重新编译，获得针对当前参数的最优计划。适用于执行不频繁但要求高的查询。

     CREATE PROCEDURE MyProc @Param INT
     AS
     BEGIN
         SELECT * FROM BigTable WHERE Column = @Param
         OPTION (RECOMPILE); -- 每次执行都重编译
     END
     

   • 使用 OPTION (OPTIMIZE FOR (@Param = TypicalValue)) ：告诉优化器针对一个“典型”的参数值来生成计划。适用于参数值分布相对均匀的场景。
   • 使用 OPTION (OPTIMIZE FOR UNKNOWN) ：让优化器使用平均密度来生成计划，避免对特定参数值过度优化。
   • 禁用参数嗅探（谨慎使用） ：使用 OPTION (USE HINT ('DISABLE_PARAMETER_SNIFFING')) 。这通常是最后的手段，因为它可能对整体性能产生负面影响。

3.3 原因三：缺失索引

缺失索引会导致查询进行全表扫描或索引扫描，而不是高效的索引查找，从而消耗大量 CPU 和 I/O。

如何查找缺失索引？ SQL Server 会自动记录它认为可能有益的缺失索引建议。可以通过以下 DMV 查询：

SELECT TOP 10
    CONVERT(DECIMAL(28,1), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) AS improvement_measure,
    'CREATE INDEX missing_index_' + CONVERT(VARCHAR, mig.index_group_handle) + '_' + CONVERT(VARCHAR, mid.index_handle)
    + ' ON ' + mid.statement
    + ' (' + ISNULL(mid.equality_columns, '')
    + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ','
           ELSE ''
      END
    + ISNULL(mid.inequality_columns, '')
    + ')'
    + ISNULL(' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement,
    migs.*,
    mid.database_id,
    mid.object_id
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE CONVERT(DECIMAL(28,1), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) > 10 -- 设置一个改进度量阈值
ORDER BY improvement_measure DESC;

解读与行动 ：

• improvement_measure 是一个估算的收益值，越高表示创建该索引的潜在收益越大。
• create_index_statement 是 SQL Server 建议的创建索引语句。
• 不要盲目创建所有建议的索引 ！索引本身也有维护开销（写操作变慢）。需要结合 user_seeks （查找次数）、 avg_user_impact （影响度）以及你的业务逻辑来判断。优先为 improvement_measure 最高的前几条建议创建索引，并在测试环境验证效果。

3.4 原因四：非 SARGable 查询

SARGable（Search Argument Able）指的是查询条件能够有效地利用索引。如果查询的 WHERE 子句中对列进行了函数操作、计算或类型转换，就会导致索引失效，引发全表扫描。

常见非 SARGable 写法示例 ：

-- 示例1：对列使用函数
SELECT * FROM Orders WHERE YEAR(OrderDate) = 2023 AND MONTH(OrderDate) = 10;
-- 示例2：对列进行计算
SELECT * FROM Products WHERE UnitPrice * 0.9 > 100;
-- 示例3：隐式类型转换（假设 ProductID 是 VARCHAR，但传入 INT）
SELECT * FROM Products WHERE ProductID = 12345; -- 数据库可能将 ProductID 转换为 INT 再比较
-- 示例4：使用 LIKE 通配符开头
SELECT * FROM Customers WHERE Name LIKE '%Smith%';

优化为 SARGable 写法 ：

-- 优化示例1：避免对列使用函数，改为范围查询
SELECT * FROM Orders WHERE OrderDate >= '2023-10-01' AND OrderDate < '2023-11-01';
-- 优化示例2：将计算移到运算符另一边
SELECT * FROM Products WHERE UnitPrice > 100 / 0.9;
-- 优化示例3：确保比较双方类型一致
SELECT * FROM Products WHERE ProductID = '12345';
-- 优化示例4：如果必须前缀模糊，考虑全文索引；否则尽量使用后缀模糊
SELECT * FROM Customers WHERE Name LIKE 'Smith%';

3.5 原因五：阻塞与锁竞争

虽然高 CPU 通常直接指向计算密集型操作，但严重的阻塞（Blocking）可能导致大量会话处于等待状态，不断重试或执行轮询逻辑，间接推高 CPU 使用率。同时，一些自旋锁（Spinlock）争用也会直接表现为高 CPU。

检查当前阻塞链 ：

-- 查询当前阻塞情况
SELECT
    blocking.session_id AS blocking_session_id,
    blocked.session_id AS blocked_session_id,
    waitstats.wait_type AS blocking_wait_type,
    waitstats.wait_duration_ms,
    blocking.text AS blocking_text,
    blocked.text AS blocked_text
FROM sys.dm_exec_requests blocked
INNER JOIN sys.dm_exec_requests blocking ON blocked.blocking_session_id = blocking.session_id
CROSS APPLY sys.dm_exec_sql_text(blocked.sql_handle) blocked
CROSS APPLY sys.dm_exec_sql_text(blocking.sql_handle) blocking
OUTER APPLY sys.dm_os_waiting_tasks waitstats ON waitstats.session_id = blocked.session_id
WHERE blocked.blocking_session_id > 0;

如果发现长时间阻塞，需要分析阻塞会话正在执行的操作（ blocking_text ），可能是长时间运行的事务、缺失索引的更新操作或设计不佳的并发逻辑。

4. 系统性排查清单与进阶工具

除了上述针对 SQL 语句的分析，还需要从更系统的层面进行检查。

4.1 检查外部因素

1. 资源争用 ：检查同一服务器上是否有其他进程（如备份、ETL、报表服务）在同一时间消耗大量 CPU、内存或磁盘 I/O。
2. 虚拟机配置 ：如果 SQL Server 运行在虚拟机上，检查是否被过度分配了 vCPU，或者宿主机是否存在资源争用。确保为虚拟机预留了足够的 CPU 资源。
3. 电源计划 ：在 Windows Server 上，确保电源选项设置为“高性能”。“平衡”模式可能会限制 CPU 频率以节省能耗，导致性能下降。
4. 跟踪与审计 ：检查是否启用了 SQL 跟踪（SQL Trace）或扩展事件（Extended Events）会话，特别是那些捕获了大量事件（如 sql_statement_completed ）的会话。它们会带来不小的开销。使用以下查询检查：

   -- 检查正在运行的扩展事件会话
   SELECT s.name, s.total_buffer_size, s.total_events_fired
   FROM sys.dm_xe_sessions s
   WHERE s.name IS NOT NULL;
   

4.2 使用执行计划进行深度分析

对于找到的高 CPU 查询，获取其实际执行计划是诊断的黄金标准。在 SSMS 中，可以在查询前加上 SET STATISTICS PROFILE ON 或使用“包括实际执行计划”按钮。

在执行计划中重点关注 ：

• 高成本操作 ：查看图形化执行计划中成本占比最高的运算符（通常颜色最深）。
• 扫描（Scan） vs 查找（Seek） ：对大型表进行 Clustered Index Scan 或 Table Scan 通常是性能杀手，应尝试优化为 Index Seek 。
• 预估行数与实际行数 ：如果两者差异巨大（例如，预估 10 行，实际 100 万行），这强烈暗示统计信息有问题或参数嗅探，导致优化器选择了错误的计划。
• 警告符号 ：执行计划中的黄色感叹号会提示缺失索引、隐式类型转换等关键问题。

4.3 性能监控与基线对比

建立性能基线至关重要。如果昨天 SQL 跑 50 毫秒，今天跑 5 秒，你需要知道昨天和今天的系统状态有何不同。

• 关键性能计数器（Perfmon） ：持续监控 SQLServer:SQL Statistics - Batch Requests/sec , SQLServer:SQL Statistics - SQL Compilations/sec , SQLServer:Buffer Manager - Page life expectancy 等。
• 查询存储（Query Store） ：如果你使用的是 SQL Server 2016 或更高版本，务必启用 Query Store。它能自动捕获查询性能历史、执行计划和运行时统计信息。你可以轻松对比同一个查询在不同时间点的性能差异。

  -- 启用 Query Store
  ALTER DATABASE [YourDatabase] SET QUERY_STORE = ON;
  -- 配置 Query Store（建议）
  ALTER DATABASE [YourDatabase] SET QUERY_STORE (
      OPERATION_MODE = READ_WRITE,
      CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),
      DATA_FLUSH_INTERVAL_SECONDS = 900,
      INTERVAL_LENGTH_MINUTES = 60,
      MAX_STORAGE_SIZE_MB = 1024
  );
  

5. 总结与最佳实践

面对线上 SQL 突然变慢导致 CPU 飙升的问题，遵循一套清晰的排查路径可以极大提高效率。以下是一个快速行动清单：

预防性最佳实践 ：

1. 建立监控与告警 ：对关键数据库的 CPU 使用率、慢查询、锁等待等指标设置监控和告警。
2. 定期更新统计信息 ：对于数据变化频繁的表，设置定期的统计信息更新作业，而非依赖自动更新。
3. 使用 Query Store ：在 SQL Server 2016+ 中启用并合理配置 Query Store，它是进行性能回归分析和历史对比的利器。
4. 代码审查 ：在开发阶段，对 SQL 代码进行审查，避免非 SARGable 写法、不必要的函数调用和隐式类型转换。
5. 压力测试与基线建立 ：在上线前，对核心业务 SQL 进行压力测试，并记录其性能基线（执行时间、资源消耗），以便上线后对比。
6. 谨慎使用计划指南和提示 ：对于已知的参数嗅探问题，可以考虑使用计划指南（Plan Guide）来固定一个良好的执行计划，但这需要持续维护。

记住，数据库性能调优是一个持续的过程，而非一劳永逸。掌握这套从现象到根因的排查方法论，结合扎实的数据库原理知识，你就能在关键时刻稳住阵脚，快速恢复业务。