Sql Server之旅第十一站 简单说说sqlserver的执行计划
- WBOYOriginal
- 2016-06-07 15:18:34853browse
我们知道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
Statement：
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Previous article：系统运维技巧（五）MySQL的其他安装方式Next article：codeforces Round #275(div2) D解题报告