优化 SQL Server CPU 性能

原文：http://technet.microsoft.com/zh-cn/magazine/2007.10.sqlcpu.aspx

概览:

• 数据库性能问题故障排除
• 检查硬件原因
• 使用 PerfMon 跟踪数据库瓶颈
• 评估查询性能

解决数据库系统的性能问题可能是一项艰巨的任务。了解如何找到问题很重要，但是了解系统对特定请求作出特定反应的原因更加重要。影响数据库服务器上的 CPU 利用率

的因素有很多：SQL 语句的编译和重新编译、缺少索引、多线程操作、磁盘瓶颈、内存瓶颈、日常维护以及抽取、转换和装载 (ETL) 活动和其他因素。利用 CPU 本身并不是一件坏事情，执行任务是 CPU 的职责所在。CPU 利用率正常的关键是确保 CPU 处理您需要它处理的任务，而不是将循环浪费在不良优化的代码或缓慢的硬件上。

达到同一目的的两种途径

概括来讲，有两种途径可以确定 CPU 的性能问题。第一种途径是检查系统的硬件性能，这种做法有助于继续确定使用第二种途径 — 检查服务器的查询效率时从何处入手。第二种途径在确定 SQL Server™ 性能问题时通常更有效。然而，除非您确切知道查询性能问题的具体所在，否则，应该始终从系统性能评估开始着手。最后，通常是这两种途径齐头并进。让我们先了解一些基础知识，以便我们研究这两种途径。

了解基础知识

超线程

超线程影响 SQL Server 的方式使超线程成为一个非常值得讨论的主题。超线程实际上代表每个物理处理器的两个操作系统逻辑处理器。超线程实质上利用物理处理器上的闲置时间，以便每个处理器得到更充分的利用。Intel 网站 ( intel.com/technology/platform-technology/hyper-threading/index.htm) 对超线程的工作原理进行了更为全面的介绍。

对于 SQL Server 系统，DBMS 实际上处理自己的极其有效的操作系统队列和线程，因此，超线程仅在 CPU 利用率已经很高、系统上的物理 CPU 超载的情况下使用。当 SQL Server 在多个计划程序上对执行任务请求进行排队时，实际上操作系统必须在物理处理器上来回切换线程的上下文，以满足不断发出的请求，即使同一物理处理器上存在两个逻辑处理器也不例外。如果每个物理处理器的 Context Switches/sec 高于 5000，我们强烈建议您考虑关闭系统上的超线程并重新测试性能。

在 SQL Server 上出现高 CPU 利用率时应用程序极少能有效地使用超线程。在生产系统上实施更改之前，必须在超线程启动和关闭这两种情况下针对 SQL Server 测试应用程序。

高端双核处理器肯定会优于计算机中的 RAM，而后者又比附加的存储设备快。一个好的 CPU 可以处理的吞吐量大约是当前顶尖 DDR2 内存的六倍，大约是顶尖 DDR3 内存的两倍。典型内存吞吐量是最快的光纤信道驱动器的 10 倍以上。这样，硬盘只能执行次数有限的 IOPS（每秒的输入/输出操作），该值完全受驱动器每秒可以执行的寻道次数限制。公平地讲，仅使用一个存储驱动器处理企业数据库系统的所有存储需求的情况并不常见。目前，大多数组织在企业数据库服务器或更大的 RAID 组（消除或最大程度地减小磁盘 I/O 处理器问题）上利用存储区域网络 (SAN)。最重要的是，无论您的组织规模如何，磁盘瓶颈和内存瓶颈都会影响处理器的性能。

由于 I/O 速度不同，从磁盘中检索数据的开销会远远大于从内存中检索数据的开销。SQL Server 中的一个数据页为 8KB。SQL Server 中的一个扩展由八个 8KB 页组成，即该扩展的大小等于 64KB。了解这一点非常重要，因为当 SQL Server 从磁盘请求特定数据页时，它不仅仅检索该数据页，还会检索该数据页驻留的整个扩展。实际上，存在各种使 SQL Server 更经济高效的因素，但是我在此不再详细阐述。在性能最佳的状态下，从缓冲池中提取已经缓存的数据页花费的时间应该不到半毫秒；在最佳环境下，从磁盘检索单个扩展会花费 2 至 4 毫秒的时间。通常，我认为从性能良好、状态正常的磁盘子系统读取数据应花费 4 至 10 毫秒。从内存检索数据页通常比从磁盘提取数据快 4 至 20 倍。

