Sql Server之旅第十一站 简单说说sqlserver的执行计划
- WBOYオリジナル
- 2016-06-07 15:18:34857ブラウズ
我们知道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
声明：
この記事の内容はネチズンが自主的に寄稿したものであり、著作権は原著者に帰属します。このサイトは、それに相当する法的責任を負いません。盗作または侵害の疑いのあるコンテンツを見つけた場合は、admin@php.cn までご連絡ください。