• 项目

SQL Server

优化 SQL Server 查询性能

Maciej Pilecki

 

概览:

  • 分析执行计划
  • 优化查询
  • 识别要优化的查询

优化数据库服务器时，您需要优化个人查询性能。这与优化服务器安装的其他影响硬件和软件配置等性能的方面一样重要 — 甚至更为重要。

即使数据库服务器是在功能最强大的硬件上运行，它的性能还是可能会因为一些行为错误的查询而受到负面影响。实际上，只要有一个坏的查询（有时称为“失控查询”），就可能导致数据库出现严重的性能问题。

反之，通过优化一组成本最高或最常执行的查询，可以极大地提高数据库的整体性能。在本文中，我将探讨一些技术，您可使用这些技术识别并优化服务器上成本最高和性能最坏的查询。

分析执行计划

优化个人查询时，通常通过查看查询的执行计划开始。此执行计划描述 SQL ServerTM 为满足查询和生成所需结果集而执行的物理和逻辑操作的顺序。此执行计划是在查询处理的最优化阶段由称为查询优化器的数据库引擎组件生成的，这要考虑到许多不同的因素，例如，查询中使用的搜索谓词、涉及的表和其连接条件、返回的列清单，以及是否存在能用作数据的有效访问路径的有用索引。

对于复杂的查询，所有可能排列的数量可能非常大，因此查询优化器不评估所有的可能性，而是尝试查找对于给定查询“足够好”的计划。这是因为并不总能查找完美计划；即便可能，评估所有可能性以查找完美计划的开销与所获得的性能改进相比起来，也很可能会得不偿失。从 DBA 观点来看，了解其过程和局限性很重要。

有许多方法可用于检索查询的执行计划：

  • Management Studio 提供显示实际执行计划和显示估计执行计划等功能，这将用图形方式来呈现计划。这些功能为直接检查提供了最适合的解决方案，是目前最常用的显示和分析执行计划的方法（在本文中，我将使用以这种方式生成的图表计划来说明我的示例）。
  • 各种 SET 选项，例如 SHOWPLAN_XML 和 SHOWPLAN_ALL，会以 XML 文档或行集的形式返回执行计划，XML 文档使用特殊架构描述计划，行集中则包含对执行计划中的每个操作的文字说明。
  • SQL Server Profiler 事件类，例如 Showplan XML，允许您收集跟踪所收集语句的执行计划。

虽然 XML 形式的执行计划可能不是人们最容易读懂的格式，但此选项允许您编写能分析执行计划的步骤和实用程序，以查找性能问题的迹象及不够理想的计划。基于 XML 的表示也可保存到具有 .sqlplan 扩展名的文件中，然后在 Management Studio 中打开以生成图形表示形式。还可以保存这些文件以稍后供分析使用，当然，这样就不用在每次想要分析时重新生成执行计划。当您希望比较计划来查看计划随时间的变化情况时，这尤其有用。

估计的执行开销

对于执行计划，首先您需要了解它们是如何生成的。SQL Server 使用基于开销的查询优化器，也就是说，它会试图使用最低的估计开销来生成执行计划。该估计值是根据优化器评估查询所涉及的每个表时，提供给优化器的数据分发统计资料得到的。如果那些统计资料丢失或过期，查询优化器将缺少查询优化过程所需的重要信息，因而可能会得到错误的估计值。在这类情况下，优化器可能会由于高估或低估不同计划的执行开销而选择不太理想的计划。

对于估计的执行开销，有一些常见的错误假定。特别是，人们常常假设估计的执行开销能够很好地指示查询需花多长时间执行，而该估计值能让您分出好的计划和坏的计划。实际情况并不是这样。首先，表达估计开销采用什么单位以及它们是否与执行时间有直接关联都已记录得相当清楚。其次，由于这只是一个估计值，且可能并不正确，因此，在 CPU、I/O 和执行时间方面，有时候，尽管具有较高估计开销的估计值高一些，但效率反而比较高。这通常发生在涉及表变量的查询中 — 因为没有统计数据可用于表变量，即使表变量中包含许多行，查询优化器都始终假设表变量中仅包含一行。因而，查询优化器会基于不准确的估计值选择计划。因此，比较各查询的执行计划时，不应只依赖估计的查询开销，而应在分析中包含 STATISTICS I/O 和 STATISTICS TIME 选项输出结果，以了解 I/O 和 CPU 时间方面的实际执行开销。

