dissecting sql execution plan--chapter1

chapter 1 Execution Plan Basic

本章的目标是使你能够通过图形、文本以及xml方式捕获实际的和预估的执行计划，并且理解如何解释它们的基础知识。为此，我们将覆盖一下内容：

1、查询优化器（query optimizer）简介      

执行计划是查询优化器的计算结果，那么懂一点优化器的工作内容和工作方式会有所裨益

2、实际的（Actual）和预估的（Estimated）执行计划

它们是什么和它们的区别

3、捕获和解释不同的可视执行计划

我们将使用一个基本的Select查询来研究图形、文本、xml形式的执行计划

4、自动捕获执行计划

使用Sql Server Profile Tool

 

当查询被提交后发生了什么

当查询被提交到Sql Server DataBase之后，很多进程就开始工作，最终目的是将数据尽快返回给用户或者存储起来，同时保持数据的一致性。这些进程为每个提交到服务器的查询服务，因而有很多的不同的动作同时发生在服务器上，我们只关注T-SQL周围的事情，粗略的分为两个阶段：

1、关系引擎（relational engine）中发生的进程

2、存储引擎（storage engine）中发生的进程

在关系引擎中查询被解析然后又查询优化器处理产生执行计划，然后被送到存储引擎（二进制方式）用来提取和更新下层数据（underlying data)。存储引擎就是Locking、Index maintenance、transaction等发生的地方。由于执行计划是由关系引擎产生的，我们将主要关注关系引擎。

查询解析（query parsing）

T-sql查询到达服务器后去的第一个地方就是关系引擎，它被传递给一个进程来检查拼写和形式，这个进程就是查询解析（query parsing）进程。解析进程的输出是一个解析树（parse tree），或者说查询树（query tree）甚至是even sequence tree。解析树代表了执行查询的逻辑步骤。

如果T-sql语句不是一个数据操纵语言（DML)声明就不会被优化，举个例子，对于创建table，系统只有一种“正确”的方式，那么就没有机会来提升性能。如果T-sql是DML声明，解析树就被传递给一个叫algebrizer的进程，algebrizer解析查询引用到的所有的对象、表，列的名字，并且甄别列的类型（varchar(50) vs nvarchar(25)等），除此之外还要执行一个叫做aggregate binding 的过程来决定聚合（aggregates，例如Group by ，Max）的位置。algebrizer进程很重要，因为查询里可能包或了别名、同义词或者不存在的名字，这些需要被解析，或者查询引用了不存在的对象。

algebrizer的输出是query processsor tree，二进制形式，然后被传递给了查询优化器（query optimizer）

查询优化器（query optimizer）

查询优化器本质上更像是关系引擎的工作模型，使用query processor tree 和数据库的统计数据并应用模型，就得出了查询优化器所认为的查询的最佳执行方式，也就是，产生了执行计划。

查询优化器决定了数据能否访问索引、使用哪种连接还有其他很多东西。这种决定是基于开销的，所需的cpu、io等。查询优化器将会产生并评估很多的计划（除非cache里已经有了），一般来说，选择开销最低的那个，比如运行最快，使用最少的资源、cpu、I/O的那个。执行速度仍然是最重要的因素，如果能够更快返回结果，优化器会选择cpu密集型的过程。有时候优化器也会选择效率较低的计划，如果它认为花时间去评估很多的执行计划还不如采用较低效率的过程。

如果你提交了一个非常简单的查询，比方说，单表、没有索引、没有聚合、没有计算，那么优化器就不会花时间来计算优化，而是简单的使用trival plan。

如果查询是非trival的，优化器就执行开销计算来选择一个。为此，它需要Sql Server维护的统计数据（statistics）。统计数据是数据库收集的关于列和索引的数据，它描述了数据的分布（distribution）、唯一性（uniqueness）和选择性（selectivity）。构成统计数据的信息使用一个直方图（histogram）和表格（tabulation）来表示，它是从200个平均分布的数据点（data Points）取出来的表示特定数据的出现次数。这种“关于数据的数据”给优化器提供了计算所需的必要信息。

如果列和索引相关的统计数据存在，那么优化器就会使用它们来计算。缺省地，系统会为所有索引和那些用作谓词（predicate）、where子句的一部分、join on子句的一部分的列创建和更新统计数据。Table变量不会产生统计数据，优化器始终假定它只有一行而无视它真正的大小。临时表有统计数据，和永久表的统计数据存储在同一个直方图里供优化器使用。

