最近在面试中经常被问到这样一个经典问题“线上有一条SQL昨天跑50毫秒今天突然跑了5秒数据库CPU直接飙到90%你怎么排查” 这不仅是面试官考察候选人数据库性能排查能力的试金石更是我们日常运维和开发中必须掌握的硬核技能。一条SQL的性能突然断崖式下跌往往意味着线上服务即将面临风险能否快速定位并解决问题直接体现了工程师的实战经验和系统化思维。本文将为你系统性地梳理一套从现象到根因的完整排查流程。无论你是正在准备面试的求职者还是在实际工作中遇到了类似问题的开发者都能从本文中找到清晰的排查思路和可直接执行的SQL脚本。我们将从确认问题、定位元凶、分析原因到最终解决一步步拆解这个高频难题。1. 问题背景与核心排查思路当数据库服务器的CPU使用率突然飙升到90%甚至更高并且已知是由某条SQL语句性能劣化引起时我们面临的通常是一个典型的“性能回归”问题。其核心特征是同一SQL相同数据量在不同时间点执行性能差异巨大。1.1 为什么SQL性能会突然变差在深入排查步骤之前我们需要理解可能导致这种“昨日良今日劣”现象的常见原因。这有助于我们在排查时建立正确的思维模型执行计划突变这是最常见的原因。数据库优化器为SQL生成的执行计划比如是走索引扫描还是全表扫描发生了变化导致执行效率天差地别。统计信息过时数据库依靠表的统计信息如数据分布、直方图来生成最优执行计划。如果数据发生大量增删改例如夜间批量作业而统计信息未及时更新优化器可能会基于错误的信息做出糟糕的选择。参数嗅探Parameter Sniffing问题对于带参数的查询如存储过程SQL Server会缓存首次执行时生成的、基于特定参数值的执行计划。当后续传入的参数值数据分布差异巨大时这个“为别人量身定做”的计划可能对当前值极其低效。缺失索引随着业务增长新的查询模式可能出现而现有的索引无法有效支持导致查询必须进行昂贵的全表扫描。系统资源争用同一时间点可能有其他重型查询在运行竞争CPU、内存或I/O资源导致你的查询变慢。但这通常不会导致单条SQL的执行计划本身变差。锁或阻塞查询可能因为等待锁如行锁、页锁、表锁而长时间挂起虽然这通常表现为等待时间wait_time激增但也会间接导致CPU资源消耗观测异常。我们的排查将围绕这些可能性展开遵循一个清晰的路径先确认现象再定位具体查询最后分析并解决根本原因。2. 第一步确认问题根源是否在数据库在深入SQL内部之前首先要排除“误判”。CPU飙高可能是由其他进程如杀毒软件、系统更新、其他应用引起的。2.1 使用任务管理器/资源监视器初步判断打开Windows任务管理器或资源监视器resmon.exe在“进程”页签下观察sqlservr.exe进程的CPU占用率。如果它持续接近或达到100%对于多核CPU可能是一个核心的100%那么基本可以确定是SQL Server自身进程导致了高CPU。2.2 使用性能计数器精准定位为了更精确地监控我们可以使用性能监视器perfmon。添加以下计数器对象Process计数器% User Time,% Privileged Time实例sqlservr如果% User Time持续高于90%则表明是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 }2.3 使用SQL Server内置报表在SQL Server Management Studio (SSMS)中右键点击实例名选择“报表” - “标准报表” - “活动 - 性能仪表板”。这个仪表板可以直观地展示当前消耗CPU资源最多的查询。至此如果确认是sqlservr.exe进程导致高CPU我们就可以进入下一步找出是哪些具体的查询在“作祟”。3. 第二步定位消耗CPU最高的查询我们需要从数据库内部视角找出正在运行或最近运行过的、消耗CPU最多的SQL语句。3.1 查看当前正在运行的昂贵查询以下查询可以列出当前正在执行、且消耗CPU最多的前10个会话和请求。cpu_time字段表示该请求已使用的CPU时间毫秒。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;关键字段解读session_id: 会话ID可用于后续跟踪或终止 (KILL) 操作。cpu_time: 该请求已消耗的CPU时间毫秒是排序的关键。statement_text: 当前正在执行的SQL语句文本可能只是批处理中的一部分。logical_reads: 逻辑读取次数高值可能暗示缺少索引或表扫描。Elaps_M: 请求已运行的总时间分钟结合cpu_time可判断是CPU密集型还是等待密集型。3.2 查看历史累计消耗CPU高的查询有时问题查询已经执行完毕我们需要从计划缓存中查找“历史罪魁祸首”。以下查询按平均CPU时间排序找出执行频繁且平均消耗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, qs.execution_count FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st ORDER BY (qs.total_worker_time / qs.execution_count) DESC;关键字段解读avg_cpu_time_ms: 该查询每次执行平均消耗的CPU时间毫秒。这是定位“慢查询”的核心指标。execution_count: 执行次数。结合平均CPU时间可以判断是偶发的大查询还是高频的小查询拖累了系统。batch_text/statement_text: 完整的批处理语句和具体的SQL语句。通过这一步你应该能锁定一两条“嫌疑”最大的SQL语句。记录下它们的完整文本接下来我们要分析它为什么变慢了。4. 第三步分析查询变慢的根本原因找到问题SQL后我们需要像侦探一样检查其“执行计划”这是理解数据库如何执行这条SQL的蓝图。4.1 获取并查看执行计划在SSMS中选中你的问题SQL按下Ctrl L或点击“显示估计的执行计划”。更准确的是在SQL前加上SET STATISTICS PROFILE ON;然后执行获取实际执行计划。在执行计划中重点关注以下成本高昂的操作表扫描 (Table Scan): 成本极高意味着没有可用索引或索引失效数据库读取了整张表。索引扫描 (Index Scan): 虽然走了索引但仍然是扫描了整个索引的所有条目对于大表依然很慢。键查找 (Key Lookup/RID Lookup): 当使用非聚集索引查找后还需要回到主表堆或聚集索引去获取其他列的数据。如果次数很多成千上万次成本会急剧上升。排序 (Sort):ORDER BY,DISTINCT,GROUP BY未使用索引时可能导致内存或磁盘排序消耗大量CPU和内存。哈希匹配 (Hash Match): 通常发生在连接JOIN或聚合时如果数据量大会非常消耗内存和CPU。并行执行 (Parallelism): 图标是两个蓝色箭头。虽然并行可以加速大查询但如果不当使用会瞬间榨干所有CPU核心。4.2 检查并更新统计信息过时的统计信息是导致执行计划变差的首要元凶。统计信息描述了表中数据的分布情况例如某列有多少个不同的值。优化器依靠它来估算执行成本。如果统计信息是旧的优化器可能错误地认为表很小或某个值很常见从而选择了低效的计划。如何更新统计信息你可以更新特定表的统计信息或者更新整个数据库的统计信息。-- 更新单个表的统计信息 UPDATE STATISTICS YourTableName; -- 更新当前数据库所有用户表的统计信息 EXEC sp_updatestats;注意sp_updatestats会更新当前数据库中所有用户表和内部表的统计信息。在生产环境建议在业务低峰期进行因为它可能消耗一定资源并持有锁。对于大型表可以考虑使用WITH SAMPLE子句进行抽样更新以减轻负担。执行更新后再次运行你的问题SQL观察性能是否恢复。如果恢复那么根本原因就是统计信息过时。4.3 检查缺失索引执行计划可能会直接提示“缺失索引”Missing Index。这是一个强烈的优化信号。如何查找缺失索引建议运行以下查询它可以找出那些可能通过添加索引大幅提升性能的查询。improvement_measure值越高表示创建该索引的潜在收益越大。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 migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks migs.user_scans) DESC;重要提示不要盲目创建所有建议的索引每个索引都会增加写操作INSERT/UPDATE/DELETE的开销。需要评估索引是否真的被频繁用到user_seeks,user_scans值高创建索引的列是否合理equality_columns用于等值查询inequality_columns用于范围查询,,BETWEENincluded_columns是包含列用于覆盖查询避免键查找。在生产环境创建索引前务必在测试环境验证其效果和影响。4.4 调查参数敏感型计划 (PSP) 问题这是“昨天快今天慢”的经典场景。当SQL特别是存储过程使用参数时SQL Server在第一次编译时会“嗅探”传入的参数值并生成一个针对该特定值的优化执行计划然后将其缓存。如果后续传入的参数值数据分布差异巨大例如第一次传UserId1有100条订单第二次传UserIdNULL查询所有用户订单这个缓存的计划可能对新的参数值极其低效。如何诊断PSP问题一个快速验证的方法是清空特定查询的计划缓存观察性能是否恢复。警告此操作会影响生产性能请在测试环境或业务低峰期谨慎操作。-- 首先找到问题查询的 plan_handle SELECT plan_handle, st.text FROM sys.dm_exec_cached_plans cp CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st WHERE st.text LIKE %你的问题SQL关键词%; -- 假设找到的 plan_handle 是 0x06000500A27E...则清除该特定计划 DBCC FREEPROCCACHE (0x06000500A27E...);注意使用不带参数的DBCC FREEPROCCACHE会清空整个服务器的计划缓存导致所有查询重新编译可能引发瞬时性能下降生产环境严禁使用。如果清空特定计划后查询性能恢复正常那么PSP的可能性就很大。解决PSP问题的方案使用OPTION (RECOMPILE)查询提示强制该语句每次执行时都重新编译生成最适合当前参数值的计划。适用于执行不频繁但参数多变的查询。SELECT * FROM Orders WHERE UserId UserId OPTION (RECOMPILE);使用OPTION (OPTIMIZE FOR (VARIABLE UNKNOWN))让优化器基于平均数据密度来生成计划而不是嗅探到的具体值。适用于参数值分布均匀的场景。SELECT * FROM Orders WHERE UserId UserId OPTION (OPTIMIZE FOR (UserId UNKNOWN));使用OPTION (OPTIMIZE FOR (VARIABLE ‘SpecificValue’))指定一个具有代表性的“典型值”来生成计划。你需要对业务数据有深入了解。重写SQL使用本地变量将参数值赋给一个本地变量然后在WHERE子句中使用该变量。这会阻止参数嗅探但可能导致优化器使用不准确的密度估计。DECLARE LocalUserId INT UserId; SELECT * FROM Orders WHERE UserId LocalUserId;5. 第四步检查查询设计问题SARGabilitySARGableSearch Argument Able指的是查询条件能够有效地利用索引。非SARGable的写法会强制数据库进行全表扫描即使相关列上有索引。常见的非SARGable写法在列上使用函数或计算-- 非SARGable无法使用 ProductNumber 上的索引 SELECT * FROM Products WHERE SUBSTRING(ProductNumber, 1, 2) AB; -- SARGable 改写使用 LIKE如果前缀匹配 SELECT * FROM Products WHERE ProductNumber LIKE AB%;在列上进行数学运算或数据类型转换-- 非SARGable SELECT * FROM Sales WHERE UnitPrice * 0.9 100; -- SARGable 改写将计算移到运算符另一侧 SELECT * FROM Sales WHERE UnitPrice 100 / 0.9;-- 非SARGable (隐式转换或显式转换在列上) SELECT * FROM TableA WHERE CAST(CharColumn AS INT) 1; SELECT * FROM TableA JOIN TableB ON CONVERT(INT, TableA.VarcharColumn) TableB.IntColumn; -- 解决方案确保比较的两边数据类型一致。可能需要修改表结构或增加计算列并索引。 ALTER TABLE TableA ADD IntColumn AS CAST(CharColumn AS INT); CREATE INDEX IX_TableA_IntColumn ON TableA(IntColumn);使用NOT,,NOT IN,NOT LIKE这些操作通常难以有效利用索引。使用OR连接不同列的条件可能导致索引失效考虑改用UNION ALL。检查你的问题SQL看是否存在上述非SARGable的写法并尝试进行优化重写。6. 第五步检查系统级影响因素如果上述针对SQL本身的优化都尝试了问题依旧可能需要查看系统级配置。6.1 检查并禁用开销大的跟踪或XEvent会话正在运行的SQL Server Profiler跟踪或扩展事件XEvent会话如果捕获了过多事件如每条语句的完成事件会产生显著开销。-- 检查是否有活动的跟踪 SELECT * FROM sys.traces WHERE is_default 0; -- 检查是否有活动的XEvent会话 SELECT sess.name, sess.create_time, sess.* FROM sys.dm_xe_sessions sess WHERE sess.name IS NOT NULL;如果发现非必要的、高开销的跟踪或会话考虑停止它们。6.2 检查自旋锁Spinlock争用在极高并发或特定硬件配置下SQL Server内部的自旋锁如SOS_CACHESTORE可能发生争用表现为CPU使用率高但实际查询负载并不高。这属于较深层次的问题通常需要微软支持或应用特定的跟踪标志Trace Flag来缓解例如针对SOS_CACHESTORE争用的 TF174。此类操作风险较高需在专家指导下进行。6.3 检查操作系统电源计划在Windows Server上错误的电源计划可能导致CPU降频运行。虽然CPU利用率显示很高但实际处理能力不足导致查询变慢。确保电源计划设置为“高性能”打开“控制面板” - “电源选项”。选择“高性能”计划。在虚拟机环境中也需确保宿主机和虚拟机的电源策略配置正确。6.4 检查虚拟化配置如果SQL Server运行在虚拟机上需要确保为虚拟机分配了足够的、专有的CPU资源并且没有过度配置Overcommit。同时检查虚拟化层如VMware的CPU就绪时间%RDY是否存在瓶颈。7. 总结与系统化排查清单面对“SQL突然变慢导致CPU飙升”的问题遵循一个系统化的排查路径至关重要。以下是完整的排查清单你可以像查字典一样按顺序使用步骤操作目的/命令预期结果/下一步1. 确认根源检查sqlservr.exe进程CPU任务管理器 /PerfMon计数器确认是SQL Server进程导致高CPU。2. 定位查询查找当前/历史高CPU查询sys.dm_exec_requests/sys.dm_exec_query_stats找到1-N条嫌疑SQL语句。3. 分析计划获取嫌疑SQL的执行计划SSMS中Ctrl L或SET STATISTICS XML ON查看是否存在表扫描、键查找等高成本操作。4. 更新统计更新相关表的统计信息UPDATE STATISTICS TableName;或EXEC sp_updatestats;观察SQL性能是否恢复。若恢复则问题解决。5. 检查索引查看缺失索引建议sys.dm_db_missing_index_details相关查询评估并创建高收益的缺失索引。6. 验证PSP清空特定查询计划缓存DBCC FREEPROCCACHE (plan_handle);若性能恢复则定位为参数嗅探问题采用OPTION (RECOMPILE)等方案。7. 优化写法检查SQL是否SARGable检查WHERE/JOIN子句中列是否被函数、计算包裹重写SQL确保条件能有效利用索引。8. 系统检查检查跟踪、电源计划等停止非必要Profiler跟踪设置电源计划为“高性能”排除外部干扰因素。9. 深入诊断考虑锁、阻塞、自旋锁检查sys.dm_os_wait_stats,sys.dm_os_spinlock_stats针对特定等待类型或自旋锁争用进行深入优化。记住排查的过程往往是迭代的。可能更新统计信息后问题就解决了也可能需要结合创建索引和重写SQL。掌握这套方法不仅能应对面试更能让你在真实的线上故障面前从容不迫快速恢复服务稳定。