在此，有个称为并行计划的特殊类型的执行计划值得一提。如果您在具有多个 CPU 的服务器上运行查询，并且您的查询符合并行执行的条件，则可以选择使用并行计划（通常，查询优化器只对其开销超出特定的可配置阈值的查询考虑使用并行计划）。由于管理多个并行执行的线程（指跨线程分发任务、执行同步以及收集结果）会产生开销，因此执行并行计划开销更大，这会在估计开销中有所体现。那么，为什么并行计划比较为便宜的非并行计划更受欢迎呢？这是因为多个 CPU 的处理能力发挥了威力，并行计划得出结果的速度往往比标准计划更快。根据您的特定方案，包括来自其他查询的可用资源和并行负载一类的变量，此情形可能正是您的设置所期望的。如果真是这样，您应控制哪些查询能够生成并行计划，以及每个查询有多少 CPU 可供利用。通过在服务器级别设置最大并行度选项，然后根据需要在单个查询级别使用 OPTION (MAXDOP n) 覆盖它，即可达到此目的。

分析执行计划

现在，我要让大家看一个简单的查询以及该查询的执行计划和改进其性能的一些方法。假设我使用 Management Studio 执行查询，并在 SQL Server 2005 上的 Adventure Works 示例数据库中启用了“包括实际的执行计划”选项：

SELECT c.CustomerID, SUM(LineTotal)
FROM Sales.SalesOrderDetail od 
JOIN Sales.SalesOrderHeader oh
ON od.SalesOrderID=oh.SalesOrderID
JOIN Sales.Customer c ON oh.CustomerID=c.CustomerID
GROUP BY c.CustomerID

结果，我看到了图 1 中所述的执行计划。此示例查询计算每个 Adventure Works 客户发出的订单总数。看一下这个执行计划，您会看到数据库引擎是如何处理查询并生成结果的。应按从上到下、从左到右的顺序阅读图形执行计划。每个图标都代表一个执行的逻辑和物理操作，箭头指示操作之间的数据流向。箭头厚度代表操作之间传递的行数，箭头越厚，包含的行数越多。如果将指针放在其中一个运算符图标上，会在黄色的工具提示（如图 2 所示）中显示此具体操作的详细信息。

图 1 执行计划示例

图 1** 执行计划示例 **(单击该图像获得较大视图)

图 2 操作详细信息

图 2** 操作详细信息 **(单击该图像获得较大视图)

通过查看每个运算符，可以分析所执行步骤的顺序：

  1. 数据库引擎对 Sales.Customer 表执行聚集索引扫描操作，然后返回该表中所有行的 CustomerID 列。
  2. 然后它对 Sales.SalesOrderHeader 表中的一个索引执行索引扫描（指非聚集索引扫描）。这是 CustomerID 列上的一个索引，但在其中也暗含了 SalesOrderID 列（表聚集键）。扫描会同时返回这两列中的值。
  3. 来自两个扫描的输出都使用“合并联接”物理运算符联接到 CustomerID 列（这是执行逻辑联接操作的三种可用物理方法中的一种。此方法速度很快，但需要在联接的列上对这两个输入进行排序。在本例中，两个扫描操作均已返回在 CustomerID 排序的行，因此无需另外执行排序操作）。
  4. 接下来，数据库引擎对 Sales.SalesOrderDetail 表上的聚集索引执行扫描，从此表内的所有行中检索四列（SalesOrderID、OrderQty、UnitPrice 和 UnitPriceDiscount）的值（估计此操作将返回 123、317 行，而实际上也确实如此，您可以从图 2 中的“估计行数”和“实际行数”属性中看到。因此，该估计非常准确）。
  5. 聚集索引扫描生成的行将传递到第一个“计算标量”运算符，这样，根据公式中涉及的 OrderQty、UnitPrice 和 UnitPriceDiscount 列，即可计算出每行的计算列 LineTotal 的值。
  6. 根据计算列公式的需要，第二个“计算标量”运算符将对上一计算的结果应用 ISNULL 函数。这样就完成了对 LineTotal 列的计算，并将计算结果连同 SalesOrderID 列一起返回到下一运算符。
  7. 通过使用“哈希匹配”物理运算符，联接步骤 3 中“合并联接”运算符的输出内容与步骤 6 中“计算标量”运算符的输出内容。
  8. 然后，对由 CustomerID 列值和计算所得的 LineTotal 列的 SUM 合计从“合并联接”返回的行组应用另一个“哈希匹配”运算符。
  9. 最后一个节点 SELECT 既不是物理运算符也不是逻辑运算符，而是一个占位符，用以表示总计查询结果和开销。