当 SQL Server 请求某个数据页时，它会在从磁盘子系统上查找此数据页之前检查内存中的缓冲区高速缓存。如果该数据页位于缓冲池中，则处理器将检索数据，然后执行请求的任务。这称为软页面错误。软页面错误是 SQL Server 的理想选择，因为作为请求的一部分检索的数据必须位于缓冲区高速缓存中才能使用。在缓冲区高速缓存中找不到的数据页必须从服务器的磁盘子系统中检索。当操作系统必须从磁盘检索数据页时，这称为硬页面错误。

使内存性能、磁盘性能和 CPU 性能相互关联时，一个通用标准可以帮助我们看清所有问题：吞吐量。从不太专业的角度而言，吞吐量是对可以填满有限管道的数据量的测量结果。

途径 1：系统性能

实际上，用于确定服务器是否遇到 CPU 瓶颈的方法只有几种，而且导致高 CPU 利用率的潜在因素并不多。其中的部分问题可以使用 PerfMon 或类似的系统监视工具进行跟踪，而其他问题可以使用 SQL Profiler 或类似的工具进行跟踪。另一种方法是通过查询分析器或 SQL Server Management Studio (SSMS) 使用 SQL 命令。

评估系统性能时我使用的基本原理是“先广泛开展调查，然后集中深入研究”。显然，只有确定问题范围之后，才能集中进行深入研究。使用像 PerfMon 这样的工具评估整体 CPU 利用率之后，您可以使用该工具查看两个非常简单、易于理解的性能计数器。

最熟悉的性能计数器之一是 % Processor Time；在 PerfMon 中，打开“添加计数器”窗口时就会突出显示该计数器。% Processor Time 是处理器忙于执行任务的时间量。当大多数高峰操作时间此值为 80％ 或更高时，通常认为处理器的利用率很高。即使服务器的利用率不到 80％，通常您也会看到峰值高达 100％，您应该会预见到这种情况。

您应该查看的另一个计数器是 Processor Queue Length，在 PerfMon 中的“系统性能”对象下可以找到该计数器。Processor Queue Length 显示正在等待在 CPU 上执行任务的线程数。SQL Server 通过数据库引擎中的计划程序管理其任务，在计划程序中对请求进行排队和处理。由于 SQL Server 管理自己的任务，对于每个逻辑处理器它将只利用单个 CPU 线程。这意味着在专用于 SQL Server 的系统上，正在处理器队列中等待执行任务的线程数应该最少。通常，在专用的 SQL Server 上，线程数不应超过物理处理器数的五倍，但是我认为超过两倍就会出现问题。在 DBMS 与其他应用程序共享系统的服务器上，除了此计数器外，还需要查看 % Processor Time 和 Context Switches/sec 性能计数器（稍后我将简要介绍一下上下文切换），从而确定是否需要将其他应用程序或 DBMS 移至其他服务器。

了解处理器队列和高 CPU 利用率后，下面我们查看 SQL Server:SQL Statistic 性能对象下的 Compilations/sec 和 Re-Compilations/sec 计数器（请参阅 图 1）。编译和重新编译查询计划增加了系统的 CPU 利用率。您应该可以看到 Re-Compilations 的值接近于零，但是观察系统内的趋势可以确定服务器的通常行为以及正常的编译次数。不可能始终避免重新编译，但是可优化查询和存储过程以在最大程度上减少重新编译，并重新使用查询计划。通过 Batch Requests/sec（也可以在 SQL Server:SQL Statistic 性能对象中找到）将这些值与进入系统的实际 SQL 语句进行比较。如果每秒的编译和重新编译在进入系统的批请求中占很高比例，则说明应该检查这一方面。在某些情况下，SQL 开发人员也许并不了解他们的代码对这些类型的系统资源问题有什么影响以及为什么会有影响。在下文中，我将提供一些参考资料，帮助您在最大程度上减少这种行为。

图 1  选择用于监视的计数器 (单击该图像获得较大视图)

在 PerfMon 中，请检查名为 Context Switches/sec 的性能计数器（参阅 图 2）。此计数器显示为了为其他等待线程执行任务必须从操作系统计划程序（而非 SQL 计划程序）中取出线程的次数。对于与其他应用程序（如 IIS）或其他供应商应用程序服务器组件共享的数据库系统，上下文切换可能更加频繁。我使用的 Context Switches/sec 阈值大约是服务器中处理器数量的 5000 倍。在启用了超线程并且对 CPU 的利用率达到中高程度的系统上，该阈值还可更高。如果 CPU 利用率和上下文切换经常同时超出各自的阈值，这就表明出现了 CPU 瓶颈。如果经常发生这种情况，并且您的系统已过时，则应该开始计划购买更多或处理速度更快的 CPU。有关详细信息，请参阅侧栏上的“超线程”。

 Figure 2 需要注意的性能计数器

