合解决 SQL Server 中 CPU 使用率过高的问题

发布日期 2023年2月16日 · 已更新 2024年7月16日

0 260 2

Tags： MSSQL SQL Server CPU CPU很高 CPU使用率高

👉 本文共约6727个字，系统预计阅读时间或需26分钟。

简介
步骤 1：验证 SQL Server 是否导致 CPU 使用率过高
步骤 2：确定影响 CPU 使用率的查询
步骤 3：更新统计信息
步骤 4：添加缺失索引
步骤 5：调查并解决参数敏感问题
步骤 6：调查并解决 SARGability 问题
步骤 7：禁用重度跟踪
步骤 8：修复 SOS_CACHESTORE 旋转锁争用
步骤 9：配置虚拟机
步骤 10：纵向扩展系统以使用更多 CPU
另请参阅
使用SQL Profiler监控
总结
参考

简介

本文提供了诊断和修复运行 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
  }
}

$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

}

如果 % 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

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 活动的查询，请运行以下语句：

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

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 的查询：

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

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 系统存储过程更新当前数据库中所有用户定义表和内部表的统计信息。例如：

exec sp_updatestats

1	exec sp_updatestats

备注

sp_updatestats系统存储过程针对当前数据库中的所有用户定义表和内部表运行UPDATE STATISTICS。对于定期维护，请确保定期计划维护使统计信息保持最新。使用“自适应索引碎片整理”等解决方案自动管理一个或多个数据库的索引碎片整理和统计信息更新。此过程自动选择是根据索引的碎片级别和其他参数重新生成还是重新组织索引，并使用线性阈值更新统计信息。

有关 sp_updatestats 的详细信息，请参阅 sp_updatestats。

如果 SQL Server 的 CPU 占用仍然过高，请前往下一步。

步骤 4：添加缺失索引

缺少索引可能导致运行速度较慢的查询和 CPU 使用率过高。可以识别缺失的索引并创建这些索引，以改善这种性能影响。

运行以下查询以识别导致 CPU 使用率高且在查询计划中至少包含一个缺失索引的查询：

-- Captures the Total CPU time spent by a query along with the query plan and total executions
SELECT
   qs_cpu.total_worker_time / 1000 AS total_cpu_time_ms,
   q.[text],
   p.query_plan,
   qs_cpu.execution_count,
   q.dbid,
   q.objectid,
   q.encrypted AS text_encrypted