优化器使用这些统计数据和query processor tree一起决定最佳的执行计划。这就意味着，它需要测试一系列的计划，测试不同的join类型，组织join的顺序，尝试不同的索引等等，直到达成它认为的最快的执行计划。在这个计算中，每一步都赋予了一个数值，代表了优化器预估的时间开销（estimated cost），每一步的开销加起来就是执行计划的开销。

有必要指出，预估的开销毕竟是预估的，如果有无限的时间和完整的最新的统计数据，优化器就能找到执行查询的完美计划，但是优化器是试图在最短的时间找到最佳的执行计划，并且明显的，可用的统计数据的质量也是有限的，因此，虽然这个开销估算是个非常有用的手段，但是不能精确的反映现实。

优化器决定执行计划后，实际的执行计划就被创建并且存储进内存空间plan cache，除非相同的执行计划cache里已经存在。优化器产生可能的执行计划（potential plans），和cache里边已经存在的进行比较，如果匹配就是用cache里边的那个。

执行查询（query execution）

执行计划产生后，动作就转移到了存储引擎，在这里根据执行计划实际执行查询。这里不再详细讨论，除了一点，千辛万苦生成的执行计划和设计执行的可能并不一样，比方说一下情景：

1、执行计划超出了并行执行（parallel execution）的界限

* parallel execution 利用多处理器提高执行效率

2、统计数据过期或者发生了改变

 

预估和实际的执行计划 Estimated and Actual Execution Plan

如前所述，有两种不同的执行计划，第一个是由优化器产生的预估的执行计划（Estimated execution plan），操作符和步骤被贴了Logical标签，代表了优化器的观点，另一个是实际的执行计划(Actual execution plan),代表了实际发生的事情。

执行计划重用

服务器产生执行计划开销是昂贵的，可能的情况下Sql Server会尽量保持和重用执行计划。执行计划生成后就被存储进内存Plan Cache。

执行计划并不是永久驻留内存，它们会慢慢地根据age变化从系统消失，age的计算公式为执行计划的预估开销*被使用的次数，例如一个计划它的开销是10，被引用了5次，那么它的age值就是50。延迟写入（lazywriter）进程负责释放所有类型的cache（包括plan cache），它周期性地扫描cache里的对象，并每次减去一定的age值。如果达到下列条件，执行计划将会从内从中被清除：

1、系统需要更多内存

2、age值达到了0

3、执行计划没有被任何连接（connection）所引用

 

执行计划也不是不可改变的，有些事件或动作会迫使执行计划重新编译。记住这些很重要，因为重新编译执行计划的开销可能非常大，

下面的动作会导致执行计划重新编译：

1、改变查询中引用的表的结构或schema

2、改变了查询中用到的索引

3、删除了查询中用到的索引

4、更新了查询用到的统计数据

5、调用了函数sp_recompile

6、对查询用到的表的keys进行了大量insert或delete操作

7、对带有触发器的表，因inserted和deleted导致的明显增长

8、一个查询中混合了ddl和dml

9、查询执行中改变了SET选项

10、改变了查询使用的临时表的结构或schema

11、改变了查询中用到的动态试图（dynamic views）

12、改变了查询中的游标选项

13、改变了远程行集，就像在分布式分割试图（distributed partitioned view）里边

14、使用客户端游标时，改变了FOR BROWSE选项

 

下面的工具也许有用

//清除cache DBCC FREEPROCCACHE //查看cache里的内容 SELECT [cp].[refcounts] ,[cp].[usecounts] ,[cp].[objtype] ,[st].[dbid] ,[st].[objectid] ,[st].[text] ,[qp].[query_plan] FROM sys.dm_exec_cached_plans cp CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp ;

 

执行计划的形式

1、图形方式

2、文本方式

    SHOWPLAN_ALL

    SHOWPLAN_TEXT

    STATISTICS PROFILE

3、Xml方式

    SHOWPLAN_XML

    STATISTICS_XML

---

 

 

GETTING STARTED

如果你不是sysadmin、dbcreator、db_owner，那么你需要权限查看执行计划

GRANT SHOWPLAN TO [username]

 

 

 

第一个例子非常简单，打开management studio,键入以下查询

SELECT * FROM [dbo].[DatabaseLog];

 

点击动作条上Display estimated Execution Plan按钮，就看到了我们的第一个预估执行计划

 

Figure1

 

我们稍后再加以解释，现在我们再抓一个实际的执行计划，和预估的执行计划不同实际的执行计划并不代表优化器的计算结果，

