sqlserver的執行計畫

標籤：

一：執行計畫產生過程

　　說到執行計畫，首先要知道的是執行計畫大概產生的過程，這樣就可以做到就心中有數了，下面我畫下簡圖：

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. 為了方便查看緩衝，我需要先將所有的緩衝清空，比如下面的語句。

DBCC freeproccacheSELECT c.* FROM dbo.Category AS cJOIN dbo.Product AS pON c.CategoryId=p.CategoryIdWHERE c.CategoryId=23794

2. 通過sys.dm_exec_cached_plans拿到sql和plan的指標（plan_handle），如

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>

 

　　好了，到現在你應該認識到重建執行計畫是不容易的。。。下一篇我們討論討論重用，重編譯，重建等相關情況。

sqlserver的執行計畫