第一次翻译

执行计划的基础

简单地说，执行计划是查询计算实现由您提交的T-SQL查询表示的请求的最有效方法的结果。执行计划可以告诉您SQLServer如何执行查询，或者它是如何执行查询的。因此，它们是性能较差的（计算机）查询数据的主要手段。而不是猜测为什么对于给定的查询执行数千次扫描，将输入输出流通过顶层的原因，您可以使用执行计划来确定引起问题的SQL代码的确切部分。举个栗子，如果删除WHERE子句中的函数，则查询可能读取整个表值的数据，只需检索所需的行即可。执行计划显示所有这些和更多内容。本章的目的是教您以图形、文本或xml格式捕获实际和估计的执行计划，并了解如何解释这些计划的基础知识。为了做到这一点，我们将讨论以下主题：1.查询执行计划的简要说明-执行计划是OPERA的结果。因此，至少稍微了解一下它的功能，以及它是如何工作的，这是很有用的。2.实际和估计的执行计划-它们是什么以及它们有何不同。3.捕获和解释不同的可视化执行计划格式-我们将研究图形化、文本化和XML转换计划。4.直接从缓存中检索执行计划-通过动态管理对象(DynamicManagementObjects)访问计划缓存。5.执行计划捕获-使用SQLServer跟踪事件。

提交查询时发生什么？

当您向SQLServer提交查询时，服务器上的多个进程将开始处理该查询。所有这些过程的目的都是为了管理系统，使其能够选择、插入、转接。 或删除数据。每当我们向系统提交查询时，这些进程就会启动。虽然SQLServer内部同时发生许多不同的操作，但我们将关注围绕查询的流程。

满足查询需求的过程大致分为两个阶段：1.发生在关系引擎中的进程。2.存储引擎中发生的进程。在关系引擎时，查询被重新调整，然后由查询进行处理，从而生成一个执行计划。计划(以二进制格式)发送到存储引擎，然后存储引擎使用该计划作为检索或修改基础数据的基础。存储引擎是发生锁定、索引维护和事务等进程的地方。因为执行计划是在关系引擎中创建的。这就是为什么我们会在的地方聚焦我们的大部分注意力。

查询解析

当我们将T-SQL查询传递给SQLServer系统时，当T-SQL到达时，它首先传递给关系型查询，它通过一个检查T-SQL是否被写入的进程。它的结构很好。这个过程是查询解析。如果查询不能正确地解析，例如，如果您键入ACCER而不是SELECT，则解析停止，SQLServer向查询源返回一个错误。流程的输出是一个解析树或查询树(甚至称为序列树)。解析树表示 执行所请求的查询所需的步骤。如果T-sql字符串不是数据操作语言(DEL)语句，而是数据定义语言(DDL)查询，那么它将不是最优化的结果。这是因为这是唯一的“正确方式”，例如，SQLServer系统只有一个"正确的方式"可以创建一个表；因此，没有机会改善这种类型的声明。

Algebrizer

如果T-SQL字符串是一条ACCER语句，并且它已经正确，那么解析树将传递给一个名为ACCER的进程。这个Algebrizer解析各种对象、表的所有名称，以及查询字符串中引用的列。在单独的列级别上，将标识正在访问的对象的所有数据类型varchar(50)和其他数据类型。它还可以确定查询(如GROUPBY和MAX)在查询中的位置，这是一个称为聚合绑定的进程。这个Algebrizer过程很重要，因为查询可能有以下几个方面： 使用了别名或是同义词或者是数据库中不存在、需要解析的名称，或者查询可能引用数据库中不存在的对象。当数据库中不存在对象时，SQLServer将从此步骤返回一个错误，定义无效的对象名称。例如，Algebrizer快速找到表，在他个人2008R2的数据库。但是，不存在的辅助表将导致错误，整个优化过程将停止。再输出一个名为查询处理器树的二进制文件，然后再传递给查询处理树。对应的输出包括一个哈希，一个表示查询的编码值。优化程序的使用哈希来确定是否已经生成并存储在计划缓存中的计划。如果那里有一个计划，流程就在这里停止，然后使用该计划。这就减少了所有的查询生成新计划所需的总开销。

 

查询优化器

 

