原文链接: http://www.sqlservercentral.com/articles/Stairway+Series/72441/
阅读查询计划:通往SQL Server索引级别9的阶梯
大卫•杜兰特2011/10/05
该系列
本文是楼梯系列的一部分:SQL Server索引的阶梯
索引是数据库设计的基础，并告诉开发人员使用数据库非常了解设计器的意图。不幸的是，当性能问题出现时，索引常常被添加到事后。这里最后是一个简单的系列文章，它应该能让任何数据库专业人员快速“跟上”他们的步伐
在整个楼梯中，我们经常声明某个查询以某种方式执行;我们引用生成的查询计划来支持我们的语句。管理工作室的估计和实际查询计划的显示可以帮助您确定索引的优点或不足。因此，这个级别的目的是让您充分了解查询计划，您可以:
当您阅读这段楼梯时，验证我们的断言。
确定您的索引是否有利于您的查询。
有许多关于阅读查询计划的文章，包括MSDN库中的一些文章。我们的目的不是扩大或取代它们。事实上，我们将在这个层面上为他们中的许多人提供链接/参考。一个很好的起点是图形显示执行计划(http://msdn.microsoft.com/en-us/library/ms178071.aspx)。其他有用的资源包括Grant Fritchey的书，SQL Server执行计划(免费提供电子书格式)，还有Fabiano Amorim的一系列关于查询计划输出中发现的操作符的简单讨论文章(http://www.simple-talk.com/author/fabiano - amorim/)。
图形查询计划
查询计划是SQL服务器执行查询的指令集。SQL Server Management Studio将以文本、图形或XML格式为您显示查询计划。例如，考虑以下简单查询:
SELECT LastName, FirstName, MiddleName, Title 
FROM Person.Contact
WHERE Suffix = 'Jr.'
ORDER BY Title
图1 -图形格式的实际查询计划
或者，它可以被视为文本:
|——排序(按:[AdventureWorks]。[人] (联系) [标题]ASC))
|——聚集索引
扫描(对象:([AdventureWorks],[人] [联系] [PK_Contact_ContactID]),
地点:([AdventureWorks],[人] (接触) (后缀)= N 'Jr。'))
或者作为一个XML文档，开始这样:
查询计划的显示可被要求如下:
要请求图形化查询计划，请使用Management Studio的SQL Editor工具栏，该工具栏有“显示估计执行计划”和“包含实际执行计划”按钮。“显示估计执行计划”选项将立即显示所选的TSQL代码的查询计划图，而不执行查询。“包含实际执行计划”按钮是一个开关，一旦你选择了这个选项，你执行的每一个查询批量都会在一个新标签中显示你的查询计划图，以及结果和消息。这个选项如图1所示。
要请求文本查询计划，请使用SET SHOWPLAN_TEXT语句。打开文本版本将会关闭图形化版本，不会执行任何查询。
阅读图形查询计划
图形化查询计划通常从右到左读取;最右边的图标代表数据收集流中的第一步。这通常是访问堆或索引。您将看不到这里使用的单词表;相反，您将看到集群索引扫描或堆扫描。这是第一个查看哪些索引(如果有的话)正在使用的地方。
图形化查询计划中的每个图标表示一个操作。附加信息的可能的图标,看到图形在http://msdn.microsoft.com/en-us/library/ms175913.aspx上执行计划图标
连接操作的箭头表示行，从一个操作流到下一个操作。
将鼠标放在图标或箭头上，将会显示更多信息。
不要将操作视为步骤，因为这意味着必须在下一次操作开始之前完成一个操作。这并不一定是真的。例如，当一个WHERE子句被评估时，也就是说，当执行一个筛选器操作时，会一次对行进行评估;并不是所有的。行可以移动到下一个操作，然后在随后一行到达过滤器操作。另一方面，排序操作必须在第一行移动到下一个操作之前完整地完成。
使用一些额外的信息
图形化查询计划显示了两个可能有用的信息，这些信息不是计划本身的一部分;建议指标及每次操作的相对成本。
在上面的例子中，建议的索引，以绿色表示，并被空间需求截断，建议在联系人表的后缀列上有一个非聚集索引;包含标题、FirstName、MiddleName和LastName列。
这个计划的每个操作的相对成本告诉我们排序操作占总成本的5%，而表扫描是95%的工作。因此，如果我们想要提高这个查询的性能，我们应该处理表扫描，而不是排序;这就是为什么要提出一个指数。如果我们创建推荐索引，如下所示:
CREATE NONCLUSTERED INDEX IX_Suffix ON Person.Contact
(
Suffix
)
INCLUDE ( Title, FirstName, MiddleName, LastName )
然后重新运行查询，我们的读数从569下降到3;下面显示的新查询计划说明了原因。
新的非聚集索引，其索引键的后缀，有“WHERE后缀= 'Jr .”。”条目聚集在一起;因此，IO的还原需要检索数据。结果是，排序操作，与之前的计划相同的排序操作，现在占查询总成本的75%，而不是它所花费的5%。因此，最初的计划需要75 / 5 = 15倍的工作量，以收集与当前计划相同的信息。
由于我们的WHERE子句只包含一个等式运算符，所以我们可以通过将标题列移动到索引键来提高索引值，例如:
IF  EXISTS (SELECT * FROM sys.indexes
WHERE OBJECT_ID = OBJECT_ID(N'Person.Contact')
AND name = N'IX_Suffix')
DROP INDEX IX_Suffix ON Person.Contact
CREATE NONCLUSTERED INDEX IX_Suffix ON Person.Contact
(
Suffix, Title
)
INCLUDE ( FirstName, MiddleName, LastName )
现在，需要的条目仍然聚集在索引中，在每个集群中它们都在请求的序列中;如新的查询计划所示，如图2所示。
图2 -重建非聚集索引后的查询计划
现在，该计划表明不再需要排序操作。在这一点上，我们可以放弃我们非常有益的覆盖指数。它将联系表恢复到我们开始时的样子;当我们进入下一个主题时，我们希望它进入状态。
查看平行流
如果两个行可以并行处理，它们将在图形显示中出现在上面和下面。箭头的相对宽度表示每个流中处理了多少行。
例如，下面的连接扩展了以前的查询，以包含销售信息:
SELECT C.LastName, C.FirstName, C.MiddleName, C.Title
, H.SalesOrderID, H.OrderDate
FROM Person.Contact C
JOIN Sales.SalesOrderHeader H ON H.ContactID = C.ContactID
WHERE Suffix = 'Jr.'
ORDER BY Title
图3 -一个连接的查询计划
快速浏览一下计划告诉我们一些事情:
两张桌子同时扫描。
大部分的工作都花在了扫描表格上。
出现更多的行或SalesOrderHeader表，而不是从表中取出。
这两张表没有按相同的顺序排列;因此，将每个SalesOrderHeader与它的联系人行匹配将需要额外的努力。在这种情况下，使用散列匹配操作。(稍后将详细介绍散列)。
排序选定行所需的工作量可以忽略不计。
即使单独的行流也可以被分割成不同的流，每一行都可以使用并行处理。例如，如果我们将上述查询中的WHERE子句更改为NULL。
将会返回更多的行，因为95%的联系人行有一个NULL后缀。新的查询计划反映了这一点，如图4所示。
图4 -一个并行查询计划
新计划还向我们表明，越来越多的接触行导致匹配和排序操作成为该查询的关键路径。如果我们需要改进它的性能，我们必须首先攻击这两个操作。同样，包含列的索引也会有所帮助。
与大多数连接一样，我们的示例通过外键/主键关系连接两个表。其中一个表，Contact，是由ContactID进行排序的，这也是它的主键。在另一个表SaleOrderHeader中，ContactID是一个外键。因为ContactID是一个外键，所以对于ContactID访问的SaleOrderHeader数据的请求，例如我们的join示例，可能是一个常见的业务需求。这些请求将受益于ContactID的索引。
每当索引外键列时，总是要问自己，如果有的话，应该将列添加到索引中。在我们的例子中，我们只有一个查询，而不是支持的查询系列。因此，我们仅包含的列将是OrderDate。为了支持针对SaleOrderHeader表的面向对象的查询系列，我们将在需要时包含更多的SaleOrderHeader列，以支持这些额外的查询。
我们的CREATE INDEX语句是:
CREATE NONCLUSTERED INDEX IX_ContactID ON Sales.SalesOrderHeader
(
ContactID
)
INCLUDE ( OrderDate )
图5 -每个表上有一个支持索引的连接查询计划
因为现在两个输入流都由连接谓词列进行排序，ContactID;查询的连接部分可以在不分割流和不进行哈希的情况下完成;因此，减少26 + 5 + 3 = 34%的工作负载下降到工作负载的4%。
排序、预分类和散列
许多查询操作要求在执行操作之前将数据分组。这些包括不同的、联合的(意味着不同的)、组(以及它的各种聚合函数)，并加入。通常，SQL Server将使用三种方法中的一种来实现这个分组，第一个方法需要您的帮助:
很高兴地发现数据已经被按分组顺序显示出来了。
通过执行散列操作来分组数据。
将数据排序到分组序列中。
预分类
索引是您进行数据显示的方式;也就是说，在经常需要的序列中向SQL Server提供数据。这就是为什么创建非聚集索引(每个包含包含列)的原因，使我们以前的例子受益。实际上，如果您将鼠标放在最近查询中的Merge Join图标上，那么这个短语匹配两个适当排序的输入流中的行，利用它们的排序顺序。就会出现。这告诉您两个表/索引的行使用了绝对最小的内存和处理器时间。适当的排序输入是一个很好的短语，当鼠标在查询计划图标上进行鼠标操作时，它可以验证您选择的索引。
哈希
如果传入的数据不是理想的序列，那么SQL Server可能会使用散列操作来分组数据。哈希是一种可以使用大量内存的技术，但通常比排序更有效。在执行不同的、联合和连接操作时，哈希比在单独的行中排序可以传递到下一个操作，而不必等待所有传入的行被散列。然而，在计算分组集合时，必须读取所有输入行，然后才能将任何聚合值传递给下一个操作。
散列信息所需的内存数量与所需的组数直接相关。因此，hashing需要解决:
SELECT Gender, COUNT(*)
FROM NewYorkCityCensus
GROUP BY Gender
只需要很少的记忆，因为只有两组;女性和男性，不考虑输入行数。另一方面:
SELECT LastName, FirstName, COUNT(*)
FROM NewYorkCityCensus
GROUP BY LastName, FirstName
会导致大量的群体，每个群体都需要自己的记忆空间;可能消耗了太多的内存，因此哈希成了解决查询的一种不受欢迎的技术。
更多查询计划散列,请访问http://msdn.microsoft.com/en-us/library/ms189582.aspx。
分类
如果数据没有被预置(索引)，如果SQL Server认为不能有效地进行哈希，那么SQL Server将对数据进行排序。这通常是最不可取的选择。因此，如果在计划的早期出现了排序图标，请检查是否可以改进索引。如果Sorticon在计划的末尾出现，它可能意味着SQL Server将最终输出排序为ORDER by子句所请求的序列;这个序列与用于解析查询的连接、组BYs和union的序列不同。通常，在这一点上，你几乎没有办法避免这种情况。
结论
查询计划向您展示了SQL Server打算使用或使用的方法来执行查询。它通过详细描述将要使用的操作、从操作到操作的行流程以及所涉及的并行性来实现。
您将此信息视为文本、图形或XML显示。
图形化计划显示了每个操作的相对工作负载。
图形化的计划可以建议一个索引来提高查询的性能。
了解查询计划将帮助您评估和优化您的索引设计。
本文是通往SQL Server索引楼梯的楼梯的一部分