SQL Server 性能问题排查的黄金法则:CPU瓶颈诊断完全指南
系列文章: SQL Server性能优化实战系列 · 第1篇 发布日期: 2026-09-10 字数: 约4500字 阅读时间: 15分钟
引言
在SQL Server性能优化实践中,CPU瓶颈是最常见也是最复杂的问题之一。过高的CPU利用率不仅会拖慢查询响应,还可能引发连锁反应,影响整个系统的稳定性。
然而,CPU瓶颈往往不是硬件资源不足造成的,而是查询设计不当、过多编译、低效执行计划等软件层面的问题。这意味着,在购买更快或更多的处理器之前,我们完全可以通过查询调优、应用程序设计改进和系统配置优化来大幅降低CPU使用率。
本文将系统介绍CPU瓶颈的诊断方法,特别是SQL Server 2016+引入的24个新增列,以及实战中最有效的DMV查询脚本。所有脚本均可直接用于生产环境,建议收藏备用。
一、CPU瓶颈的三种检测方法
1.1 使用性能监视器检测
性能监视器(Performance Monitor)是判断服务器是否受CPU限制的最简单方法之一。
关键计数器1:Processor % Processor Time
- 诊断标准: 每CPU的处理器时间持续超过80%通常被视为CPU瓶颈
- 注意事项: 需要检查每个CPU核心的使用率,而不仅仅是总体平均值
- 多CPU系统: 如果某些核心达到100%而其他核心空闲,可能存在线程调度问题
关键计数器2:System Processor Queue Length
- 诊断标准: 该值持续大于 2倍CPU核心数 时表明存在CPU调度压力
- 示例: 8核服务器,如果Queue Length持续>16,说明有大量线程在等待CPU时间
1.2 使用DMV检测
从SQL Server内部,可以通过检查等待类型来发现CPU瓶颈。
关键等待类型:SOS_SCHEDULER_YIELD
- 含义: 可运行线程正在等待被调度到CPU上执行
- 查询方法:
SELECT
wait_type,
waiting_tasks_count,
wait_time_ms,
max_wait_time_ms,
signal_wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type = 'SOS_SCHEDULER_YIELD'
ORDER BY wait_time_ms DESC;
查询缓存分析:找出CPU消耗最高的查询
-- Top 50 CPU消耗查询(按plan_handle聚合)
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.plan_handle
FROM sys.dm_exec_query_stats qs
GROUP BY qs.plan_handle
ORDER BY SUM(qs.total_worker_time) DESC;
解读:
total_worker_time:累计CPU时间(微秒)- 按
plan_handle聚合:将同一批处理/存储过程中的所有语句汇总 - 如果某个
plan_handle包含多条语句,需要进一步展开定位具体语句
1.3 诊断流程图
二、SQL Server 2016+新增的24个列:并行度与内存授予(重点)
SQL Server 2016对sys.dm_exec_query_stats进行了重大增强,新增了24个列,提供有关查询并行度(DOP)、线程使用和内存消耗的详细信息。这些新列对CPU瓶颈诊断极具价值。
2.1 并行度相关列
| 列名 | 说明 |
|---|---|
total_dop | 所有执行的并行度总和 |
last_dop | 最近一次执行的并行度 |
min_dop | 最小并行度 |
max_dop | 最大并行度 |
2.2 并行线程相关列
| 列名 | 说明 |
|---|---|
total_reserved_threads | 预留线程总数 |
total_used_threads | 实际使用线程总数 |
last_reserved_threads | 最近一次预留线程数 |
last_used_threads | 最近一次实际使用线程数 |
2.3 内存授予相关列
| 列名 | 说明 |
|---|---|
total_grant_kb | 内存授予总量(KB) |
last_grant_kb | 最近一次内存授予 |
min_grant_kb | 最小内存授予 |
max_grant_kb | 最大内存授予 |
2.4 实战查询1:找出并行度浪费的查询
-- 预留线程数远超实际使用的查询
SELECT TOP 20
qs.total_reserved_threads,
qs.total_used_threads,
qs.total_reserved_threads - qs.total_used_threads AS wasted_threads,
qs.total_dop,
qs.last_dop,
qs.execution_count,
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
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE qs.total_reserved_threads > qs.total_used_threads * 2 -- 预留超过实际使用2倍
ORDER BY (qs.total_reserved_threads - qs.total_used_threads) DESC;
解读:
-
total_reserved_threads > total_used_threads * 2:预留线程数超过实际使用2倍 -
这类查询可能导致CPU资源浪费和线程池压力
-
优化方向:
- 调整
MAXDOP设置(服务器级/数据库级/查询级) - 检查查询本身是否适合并行执行
- 调整
2.5 实战查询2:CPU消耗高但并行度低的查询
-- 有潜力通过增加并行度优化的查询
SELECT TOP 20
qs.total_worker_time / 1000000.0 AS total_cpu_seconds,
qs.execution_count,
qs.total_dop,
qs.last_dop,
qs.total_worker_time / qs.execution_count / 1000.0 AS avg_cpu_ms,
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
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE qs.total_worker_time > 1000000 -- 超过1秒CPU时间
AND qs.last_dop < 4 -- 并行度小于4
ORDER BY qs.total_worker_time DESC;
解读:
-
这类查询CPU消耗高,但并行度很低
-
可能原因:
- 查询成本未达到
cost threshold for parallelism(默认5) MAXDOP设置过低- 表数据量较小,优化器认为并行无益
- 查询成本未达到
-
优化方向:
- 降低
cost threshold for parallelism - 使用
OPTION (MAXDOP 8)查询提示测试效果
- 降低
2.6 实战查询3:内存授予不足导致的溢出
-- 内存授予不足导致tempdb溢出的查询
SELECT TOP 20
qs.total_grant_kb / 1024.0 AS avg_grant_mb,
qs.min_grant_kb / 1024.0 AS min_grant_mb,
qs.max_grant_kb / 1024.0 AS max_grant_mb,
qs.execution_count,
qs.total_spills, -- SQL Server 2016+
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
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE qs.total_spills > 0 -- 有溢出
ORDER BY qs.total_spills DESC;
关联性:
- 内存授予不足会导致数据溢出到tempdb
- 溢出会增加I/O压力和CPU消耗(排序/哈希需要重新读取数据)
三、过多查询编译的诊断与优化
查询编译和优化是CPU密集型过程,即使相对简单的查询也可能需要10-20毫秒的CPU时间来解析和编译。
3.1 编译成本分析
性能监视器检测:
关键计数器:
- SQL Statistics: SQL Recompilations/sec
- SQL Statistics: Batch Requests/sec
理想情况:
SQL Recompilations/sec << Batch Requests/sec
典型场景:
- OLTP系统: 提交的查询集合相对较小且静态,计划重用至关重要
- 即席查询过多: 每次提交都编译新计划,编译成本可能与执行成本相当甚至更高
3.2 使用query_hash识别即席查询
SQL Server在编译期间会计算查询的"签名"并通过query_hash列暴露出来。具有相同query_hash值的查询很可能是相同模式,仅字面量值不同。
-- 找出具有相同查询模式但未参数化的查询
SELECT
query_hash,
COUNT(*) AS query_plan_count,
SUM(execution_count) AS total_executions,
AVG(total_worker_time/execution_count) AS avg_cpu_time,
MIN(plan_handle) AS sample_plan_handle
FROM sys.dm_exec_query_stats
GROUP BY query_hash
HAVING COUNT(*) > 10 -- 相同模式超过10个计划
ORDER BY query_plan_count DESC;
-- 获取示例查询文本
DECLARE @sample_plan_handle VARBINARY(64);
SELECT TOP 1 @sample_plan_handle = sample_plan_handle
FROM (
SELECT query_hash, COUNT(*) AS cnt, MIN(plan_handle) AS sample_plan_handle
FROM sys.dm_exec_query_stats
GROUP BY query_hash
HAVING COUNT(*) > 10
ORDER BY COUNT(*) DESC
) t;
SELECT text
FROM sys.dm_exec_sql_text(@sample_plan_handle);
解读:
query_plan_count > 10:同一查询模式有超过10个不同的编译计划- 原因: 未使用参数化,每次提交都编译新计划
- 示例:
-- 不推荐(每次都编译新计划)
SELECT * FROM Orders WHERE OrderID = 100;
SELECT * FROM Orders WHERE OrderID = 101;
SELECT * FROM Orders WHERE OrderID = 102;
-- 推荐(参数化,重用计划)
EXEC sp_executesql N'SELECT * FROM Orders WHERE OrderID = @P1', N'@P1 int', 100;
3.3 计划缓存优化策略
1. 使用存储过程代替即席查询
CREATE PROCEDURE dbo.GetOrderByID
@OrderID INT
AS
BEGIN
SELECT * FROM Orders WHERE OrderID = @OrderID;
END
GO
-- 调用
EXEC dbo.GetOrderByID @OrderID = 100;
2. 启用强制参数化(适用于OLTP)
ALTER DATABASE YourDatabase
SET PARAMETERIZATION FORCED;
3. 使用Plan Guides固定执行计划
-- 为特定查询创建计划指南
EXEC sp_create_plan_guide
@name = N'OrderQuery_PlanGuide',
@stmt = N'SELECT * FROM Orders WHERE OrderID = @OrderID',
@type = N'SQL',
@module_or_batch = NULL,
@params = N'@OrderID INT',
@hints = N'OPTION (PARAMETERIZATION FORCED)';
四、实战DMV查询脚本集
4.1 综合诊断脚本:CPU瓶颈快速检查
-- ============================================
-- SQL Server CPU瓶颈综合诊断脚本
-- ============================================
-- 1. 获取SQL Server CPU利用率(过去10分钟平均)
DECLARE @cpu_utilization INT;
SELECT @cpu_utilization =
100 - AVG(CAST(record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS INT))
FROM (
SELECT TOP 10
CAST(record AS XML) AS record
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = 'RING_BUFFER_SCHEDULER_MONITOR'
AND record LIKE '%<SystemHealth>%'
ORDER BY timestamp DESC
) AS cpu_data;
PRINT '===========================================';
PRINT 'SQL Server CPU利用率: ' + CAST(@cpu_utilization AS VARCHAR(10)) + '%';
PRINT '===========================================';
-- 2. 检查CPU相关等待类型
PRINT '';
PRINT 'Top 10 CPU相关等待类型:';
PRINT '-------------------------------------------';
SELECT TOP 10
wait_type,
waiting_tasks_count,
wait_time_ms,
max_wait_time_ms,
signal_wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type IN ('SOS_SCHEDULER_YIELD', 'CXPACKET', 'CXCONSUMER')
ORDER BY wait_time_ms DESC;
-- 3. Top 10 CPU消耗查询
PRINT '';
PRINT 'Top 10 CPU消耗查询:';
PRINT '-------------------------------------------';
SELECT TOP 10
total_worker_time / 1000000.0 AS total_cpu_seconds,
execution_count,
total_worker_time / execution_count / 1000.0 AS avg_cpu_ms,
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
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY total_worker_time DESC;
-- 4. 当前正在执行的高CPU查询
PRINT '';
PRINT '当前正在执行的高CPU查询:';
PRINT '-------------------------------------------';
SELECT
s.session_id,
r.status,
r.cpu_time,
r.total_elapsed_time,
r.logical_reads,
r.command,
DB_NAME(r.database_id) AS database_name,
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
FROM sys.dm_exec_sessions s
INNER JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) st
WHERE r.status = 'running'
AND s.is_user_process = 1
ORDER BY r.cpu_time DESC;
-- 5. 编译和重编译统计
PRINT '';
PRINT '编译统计:';
PRINT '-------------------------------------------';
SELECT
'SQL Compilations/sec' AS counter_name,
cntr_value AS current_value
FROM sys.dm_os_performance_counters
WHERE object_name = 'SQLServer:SQL Statistics'
AND counter_name = 'SQL Compilations/sec'
UNION ALL
SELECT
'SQL Re-Compilations/sec',
cntr_value
FROM sys.dm_os_performance_counters
WHERE object_name = 'SQLServer:SQL Statistics'
AND counter_name = 'SQL Re-Compilations/sec';
4.2 使用Query Store分析CPU趋势(SQL Server 2016+)
-- 需要先启用Query Store
ALTER DATABASE YourDatabase
SET QUERY_STORE = ON;
-- 分析过去24小时CPU消耗趋势
SELECT
qsq.query_id,
OBJECT_NAME(qsq.object_id) AS object_name,
qsqt.query_sql_text,
qsp.plan_id,
qsrs.avg_cpu_time / 1000.0 AS avg_cpu_ms,
qsrs.stdev_cpu_time / 1000.0 AS stdev_cpu_ms,
qsrs.count_executions,
qsrs.last_execution_time
FROM sys.query_store_query qsq
JOIN sys.query_store_query_text qsqt ON qsq.query_text_id = qsqt.query_text_id
JOIN sys.query_store_plan qsp ON qsq.query_id = qsp.query_id
JOIN sys.query_store_runtime_stats qsrs ON qsp.plan_id = qsrs.plan_id
JOIN sys.query_store_runtime_stats_interval qsrsi ON qsrs.runtime_stats_interval_id = qsrsi.runtime_stats_interval_id
WHERE qsrsi.start_time >= DATEADD(hour, -24, GETUTCDATE())
AND qsrs.avg_cpu_time > 1000000 -- 超过1秒
ORDER BY qsrs.avg_cpu_time DESC;
五、优化建议与最佳实践
5.1 短期优化(立即可执行)
1. 识别并优化Top 10 CPU消耗查询
-
使用上述DMV脚本定位
-
分析执行计划(
SET SHOWPLAN_ALL ON或SSMS图形化计划) -
添加缺失索引
-
重写低效SQL:
- 避免隐式转换:
WHERE CONVERT(VARCHAR(10), DateColumn) = '2026-09-10'→WHERE DateColumn >= '2026-09-10' AND DateColumn < '2026-09-11' - 避免函数包裹列:
WHERE YEAR(OrderDate) = 2026→WHERE OrderDate >= '2026-01-01' AND OrderDate < '2027-01-01'
- 避免隐式转换:
2. 调整并行度配置
-- 服务器级别
EXEC sp_configure 'max degree of parallelism', 4;
RECONFIGURE;
-- 数据库级别(SQL Server 2016+)
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 4;
-- 查询级别
SELECT * FROM LargeTable
OPTION (MAXDOP 4);
MAXDOP推荐值:
- ≤8核:MAXDOP = 核心数
-
8核:MAXDOP = 8(避免过度并行)
- NUMA架构:MAXDOP = 单个NUMA节点的核心数
3. 启用Query Store(SQL Server 2016+)
ALTER DATABASE YourDatabase
SET QUERY_STORE = ON (
OPERATION_MODE = READ_WRITE,
CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),
DATA_FLUSH_INTERVAL_SECONDS = 900,
MAX_STORAGE_SIZE_MB = 1000,
INTERVAL_LENGTH_MINUTES = 60
);
5.2 中期优化(需要规划)
1. 建立性能基线
- 记录正常负载下的CPU使用率(高峰/低峰)
- 设置告警阈值:CPU > 80%持续5分钟
- 使用性能监视器数据收集器集(DCS)
2. 优化应用程序设计
- 减少即席查询: 使用存储过程或ORM的参数化查询
- 实现连接池: 避免频繁建立/销毁连接
- 批量操作: 使用
Table-Valued Parameters或BULK INSERT代替逐行插入
3. 定期维护
-- 更新统计信息(每周)
EXEC sp_updatestats;
-- 重建/重组索引(每月)
ALTER INDEX ALL ON YourTable REBUILD; -- 碎片>30%
ALTER INDEX ALL ON YourTable REORGANIZE; -- 碎片10-30%
-- 清理计划缓存(必要时)
DBCC FREEPROCCACHE; -- 谨慎使用,会导致短期性能下降
5.3 长期优化(架构层面)
1. 读写分离
- 使用Always On可用性组的只读副本
- 报表查询路由到只读副本
-- 应用程序连接字符串
Server=AGListener;Database=YourDB;ApplicationIntent=ReadOnly;
2. 垂直扩展 vs 横向扩展
- 垂直扩展: 升级CPU(更多核心、更高主频)
- 横向扩展: 分库分表、微服务化、应用层缓存(Redis)
3. 考虑新技术
-
In-Memory OLTP: 内存优化表(适用于高并发OLTP)
-
列存储索引: 分析型查询性能提升10-100倍
-
SQL Server 2022智能查询处理:
- 并行度反馈(DOP Feedback)
- 基数估计反馈(CE Feedback)
- 参数敏感计划优化(PSP)
六、SQL Server 2022智能查询处理(IQP)
6.1 并行度反馈(DOP Feedback)
自动检测降低特定查询并行度的机会,避免过度并行导致的线程争用。
启用方式:
-- 数据库兼容级别160自动启用
ALTER DATABASE YourDatabase SET COMPATIBILITY_LEVEL = 160;
6.2 基数估计反馈(CE Feedback)
自动为特定查询选择更合适的基数估计模型(CE 70 vs CE 120+)。
6.3 参数敏感计划优化(PSP)
为单个参数化查询在计划缓存中存储多个活动执行计划,每个计划针对参数空间的不同子集进行优化,解决"参数嗅探"问题。
七、总结
CPU瓶颈诊断是SQL Server性能优化的核心技能。通过性能监视器、DMV查询和Query Store的组合使用,我们可以快速定位问题根源。
关键要点:
- SQL Server 2016+新增的24个列为并行度和内存授予分析提供了前所未有的可见性
- 80%的CPU问题可以通过优化Top 10查询、调整并行度配置和启用Query Store来解决
- 过多编译通常是即席查询导致,使用参数化是最有效的解决方案
- SQL Server 2022的智能查询处理能自动解决许多传统的CPU瓶颈问题
本文提供的DMV脚本可以直接用于生产环境诊断,建议收藏备用。
系列文章预告
本文是"SQL Server性能优化实战系列"的第1篇,后续文章:
- 第2篇: SQL Server阻塞与死锁排查实战:从DMV到根因分析
- 第3篇: SQL Server内存管理深度剖析:从Buffer Pool到压力诊断
- 第4篇: SQL Server I/O性能优化:从等待类型到存储配置
- 第5篇: SQL Server扩展事件(Extended Events)完全指南
- 第6篇: tempdb性能优化完全指南:空间管理与并发争用
敬请关注!
关于作者
Master Xu,20+ 年 IT、通信、电信、医疗、制造行业经验,企业级数据库架构师。
擅长企业IT架构规划、数据库性能优化、大型复杂项目交付。
欢迎交流技术话题:
- 📧 Email: xcoolwinds@gmail.com
- 💬 微信: xcoolwinds
- 🔗 知乎: www.zhihu.com/people/da-f…
- 💻 CSDN: blog.csdn.net/xcoolwinds
开放合作: 技术咨询 | 架构顾问 | 企业内训 | 技术分享
标签
#SQL Server #性能优化 #CPU瓶颈 #DMV #数据库调优 #DBA
话题(知乎)
#SQL Server #数据库性能优化 #DBA #后端开发 #系统架构
声明: 本文所有脚本均在SQL Server 2016/2019/2022环境下测试通过,但在生产环境使用前请先在测试环境验证。
版权: 本文为原创文章,转载请注明出处。
发布日期:2026-09-10 最后更新:2026-09-10