查询优化器本质上是一种软件，它对数据库关系引擎的工作方式进行“建模”。优化器使用的最重要的数据片段是统计数据，SQL Server根据索引和列生成和维护统计数据，显式地供优化器使用。使用查询处理器树和它拥有的关于数据的统计信息，优化器应用该模型，以便计算出它认为执行查询的最佳方式——也就是说，它生成一个执行计划。换句话说，优化器计算出如何最好地实现由您提交的T-SQL查询表示的请求。它决定是否可以通过索引访问数据、使用哪种类型的连接等等。优化器所做的决策基于它计算出的给定执行计划的成本，即所需的CPU处理和I/O。因此，这是一个基于成本的计划。优化器将生成并评估许多计划(除非已经缓存了计划)，并且通常会选择成本最低的计划，即它认为将尽可能快地执行查询并使用最少的资源(CPU和I/O)的计划。执行成本的计算是最重要的计算，如果优化器返回结果的速度快得多，那么它将使用更CPU密集型的进程。有时候，如果优化器认为评估多个计划要比运行一个效率较低的计划花费更多的时间，那么它会接受一个效率较低的计划。优化器找不到最佳的计划。优化器在尽可能短的迭代次数中找到成本最低的计划，这意味着处理器内的时间最少。如果你提交一个非常简单的查询,例如,一个SELECT语句对单个表内没有骨料或计算查询——那么,而不是花时间尝试计算绝对最优计划,优化器将只是一个微不足道的计划适用于这些类型的查询。

例如，列表1.1中的查询将创建一个简单的计划。

SELECT d.Name

FROM HumanResources.Department AS d

WHERE d.DepartmentID = 42

列表1.1

即使再添加一个带有JOIN的表，也会使计划变得非常重要。如果查询不简单，优化器将执行基于成本的计算来选择计划。为此，它依赖于by SQL Server维护的统计数据。对数据库中的列和索引收集统计信息，并描述数据分布和数据的惟一性或选择性。我们不想让优化器读取所有数据查询中引用的表每次试图产生一个计划,它依赖于统计数据,样本数据的数据,它提供了一个数学构造优化器使用的代表整个数据集合。优化器对统计数据的依赖意味着这些东西需要尽可能精确，否则优化器可能会对它创建的执行计划做出错误的选择。构成统计信息的信息由直方图表示，直方图是从均匀分布在数据中的200个数据点中提取的特定值的出现次数的列表。正是这种“关于数据的数据”为优化器进行计算提供了必要的信息。如果存在相关列或索引的统计信息，那么优化器将在计算中使用它们。优化器将检查统计数据，以确定索引是否提供了足够的选择性，可以作为查询的辅助。选择性是指数据在整个数据集中的独特性。为索引提供帮助所需的选择性水平相当高，通常在大多数情况下需要x%的惟一值。默认情况下，在系统中为所有索引或用作谓词的任何列(作为WHERE子句或JOIN ON子句的一部分)自动创建和更新统计信息。表变量从来没有对它们生成统计信息，因此优化器总是假设它们只包含一行，而不考虑它们的实际大小。临时表上确实生成了统计信息，它们的统计信息存储在与永久表相同的直方图中，优化器可以使用这些统计信息。优化器使用这些统计数据和查询处理器树，并试探地确定最佳计划。这意味着它要通过一系列计划工作，测试访问数据的不同方法，尝试不同类型的连接，重新安排连接顺序，尝试不同的索引，等等，直到得到它认为成本最低的计划。在这些计算期间，优化器为计划中的每个步骤分配一个数字，表示它对它认为每个步骤将花费的CPU和磁盘I/O时间总和的估计。这个数字是该步骤的估计费用。每个步骤的成本累积是执行计划本身的估计成本。重要的是要注意估计的成本只是一个估计。给定无限的时间和完整的最新统计数据，优化器将找到执行查询的完美计划。然而，它试图在尽可能少的时间内计算出最好的计划，并且受到可用统计数据质量的限制。因此，这些成本估算作为衡量指标是非常有用的，但不太可能准确地反映现实。一旦优化器到达一个执行计划，估计的计划就被创建并存储在一个称为计划缓存的内存空间中——尽管如果一个计划已经存在于缓存中，那么这一切都是不同的(关于这一点的更多信息，请参阅关于执行计划重用的部分)。如前所述，如果优化器在缓存中发现与当前执行的查询匹配的计划，则整个流程将短路。