性能计数器	计数器对象	阈值	注释
% Processor Time	处理器	> 80%	潜在因素包括内存不足、低查询计划重用率和未经优化的查询。
Context Switches/sec	系统	> 5000 x 处理器数	潜在因素包括服务器上的其他应用程序、在同一服务器上运行了多个 SQL Server 实例和超线程已打开。
Processor Queue Length	系统	> 5 x 处理器数	潜在因素包括服务器上的其他应用程序、频繁的编译或重新编译以及在同一服务器上运行了多个 SQL Server 实例。
Compilations/sec	SQLServer:SQL Statistics	趋势	与 Batch Requests/sec 进行比较。
Re-Compilations/sec	SQLServer:SQL Statistics	趋势	与 Batch Requests/sec 进行比较。
Batch Request/sec	SQLServer:SQL Statistics	趋势	与每秒编译和重新编译数进行比较。
Page Life Expectancy	SQLServer:Buffer Manager	< 300	内存不足的潜在因素。
Lazy Writes/sec	SQLServer:Buffer Manager	趋势	大量数据缓存刷新或内存不足的潜在因素。
Checkpoints/sec	SQLServer:Buffer Manager	趋势	根据 PLE 和 Lazy Writes/sec 评估检查点。
Cache Hit Ratio:SQL Plans	SQLServer:Plan Cache	< 70%	表示计划重用率低。
Buffer Cache Hit Ratio	SQLServer:Buffer Manager	< 97%	内存不足的潜在因素。

当 CPU 的利用率很高时，还需要监视 SQL Server Lazy Writer（在 SQL Server 2000 中）或 Resource Monitor（在 SQL Server 2005 中）。通过称为 Resource Monitor 的资源线程刷新缓冲区和过程缓存可增加 CPU 时间。Resource Monitor 是一个 SQL Server 进程，它确定要保留的页以及需要从缓冲池刷新到磁盘的页。缓冲区和过程缓存中的每一个页最初都分配了成本，成本代表将该页放置到缓存时所耗费的资源。Resource Monitor 每次扫描该页，该成本值都会减少。当请求要求提供缓存空间时，系统会根据与每个页关联的成本刷新这些页；值最低的页将首先被刷新。Resource Monitor 的活动可通过 PerfMon 中 SQL Server:Buffer Manager 对象下的 Lazy Writes/sec 性能计数器进行跟踪。您应该跟踪查看该值的趋势，以确定系统上的典型阈值。该计数器通常与 Page Life Expectancy 和 Checkpoints/sec 计数器一起查看，以确定是否内存不足。

Page Life Expectancy (PLE) 计数器帮助确定是否内存不足。PLE 计数器显示数据页在缓冲区高速缓存中停留的时间。行业中该计数器的可接受阈值为 300 秒。如果在很长一段时间内显示的值平均小于 300 秒，则表明从内存中刷新数据页的频率过高。如果出现这种情况，将导致 Resource Monitor 负载加重，从而导致处理器的活动增多。应该将 PLE 计数器和 Checkpoints Pages/sec 计数器一起进行评估。在系统中出现检查点时，缓冲区高速缓存中的脏数据页被刷新到磁盘，从而导致 PLE 值下降。Resource Monitor 进程是真正将这些页刷新到磁盘的机制，所以在出现这些检查点期间，您应该还会看到 Lazy Writes/sec 值增加。如果完成检查点后 PLE 值立即增加，您可以忽略这种短暂现象。另一方面，如果发现经常低于 PLE 阈值，则此时非常适合使用多出的内存缓解您的问题，同时将一些资源释放回 CPU。所有这些计数器都可在 SQL Server:Buffer Manager 性能对象中找到。

途径 2：查询性能

SP 跟踪

跟踪 SQL Server 应用程序时，应该先熟悉用于跟踪的存储过程。使用 GUI 界面 (SQL Server Profiler) 进行跟踪可将系统负载增加 15％ 到 25％。如果可以在跟踪过程中利用存储过程，则对系统产生的负载可减少大约一半。

当我知道系统在某个地方发生瓶颈，并且想确定哪些 SQL 语句导致服务器出现问题时，我运行以下查询。这个查询帮助我了解各个语句及其当前正在使用的资源，以及需要对其进行检查以改进性能的语句。有关 SQL 跟踪的详细信息，请参阅 msdn2.microsoft.com/ms191006.aspx。

