解决 SQL Server 中 CPU 使用率过高的问题 - 微软文档_sql server cpu 100%

原文 : 微软文档
本文提供了诊断和修复运行 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 秒的跨度内收集计数器数据：

$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
3
4
5
6
7
8
9
10
11
12
13
14

如果 % User Time 始终大于 90% (% 用户时间是每个处理器上的处理器时间总和，则其最大值为 100% * (没有 CPU) ) ，则SQL Server进程会导致 CPU 使用率过高。 但是，如果 % Privileged time 始终大于 90%，则是防病毒软件、其他驱动程序或计算机上的其他操作系统组件导致 CPU 使用率过高。 应与系统管理员合作，分析此行为的根本原因。
步骤 2：确定影响 CPU 使用率的查询
如果 Sqlservr.exe 进程导致 CPU 使用率过高，则最常见的原因是执行表或索引扫描的 SQL Server 查询，其次是排序、哈希操作和循环 (嵌套循环运算符或 WHILE (T-SQL) ) 。 要了解查询当前在总 CPU 使用率中的占比，请运行以下语句：

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
1
2
3
4
5
6
7
8
9
10
11
12

若要确定当前负责高 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) '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
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

如果查询目前未驱动 CPU，可以运行以下语句来查找历史占用大量 CPU 的查询：

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
1
2
3
4
5
6
7
8
9
10

步骤 3：更新统计信息

在确定 CPU 占用最高的查询后，请更新这些查询使用的表的“更新统计信息” 。 可以使用 sp_updatestats 系统存储过程更新当前数据库中所有用户定义表和内部表的统计信息。 例如：

exec sp_updatestats
1