在我的便携式计算机上，此执行计划的估计开销是 3,31365（如图 3 所示）。如果在 STATISTICS I/O 处于 ON 状态时执行此计划，查询报告对涉及的三个表执行了共计 1,388 个逻辑读取操作。每个运算符下显示的百分比表示与整个执行计划的总体估计开销相关的每个单个运算符的开销。看一下图 1 中的计划，可以看出整个执行计划的大部分总开销都与以下三个运算符相关联：Sales.SalesOrderDetail 表的聚集索引扫描以及两个哈希匹配运算符。但在对其进行优化之前，我想指出我的查询中的一个非常简单的改动，这一改动将允许我同时消除两个运算符。

图 3 查询的总估计执行开销

图 3** 查询的总估计执行开销 **

由于我从 Sales.Customer 表中返回的只是 CustomerID 列，而此列又是 Sales.SalesOrderHeader 表中的外键，因此，我能够使用以下代码从查询中完全消除生成的 Customer 表，并且不必更改查询所生成的逻辑意义或结果：

SELECT oh.CustomerID, SUM(LineTotal)
FROM Sales.SalesOrderDetail od JOIN Sales.SalesOrderHeader oh
ON od.SalesOrderID=oh.SalesOrderID
GROUP BY oh.CustomerID

这样就产生了另一个执行计划，如图 4 所示。

图 4 从查询中去除了 Customer 表的执行计划

图 4** 从查询中去除了 Customer 表的执行计划 **(单击该图像获得较大视图)

以下两个操作被完全消除：Customer 表的聚集索引扫描以及 Customer 与 SalesOrderHeader 之间的合并联接，并且哈希匹配联接将由更高效的合并联接取代。但是，为在 SalesOrderHeader 和 SalesOrderDetail 表之间使用“合并联接”，必须返回经过联接列 SalesOrderID 排序的两个表中的所有行。为实现此目的，查询优化器决定对 SalesOrderHeader 表执行聚集索引扫描，而不使用非聚集索引扫描，这样在涉及的 I/O 方面开销较低。此示例很好地说明了查询优化器的实际工作原理：由于更改执行联接操作的物理方式所节省的开销要比聚集索引扫描生成的附加 I/O 开销多，因此，查询优化器选择得出的运算符组合，因为它产生的总估计执行开销可能会是最低的。在我的便携式计算机上，尽管逻辑读取数量上升了（已升至 1,941），但所耗费的 CPU 时间实际上却少了，并且此查询的估计执行开销也下降了大约 13％ (2,89548)。

假设我希望进一步改进此查询的性能。现在我要看一下 SalesOrderHeader 表的聚集索引扫描，此运算符已成为该执行计划中开销最大的运算符了。由于我只需要使用此表中的两列来填充查询，因此我可以创建一个只包含这两列的非聚集索引，这样，即可通过对小得多的非聚集索引进行扫描来替代对整个表的扫描。索引定义可能会如下所示：

CREATE INDEX IDX_OrderDetail_OrderID_TotalLine
ON Sales.SalesOrderDetail (SalesOrderID) INCLUDE (LineTotal)

请注意，我创建的索引包含一个计算列。并不一定总会这样，具体情况取决于对计算列的定义。

创建此索引并执行同样的查询后，我得到一个新的执行计划，如图 5 所示。

图 5 优化后的执行计划

图 5** 优化后的执行计划 **(单击该图像获得较大视图)

对 SalesOrderDetail 表进行的聚集索引扫描已被非聚集索引扫描取代，后者的 I/O 开销要小得多。我还消除了其中的一个“计算标量”运算符，因为我的索引中包含已计算出的 LineTotal 列的值。现在，估计执行计划开销是 2,28112，并会在执行查询时进行 1,125 次逻辑读取。

覆盖索引

客户订单查询练习

问：下面是客户订单查询练习：请尝试给出索引定义 — 要成为此查询的覆盖索引，应包含哪些列，以及索引定义中的列顺序是否会对性能产生不同影响。