SELECT 
    substring(text,qs.statement_start_offset/2
        ,(CASE    
            WHEN qs.statement_end_offset = -1 THEN len(convert(nvarchar(max), text)) * 2 
            ELSE qs.statement_end_offset 
        END - qs.statement_start_offset)/2) 
    ,qs.plan_generation_num as recompiles
    ,qs.execution_count as execution_count
    ,qs.total_elapsed_time - qs.total_worker_time as total_wait_time
    ,qs.total_worker_time as cpu_time
    ,qs.total_logical_reads as reads
    ,qs.total_logical_writes as writes
FROM sys.dm_exec_query_stats qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
    LEFT JOIN sys.dm_exec_requests r 
        ON qs.sql_handle = r.sql_handle
ORDER BY 3 DESC

向 SQL Server 提交新查询后，会对查询计划进行评估、优化、编译，并将其放置到过程缓存中。每次向服务器提交查询，都会检查过程缓存以尝试使查询计划与请求匹配。如果找不到，则 SQL Server 将为其创建新计划，此操作可能会造成很大的开销。

T-SQL CPU 优化应注意以下几个方面：

• 查询计划重用
• 减少编译和重新编译
• 排序操作
• 不适当联接
• 缺少索引
• 表/索引扫描
• SELECT 和 WHERE 子句中的函数使用情况
• 多线程操作

我们来更详细地了解一下。SQL Server 经常从内存和磁盘中提取数据，所以仅使用单一数据页的情况很少见。在大多数情况下，应用程序的多个部分会对一项记录进行操作、运行多个较小的查询，或者联接表以提供相关数据的完整视图。在 OLAP 环境中，应用程序可能会从一个或两个表中提取成千上百行，以便您合并、累积或汇总数据以生成地区销售报表。在上述情况下，如果数据存在于内存中，返回数据的速度可以毫秒计算，但是如果从磁盘而非 RAM 上检索相同数据，就要以分钟计算。

第一个示例是存在大量事务的情况，并且计划重用情况取决于应用程序。低计划重用造成对 SQL 语句的大量编译，从而导致大量 CPU 处理开销。在第二个示例中，由于必须经常从缓冲区高速缓存刷新数据，以便为大量新数据页留出空间，所以大量使用系统资源可导致系统的 CPU 过度活跃。

假设存在一个高事务性的系统，在该系统中，为了检索装运箱的相关信息，下面显示的 SQL 语句在 15 分钟内执行了 2000 次。如果没有查询计划重用，可能每个语句大约 450 毫秒就要执行一次。如果在第一次执行后使用相同的查询计划，则每个后继查询可以在大约 2 毫秒内完成，从而使整体执行时间减少到大约 5 秒钟。

USE SHIPPING_DIST01;
SELECT 
    Container_ID
    ,Carton_ID
    ,Product_ID
    ,ProductCount
    ,ModifiedDate
FROM Container.Carton
WHERE Carton_ID = 982350144;

查询计划重用对于实现存在大量事务的系统的最佳性能至关重要，并且大多数情况下是通过参数化查询或存储过程实现的。以下是有关查询计划重用信息的一些有用资源：

• SQL Server 2005 中的批处理编译、重新编译和计划缓存问题 (microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx)
• 优化 SQL Server 存储过程以避免重新编译 (sql-server-performance.com/rd_optimizing_sp_recompiles.asp)
• SQL Server 2000 中的查询重新编译 (msdn2.microsoft.com/aa902682.aspx)

SQL Server 2005 动态管理视图 (DMV) 中包含大量信息，很有帮助。当 CPU 利用率很高时，我使用几个 DMV 帮助我确定对 CPU 的利用是否合理。

我查看的一个 DMV 是 sys.dm_os_wait_stats，它用于为 DBA 提供一种确定 SQL Server 使用的每种资源类型或函数的方法，并测量由于该资源导致的系统等待时间。此 DMV 中的计数器具有累积性。这意味着为了清楚地了解哪些资源会影响系统的不同领域，在查看任何未解决的问题的数据之后，首先您必须发出 DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR) 命令以重置所有的计数器。Sys.dm_os_wait_stats DMV 与 SQL Server 2000 中的数据库一致性检查命令 DBCC SQLPERF(WAITSTATS) 等效。您可以在 msdn2.microsoft.com/ ms179984.aspx 上的 SQL Server 联机丛书中找到有关不同等待类型的更多信息。

应该了解，即使在一切都以最优方式运行时，系统出现等待也是很平常的。您只需确定等待是否受 CPU 瓶颈影响。信号等待相对于全部等待时间来说，应该尽可能短。特殊资源等待处理器资源的时间可以直接由总等待时间减去信号等待时间来确定；此值不应该大于总等待时间的大约 20％。

