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

简介

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

  $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 使用率中的占比，请运行以下语句：

若要确定当前负责高 CPU 活动的查询，请运行以下语句：

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

步骤 3：更新统计信息

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

备注

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

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

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

步骤 4：添加缺失索引

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

1. 运行以下查询以识别导致 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

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

   -- 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

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

   

3. 使用以下查询检查是否缺少索引，并应用具有高改进度量值的任何建议索引。 从输出中具有最高 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

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

   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”或者微信扫描右侧二维码都可以关注本站微信公众号。

  相关文章

  • sqlcmd备份MSSQL数据库报错 SSL Provider: [error:1416F086:SSL routines:tls_process_server_certificate:certificate verify failed:self signed certificate]
  • MSSQL中的幽灵数据删除进程、虚影清除进程（ghost cleanup）
  • SSMS配置错误，导致 SELECT 失败，因为下列 SET 选项的设置不正确: 'QUOTED_IDENTIFIER, ANSI_PADDING'。请确保 SET 选项正确无误，可以用于 计算列上的索引视图和/或索引和/或筛选的索引和/或查询通知和/或 XML 数据类型方法和/或空间索引操作。。
  • MSSQL删除tempdb的数据文件
  • MSSQL中的等待事件SQLTRACE_FILE_BUFFER说明
  • MSSQL的数据库快照
  • MSSQL 2014新功能之延迟事务持续性(Delayed Transaction Durability)--延迟提交
  • MSSQL中的实时查询统计
  • MSSQL中查询索引的创建时间和删除时间
  • MSSQL中查询依赖关系
  • MSSQL的ldf日志文件丢失, 如何仅用mdf文件恢复数据库呢
  • MSSQL移动数据库文件（分离和附加）
  • Windows的资源监视器中进程对磁盘的响应时间多少毫秒即为延迟比较厉害影响磁盘性能IO
  • MSSQL扩展事件之system_health会话
  • MSSQL的创建索引占用的内存
  • MSSQL 2014新功能之缓存池扩展BPE (Buffer Pool Extension)
  • MSSQL中的锁和阻塞
  • MSSQL中的数据库兼容性级别
  • MSSQL中的索引填充因子（fill factor）
  • MSSQL服务主密钥
  打赏赞(2)分享