查询执行

一旦优化器生成了执行计划，或者从缓存中检索了执行计划，操作就切换到存储引擎，存储引擎通常根据计划执行查询。我们不会在这里详细讨论，只是要注意，在实际执行过程中，仔细生成的执行计划可能会发生更改。例如，如果:

•SQL Server确定计划超出了并行执行的阈值(一个利用机器上多个处理器的执行——更多关于并行执行，见第3章)

•用于生成计划的统计数据已经过时，或者自创建原始执行计划以来已经更改

•查询中的进程或对象，如临时表中的数据插入，会导致执行计划的重新编译。其中任何一个都可能改变估计的执行计划。假设SQL Server是一个SELECT，关系引擎更改格式以匹配提交的T-SQL语句中请求的格式后，SQL Server返回查询结果。

 

估计和实际执行计划

 

如前所述，有两种不同类型的执行计划。首先是代表优化器输出的计划。这是一个估计执行计划。

计划中的操作符或步骤是逻辑步骤，因为它们在优化器对计划的视图中具有代表性，而不代表当查询运行时实际发生的情况。

另一个是表示实际查询执行的输出的计划。这种实际执行计划的类型很有趣的。它显示了代表查询执行时实际发生情况的数据。

这些计划代表明显不同的数据集，但看上去大致相同。大多数时候，两个计划中都将有同样的运营商承担同样的费用。有时通常由于重新编译，SQL Server将从计划中删除计划缓存并重新创建它，这些版本可能会有很大的不同。通常是因为发生在统计信息中的更改，或者存储引擎处理查询时发生的其他更改中。我们会在本章后面更详细地讨论这个问题。

估计计划是存储在计划缓存中的计划类型，因此这意味着我们只有通过捕获一个语句的执行才能访问实际执行计划中可用的数据。由于估计的计划从不访问数据，所以它们对于可能需要很长时间去运行的大型复杂的语句非常有用。实际的执行计划是首选的，因为它们显示了重要的执行统计信息，例如给定的运算符的行数。通常，这些附加信息使你使用实际执行计划最多。但估计计划是非常重要的，特别是因为这是你从计划缓存中得到的东西。

 

执行计划重复使用

 

服务器通过上面描述的生成执行计划所需的所有进程是非常昂贵的。而SQL Server可以在少于一毫秒的时间内完成这些工作，根据语句的不同它可以在几秒钟或几分钟的时间里创建一个执行计划，因此SQL Server将保持和在可能的情况下重新使用计划来减少开销。创建时，计划将存储在一个名为计划缓存的内存部分中 (在SQL Server 2005之前，这被称为过程缓存)。

当我们向服务器提交查询时，代数化器进程创建哈希，类似查询的编码标识。哈希是唯一的标识符；它的昵称是查询指纹。

使用对任何给定查询唯一的标识符，包括所有定义查询的文本，包括空格和回车，优化器与哈希到缓存中的查询比较。如果在与进入引擎的查询匹配的缓存中存在查询，则跳过优化过程的整个成本，并重新使用计划缓存中的执行计划。

这是SQL Server的优点之一，因为它减少了创建计划的开销。

SQL Server可以重用它们的计划编写查询时是一个主要的很好的实践。为了确保这种重用，最好使用存储的过程或参数化查询。参数化查询是查询中的变量为使用类似于存储过程的参数标识，并且这些参数被馈送值，同样类似于存储过程。

 

如果，变量是硬编码的，然后定义查询的字符串的最小更改可能导致缓存未命中，这意味着SQL Server没有在缓存中找到计划（即使使用参数化，也可能存在一个完美的适配项）。因此，启动了优化过程并创建了一个新的计划。有可能可以查看查询哈希，并将其用于性能的某些调查（更多信息请参见DMO)。

SQL Server不会将执行计划永远保存在内存中。他们在系统中慢慢老化，使用了一个“年龄”公式将计划的估计成本乘以所使用的次数。（例如，成本估计为10，但已被引用5次的计划，其“年龄”值为50。)。惰性写入器进程这个内部进程可以释放所有类型的缓存(包括计划缓存)，定期扫描缓存中的对象并将此值每次递减一次。

如果满足下列条件后，计划将从内存中删除：

