昨天跑 50 毫秒的 SQL今天突然飙到 5 秒数据库 CPU 直接冲到 90%。这不是一个假设性问题而是很多 DBA 和开发者在生产环境里真实踩过的坑。问题出现时业务方在催监控在报警而你手头只有一堆零散的线索一条 SQL、一个时间点、一个飙升的 CPU 指标。很多人第一反应是“加索引”或者“优化 SQL”但真正的问题往往藏在更深的地方。一次性能的断崖式下跌很少是单一原因造成的它更像是一个信号告诉你系统里某个长期存在的隐患被触发了。今天我们就来拆解这个经典面试题背后的完整排查链路它不仅是面试技巧更是一套能在关键时刻救场的实战方法。1. 先别急着优化 SQL确认问题边界和影响范围当 CPU 飙到 90%你的第一反应不应该是立刻打开 SQL 优化工具。在动手之前必须先搞清楚三件事问题是不是 SQL Server 引起的影响面有多大是持续性的还是间歇性的盲目优化可能让你在错误的方向上浪费大量时间。1.1 确认 CPU 高负载的“元凶”在 Windows 环境下打开任务管理器查看sqlservr.exe进程的 CPU 占用率。如果它持续接近 100%那基本可以确定问题出在数据库引擎内部。但如果sqlservr.exe的 CPU 并不高而是系统整体的 CPU 很高那可能是防病毒软件、其他驱动或操作系统组件的问题需要联系系统管理员一起排查。一个更精确的方法是使用性能计数器。你可以通过 PowerShell 脚本定期采集Process(sqlservr*)\% User Time和% Privileged Time的数据。如果% User Time持续高于 90%基本可以锁定是 SQL Server 的用户态代码也就是你的查询导致了高 CPU。如果% Privileged Time很高则可能是系统调用或驱动问题。$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 } }在 SQL Server Management Studio (SSMS) 中你也可以使用标准报表中的“性能仪表板”。深色部分代表 SQL Server 进程的 CPU 使用率浅色部分代表整个系统的 CPU 使用率。这是一个快速可视化的方法。1.2 量化 SQL 查询的 CPU 贡献度确定了是 SQL Server 的问题后下一步是量化当前所有正在执行的查询总共占用了多少 CPU 资源这能帮你判断是少数几个“坏查询”作祟还是大量并发查询的累积效应。DECLARE init_sum_cpu_time int, utilizedCpuCount int -- 获取 SQL Server 使用的 CPU 核心数 SELECT utilizedCpuCount COUNT( * ) FROM sys.dm_os_schedulers WHERE status VISIBLE ONLINE -- 计算过去 5 秒内查询消耗的 CPU 占总容量的百分比 SELECT init_sum_cpu_time SUM(cpu_time) FROM sys.dm_exec_requests WAITFOR DELAY 00:00:05 SELECT CONVERT(DECIMAL(5,2), ((SUM(cpu_time) - init_sum_cpu_time) / (utilizedCpuCount * 5000.00)) * 100 ) AS [CPU from Queries as Percent of Total CPU Capacity] FROM sys.dm_exec_requests如果这个百分比很高比如超过 70%说明当前活跃查询就是罪魁祸首。如果百分比很低但sqlservr.exe的 CPU 依然很高那就要怀疑是不是后台任务如统计信息更新、索引重建、锁等待或 SQL Server 内部组件如锁管理器、任务调度器出现了问题。1.3 建立问题的时间线是突然发生还是缓慢恶化询问业务方或查看监控确定性能下降是精确发生在某个时间点还是一段时间内逐渐变慢。突然发生通常与数据/结构变更相关。例如统计信息更新、索引被意外删除或禁用、数据量突变如凌晨的批量导入、应用程序发布了新版本并带来了新的低效查询。缓慢恶化通常与数据增长、资源竞争或计划缓存恶化相关。例如表数据持续增长导致原有执行计划不再高效参数嗅探Parameter Sniffing问题随着数据分布变化而凸显。这个判断能极大缩小你的排查范围。如果是“昨天50ms今天5s”这种突变重点就应该放在变更上。2. 定位罪魁祸首找出正在消耗 CPU 的查询确认了边界接下来就是抓“现行犯”。我们的目标是找到那些正在执行或刚刚执行完的、消耗大量 CPU 的查询。2.1 抓取当前正在运行的高 CPU 查询使用sys.dm_exec_requests和sys.dm_exec_sessions这两个动态管理视图 (DMV)可以实时看到每个会话正在执行的请求及其资源消耗。SELECT TOP 10 s.session_id, r.status, r.cpu_time, r.logical_reads, r.reads, r.writes, r.total_elapsed_time / (1000 * 60) AS Elaps M, 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 statement_text, COALESCE(QUOTENAME(DB_NAME(st.dbid)) N. QUOTENAME(OBJECT_SCHEMA_NAME(st.objectid, st.dbid)) N. QUOTENAME(OBJECT_NAME(st.objectid, st.dbid)), ) AS command_text, 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 ORDER BY r.cpu_time DESC这个查询结果非常关键cpu_time: 该请求已消耗的 CPU 时间毫秒。这是排序的核心依据。statement_text: 当前正在执行的 SQL 语句片段。command_text: 所属的数据库和对象如果可解析。login_name,host_name,program_name: 谁、从哪里、用什么工具执行的。这能帮你判断是来自应用服务器、报表工具还是人为的即席查询。status: 查询状态如running,suspended。如果status是suspended但cpu_time很高说明它之前已经消耗了大量 CPU现在可能在等待资源如 I/O。注意如果当前没有高 CPU 的活跃查询问题可能是间歇性的或者罪魁祸首已经执行完毕。这时就需要查询历史执行记录。2.2 查询历史高 CPU 查询计划缓存Plan Cache中存储了之前执行过的查询及其统计信息。通过查询sys.dm_exec_query_stats我们可以找到历史上消耗 CPU 最多的查询。SELECT TOP 10 qs.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_all_executions_ms, (qs.total_elapsed_time / 1000) AS cumulative_elapsed_time_all_executions_ms FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(sql_handle) st ORDER BY (qs.total_worker_time / qs.execution_count) DESC这里重点关注avg_cpu_time_ms平均每次执行消耗的 CPU 时间和execution_count执行次数。一个平均 CPU 很高但执行次数少的查询可能是今天新出现的“坏查询”。一个平均 CPU 不高但执行次数极高的查询可能是由于并发量突增导致的累积效应。找到目标 SQL 后立即保存其完整的 SQL 文本、执行计划如果可能以及相关的sql_handle或plan_handle。这是后续分析的基石。3. 深度分析为什么同一条 SQL 今天变慢了找到了消耗 CPU 的 SQL这只是第一步。核心问题是为什么昨天快今天慢执行计划变了。99% 的此类性能突变根源都在于执行计划Execution Plan的变更。我们需要像一个侦探一样检查执行计划的“健康状态”。3.1 首要检查统计信息是否过时统计信息是查询优化器Query Optimizer为表数据构建的“数据画像”包括行数、唯一值数量、数据分布等。如果这个画像过时了优化器就会基于错误的信息制定一个低效的执行计划。如何检查直接更新对查询涉及的表执行UPDATE STATISTICS。最粗暴但有效的方法是更新整个数据库的统计信息EXEC sp_updatestats注意sp_updatestats会对所有用户表运行UPDATE STATISTICS。在生产环境这可能会消耗大量 I/O 和 CPU并阻塞查询。建议在业务低峰期进行或针对特定表更新。查看统计信息最后更新时间SELECT OBJECT_NAME(s.object_id) AS TableName, s.name AS StatsName, STATS_DATE(s.object_id, s.stats_id) AS LastUpdated, s.auto_created, s.user_created FROM sys.stats s WHERE OBJECT_NAME(s.object_id) IN (YourTableName) -- 替换为你的表名 ORDER BY LastUpdated;如果LastUpdated远早于数据发生重大变化的时间例如大批量增删改那么统计信息很可能已经过时。为什么统计信息过时会导致计划变差假设你的查询条件是WHERE Status Active昨天有 1000 行是Active优化器可能选择索引查找。今天经过批量更新有 100 万行变成了Active但统计信息没更新优化器仍然认为只有 1000 行可能还是选择索引查找实际需要回表 100 万次而不是更高效的表扫描或批处理模式。3.2 检查缺失索引缺失索引是导致表/索引扫描Scan的常见原因而扫描会消耗大量 CPU 和 I/O。SQL Server 会自动记录它认为可能有益的缺失索引建议。SELECT CONVERT(VARCHAR(30), GETDATE(), 126) AS runtime, mig.index_group_handle, mid.index_handle, 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值最高的前几条建议。但请谨慎对待不要盲目创建每个索引都有维护成本写操作变慢。评估索引的使用频率和收益。合并索引建议SQL Server 可能对同一个表给出多个相似的缺失索引建议需要人工合并。检查现有索引有时问题不是没有索引而是现有索引的字段顺序不对或者需要重建ALTER INDEX ... REBUILD。3.3 参数嗅探Parameter Sniffing问题这是导致“同一条 SQL有时快有时慢”的经典元凶。当存储过程或参数化查询第一次编译时SQL Server 会“嗅探”传入的参数值并基于该值的数据分布生成一个“认为最优”的执行计划然后将其缓存。如果后续传入的参数值数据分布差异巨大这个缓存的计划就可能非常低效。如何诊断清空计划缓存临时验证这是最直接的验证方法。找到问题查询的plan_handle然后只清除它的缓存-- 查找特定查询的计划句柄 SELECT text, DBCC FREEPROCCACHE (0x CONVERT(VARCHAR (512), plan_handle, 2) ) AS dbcc_freeproc_command FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_query_plan(plan_handle) CROSS APPLY sys.dm_exec_sql_text(plan_handle) WHERE text LIKE %YourProblemQueryText% -- 替换部分查询文本执行查询结果中生成的DBCC FREEPROCCACHE命令。然后重新运行你的问题查询用今天的参数。如果速度恢复正常那么参数嗅探的可能性就很大。警告不要在生产环境直接运行不带参数的DBCC FREEPROCCACHE这会清空所有计划缓存导致短时间内所有查询都需要重新编译可能引发性能雪崩。对比执行计划使用 SSMS分别用“快”的参数和“慢”的参数执行同一条 SQL并比较它们的实际执行计划。观察是否使用了不同的索引、连接方式如 Hash Join 变成 Nested Loops或预估行数与实际行数差异巨大。如何解决参数嗅探使用OPTION (RECOMPILE)在查询末尾添加此提示强制每次执行都重新编译生成针对当前参数的最优计划。适用于执行不频繁但要求高的查询。CREATE PROCEDURE MyProc Param INT AS SELECT ... FROM ... WHERE ... Param OPTION (RECOMPILE)使用OPTION (OPTIMIZE FOR UNKNOWN)或OPTIMIZE FOR (variable value)前者让优化器使用平均数据密度来生成计划后者指定一个“典型”值来生成计划。使用本地变量在存储过程内部先将输入参数赋值给一个本地变量然后在查询中使用本地变量。这会阻止优化器嗅探到原始参数值。CREATE PROCEDURE MyProc Param INT AS BEGIN DECLARE LocalParam INT Param; SELECT ... FROM ... WHERE ... LocalParam; END更新统计信息有时过时的统计信息会加剧参数嗅探的问题确保统计信息最新是基础。3.4 非 SARGable 查询导致扫描SARGable (Search Argument Able) 指的是查询条件能够有效地利用索引。非 SARGable 的写法会强制 SQL Server 进行全表或全索引扫描消耗大量 CPU。常见非 SARGable 写法在列上使用函数或计算-- 坏无法使用 ProductNumber 上的索引 SELECT * FROM Production.Product WHERE SUBSTRING(ProductNumber, 0, 4) HN- -- 好重写为 LIKE如果前导字符固定 SELECT * FROM Production.Product WHERE ProductNumber LIKE HN-%在列上进行运算-- 坏无法使用 UnitPrice 上的索引 SELECT * FROM Sales.SalesOrderDetail WHERE UnitPrice * 0.10 300 -- 好将运算移到条件另一侧 SELECT * FROM Sales.SalesOrderDetail WHERE UnitPrice 300 / 0.10隐式或显式类型转换-- 坏T1.ProdID 是 VARCHAR但被转换为 INT无法使用索引 SELECT * FROM T1 JOIN T2 ON CONVERT(INT, T1.ProdID) T2.ProductID -- 好确保连接列数据类型一致。或者为 T1 创建计算列并索引。 ALTER TABLE dbo.T1 ADD IntProdID AS CONVERT(INT, ProdID); CREATE INDEX IndProdID_int ON dbo.T1 (IntProdID);检查你找到的高 CPU SQL是否存在这类写法。修改为 SARGable 形式往往是成本最低、效果最显著的优化。4. 超越 SQL 本身系统级和配置问题排查如果上述针对 SQL 和索引的分析都未能找到根本原因或者 CPU 高企但活跃查询不多就需要将视线扩大到整个 SQL Server 实例和操作系统环境。4.1 检查并禁用不必要的跟踪和 XEvent 会话SQL Trace 和扩展事件 (XEvent) 会话如果配置不当尤其是捕获了过多事件如sql_statement_completed会产生巨大的性能开销。-- 检查活动的 Profiler 跟踪 PRINT --Profiler trace summary-- SELECT traceid, property, CONVERT(VARCHAR(1024), value) AS value FROM ::fn_trace_getinfo(default) GO -- 检查活动的 XEvent 会话 PRINT --XEvent Session Details-- SELECT sess.NAME session_name, event_name, xe_event_name, trace_event_id FROM sys.dm_xe_sessions sess JOIN sys.dm_xe_session_events evt ON sess.address evt.event_session_address INNER JOIN sys.trace_xe_event_map xemap ON evt.event_name xemap.xe_event_name GO如果发现非必要的、高开销的跟踪或会话考虑在业务低峰期停止它们。4.2 自旋锁Spinlock争用在高并发、高性能的系统中SQL Server 内部的自旋锁争用可能导致 CPU 利用率虚高。常见的可疑对象包括SOS_CACHESTORE、SOS_BLOCKALLOCPARTIALLIST、XVB_LIST等。症状CPU 使用率很高但通过sys.dm_exec_requests查看到的活跃查询 CPU 并不高或者大量查询状态为SIGNAL_WAIT类型且等待资源是SOS_SCHEDULER_YIELD。诊断与缓解查询sys.dm_os_spinlock_stats查看自旋锁的争用情况。对于特定的自旋锁问题微软可能会提供跟踪标志Trace Flag作为临时解决方案。例如历史上TF174用于缓解SOS_CACHESTORE争用TF8102和TF8101用于缓解XVB_LIST争用。重要跟踪标志是高级功能必须经过充分测试并在微软官方文档或知识库文章的建议下使用。错误使用可能导致不稳定。4.3 操作系统电源计划这是一个容易被忽略但影响巨大的配置。Windows 服务器的电源计划如果设置为“平衡”操作系统可能会动态降低 CPU 频率以节省能耗。这会导致 SQL Server 需要更长的 CPU 时间来完成相同的工作从而表现出更高的 CPU 使用率百分比。解决方案将电源计划设置为“高性能”或“卓越性能”。这可以确保 CPU 始终以最高额定频率运行提供稳定可预测的性能。4.4 虚拟机配置问题如果 SQL Server 运行在虚拟化环境如 VMware ESXi需要确保不要过度分配 CPU为虚拟机分配超过物理核心数的 vCPU 会导致严重的调度竞争。正确配置 CPU 关联性和保留咨询虚拟化管理员确保 SQL Server VM 获得了有保障的 CPU 资源。安装并更新 VMware Tools确保使用了优化的虚拟硬件驱动。4.5 纵向扩展增加 CPU 资源如果经过以上所有优化单条查询的 CPU 时间已经降到最低但整体工作负载的并发量就是那么大导致总 CPU 持续高位那么唯一的出路就是增加 CPU 资源纵向扩展。在决定扩容前可以用以下查询识别那些执行频繁、单次消耗 CPU 适中的“温和小查询”它们可能是并发压力的主要来源-- 找出平均CPU时间超过200毫秒且执行超过1000次的查询 DECLARE cputime_threshold_microsec INT 200*1000 -- 200毫秒 DECLARE execution_count INT 1000 SELECT qs.total_worker_time/1000 AS total_cpu_time_ms, qs.max_worker_time/1000 AS max_cpu_time_ms, (qs.total_worker_time/1000)/qs.execution_count AS average_cpu_time_ms, qs.execution_count, q.[text] FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS q WHERE (qs.total_worker_time/qs.execution_count cputime_threshold_microsec OR qs.max_worker_time cputime_threshold_microsec) AND qs.execution_count execution_count ORDER BY qs.total_worker_time DESC如果这类查询很多且业务无法再优化那么增加 CPU 核心数就是合理的硬件投资。5. 构建你的排查清单从现象到根因的决策树面对突发的 SQL 性能问题遵循一个清晰的排查路径能帮你节省大量时间。下面这个决策树可以作为一个快速参考现象CPU 持续 90%。第一步定位源头任务管理器/性能计数器确认是sqlservr.exe进程导致。使用sys.dm_exec_requests和sys.dm_exec_query_stats定位高 CPU 查询。保存问题 SQL 文本和执行计划。第二步分析 SQL 与计划检查统计信息是否过时尝试更新。检查缺失索引DMV 是否有高收益建议检查执行计划对比快/慢时的计划。关注预估行数 vs 实际行数巨大差异指向统计信息问题。扫描Scan vs 查找Seek。连接类型如出现意外的 Hash Join 或 Nested Loops。参数嗅探迹象编译时间 vs 不同参数。检查查询写法是否存在非 SARGable 写法列上函数、运算、类型转换第三步检查系统与环境是否有高开销的跟踪或 XEvent 会话检查自旋锁争用情况sys.dm_os_spinlock_stats。检查操作系统电源计划是否为“高性能”。如果是虚拟机检查 CPU 资源配置。第四步验证与解决统计信息/索引问题在测试环境验证后于业务低峰期实施变更。参数嗅探根据查询特性选择RECOMPILE、OPTIMIZE FOR或使用本地变量。非 SARGable 查询重写查询。系统配置问题调整电源计划、停止非必要跟踪、咨询虚拟化管理员。资源瓶颈论证并申请增加 CPU 资源。最后记住一个原则一次只做一个变更并观察效果。生产环境的优化最忌讳“乱拳打死老师傅”。每次变更后清晰地记录下变更内容、时间、预期效果和实际结果。这样当下次“昨天50ms今天5s”的问题再次出现时你不仅知道怎么排查还能积累下属于你自己的、经过实战检验的故障知识库。
SQL Server CPU飙升90%：从性能断崖到根因排查的完整实战指南
昨天跑 50 毫秒的 SQL今天突然飙到 5 秒数据库 CPU 直接冲到 90%。这不是一个假设性问题而是很多 DBA 和开发者在生产环境里真实踩过的坑。问题出现时业务方在催监控在报警而你手头只有一堆零散的线索一条 SQL、一个时间点、一个飙升的 CPU 指标。很多人第一反应是“加索引”或者“优化 SQL”但真正的问题往往藏在更深的地方。一次性能的断崖式下跌很少是单一原因造成的它更像是一个信号告诉你系统里某个长期存在的隐患被触发了。今天我们就来拆解这个经典面试题背后的完整排查链路它不仅是面试技巧更是一套能在关键时刻救场的实战方法。1. 先别急着优化 SQL确认问题边界和影响范围当 CPU 飙到 90%你的第一反应不应该是立刻打开 SQL 优化工具。在动手之前必须先搞清楚三件事问题是不是 SQL Server 引起的影响面有多大是持续性的还是间歇性的盲目优化可能让你在错误的方向上浪费大量时间。1.1 确认 CPU 高负载的“元凶”在 Windows 环境下打开任务管理器查看sqlservr.exe进程的 CPU 占用率。如果它持续接近 100%那基本可以确定问题出在数据库引擎内部。但如果sqlservr.exe的 CPU 并不高而是系统整体的 CPU 很高那可能是防病毒软件、其他驱动或操作系统组件的问题需要联系系统管理员一起排查。一个更精确的方法是使用性能计数器。你可以通过 PowerShell 脚本定期采集Process(sqlservr*)\% User Time和% Privileged Time的数据。如果% User Time持续高于 90%基本可以锁定是 SQL Server 的用户态代码也就是你的查询导致了高 CPU。如果% Privileged Time很高则可能是系统调用或驱动问题。$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 } }在 SQL Server Management Studio (SSMS) 中你也可以使用标准报表中的“性能仪表板”。深色部分代表 SQL Server 进程的 CPU 使用率浅色部分代表整个系统的 CPU 使用率。这是一个快速可视化的方法。1.2 量化 SQL 查询的 CPU 贡献度确定了是 SQL Server 的问题后下一步是量化当前所有正在执行的查询总共占用了多少 CPU 资源这能帮你判断是少数几个“坏查询”作祟还是大量并发查询的累积效应。DECLARE init_sum_cpu_time int, utilizedCpuCount int -- 获取 SQL Server 使用的 CPU 核心数 SELECT utilizedCpuCount COUNT( * ) FROM sys.dm_os_schedulers WHERE status VISIBLE ONLINE -- 计算过去 5 秒内查询消耗的 CPU 占总容量的百分比 SELECT init_sum_cpu_time SUM(cpu_time) FROM sys.dm_exec_requests WAITFOR DELAY 00:00:05 SELECT CONVERT(DECIMAL(5,2), ((SUM(cpu_time) - init_sum_cpu_time) / (utilizedCpuCount * 5000.00)) * 100 ) AS [CPU from Queries as Percent of Total CPU Capacity] FROM sys.dm_exec_requests如果这个百分比很高比如超过 70%说明当前活跃查询就是罪魁祸首。如果百分比很低但sqlservr.exe的 CPU 依然很高那就要怀疑是不是后台任务如统计信息更新、索引重建、锁等待或 SQL Server 内部组件如锁管理器、任务调度器出现了问题。1.3 建立问题的时间线是突然发生还是缓慢恶化询问业务方或查看监控确定性能下降是精确发生在某个时间点还是一段时间内逐渐变慢。突然发生通常与数据/结构变更相关。例如统计信息更新、索引被意外删除或禁用、数据量突变如凌晨的批量导入、应用程序发布了新版本并带来了新的低效查询。缓慢恶化通常与数据增长、资源竞争或计划缓存恶化相关。例如表数据持续增长导致原有执行计划不再高效参数嗅探Parameter Sniffing问题随着数据分布变化而凸显。这个判断能极大缩小你的排查范围。如果是“昨天50ms今天5s”这种突变重点就应该放在变更上。2. 定位罪魁祸首找出正在消耗 CPU 的查询确认了边界接下来就是抓“现行犯”。我们的目标是找到那些正在执行或刚刚执行完的、消耗大量 CPU 的查询。2.1 抓取当前正在运行的高 CPU 查询使用sys.dm_exec_requests和sys.dm_exec_sessions这两个动态管理视图 (DMV)可以实时看到每个会话正在执行的请求及其资源消耗。SELECT TOP 10 s.session_id, r.status, r.cpu_time, r.logical_reads, r.reads, r.writes, r.total_elapsed_time / (1000 * 60) AS Elaps M, 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 statement_text, COALESCE(QUOTENAME(DB_NAME(st.dbid)) N. QUOTENAME(OBJECT_SCHEMA_NAME(st.objectid, st.dbid)) N. QUOTENAME(OBJECT_NAME(st.objectid, st.dbid)), ) AS command_text, 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 ORDER BY r.cpu_time DESC这个查询结果非常关键cpu_time: 该请求已消耗的 CPU 时间毫秒。这是排序的核心依据。statement_text: 当前正在执行的 SQL 语句片段。command_text: 所属的数据库和对象如果可解析。login_name,host_name,program_name: 谁、从哪里、用什么工具执行的。这能帮你判断是来自应用服务器、报表工具还是人为的即席查询。status: 查询状态如running,suspended。如果status是suspended但cpu_time很高说明它之前已经消耗了大量 CPU现在可能在等待资源如 I/O。注意如果当前没有高 CPU 的活跃查询问题可能是间歇性的或者罪魁祸首已经执行完毕。这时就需要查询历史执行记录。2.2 查询历史高 CPU 查询计划缓存Plan Cache中存储了之前执行过的查询及其统计信息。通过查询sys.dm_exec_query_stats我们可以找到历史上消耗 CPU 最多的查询。SELECT TOP 10 qs.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_all_executions_ms, (qs.total_elapsed_time / 1000) AS cumulative_elapsed_time_all_executions_ms FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(sql_handle) st ORDER BY (qs.total_worker_time / qs.execution_count) DESC这里重点关注avg_cpu_time_ms平均每次执行消耗的 CPU 时间和execution_count执行次数。一个平均 CPU 很高但执行次数少的查询可能是今天新出现的“坏查询”。一个平均 CPU 不高但执行次数极高的查询可能是由于并发量突增导致的累积效应。找到目标 SQL 后立即保存其完整的 SQL 文本、执行计划如果可能以及相关的sql_handle或plan_handle。这是后续分析的基石。3. 深度分析为什么同一条 SQL 今天变慢了找到了消耗 CPU 的 SQL这只是第一步。核心问题是为什么昨天快今天慢执行计划变了。99% 的此类性能突变根源都在于执行计划Execution Plan的变更。我们需要像一个侦探一样检查执行计划的“健康状态”。3.1 首要检查统计信息是否过时统计信息是查询优化器Query Optimizer为表数据构建的“数据画像”包括行数、唯一值数量、数据分布等。如果这个画像过时了优化器就会基于错误的信息制定一个低效的执行计划。如何检查直接更新对查询涉及的表执行UPDATE STATISTICS。最粗暴但有效的方法是更新整个数据库的统计信息EXEC sp_updatestats注意sp_updatestats会对所有用户表运行UPDATE STATISTICS。在生产环境这可能会消耗大量 I/O 和 CPU并阻塞查询。建议在业务低峰期进行或针对特定表更新。查看统计信息最后更新时间SELECT OBJECT_NAME(s.object_id) AS TableName, s.name AS StatsName, STATS_DATE(s.object_id, s.stats_id) AS LastUpdated, s.auto_created, s.user_created FROM sys.stats s WHERE OBJECT_NAME(s.object_id) IN (YourTableName) -- 替换为你的表名 ORDER BY LastUpdated;如果LastUpdated远早于数据发生重大变化的时间例如大批量增删改那么统计信息很可能已经过时。为什么统计信息过时会导致计划变差假设你的查询条件是WHERE Status Active昨天有 1000 行是Active优化器可能选择索引查找。今天经过批量更新有 100 万行变成了Active但统计信息没更新优化器仍然认为只有 1000 行可能还是选择索引查找实际需要回表 100 万次而不是更高效的表扫描或批处理模式。3.2 检查缺失索引缺失索引是导致表/索引扫描Scan的常见原因而扫描会消耗大量 CPU 和 I/O。SQL Server 会自动记录它认为可能有益的缺失索引建议。SELECT CONVERT(VARCHAR(30), GETDATE(), 126) AS runtime, mig.index_group_handle, mid.index_handle, 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值最高的前几条建议。但请谨慎对待不要盲目创建每个索引都有维护成本写操作变慢。评估索引的使用频率和收益。合并索引建议SQL Server 可能对同一个表给出多个相似的缺失索引建议需要人工合并。检查现有索引有时问题不是没有索引而是现有索引的字段顺序不对或者需要重建ALTER INDEX ... REBUILD。3.3 参数嗅探Parameter Sniffing问题这是导致“同一条 SQL有时快有时慢”的经典元凶。当存储过程或参数化查询第一次编译时SQL Server 会“嗅探”传入的参数值并基于该值的数据分布生成一个“认为最优”的执行计划然后将其缓存。如果后续传入的参数值数据分布差异巨大这个缓存的计划就可能非常低效。如何诊断清空计划缓存临时验证这是最直接的验证方法。找到问题查询的plan_handle然后只清除它的缓存-- 查找特定查询的计划句柄 SELECT text, DBCC FREEPROCCACHE (0x CONVERT(VARCHAR (512), plan_handle, 2) ) AS dbcc_freeproc_command FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_query_plan(plan_handle) CROSS APPLY sys.dm_exec_sql_text(plan_handle) WHERE text LIKE %YourProblemQueryText% -- 替换部分查询文本执行查询结果中生成的DBCC FREEPROCCACHE命令。然后重新运行你的问题查询用今天的参数。如果速度恢复正常那么参数嗅探的可能性就很大。警告不要在生产环境直接运行不带参数的DBCC FREEPROCCACHE这会清空所有计划缓存导致短时间内所有查询都需要重新编译可能引发性能雪崩。对比执行计划使用 SSMS分别用“快”的参数和“慢”的参数执行同一条 SQL并比较它们的实际执行计划。观察是否使用了不同的索引、连接方式如 Hash Join 变成 Nested Loops或预估行数与实际行数差异巨大。如何解决参数嗅探使用OPTION (RECOMPILE)在查询末尾添加此提示强制每次执行都重新编译生成针对当前参数的最优计划。适用于执行不频繁但要求高的查询。CREATE PROCEDURE MyProc Param INT AS SELECT ... FROM ... WHERE ... Param OPTION (RECOMPILE)使用OPTION (OPTIMIZE FOR UNKNOWN)或OPTIMIZE FOR (variable value)前者让优化器使用平均数据密度来生成计划后者指定一个“典型”值来生成计划。使用本地变量在存储过程内部先将输入参数赋值给一个本地变量然后在查询中使用本地变量。这会阻止优化器嗅探到原始参数值。CREATE PROCEDURE MyProc Param INT AS BEGIN DECLARE LocalParam INT Param; SELECT ... FROM ... WHERE ... LocalParam; END更新统计信息有时过时的统计信息会加剧参数嗅探的问题确保统计信息最新是基础。3.4 非 SARGable 查询导致扫描SARGable (Search Argument Able) 指的是查询条件能够有效地利用索引。非 SARGable 的写法会强制 SQL Server 进行全表或全索引扫描消耗大量 CPU。常见非 SARGable 写法在列上使用函数或计算-- 坏无法使用 ProductNumber 上的索引 SELECT * FROM Production.Product WHERE SUBSTRING(ProductNumber, 0, 4) HN- -- 好重写为 LIKE如果前导字符固定 SELECT * FROM Production.Product WHERE ProductNumber LIKE HN-%在列上进行运算-- 坏无法使用 UnitPrice 上的索引 SELECT * FROM Sales.SalesOrderDetail WHERE UnitPrice * 0.10 300 -- 好将运算移到条件另一侧 SELECT * FROM Sales.SalesOrderDetail WHERE UnitPrice 300 / 0.10隐式或显式类型转换-- 坏T1.ProdID 是 VARCHAR但被转换为 INT无法使用索引 SELECT * FROM T1 JOIN T2 ON CONVERT(INT, T1.ProdID) T2.ProductID -- 好确保连接列数据类型一致。或者为 T1 创建计算列并索引。 ALTER TABLE dbo.T1 ADD IntProdID AS CONVERT(INT, ProdID); CREATE INDEX IndProdID_int ON dbo.T1 (IntProdID);检查你找到的高 CPU SQL是否存在这类写法。修改为 SARGable 形式往往是成本最低、效果最显著的优化。4. 超越 SQL 本身系统级和配置问题排查如果上述针对 SQL 和索引的分析都未能找到根本原因或者 CPU 高企但活跃查询不多就需要将视线扩大到整个 SQL Server 实例和操作系统环境。4.1 检查并禁用不必要的跟踪和 XEvent 会话SQL Trace 和扩展事件 (XEvent) 会话如果配置不当尤其是捕获了过多事件如sql_statement_completed会产生巨大的性能开销。-- 检查活动的 Profiler 跟踪 PRINT --Profiler trace summary-- SELECT traceid, property, CONVERT(VARCHAR(1024), value) AS value FROM ::fn_trace_getinfo(default) GO -- 检查活动的 XEvent 会话 PRINT --XEvent Session Details-- SELECT sess.NAME session_name, event_name, xe_event_name, trace_event_id FROM sys.dm_xe_sessions sess JOIN sys.dm_xe_session_events evt ON sess.address evt.event_session_address INNER JOIN sys.trace_xe_event_map xemap ON evt.event_name xemap.xe_event_name GO如果发现非必要的、高开销的跟踪或会话考虑在业务低峰期停止它们。4.2 自旋锁Spinlock争用在高并发、高性能的系统中SQL Server 内部的自旋锁争用可能导致 CPU 利用率虚高。常见的可疑对象包括SOS_CACHESTORE、SOS_BLOCKALLOCPARTIALLIST、XVB_LIST等。症状CPU 使用率很高但通过sys.dm_exec_requests查看到的活跃查询 CPU 并不高或者大量查询状态为SIGNAL_WAIT类型且等待资源是SOS_SCHEDULER_YIELD。诊断与缓解查询sys.dm_os_spinlock_stats查看自旋锁的争用情况。对于特定的自旋锁问题微软可能会提供跟踪标志Trace Flag作为临时解决方案。例如历史上TF174用于缓解SOS_CACHESTORE争用TF8102和TF8101用于缓解XVB_LIST争用。重要跟踪标志是高级功能必须经过充分测试并在微软官方文档或知识库文章的建议下使用。错误使用可能导致不稳定。4.3 操作系统电源计划这是一个容易被忽略但影响巨大的配置。Windows 服务器的电源计划如果设置为“平衡”操作系统可能会动态降低 CPU 频率以节省能耗。这会导致 SQL Server 需要更长的 CPU 时间来完成相同的工作从而表现出更高的 CPU 使用率百分比。解决方案将电源计划设置为“高性能”或“卓越性能”。这可以确保 CPU 始终以最高额定频率运行提供稳定可预测的性能。4.4 虚拟机配置问题如果 SQL Server 运行在虚拟化环境如 VMware ESXi需要确保不要过度分配 CPU为虚拟机分配超过物理核心数的 vCPU 会导致严重的调度竞争。正确配置 CPU 关联性和保留咨询虚拟化管理员确保 SQL Server VM 获得了有保障的 CPU 资源。安装并更新 VMware Tools确保使用了优化的虚拟硬件驱动。4.5 纵向扩展增加 CPU 资源如果经过以上所有优化单条查询的 CPU 时间已经降到最低但整体工作负载的并发量就是那么大导致总 CPU 持续高位那么唯一的出路就是增加 CPU 资源纵向扩展。在决定扩容前可以用以下查询识别那些执行频繁、单次消耗 CPU 适中的“温和小查询”它们可能是并发压力的主要来源-- 找出平均CPU时间超过200毫秒且执行超过1000次的查询 DECLARE cputime_threshold_microsec INT 200*1000 -- 200毫秒 DECLARE execution_count INT 1000 SELECT qs.total_worker_time/1000 AS total_cpu_time_ms, qs.max_worker_time/1000 AS max_cpu_time_ms, (qs.total_worker_time/1000)/qs.execution_count AS average_cpu_time_ms, qs.execution_count, q.[text] FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS q WHERE (qs.total_worker_time/qs.execution_count cputime_threshold_microsec OR qs.max_worker_time cputime_threshold_microsec) AND qs.execution_count execution_count ORDER BY qs.total_worker_time DESC如果这类查询很多且业务无法再优化那么增加 CPU 核心数就是合理的硬件投资。5. 构建你的排查清单从现象到根因的决策树面对突发的 SQL 性能问题遵循一个清晰的排查路径能帮你节省大量时间。下面这个决策树可以作为一个快速参考现象CPU 持续 90%。第一步定位源头任务管理器/性能计数器确认是sqlservr.exe进程导致。使用sys.dm_exec_requests和sys.dm_exec_query_stats定位高 CPU 查询。保存问题 SQL 文本和执行计划。第二步分析 SQL 与计划检查统计信息是否过时尝试更新。检查缺失索引DMV 是否有高收益建议检查执行计划对比快/慢时的计划。关注预估行数 vs 实际行数巨大差异指向统计信息问题。扫描Scan vs 查找Seek。连接类型如出现意外的 Hash Join 或 Nested Loops。参数嗅探迹象编译时间 vs 不同参数。检查查询写法是否存在非 SARGable 写法列上函数、运算、类型转换第三步检查系统与环境是否有高开销的跟踪或 XEvent 会话检查自旋锁争用情况sys.dm_os_spinlock_stats。检查操作系统电源计划是否为“高性能”。如果是虚拟机检查 CPU 资源配置。第四步验证与解决统计信息/索引问题在测试环境验证后于业务低峰期实施变更。参数嗅探根据查询特性选择RECOMPILE、OPTIMIZE FOR或使用本地变量。非 SARGable 查询重写查询。系统配置问题调整电源计划、停止非必要跟踪、咨询虚拟化管理员。资源瓶颈论证并申请增加 CPU 资源。最后记住一个原则一次只做一个变更并观察效果。生产环境的优化最忌讳“乱拳打死老师傅”。每次变更后清晰地记录下变更内容、时间、预期效果和实际结果。这样当下次“昨天50ms今天5s”的问题再次出现时你不仅知道怎么排查还能积累下属于你自己的、经过实战检验的故障知识库。