FROM
   (SELECT TOP 500 qs.plan_handle,
    qs.total_worker_time,
    qs.execution_count FROM sys.dm_exec_query_stats qs ORDER BY qs.total_worker_time DESC) AS qs_cpu
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS q
CROSS APPLY sys.dm_exec_query_plan(plan_handle) p
WHERE p.query_plan.exist('declare namespace 
       qplan = "http://schemas.microsoft.com/sqlserver/2004/07/showplan";
       //qplan:MissingIndexes')=1

-- Captures the Total CPU time spent by a query along with the query plan and total executions

SELECT

qs_cpu.total_worker_time / 1000 AS total_cpu_time_ms,

q.[text],

p.query_plan,

qs_cpu.execution_count,

q.dbid,

q.objectid,

q.encrypted AS text_encrypted

FROM

(SELECT TOP 500 qs.plan_handle,

qs.total_worker_time,

qs.execution_count FROM sys.dm_exec_query_stats qs ORDER BY qs.total_worker_time DESC) AS qs_cpu

CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS q

CROSS APPLY sys.dm_exec_query_plan(plan_handle) p

WHERE p.query_plan.exist('declare namespace

qplan = "http://schemas.microsoft.com/sqlserver/2004/07/showplan";

//qplan:MissingIndexes')=1

查看已标识查询的执行计划，并通过进行所需的更改来优化查询。以下屏幕截图显示了一个示例，其中 SQL Server 将指出查询的缺失索引。右键单击查询计划的“缺失索引”部分，然后选择“缺少索引详细信息”，在 SQL Server Management Studio 的另一个窗口中创建索引。

使用以下查询检查是否缺少索引，并应用具有高改进度量值的任何建议索引。从输出中具有最高 improvement_measure 值的前 5 或 10 条建议开始。这些索引对性能有最显著的积极影响。确定是否要应用这些索引，并确保对应用程序进行了性能测试。然后，继续应用缺失索引建议，直到获得所需的应用程序性能结果。有关本主题的详细信息，请参阅优化包含缺失索引建议的非群集索引。

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

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

步骤 5：调查并解决参数敏感问题

可以使用 DBCC FREEPROCCACHE 命令释放计划缓存，并检查这是否解决了 CPU 使用率过高的问题。如果问题已修复，则表示是参数敏感问题（PSP，也称为“参数探查问题”）。

备注

使用不带参数的 DBCC FREEPROCCACHE 将从计划缓存中删除所有已编译的计划。这将导致再次编译新的查询执行，从而导致每个新查询的持续时间一次性延长。最佳方法是使用 DBCC FREEPROCCACHE ( plan_handle | sql_handle ) 来识别导致问题的查询，然后解决单个查询或有问题的查询。

要解决此参数敏感问题，请使用以下方法。每种方法都有相应的利弊。

使用 RECOMPILE 查询提示。可以向步骤 2 中标识的一个或多个 CPU 过高查询添加 RECOMPILE 查询提示。此提示将使编译 CPU 使用率略微增加，有助于在与每个查询执行的更佳性能之间达到平衡。有关详细信息，请参阅参数和执行计划重用、参数敏感度和 RECOMPILE 查询提示。
下面是如何将此提示应用到查询的示例。
SELECT * FROM Person.Person WHERE LastName = 'Wood' OPTION (RECOMPILE)
1
2
3
SELECT * FROM Person.Person
WHERE LastName = 'Wood'
OPTION (RECOMPILE)
使用 OPTIMIZE FOR 查询提示，使用包含数据中大多数值的更典型的参数值覆盖实际参数值。此选项需要充分了解最佳参数值和关联的计划特征。下面是如何在查询中使用此提示的示例。
DECLARE @LastName Name = 'Frintu' SELECT FirstName, LastName FROM Person.Person WHERE LastName = @LastName OPTION (OPTIMIZE FOR (@LastName = 'Wood'))
1
2
3
4
DECLARE @LastName Name = 'Frintu'
SELECT FirstName, LastName FROM Person.Person
WHERE LastName = @LastName
OPTION (OPTIMIZE FOR (@LastName = 'Wood'))
使用优化未知查询提示，使用密度向量平均值覆盖实际参数值。也可以通过捕获本地变量中的传入参数值，然后使用谓词中的本地变量而不是使用参数本身来执行此操作。对于此修复，平均密度可能足以提供可接受的性能。
使用 DISABLE_PARAMETER_SNIFFING 查询提示，完全禁用参数探查。下面是如何在查询中使用它的示例：
SELECT * FROM Person.Address WHERE City = 'SEATTLE' AND PostalCode = 98104 OPTION (USE HINT ('DISABLE_PARAMETER_SNIFFING'))
1
2
3
SELECT * FROM Person.Address
WHERE City = 'SEATTLE' AND PostalCode = 98104
OPTION (USE HINT ('DISABLE_PARAMETER_SNIFFING'))
使用 KEEPFIXED PLAN 查询提示，阻止在缓存中重新编译。此解决方法假定“足够好”的常见计划是已在缓存中的计划。还可以禁用自动统计信息更新，以减少将好计划逐出并编译新的错误计划的可能性。
将 DBCC FREEPROCCACHE 命令用作临时解决方案，直到应用程序代码修复为止。可以使用 DBCC FREEPROCCACHE (plan_handle) 命令仅删除导致问题的计划。例如，若要查找引用 AdventureWorks 中 Person.Person 表的查询计划，可以使用此查询查找查询句柄。然后，可以使用查询结果第二列中生成的 DBCC FREEPROCCACHE (plan_handle)，从缓存中释放特定查询计划。
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
后续精彩内容已被站长无情隐藏，请输入验证码解锁本文！
验证码：
获取验证码：请先关注本站微信公众号，然后回复“验证码”，获取验证码。在微信里搜索“AiDBA”或者“dbaup6”或者微信扫描右侧二维码都可以关注本站微信公众号。
相关文章
打赏赞(2)分享

标签： MSSQL SQL Server CPU CPU很高 CPU使用率高

小麦苗

学习或考证，均可联系麦老师，请加微信db_bao或QQ646634621

发表回复取消回复

要发表评论，您必须先登录。

1、声明：本网站有部分文章整合或转载自网络，文章著作权归属原作者或原团队；若有侵权，深表歉意，请联系本站站长进行处理，谢谢！！！
2、本站提供数据库的培训和考证业务，培训包含但不限于Oracle、MySQL、PostgreSQL、SQL Server、国产数据库等，考证包括但不限于Oracle OCP、Oracle OCM、MySQL OCP、PGCA、PGCE、PGCM等，私聊QQ646634621或微信dbaup66，非诚勿扰，谢谢！！！
3、麦老师新建了《西安DBA数据架构师》微信群，有志同道合的朋友可以私聊我入群，我们一起聊技术，聊生活，空了可以喝酒约饭。
4、为了方便大家更有针对性的讨论学习数据库，故新建数据库分类群，包括Oracle群、MySQL群、SQL Server群、PG&GP群、国产&信创群。志同道合的朋友可以入群聊技术聊人生，捣乱的勿进，喷子勿进，极端人士勿进，谢谢。

合解决 SQL Server 中 CPU 使用率过高的问题

简介

步骤 1：验证 SQL Server 是否导致 CPU 使用率过高

步骤 2：确定影响 CPU 使用率的查询

步骤 3：更新统计信息

步骤 4：添加缺失索引

步骤 5：调查并解决参数敏感问题

相关文章

您可能还喜欢...

发表回复取消回复

网站公告

网站寄语

本站其它工具

搜索本网站

标签云☁

网站日历

网站归档

网站分类

2024 年 11 月
一	二	三	四	五	六	日
				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	26	27	28	29	30

合 解决 SQL Server 中 CPU 使用率过高的问题

简介

步骤 1：验证 SQL Server 是否导致 CPU 使用率过高

步骤 2：确定影响 CPU 使用率的查询

步骤 3：更新统计信息

步骤 4：添加缺失索引

步骤 5：调查并解决参数敏感问题

相关文章

您可能还喜欢...

SQL Server性能监视和优化工具

MSSQL查询表报错“SQL Server 检测到基于一致性的逻辑 I/O 错误 校验和不正确”

在Docker中快速使用SQL Server 2022环境

发表回复 取消回复

网站公告

网站寄语

本站其它工具

搜索本网站

标签云☁

网站日历

网站归档

网站分类

合解决 SQL Server 中 CPU 使用率过高的问题

MSSQL查询表报错“SQL Server 检测到基于一致性的逻辑 I/O 错误校验和不正确”

发表回复取消回复