•系统需要更多的内存。

•这个计划的“年龄”已达到零。

•该计划目前未被现有连接引用。

执行计划不是不可变化的。一定的事件和操作可导致计划重新编译。记住这一点很重要，因为重新编译执行计划可以是个非常昂贵的操作。以下操作可能导致重新编译执行计划：

•改变查询引用的表的结构或架构

•更改查询使用的索引

•删除查询所使用的索引

•更新查询所使用的统计信息

•调用这个函数，sp_recompile

•对查询引用的表中的键进行大量Inserts 或 Deletes (这将导致统计数据的变化)

•对于具有触发器的表，重要的步骤是插入或已删除表

•将DDL和DML混合在一个查询中，通常称为延迟编译

•更改查询执行过程中的SET选项。

•更改查询使用的临时表的结构或架构

•对查询使用的动态视图的更改

•查询中光标选项的更改

•远程的行集的改变，类似于在分布式分区视图中

•当使用客户端游标时，如果FOR BROWSE选项已更改。

 

从计划缓存中清除计划

 

由于缓存在执行计划的运行方式中扮演着非常重要的角色，所以您需要一些工具查询和使用计划缓存。首先，在测试时，您可能希望查看一个计划编译所需的时间，或者考察微小的调整可能会如何创建稍微不同的计划。若要清除缓存，请运行以下命令：

DBCC FREEPROCCACHE

清单1.2

警告：清除生产环境中的缓存

在生产环境中运行清单1.2将清除服务器上所有数据库的缓存。这可能会导致严重的性能影响，因为SQL Server之后必须重新创建存储的每个单计划在计划缓存中，就好像计划从来没有存在，并且第一次运行查询一样。

在处理单个查询时，通常最好针对该查询将其从计划缓存中删除。你可以用DBCC FREEPROCCACHE并通过sql_handle 或 plan_handle删除所引用的计划。plan_handle 和sql_handle可从各种DMO对象获得。

 

执行计划格式

 

虽然SQL Server为给定的查询生成单个执行计划，但我们可以在              三种不同的方式：              

•作为图形计划              

•作为文本计划              

•作为XML计划。              

您选择的那个将取决于您想要查看的详细程度，以及用于生成或检索该计划的方法。

 

图形计划

 

图形计划是最常用的执行计划类型。他们很快而且容易阅读。我们可以以图形方式查看预计的和实际的执行计划格式和图形结构使理解大多数计划非常容易。然而，计划的详细数据隐藏在工具提示和属性表之后，会使其更难完成。

 

文本计划

 

这些内容可能很难阅读，但是可以立即获得详细的信息。它们的文本格式意味着我们可以将它们复制或导出到文本操作中。软件，如记事本或Word，然后针对它们运行搜索。而在细节上它们提供了即时可用性，执行计划的总体细节较少，在这些类型的计划中输出，因此它们可能不如其他计划类型有用。

             

有三种文本计划格式：

•展示计划-全部-显示估计执行情况的一组相当完整的数据              

计划查询。

•展示计划-文本- 提供一组非常有限的数据用于OSQL.exe等工具。  

它也只显示估计的执行计划

•统计概况-与展示计划-全部类似，除了它代表的是实际执行计划。

 

XML计划

 

XML计划显示了一个计划上可用的完整数据集，所有数据都显示在              结构化XML格式。XML格式非常适合传输到其他数据。             

如果你想在执行计划上获得帮助或需要与同事分享，专业人士可以。              使用XQuery，我们还可以直接查询XML数据。每个图形执行计划              实际上是在封面下的XML。XML很难读取，因此，尽管这些类型很有用              计划是，您更可能使用文本或图形计划来简单浏览。              

执行计划。              

XML计划有两种类型：

•展示计划-XML-在执行之前由优化器生成的计划。

•统计数据- XML-实际执行计划的XML格式。 

 

入门