而是代表了查询执行时实际发生的事情，通常两者是一样的，但是有时也不一样，这取决于存储引擎对执行计划的修改。

点击Include Actual Execution Plan按钮使之处于激活状态，点击Execute

 

 

Figure2

我们这个例子里预估的执行计划和实际的执行计划是一致的。

      左边的SELECT图标你将来会看到很多，通常可以忽略掉，它表示来自关系引擎的最终结果和格式（formatting）。

      右边的Table Scan图标是第一个也是最容易查找的图标。

      通常我们会从右到坐、从上到下地读一个图形执行计划。图标之间的意思是代表算子（operator）之间的数据传递。

这个例子里边，我们只有一个Table Scan算子产生了结果集（SELECT算子表示的）。箭头的宽度代表了数据量的大小，

箭头越宽意味这更多行的数据。这种可视的方式可能会给你错误的印象，你可以将鼠标悬停育箭头之上来查看它所代表的数据

的具体行数。

    每个图标下边都有个百分数，代表了该算子相对于整个查询的开销，注意，这是个比率不是实际的数字，即使是0%也会有

微小的开销。

    图标上边有个百分数表示查询在批量查询中的开销百分比，可以使用批量查询执行多个查询并获取执行计划。

ToolTips

      每个图标和箭头都有一个与之关联的pop-up窗口即ToolTips，鼠标悬停于其上就可以看到。预估执行计划中鼠标悬停于

SELECT图标之上就会看到下图Figure3.

 

Figure3

 

如图，优化器产生了以下几个数字

1. Cached plan size --  查询产生的执行计划将会占用stored procedure cache的多少内存，当你调查cache性能的时候
   这是个很有用的数字，用它能够查看哪些执行计划占用了更多的内存。
2. Estimated Operator Cost --我们在Figure1中已经看到了这个开销百分比。
3. Estimated Subtree Cost -- 它代表了前面的步骤到此步骤累计的开销，记得从右到坐读。这个数字对真实世界没有意义，
   它只是查询优化器用来决定算子开销的算术评估值，它代表了优化器认为的算子需要花费多少时间。
4. Estimated Number of Rows -- 基于统计数据计算而出
5. 上述信息下边还有sql声明

    Table Scan的ToolTips如下图Figure4

 

Figure4

 

每个不同的算子都有一套不同的数据,图4中的算子执行了和图3中有本质区别的工作，因此我们得到了完全不同的详细信息。

Logical Operation 代表了优化器的计算结果，即优化器认为的查询执行时应该发生什么操作，而Physical Operation

代表了实际发生了什么操作，一般来说两者是一致的，但也有例外，第二章有更多讨论。

然后是I/O、CPU、Operator、Subtree的预估开销，Subtree就是执行树中到当前为止的部分，再次重申，从右到坐，

从上到下。所有的预估都是基于列和索引的统计数据。

I/O Cost 和CPU cost不是实际的算子，而是查询优化器在计算中分配的开销数字。这些数字有助于确定开销是I/o密集型还是

cpu密集型。

你可能注意到Operator Cost 和Subtree Cost是一样的，这是因为Table Scan是我们唯一的算子。

Ordered布尔值表示数据是否处于排序状态

NodeID 节点序数，顺序是从左到右的，尽管阅读的最好方式是从右到坐。

算子属性（operator properties）

 右键点击图标“Properties”，可以看到更多的信息，多数已经遇到了，不过还有些新的。

 

Defined Valuesx显示了该操作向进程追加的信息，这些信息可能是基本查询的一部分，我们这个例子里是被选中的列，

也可能是查询过程中内部创建的值，比方说一些确定内部引用完整性的标记，也可能是聚合函数计数的占位符。

Estimated Rebinds和Rewinds这两个值是执行计划里init()被调用的次数。

Foreced Index，使用query hint强制使用某个索引的时候为True。Sql Server通过query hint提供了一些功能，

是用户可以对如何执行查询进行某些控制。

NoExpandHint 此项的概念和Forced Index基本相同，只不过是用于索引视图。

 

文本方式和xml方式捕获执行计划

    略

 

使用Sql Server Profiler自动捕获执行计划

    开发阶段我们可以用上述3个方法之一来捕获执行计划，但是对于一个测试服务器或者正在运行着的系统情况就不一样了。

下列跟踪事件会生成执行计划：

     Showplan Text

     Showplan Text(unencoded)

     Showplan all

     showplan all for query compile

     showplan statistices profile

     showplan xml

     showplan xml for query compile

     performance statistics

     showplan xml statistics profile