Sql Server之旅第十一站 简单说说sqlserver的执行计划

我们知道sql在底层的执行给我们上层人员开了一个窗口，那就是执行计划，有了执行计划之后，我们就清楚了那些烂sql是怎么执行的，这样 就可以方便的找到sql的缺陷和优化点。 一：执行计划生成过程 说到执行计划，首先要知道的是执行计划大概生成的过程，这样

　　我们知道sql在底层的执行给我们上层人员开了一个窗口，那就是执行计划，有了执行计划之后，我们就清楚了那些烂sql是怎么执行的，这样

就可以方便的找到sql的缺陷和优化点。

一：执行计划生成过程

　　说到执行计划，首先要知道的是执行计划大概生成的过程，这样就可以做到就心中有数了，下面我画下简图：

1. 分析过程

　　这三个比较容易理解，首先我们要保证sql的语法不能错误，select和join的表是必须存在的，以及你是有执行这个sql的权限，对不对。。。

这样我们就走完了执行计划生命周期的第一个流程。

2. 编译过程

      保证了上面sql这三点的话，引擎就必须硬着头皮看你这么一大坨烂sql，该删的删，该改的改，该转换的转换，比如说你的“子查询”会转化为

“表连接”等等。。。其实也挺难为引擎的，举个例子吧。

子查询生成的sql：

join生成的sql：

 

从上面的两个结果中，你可以看到，大家都是玩join的，如果你仔细看的话，会发现一个是“哈希匹配”，一个是“嵌套循环”，为什么不一样，这

当然是引擎根据很多情况综合评选出来的，比如说：磁盘IO，逻辑读，资源占用，硬件环境等等。。。这也是所谓的“计划选优”操作。

 

3.执行过程

　　既然执行计划都选出来了，理所当然就要执行了，执行完后会把sql和执行计划放入缓存，这样下次有同样的sql过来的时候就可以直接从

Cache中提取了，不需要再次生成计划了，你也看到，生成执行计划还是比较消耗CPU时间的。

 

二：看看sql和执行的计划的缓存

　　刚才也说了，sql和plan都已经放入缓存了，那我的好奇心比较强，我就想看看sql和plan到底在哪，并且长的是个什么丑样子，刚好

sqlserver还是比较能够满足我们G点的。

1. 为了方便查看缓存，我需要先将所有的缓存清空，比如下面的语句。

<span>DBCC</span><span> freeproccache
</span><span>SELECT</span> c.<span>*</span> <span>FROM</span> dbo.Category <span>AS</span><span> c
</span><span>JOIN</span> dbo.Product <span>AS</span><span> p
</span><span>ON</span> c.CategoryId<span>=</span><span>p.CategoryId
</span><span>WHERE</span> c.CategoryId<span>=</span><span>23794</span>

2. 通过sys.dm_exec_cached_plans拿到sql和plan的指针（plan_handle），如下图

<span>SELECT</span> <span>*</span> <span>FROM</span> sys.dm_exec_cached_plans

从图中你看到了两个adhoc（即时查询），分别是我在第一步执行的join查询和我在第二步执行的这个select。

 

3. 现在我们已经拿到了2个adhoc的plan_handle，然后通过dm_exec_sql_text查看他们的sql分别是怎样？

4. 看完text缓存，接下来我们继续看看sql的plan缓存在哪？可以通过dm_exec_query_plan来查看。

上面的query_plan字段就是所谓的执行计划，以xml的形式保存在字段中。。。所以说解析这个xml还是很费时间的。。。