答：我向您发出挑战，要给出为本文中的示例查询在 Sales.SalesOrderHeader 表上创建的最佳覆盖索引。为实现此目的，您需要注意的第一件事就是查询仅使用表中的两列：CustomerID 和 SalesOrderID。如果您仔细阅读了本文，就会发现在 SalesOrderHeader 表中，有一个包含此查询的现成索引，即 CustomerID 上的索引，并且此索引也暗含了 SalesOrderID 列，该列为此表的聚集键。

当然，我也已经介绍了查询优化器为什么决定不使用此索引。确实，您可以强制查询优化器使用此索引，但与使用聚集索引扫描和合并联接运算符的现有计划相比，该解决方案的效率要差一些。这是由于您将强制查询优化器在以下两者之间作出选择：执行额外的排序操作以便继续使用“合并联接”，或返回使用效率相对较低的“哈希联接”。与现有计划相比，上述两种选择的估计执行开销都比较高（进行排序操作的那一种执行起来效率尤其低），所以，除非使用强制手段，否则查询优化器不会使用它们。因此，在这种情形下，与聚集索引扫描相比，执行起来效果更好的唯一一个索引就是 SalesOrderID 和 CustomerID 上的非聚集索引。但请注意，这两列必须按以下顺序排序，这很重要：

CREATE INDEX IDX_OrderHeader_SalesOrderID_CustomerID
ON Sales.SalesOrderHeader (SalesOrderID, CustomerID)

如果创建此索引，则执行计划将包含索引扫描运算符，而不是聚集索引扫描运算符。这是一个重要区别。在此情形下，只包含两列的非聚集索引将比聚集索引形式的整个表小得多。因此，读取必要数据所需的 I/O 就更少。

此示例还显示了索引中列的顺序对于它在查询优化器中的用处有何重要影响。设计多列索引时，请务必记住这一点

我在 SalesOrderDetail 上创建的索引是所谓的“覆盖索引”的一个示例。它是一个包含填充查询所需的所有列的非聚集索引，无需使用表扫描运算符或聚集索引扫描运算符扫描整个表。此索引实际上是表的一个小型副本，包含该表中所有列的一个子集。只有需要响应查询的那些列才包含在该索引中，换句话说，该索引仅包含需要“覆盖”查询的列。

为最常执行的查询创建覆盖索引是查询优化中使用的最简单最常见的一种技术。当表中包含很多列，但查询最常引用的只有很少几列时，此类索引尤其适用。通过创建一个或多个覆盖索引，可以大大改进受影响的查询的性能，因为这些索引要访问的数据量小得多，从而使产生的 I/O 开销更少。但在执行数据修改操作（插入、更新和删除）期间，还存在维护附加索引所需的隐含开销。您应根据您的环境以及 SELECT 查询与数据修改之间的比例，仔细判断此附加索引的维护开销相对于查询性能的改进是否值得。

不必害怕创建多列索引（相对于单列索引而言）。多列索引往往比单列索引有用得多，查询优化器更有可能使用它们来覆盖查询。大多数的覆盖索引都是多列索引。

在我的示例查询中，仍然有改进的余地，将覆盖索引置于 SalesOrderHeader 表中可以进一步优化此查询。这将消除聚集索引扫描，而采用非聚集索引扫描。我将此留给您进行练习。请尝试给出索引定义 — 要成为此查询的覆盖索引，应包含哪些列，以及索引定义中的列顺序对性能是否有不同影响。要查看解答，请参见“客户订单查询练习”边栏。

索引视图

如果我的示例查询的性能非常重要，我可以多执行一个步骤来创建可实际存储查询的具体结果的索引视图。索引视图存在某些先决条件和限制，但如果能使用索引视图，可以显著改善性能。请记住，与标准索引相比，索引视图需要的维护开销较高。因此，应仔细斟酌在何时使用索引视图。在本例中，索引定义如下所示：

CREATE VIEW vTotalCustomerOrders
WITH SCHEMABINDING
AS
SELECT oh.CustomerID, SUM(LineTotal) AS OrdersTotalAmt, COUNT_BIG(*) AS TotalOrderLines
FROM Sales.SalesOrderDetail od 
JOIN Sales.SalesOrderHeader oh
ON od.SalesOrderID=oh.SalesOrderID
GROUP BY oh.CustomerID

请注意 WITH SCHEMABINDING 选项，此选项是在此类视图上创建索引的先决条件；还要注意 COUNT_BIG(*) 函数，如果我们的索引定义中包含聚合函数（在此示例中是 SUM），则此函数是必需的。创建此视图后，可以在其上创建一个索引，如下所示：

