解决 SQL Server 中 CPU 使用率过高的问题
Tags： CPUCPU使用率CPU很高MSSQLSQL Server高
本文提供了诊断和修复运行 Microsoft SQL Server 的计算机上 CPU 使用率过高导致的问题的过程。 尽管在 SQL Server 中出现 CPU 使用率过高有许多可能原因，但以下原因最为常见：
- 由于以下情况，表或索引扫描导致的高逻辑读取：
- 过期统计信息
- 缺少索引
- 参数敏感计划 (PSP) 问题
- 设计不佳的查询
- 工作负荷增加
可以使用以下步骤来解决 SQL Server 中 CPU 使用率过高的问题。
步骤 1：验证 SQL Server 是否导致 CPU 使用率过高
使用以下工具之一检查 SQL Server 进程是否确实导致 CPU 使用率过高：
任务管理器：在“进程”选项卡上，检查“64 位版本的 SQL Server Windows NT”的“CPU”列的值是否接近 100%。
性能和资源监视器 (perfmon)
- 计数器：
Process/%User Time,% Privileged Time - 实例：sqlservr
- 计数器：
可以使用以下 PowerShell 脚本在 60 秒的跨度内收集计数器数据：
PowerShell复制
1234567891011121314$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 = $_.TimeStampPath = $_.PathValue = ([Math]::Round($_.CookedValue, 3))}Start-Sleep -s 2}}
如果 % User Time 始终大于 90% (% 用户时间是每个处理器上的处理器时间总和，则其最大值为 100% * (没有 CPU) ) ，则SQL Server进程会导致 CPU 使用率过高。 但是，如果 % Privileged time 始终大于 90%，则是防病毒软件、其他驱动程序或计算机上的其他操作系统组件导致 CPU 使用率过高。 应与系统管理员合作，分析此行为的根本原因。
步骤 2：确定影响 CPU 使用率的查询
如果 Sqlservr.exe 进程导致 CPU 使用率过高，则最常见的原因是执行表或索引扫描的 SQL Server 查询，其次是排序、哈希操作和循环 (嵌套循环运算符或 WHILE (T-SQL) ) 。 要了解查询当前在总 CPU 使用率中的占比，请运行以下语句：
1 2 3 4 5 6 7 8 9 10 11 12 | DECLARE @init_sum_cpu_time int, @utilizedCpuCount int --get CPU count used by SQL Server SELECT @utilizedCpuCount = COUNT( * ) FROM sys.dm_os_schedulers WHERE status = 'VISIBLE ONLINE' --calculate the CPU usage by queries OVER a 5 sec interval 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 |
若要确定当前负责高 CPU 活动的查询，请运行以下语句：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | SELECT TOP 10 s.session_id, r.status, r.cpu_time, r.logical_reads, r.reads, r.writes, r.total_elapsed_time / (1000 * 60) '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，可以运行以下语句来查找历史占用大量 CPU 的查询：
1 2 3 4 5 6 7 8 9 10 | SELECT TOP 10 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 |
步骤 3：更新统计信息
在确定 CPU 占用最高的查询后，请更新这些查询使用的表的“更新统计信息” 。 可以使用 sp_updatestats 系统存储过程更新当前数据库中所有用户定义表和内部表的统计信息。 例如：
1 | exec sp_updatestats |
备注
sp_updatestats系统存储过程针对当前数据库中的所有用户定义表和内部表运行UPDATE STATISTICS。 对于定期维护，请确保定期计划维护使统计信息保持最新。 使用“自适应索引碎片整理”等解决方案自动管理一个或多个数据库的索引碎片整理和统计信息更新。 此过程自动选择是根据索引的碎片级别和其他参数重新生成还是重新组织索引，并使用线性阈值更新统计信息。
有关 sp_updatestats 的详细信息，请参阅 sp_updatestats。
如果 SQL Server 的 CPU 占用仍然过高，请前往下一步。
步骤 4：添加缺失索引
缺少索引可能导致运行速度较慢的查询和 CPU 使用率过高。 可以识别缺失的索引并创建这些索引，以改善这种性能影响。
运行以下查询以识别导致 CPU 使用率高且在查询计划中至少包含一个缺失索引的查询：
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！