<span>  1</span> <span></span><span>xml version="1.0"</span><span>?></span>
<span>  2</span> <span><span>ShowPlanXML </span><span>xmlns</span><span>="http://schemas.microsoft.com/sqlserver/2004/07/showplan"</span><span> Version</span><span>="1.1"</span><span> Build</span><span>="10.0.1600.22"</span><span>></span>
<span>  3</span>     <span><span>BatchSequence</span><span>></span>
<span>  4</span>         <span><span>Batch</span><span>></span>
<span>  5</span>             <span><span>Statements</span><span>></span>
<span>  6</span>                 <span><span>StmtSimple </span><span>StatementText</span><span>="SELECT c.* FROM dbo.Category AS c
</span><span>  7</span> <span> JOIN dbo.Product AS p
</span><span>  8</span> <span> ON c.CategoryId=p.CategoryId
</span><span>  9</span> <span> WHERE c.CategoryId=23794"</span><span> StatementId</span><span>="1"</span><span> StatementCompId</span><span>="1"</span><span> StatementType</span><span>="SELECT"</span><span> StatementSubTreeCost</span><span>="1.33278"</span><span> StatementEstRows</span><span>="1.03803"</span><span> StatementOptmLevel</span><span>="FULL"</span><span> QueryHash</span><span>="0xB10B821B9B5E6396"</span><span> QueryPlanHash</span><span>="0x8C7B3B1660E28D16"</span><span>></span>
<span> 10</span>                     <span><span>StatementSetOptions </span><span>QUOTED_IDENTIFIER</span><span>="true"</span><span> ARITHABORT</span><span>="true"</span><span> CONCAT_NULL_YIELDS_NULL</span><span>="true"</span><span> ANSI_NULLS</span><span>="true"</span><span> ANSI_PADDING</span><span>="true"</span><span> ANSI_WARNINGS</span><span>="true"</span><span> NUMERIC_ROUNDABORT</span><span>="false"</span> <span>/></span>
<span> 11</span>                     <span><span>QueryPlan </span><span>CachedPlanSize</span><span>="16"</span><span> CompileTime</span><span>="2"</span><span> CompileCPU</span><span>="2"</span><span> CompileMemory</span><span>="168"</span><span>></span>
<span> 12</span>                         <span><span>MissingIndexes</span><span>></span>
<span> 13</span>                             <span><span>MissingIndexGroup </span><span>Impact</span><span>="99.4633"</span><span>></span>
<span> 14</span>                                 <span><span>MissingIndex </span><span>Database</span><span>="[MYPETSHOP]"</span><span> Schema</span><span>="[dbo]"</span><span> Table</span><span>="[Product]"</span><span>></span>
<span> 15</span>                                     <span><span>ColumnGroup </span><span>Usage</span><span>="EQUALITY"</span><span>></span>
<span> 16</span>                                         <span><span>Column </span><span>Name</span><span>="[CategoryId]"</span><span> ColumnId</span><span>="2"</span> <span>/></span>
<span> 17</span>                                     <span></span><span>ColumnGroup</span><span>></span>
<span> 18</span>                                 <span></span><span>MissingIndex</span><span>></span>
<span> 19</span>                             <span></span><span>MissingIndexGroup</span><span>></span>
<span> 20</span>                             <span><span>MissingIndexGroup </span><span>Impact</span><span>="99.4636"</span><span>></span>
<span> 21</span>                                 <span><span>MissingIndex </span><span>Database</span><span>="[MYPETSHOP]"</span><span> Schema</span><span>="[dbo]"</span><span> Table</span><span>="[Product]"</span><span>></span>
<span> 22</span>                                     <span><span>ColumnGroup </span><span>Usage</span><span>="EQUALITY"</span><span>></span>
<span> 23</span>                                         <span><span>Column </span><span>Name</span><span>="[CategoryId]"</span><span> ColumnId</span><span>="2"</span> <span>/></span>
<span> 24</span>                                     <span></span><span>ColumnGroup</span><span>></span>
<span> 25</span>                                 <span></span><span>MissingIndex</span><span>></span>
<span> 26</span>                             <span></span><span>MissingIndexGroup</span><span>></span>
<span> 27</span>                         <span></span><span>MissingIndexes</span><span>></span>
<span> 28</span>                         <span><span>RelOp </span><span>NodeId</span><span>="0"</span><span> PhysicalOp</span><span>="Nested Loops"</span><span> LogicalOp</span><span>="Inner Join"</span><span> EstimateRows</span><span>="1.03803"</span><span> EstimateIO</span><span>="0"</span><span> EstimateCPU</span><span>="4.33898e-006"</span><span> AvgRowSize</span><span>="97"</span><span> EstimatedTotalSubtreeCost</span><span>="1.33278"</span><span> Parallel</span><span>="0"</span><span> EstimateRebinds</span><span>="0"</span><span> EstimateRewinds</span><span>="0"</span><span>></span>
<span> 29</span>                             <span><span>OutputList</span><span>></span>
<span> 30</span>                                 <span><span>ColumnReference </span><span>Database</span><span>="[MYPETSHOP]"</span><span> Schema</span><span>="[dbo]"</span><span> Table</span><span>="[Category]"</span><span> Alias</span><span>="[c]"</span><span> Column</span><span>="CategoryId"</span> <span>/></span>
<span> 31</span>                                 <span><span>ColumnReference </span><span>Database</span><span>="[MYPETSHOP]"</span><span> Schema</span><span>="[dbo]"</span><span> Table</span><span>="[Category]"</span><span> Alias</span><span>="[c]"</span><span> Column</span><span>="Name"</span> <span>/></span>
<span> 32</span>                                 <span><span>ColumnReference </span><span>Database</span><span>="[MYPETSHOP]"</span><span> Schema</span><span>="[dbo]"</span><span> Table</span><span>="[Category]"</span><span> Alias</span><span>="[c]"</span><span> Column</span><span>="Image"</span> <span>/></span>
<span> 33</span>                             <span></span><span>OutputList</span><span>></span>
<span> 34</span>                             <span><span>NestedLoops </span><span>Optimized</span><span>="0"</span><span>></span>
<span> 35</span>                                 <span><span>RelOp </span><span>NodeId</span><span>="1"</span><span> PhysicalOp</span><span>="Clustered Index Seek"</span><span> LogicalOp</span><span>="Clustered Index Seek"</span><span> EstimateRows</span><span>="1"</span><span> EstimateIO</span><span>="0.003125"</span><span> EstimateCPU</span><span>="0.0001581"</span><span> AvgRowSize</span><span>="97"</span><span> EstimatedTotalSubtreeCost</span><span>="0.0032831"</span><span> TableCardinality</span><span>="1.00001e+006"</span><span> Parallel</span><span>="0"</span><span> EstimateRebinds</span><span>="0"</span><span> EstimateRewinds</span><span>="0"</span><span>></span>
<span> 36</span>                                     <span><span>OutputList</span><span>></span>
<span> 37</span>                                         <span><span>ColumnReference </span><span>Database</span><span>="[MYPETSHOP]"</span><span> Schema</span><span>="[dbo]"</span><span> Table</span><span>="[Category]"</span><span> Alias</span><span>="[c]"</span><span> Column</span><span>="CategoryId"</span> <span>/></span>
<span> 38</span>                                         <span><span>ColumnReference </span><span>Database</span><span>="[MYPETSHOP]"</span><span> Schema</span><span>="[dbo]"</span><span> Table</span><span>="[Category]"</span><span> Alias</span><span>="[c]"</span><span> Column</span><span>="Name"</span> <span>/></span>
<span> 39</span>                                         <span><span>ColumnReference </span><span>Database</span><span>="[MYPETSHOP]"</span><span> Schema</span><span>="[dbo]"</span><span> Table</span><span>="[Category]"</span><span> Alias</span><span>="[c]"</span><span> Column</span><span>="Image"</span> <span>/></span>
<span> 40</span>                                     <span></span><span>OutputList</span><span>></span>
<span> 41</span>                                     <span><span>IndexScan </span><span>Ordered</span><span>="1"</span><span> ScanDirection</span><span>="FORWARD"</span><span> ForcedIndex</span><span>="0"</span><span> ForceSeek</span><span>="0"</span><span> NoExpandHint</span><span>="0"</span><span>></span>
<span> 42</span>                                         <span><span>DefinedValues</span><span>></span>
<span> 43</span>                                             <span><span>DefinedValue</span><span>></span>
<span> 44</span>                                                 <span><span>ColumnReference </span><span>Database</span><span>="[MYPETSHOP]"</span><span> Schema</span><span>="[dbo]"</span><span> Table</span><span>="[Category]"</span><span> Alias</span><span>="[c]"</span><span> Column</span><span>="CategoryId"</span> <span>/></span>
<span> 45</span>                                             <span></span><span>DefinedValue</span><span>></span>
<span> 46</span>                                             <span><span>DefinedValue</span><span>></span>
<span> 47</span>                                                 <span><span>ColumnReference </span><span>Database</span><span>="[MYPETSHOP]"</span><span> Schema</span><span>="[dbo]"</span><span> Table</span><span>="[Category]"</span><span> Alias</span><span>="[c]"</span><span> Column</span><span>="Name"</span> <span>/></span>
<span> 48</span>                                             <span></span><span>DefinedValue</span><span>></span>
<span> 49</span>                                             <span><span>DefinedValue</span><span>></span>
<span> 50</span>                                                 <span><span>ColumnReference </span><span>Database</span><span>="[MYPETSHOP]"</span><span> Schema</span><span>="[dbo]"</span><span> Table</span><span>="[Category]"</span><span> Alias</span><span>="[c]"</span><span> Column</span><span>="Image"</span> <span>/></span>
<span> 51</span>                                             <span></span><span>DefinedValue</span><span>></span>
<span> 52</span>                                         <span></span><span>DefinedValues</span><span>></span>
<span> 53</span>                                         <span><span>Object </span><span>Database</span><span>="[MYPETSHOP]"</span><span> Schema</span><span>="[dbo]"</span><span> Table</span><span>="[Category]"</span><span> Index</span><span>="[PK_Category]"</span><span> Alias</span><span>="[c]"</span><span> IndexKind</span><span>="Clustered"</span> <span>/></span>
<span> 54</span>                                         <span><span>SeekPredicates</span><span>></span>
<span> 55</span>                                             <span><span>SeekPredicateNew</span><span>></span>
<span> 56</span>                                                 <span><span>SeekKeys</span><span>></span>
<span> 57</span>                                                     <span><span>Prefix </span><span>ScanType</span><span>="EQ"</span><span>></span>
<span> 58</span>                                                         <span><span>RangeColumns</span><span>></span>
<span> 59</span>                                                             <span><span>ColumnReference </span><span>Database</span><span>="[MYPETSHOP]"</span><span> Schema</span><span>="[dbo]"</span><span> Table</span><span>="[Category]"</span><span> Alias</span><span>="[c]"</span><span> Column</span><span>="CategoryId"</span> <span>/></span>
<span> 60</span>                                                         <span></span><span>RangeColumns</span><span>></span>
<span> 61</span>                                                         <span><span>RangeExpressions</span><span>></span>
<span> 62</span>                                                             <span><span>ScalarOperator </span><span>ScalarString</span><span>="(23794)"</span><span>></span>
<span> 63</span>                                                                 <span><span>Const </span><span>ConstValue</span><span>="(23794)"</span> <span>/></span>
<span> 64</span>                                                             <span></span><span>ScalarOperator</span><span>></span>
<span> 65</span>                                                         <span></span><span>RangeExpressions</span><span>></span>
<span> 66</span>                                                     <span></span><span>Prefix</span><span>></span>
<span> 67</span>                                                 <span></span><span>SeekKeys</span><span>></span>
<span> 68</span>                                             <span></span><span>SeekPredicateNew</span><span>></span>
<span> 69</span>                                         <span></span><span>SeekPredicates</span><span>></span>
<span> 70</span>                                     <span></span><span>IndexScan</span><span>></span>
<span> 71</span>                                 <span></span><span>RelOp</span><span>></span>
<span> 72</span>                                 <span><span>RelOp </span><span>NodeId</span><span>="2"</span><span> PhysicalOp</span><span>="Clustered Index Scan"</span><span> LogicalOp</span><span>="Clustered Index Scan"</span><span> EstimateRows</span><span>="1.03803"</span><span> EstimateIO</span><span>="1.18831"</span><span> EstimateCPU</span><span>="0.0983419"</span><span> AvgRowSize</span><span>="11"</span><span> EstimatedTotalSubtreeCost</span><span>="1.28665"</span><span> TableCardinality</span><span>="89259"</span><span> Parallel</span><span>="0"</span><span> EstimateRebinds</span><span>="0"</span><span> EstimateRewinds</span><span>="0"</span><span>></span>
<span> 73</span>                                     <span><span>OutputList </span><span>/></span>
<span> 74</span>                                     <span><span>IndexScan </span><span>Ordered</span><span>="0"</span><span> ForcedIndex</span><span>="0"</span><span> NoExpandHint</span><span>="0"</span><span>></span>
<span> 75</span>                                         <span><span>DefinedValues </span><span>/></span>
<span> 76</span>                                         <span><span>Object </span><span>Database</span><span>="[MYPETSHOP]"</span><span> Schema</span><span>="[dbo]"</span><span> Table</span><span>="[Product]"</span><span> Index</span><span>="[PK_Product]"</span><span> Alias</span><span>="[p]"</span><span> IndexKind</span><span>="Clustered"</span> <span>/></span>
<span> 77</span>                                         <span><span>Predicate</span><span>></span>
<span> 78</span>                                             <span><span>ScalarOperator </span><span>ScalarString</span><span>="[MYPETSHOP].[dbo].[Product].[CategoryId] as [p].[CategoryId]=(23794)"</span><span>></span>
<span> 79</span>                                                 <span><span>Compare </span><span>CompareOp</span><span>="EQ"</span><span>></span>
<span> 80</span>                                                     <span><span>ScalarOperator</span><span>></span>
<span> 81</span>                                                         <span><span>Identifier</span><span>></span>
<span> 82</span>                                                             <span><span>ColumnReference </span><span>Database</span><span>="[MYPETSHOP]"</span><span> Schema</span><span>="[dbo]"</span><span> Table</span><span>="[Product]"</span><span> Alias</span><span>="[p]"</span><span> Column</span><span>="CategoryId"</span> <span>/></span>
<span> 83</span>                                                         <span></span><span>Identifier</span><span>></span>
<span> 84</span>                                                     <span></span><span>ScalarOperator</span><span>></span>
<span> 85</span>                                                     <span><span>ScalarOperator</span><span>></span>
<span> 86</span>                                                         <span><span>Const </span><span>ConstValue</span><span>="(23794)"</span> <span>/></span>
<span> 87</span>                                                     <span></span><span>ScalarOperator</span><span>></span>
<span> 88</span>                                                 <span></span><span>Compare</span><span>></span>
<span> 89</span>                                             <span></span><span>ScalarOperator</span><span>></span>
<span> 90</span>                                         <span></span><span>Predicate</span><span>></span>
<span> 91</span>                                     <span></span><span>IndexScan</span><span>></span>
<span> 92</span>                                 <span></span><span>RelOp</span><span>></span>
<span> 93</span>                             <span></span><span>NestedLoops</span><span>></span>
<span> 94</span>                         <span></span><span>RelOp</span><span>></span>
<span> 95</span>                     <span></span><span>QueryPlan</span><span>></span>
<span> 96</span>                 <span></span><span>StmtSimple</span><span>></span>
<span> 97</span>             <span></span><span>Statements</span><span>></span>
<span> 98</span>         <span></span><span>Batch</span><span>></span>
<span> 99</span>     <span></span><span>BatchSequence</span><span>></span>
<span>100</span> <span></span><span>ShowPlanXML</span><span>></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span>

View Code