执行计划帮助我们编写高效的T-SQL代码，排除现有T-SQL的故障              我们的系统的行为或监控和报告。我们如何使用它们和查看它们取决于我们，但首先我们需要了解其中包含的信息计划，以及如何解释这些信息。最好的学习方法之一执行计划就是要看到他们的行动，所以我们开始吧。请注意，偶尔，特别是当我们继续进行更复杂的计划时，如果通过执行相关脚本（所有脚本都是可在本书的代码下载中获得）可能与所提供的稍有不同。在书中。这可能是因为我们使用的是不同版本的SQL Server（不同的SP级别和修补程序），或者我们使用的AdventureWorks数据库，或者因为AdventureWorks数据库 随着时间的推移，我们每个人都在其中玩耍。所以，尽管大部分计划你得到的应该和我们在这里展示的非常相似，如果你尝试的话，不要太意外。代码和查看的内容不同。

 

查看执行计划所需的权限

 

为了生成查询的执行计划，您必须具有正确的权限在数据库中。如果您是sysadmin、dbcreator或db_所有者，则不需要其他许可。如果您将此权限授予将不在其中的开发人员在这些特权角色中，需要在正在测试的数据库。运行清单1.3中的语句。

GRANT SHOWPLAN TO [username];

清单1.3

替换用户名将允许用户查看该数据库的执行计划。另外，为了对dmos运行查询，可以查看服务器状态或者根据所讨论的DMO查看数据库状态。

 

使用图形执行计划

 

为了关注捕获估计和实际执行计划的基础，首先查询将是最简单的查询之一，我们将从中构建。打开在Management Studio和查询窗口中，输入以下内容：

SELECT *

FROM dbo.DatabaseLog;

清单1.4

 

获取估计计划

 

我们将首先查看查询优化器已生成，因此还不需要运行查询。              

我们可以找出优化器在下列方式的其中一个：

•单击工具栏上的“显示估计执行计划”图标。              

•右键单击查询窗口并从菜单中选择相同的选项。              

•单击菜单栏中的查询选项并选择相同的选项。              

•使用键盘上的ctrl+l。  

            

我经常点击图标，但不管怎样，我们第一次看到的是预计执行计划。

看上去，没有一个简单的方法来区分估计计划和实际计划。不同之处在于基础数据，我们将在一些整本书的细节。我们将很快解释这个计划代表了什么，但首先，我们要捕获执行计划。

 

获取实际计划  

 

与估计的执行计划不同，实际执行计划不表示计算优化程序的。相反，这个执行计划显示了SQL Server已执行查询。两者通常是相同的，但有时会不同，因为存储引擎对执行计划所做的更改，如我们在前面讨论的一章。             

与估计的执行计划一样，有几种方法可以生成第一个图形实际执行计划：              

•单击工具栏上名为“包含实际执行计划”的图标              

•右键单击查询窗口并选择包含实际值

执行计划

菜单项              

•在查询菜单选项中选择相同的选项              

•键盘输入ctrl+m。              

每个方法都充当一个“on”开关，然后SQL Server将创建一个所有查询的执行计划都从该查询窗口运行，直到您再次关闭它。因此，使用首选方法打开实际的执行计划并执行查询。

 

解释图形化执行计划 

 

您在图1.1和1.2中看到的图标表示大约79个图标中的前两个。              表示可能构成 执行计划。在计划的左侧是选择操作员，您将经常看到。你不仅会看到这个联系，还会经常提到它对于它包含的重要数据。这是关系的最终结果和格式的构建动力。

右边的图标表示一个表，这是在试图查找可能导致性能问题的原因时最容易查找的操作符之一。每个运算符都有一个逻辑和一个物理成分。它们通常是相同的，但是当查看估计的计划时，您只看到逻辑运算符。当查看实际计划时，您只看到实际操作人员。逻辑加工过程按SQL Server将检索查询定义的信息的逻辑处理顺序排列。这意味着，从逻辑上讲，我们从左到右阅读计划。在上述例子中，逻辑顺序是扫描操作符后面的选择标准的定义。

但是，您会发现，您通常会阅读执行计划的相反方向，从右到左。这并不是因为执行计划是“间接的”。它是仅仅因为操作的物理顺序通常比操作的逻辑顺序更容易理解。基本的过程是提取数据，而不是推送数据，所以执行计划是一致的。这是数据的提取。您将注意到，在这两个图标之间有一个箭头。此箭头表示运算符之间传递的数据，如图标所表示的。在这个地方select，如果我们按照数据流的方向读取执行计划，从右到左的物理方向，我们有一个生成结果集的Table扫描操作符，该操作符传递给Select，箭头的方向进一步强调数据流的方向。