sys.dm_exec_sessions DMV 显示 SQL Server 上所有打开的会话。此 DMV 提供了每个会话的性能以及每个会话启动后执行的所有工作的高级视图。这包括会话等待的总时间、CPU 使用总量、内存使用情况以及读取和写入计数。该 DMV 还将为您提供登录、登录时间、主机和会话最后一次发出 SQL Server 请求的时间。

使用 sys.dm_exec_sessions DMV，您能够确定的只是活动会话，因此，如果看到 CPU 使用率很高，应该先查看此视图。首先查看具有高 CPU 计数的会话。确定一直执行此任务的应用程序和用户，然后开始对其深入了解。将 sys.dm_exec_sessions 与 sys.dm_exec_requests DMV 配对使用可以提供通过 sp_who 和 sp_who2 存储过程获得的大量信息。如果通过 sql_handle 列将此数据与 sys.exec_sql_text 动态管理函数 (DMF) 结合在一起，那么可以获得会话当前运行的查询。 图 3 中的代码段显示了如何同时提取此数据以帮助确定某个服务器上当前的情形。

 Figure 3 确定服务器活动

SELECT es.session_id
    ,es.program_name
    ,es.login_name
    ,es.nt_user_name
    ,es.login_time
    ,es.host_name
    ,es.cpu_time
    ,es.total_scheduled_time
    ,es.total_elapsed_time
    ,es.memory_usage
    ,es.logical_reads
    ,es.reads
    ,es.writes
    ,st.text
FROM sys.dm_exec_sessions es
    LEFT JOIN sys.dm_exec_connections ec 
        ON es.session_id = ec.session_id
    LEFT JOIN sys.dm_exec_requests er
        ON es.session_id = er.session_id
    OUTER APPLY sys.dm_exec_sql_text (er.sql_handle) st
WHERE es.session_id > 50    -- < 50 system sessions
ORDER BY es.cpu_time DESC

我发现此语句有助于确定需要重点了解哪些应用程序。当将一个应用程序的各个会话的 CPU、内存、读取、写入和逻辑读取进行比较，确定 CPU 资源要远远高于正在使用的其他资源时，我开始重点关注这些 SQL 语句。

为了完整跟踪应用程序的 SQL 语句，我使用 SQL Server 跟踪。可以通过 SQL Server Profiler 工具或跟踪系统存储过程实现上述目的，以帮助评估正在进行的操作。（有关本主题的详细信息，请参阅侧栏“SP 跟踪”。）对于具有高 CPU 使用率的语句，以及哈希和分类警告、缓存未命中以及其他红色标志，应查看探查器。这有助于您把范围缩小到特定的 SQL 语句或产生高资源使用率的特定时间段。探查器能够跟踪 SQL 语句文本、执行计划、CPU 使用率、内存使用率、逻辑读取、写入、查询计划缓存、重新编译、对来自缓存的查询计划的拒绝、缓存未命中、表和索引扫描、缺少的统计信息以及许多其他事件。

通过 sp_trace 存储过程或 SQL Server Profiler 收集数据后，我通常会使用一个数据库，该数据库有两种填充方式：事后使用跟踪数据填充；将跟踪设置为写入数据库。事后填充数据库可以通过名为 fn_trace_getinfo 的 SQL Server 系统函数来完成。此方法的优点在于我可以通过多种方式查询和分类数据来查看哪些 SQL 语句使用的 CPU 最多或读取的次数最多、计算发生重新编译的次数等。以下示例说明如何使用此函数加载具有探查器跟踪文件的表。Default 指定将按照创建顺序加载此跟踪的所有跟踪文件：

SELECT * INTO trc_20070401
FROM fn_trace_gettable('S:\Mountpoints\TRC_20070401_1.trc', default);
GO

总结

您可以看到，高的 CPU 使用率并不一定表示出现了 CPU 瓶颈。高 CPU 使用率也可能是大量其他应用程序或硬件出现瓶颈造成的。尽管其他计数器看起来运行状况良好，但确定 CPU 使用率过高后，您可以在系统内查找原因，然后找出解决方案（购买更多的 CPU 或优化 SQL 代码）。不管怎样做，都不要放弃！使用本文中提供的提示，再进行一点实践和研究，在 SQL Server 下优化 CPU 使用率这一执行计划是完全可以实现的。

Zach Nichter是一位具有 10 多年经验的 SQL Server 专业人员。他担任过多种 SQL Server 支持角色，包括 DBA、团队领导、经理和顾问。目前，Zach 在 Levi Strauss & Co. 担任 DBA 架构师，主要工作是对 SQL Server 的性能、监视、体系结构以及其他战略规划进行研究。此外，Zach 还是 www.sqlcatch.com 上一个视频博客的作者。