SQL Server之旅（11）：簡單說說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. 為了方便查看緩存，我需要先將所有的緩存清空，比如下面的語句。
DBCC freeproccache SELECT c.* FROM dbo.Category AS c JOIN dbo.Product AS p ON c.CategoryId=p.CategoryId WHERE 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還是很費時間的。。。
<?xml version="1.0"?>
<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.1" Build="10.0.1600.22">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementText="SELECT c.* FROM dbo.Category AS c
JOIN dbo.Product AS p
ON c.CategoryId=p.CategoryId
WHERE c.CategoryId=23794" StatementId="1" StatementCompId="1" StatementType="SELECT" StatementSubTreeCost="1.33278" StatementEstRows="1.03803" StatementOptmLevel="FULL" QueryHash="0xB10B821B9B5E6396" QueryPlanHash="0x8C7B3B1660E28D16">
<StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false" />
<QueryPlan CachedPlanSize="16" CompileTime="2" CompileCPU="2" CompileMemory="168">
<MissingIndexes>
<MissingIndexGroup Impact="99.4633">
<MissingIndex Database="[MYPETSHOP]" Schema="[dbo]" Table="[Product]">
<ColumnGroup Usage="EQUALITY">
<Column Name="[CategoryId]" ColumnId="2" />
</ColumnGroup>
</MissingIndex>
</MissingIndexGroup>
<MissingIndexGroup Impact="99.4636">
<MissingIndex Database="[MYPETSHOP]" Schema="[dbo]" Table="[Product]">
<ColumnGroup Usage="EQUALITY">
<Column Name="[CategoryId]" ColumnId="2" />
</ColumnGroup>
</MissingIndex>
</MissingIndexGroup>
</MissingIndexes>
<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">
<OutputList>
<ColumnReference Database="[MYPETSHOP]" Schema="[dbo]" Table="[Category]" Alias="[c]" Column="CategoryId" />
<ColumnReference Database="[MYPETSHOP]" Schema="[dbo]" Table="[Category]" Alias="[c]" Column="Name" />
<ColumnReference Database="[MYPETSHOP]" Schema="[dbo]" Table="[Category]" Alias="[c]" Column="Image" />
</OutputList>
<NestedLoops Optimized="0">
<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">
<OutputList>
<ColumnReference Database="[MYPETSHOP]" Schema="[dbo]" Table="[Category]" Alias="[c]" Column="CategoryId" />
<ColumnReference Database="[MYPETSHOP]" Schema="[dbo]" Table="[Category]" Alias="[c]" Column="Name" />
<ColumnReference Database="[MYPETSHOP]" Schema="[dbo]" Table="[Category]" Alias="[c]" Column="Image" />
</OutputList>
<IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" NoExpandHint="0">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[MYPETSHOP]" Schema="[dbo]" Table="[Category]" Alias="[c]" Column="CategoryId" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[MYPETSHOP]" Schema="[dbo]" Table="[Category]" Alias="[c]" Column="Name" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[MYPETSHOP]" Schema="[dbo]" Table="[Category]" Alias="[c]" Column="Image" />
</DefinedValue>
</DefinedValues>
<Object Database="[MYPETSHOP]" Schema="[dbo]" Table="[Category]" Index="[PK_Category]" Alias="[c]" IndexKind="Clustered" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[MYPETSHOP]" Schema="[dbo]" Table="[Category]" Alias="[c]" Column="CategoryId" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="(23794)">
<Const ConstValue="(23794)" />
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
</IndexScan>
</RelOp>
<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">
<OutputList />
<IndexScan Ordered="0" ForcedIndex="0" NoExpandHint="0">
<DefinedValues />
<Object Database="[MYPETSHOP]" Schema="[dbo]" Table="[Product]" Index="[PK_Product]" Alias="[p]" IndexKind="Clustered" />
<Predicate>
<ScalarOperator ScalarString="[MYPETSHOP].[dbo].[Product].[CategoryId] as [p].[CategoryId]=(23794)">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[MYPETSHOP]" Schema="[dbo]" Table="[Product]" Alias="[p]" Column="CategoryId" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(23794)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Predicate>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML> 好了，到現在你應該認識到重新生成執行計劃是不容易的。。。下一篇我們討論討論重用，重編譯，重新生成等相關情況。