加载中...

第十一站 sqlserver的执行计划

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

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

一：执行计划生成过程

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

1. 分析过程

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

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

2. 编译过程

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

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

<1>子查询生成的sql：

<2>join生成的sql：

 

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

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

3.执行过程

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

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

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

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

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

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

  1. DBCC freeproccache
  2. SELECT c.* FROM dbo.Category AS c
  3. JOIN dbo.Product AS p
  4. ON c.CategoryId=p.CategoryId
  5. WHERE c.CategoryId=23794

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

  1. SELECT * FROM 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还是很费时间的。。。

  

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

好了，到现在你应该认识到重新生成执行计划是不容易的。。。下一篇我们讨论讨论重用，重编译，重新生成等相关情况。

还没有评论.