CREATE UNIQUE CLUSTERED INDEX CIX_vTotalCustomerOrders_CustomerID 
ON vTotalCustomerOrders(CustomerID)

创建此索引时，包含在视图定义中的查询结果已具体化，并以索引形式实际存储在磁盘中。请注意，对基础表执行的所有数据修改操作接着会根据其定义自动更新视图中的值。

如果我现在返回查询，会产生什么结果取决于我运行的 SQL Server 版本。在 Enterprise Edition 或 Developer Edition 中，查询优化器会自动将此查询与索引视图定义匹配，并使用索引视图，而不是查询所涉及的基表。图 6 显示了本示例中生成的执行计划。此计划仅由一个操作组成：对我在视图上创建的索引进行聚集索引扫描。估计执行开销仅为 0,09023，并且只执行 92 次逻辑读取。

图 6 使用索引视图时的执行计划

图 6** 使用索引视图时的执行计划 **(单击该图像获得较大视图)

在 SQL Server 的其他版本中，仍然可以创建并使用此索引视图，但为达到同样的效果，必须使用 NOEXPAND 提示将查询更改为直接参考该视图，如下所示：

SELECT CustomerID, OrdersTotalAmt
FROM vTotalCustomerOrders WITH (NOEXPAND)

您可以看到，如果使用得当，索引视图可以成为一项非常强大的功能。它们在优化对大量数据进行聚合的查询时最为有用。如果用在 Enterprise Edition 中，这些视图对许多查询都有用，并且不要求更改代码。

识别要优化的查询

我怎样识别值得优化的查询？我要查找执行得最为频繁的查询，单独执行这些查询的开销可能并不高，但执行这些查询的总计开销可能要比很少执行的大型查询的开销高得多。我并不是说您不应该优化大型查询，只是觉得应先关注最常执行的查询。那么，您如何识别这些查询呢？

遗憾的是，最可靠的方法有点复杂，涉及到跟踪对您的服务器执行的所有查询，然后根据其签名进行分组（即，带有实际参数值的查询文本将由占位符取代，以识别相同的查询类型，即便查询是使用不同的参数值执行的也一样）。这是一个复杂的过程，因为查询签名很难生成。Itzik Ben-Gan 在其《Microsoft SQL Server 2005: T-SQL Querying》一书中介绍了一种解决方案，此解决方案使用 CLR 用户定义的函数和正则表达式。

还有一种比较简单的方法，但可靠性较差。您可以依赖在执行计划缓存中为所有查询保留的统计数据，并使用动态管理视图查询这些数据。图 7 中包含一个示例查询，此示例向您显示缓存中累计逻辑读取次数最高的 20 个查询的文本和执行计划。对于快速识别会产生最高逻辑读取次数的查询而言，此查询非常便利，但也存在一些限制。即，此查询只显示在运行查询时缓存了其计划的那些查询。如果有未缓存的内容，这些内容就会丢失。

Figure 7 识别前 20 个在读取 I/O 方面开销最大的查询

SELECT TOP 20 SUBSTRING(qt.text, (qs.statement_start_offset/2)+1, 
        ((CASE qs.statement_end_offset
          WHEN -1 THEN DATALENGTH(qt.text)
         ELSE qs.statement_end_offset
         END - qs.statement_start_offset)/2)+1), 
qs.execution_count, 
qs.total_logical_reads, qs.last_logical_reads,
qs.min_logical_reads, qs.max_logical_reads,
qs.total_elapsed_time, qs.last_elapsed_time,
qs.min_elapsed_time, qs.max_elapsed_time,
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qt.encrypted=0
ORDER BY qs.total_logical_reads DESC

识别这些拙劣的查询后，您可以查看其查询计划，并利用我在本文中介绍的一些索引技术来寻找改进其性能的方法。如果能够成功完成，表示您的时间没有白费。

快乐优化！

Maciej Pilecki是 Solid Quality Mentors 的准顾问，这是一家专门致力于培训、指导和咨询的全球性组织。他是 Microsoft 认证培训师 (MCT) 和 SQL Server 最有价值专家 (MVP)，并经常讲授关于 SQL Server 和应用程序开发的诸多方面的课程并在讨论此类内容的会议上讲话。

© 2008 Microsoft Corporation 与 CMP Media, LLC.保留所有权利；不得对全文或部分内容进行复制.