執(zhí)行計劃，簡單的說就是查詢優(yōu)化器計算的結果，表示執(zhí)行被提交查詢的效率***的方式。執(zhí)行計劃告訴你查詢是如何被執(zhí)行的，因此它是DBA診斷低性能查詢的基礎手段。

1.查詢提交后發(fā)生了什么

當查詢被提交到Sql Server DataBase之后，很多過程就開始工作，最終目的是將數(shù)據(jù)盡快返回給用戶或者存儲起來，同時保持數(shù)據(jù)的一致性。這些過程為每個提交到服務器的查詢服務，因而有很多的不同的動作同時發(fā)生在服務器上，我們只關注T-SQL相關的事情，粗略的分為兩個階段：

• 關系引擎(relational engine)中發(fā)生的過程
• 存儲引擎(storage engine)中發(fā)生的過程

在關系引擎中，查詢優(yōu)化器解析和處理查詢，產生執(zhí)行計劃，然后執(zhí)行計劃被送到存儲引擎(二進制方式)，存儲引擎使用執(zhí)行計劃來獲取或更新數(shù)據(jù)。鎖定、索引維護、事物等都發(fā)生在存儲引擎里頭。由于執(zhí)行計劃是由關系引擎產生的，我們將主要關注關系引擎。

1.1查詢解析

T-sql查詢到達服務器后去的***個地方就是關系引擎，它被傳遞給一個進程來檢查拼寫和形式，這個處理進程就是查詢解析過程。解析過程的輸出是一個解析樹。解析樹代表了執(zhí)行查詢的邏輯步驟。如果T-sql語句不是一個數(shù)據(jù)操縱語言(DML)聲明就不會被優(yōu)化，舉個例子，對于創(chuàng)建table，系統(tǒng)只有一種“正確”的方式，那么就沒有機會來提升性能。如果T-sql是DML聲明，解析樹就被傳遞給一個叫algebrizer的進程，algebrizer解析查詢引用到的所有的對象、表、列的名字，并且識別列的類型(varchar(50) vs nvarchar(25)等)，除此之外還要執(zhí)行一個叫做聚合綁定 的過程來決定聚合的位置。algebrizer進程很重要，因為查詢里可能包或了別名、同義詞或者不存在的名字，這些需要被解析，或者查詢引用了不存在的對象。algebrizer的輸出是query processsor tree，二進制形式，然后被傳遞給了查詢優(yōu)化器。

1.2查詢優(yōu)化器

查詢優(yōu)化器決定了數(shù)據(jù)能否訪問索引、使用哪種連接還有其他很多東西。這種決定是基于開銷的，所需的cpu、io等。查詢優(yōu)化器將會產生并評估很多的計劃(除非cache里已經有了)，一般來說，選擇開銷***的那個，比如運行最快，使用最少的資源、cpu、I/O的那個。執(zhí)行速度仍然是最重要的因素，如果能夠更快返回結果，優(yōu)化器會選擇cpu密集型的過程。有時候優(yōu)化器也會選擇效率較低的計劃，如果它認為花時間去評估很多的執(zhí)行計劃還不如采用較低效率的過程。如果你提交了一個非常簡單的查詢，比方說，單表查詢、沒有索引、沒有聚合、沒有計算，那么優(yōu)化器就不會花時間來計算優(yōu)化，而是簡單的使用trival plan。

如果查詢是非Trival的，那么優(yōu)化器就會計算開銷然后選擇一個計劃。因此它需要依賴sql server服務器維護的統(tǒng)計數(shù)據(jù)。統(tǒng)計數(shù)據(jù)是數(shù)據(jù)庫收集的關于列和索引的數(shù)據(jù)，它描述了數(shù)據(jù)的分布(distribution)、唯一性(uniqueness)和選擇性(selectivity)。構成統(tǒng)計數(shù)據(jù)的信息使用一個直方圖(histogram)和表格(tabulation)來表示，它是從200個平均分布的數(shù)據(jù)點(data Points)取出來的表示特定數(shù)據(jù)的出現(xiàn)次數(shù)。這種“關于數(shù)據(jù)的數(shù)據(jù)”給優(yōu)化器提供了計算所需的必要信息。

如果列和索引相關的統(tǒng)計數(shù)據(jù)存在，那么優(yōu)化器就會使用它們來計算。缺省地，系統(tǒng)會為所有索引和那些用作謂詞(predicate)、where子句的一部分、join on子句的一部分的列創(chuàng)建和更新統(tǒng)計數(shù)據(jù)。Table變量不會產生統(tǒng)計數(shù)據(jù)，優(yōu)化器始終假定它只有一行而無視它真正的大小。臨時表有統(tǒng)計數(shù)據(jù)，和***表的統(tǒng)計數(shù)據(jù)存儲在同一個直方圖里供優(yōu)化器使用。

優(yōu)化器使用這些統(tǒng)計數(shù)據(jù)和query processor tree一起決定***的執(zhí)行計劃。這就意味著，它需要測試一系列的計劃，測試不同的join類型，組織join的順序，嘗試不同的索引等等，直到達成它認為的最快的執(zhí)行計劃。在這個計算中，每一步都賦予了一個數(shù)值，代表了優(yōu)化器預估的時間開銷(estimated cost)，每一步的開銷加起來就是執(zhí)行計劃的開銷。

有必要指出，預估的開銷畢竟是預估的，如果有無限的時間和完整的***的統(tǒng)計數(shù)據(jù)，優(yōu)化器就能找到執(zhí)行查詢的***計劃，但是優(yōu)化器是試圖在最短的時間找到***的執(zhí)行計劃，并且明顯的，可用的統(tǒng)計數(shù)據(jù)的質量也是有限的，因此，雖然這個開銷估算是個非常有用的手段，但是不能精確的反映現(xiàn)實。

優(yōu)化器決定執(zhí)行計劃后，實際的執(zhí)行計劃就被創(chuàng)建并且存儲進內存空間plan cache，除非相同的執(zhí)行計劃cache里已經存在。優(yōu)化器產生可能的執(zhí)行計劃(potential plans)，和cache里邊已經存在的進行比較，如果匹配就是用cache里邊的那個。

1.3查詢執(zhí)行

執(zhí)行計劃產生后，操作就轉移到了存儲引擎，在這里根據(jù)執(zhí)行計劃實際執(zhí)行查詢。這里不再詳細討論，除了一點，千辛萬苦生成的執(zhí)行計劃和設計執(zhí)行的可能并不一樣，比方說一下情景：

• 執(zhí)行計劃超出了并行執(zhí)行(parallel execution)的界限
  • parallel execution 利用多處理器提高執(zhí)行效率
• 統(tǒng)計數(shù)據(jù)過期或者發(fā)生了改變

1.4預估的和實際的執(zhí)行計劃

如前所述，有兩種不同的執(zhí)行計劃，***個是由優(yōu)化器產生的預估的執(zhí)行計劃(Estimated execution plan)，操作符和步驟被貼了Logical標簽，代表了優(yōu)化器的觀點，另一個是實際的執(zhí)行計劃(Actual execution plan),代表了實際發(fā)生的事情。

1.5重用執(zhí)行計劃

服務器產生執(zhí)行計劃開銷是昂貴的，可能的情況下Sql Server會盡量保持和重用執(zhí)行計劃。執(zhí)行計劃生成后就被存儲進內存Plan Cache。

執(zhí)行計劃并不是***駐留內存，它們會慢慢地根據(jù)age變化從系統(tǒng)消失，age的計算公式為執(zhí)行計劃的預估開銷*被使用的次數(shù)，例如一個計劃它的開銷是10，被引用了5次，那么它的age值就是50。延遲寫入(lazywriter)進程負責釋放所有類型的cache(包括plan cache)，它周期性地掃描cache里的對象，并每次減去一定的age值。如果達到下列條件，執(zhí)行計劃將會從內從中被清除：

1. 系統(tǒng)需要更多內存
2. age值達到了0
3. 執(zhí)行計劃沒有被任何連接(connection)所引用

執(zhí)行計劃也不是不可改變的，有些事件或動作會迫使執(zhí)行計劃重新編譯。記住這些很重要，因為重新編譯執(zhí)行計劃的開銷可能非常大，下面的動作會導致執(zhí)行計劃重新編譯：

1. 改變查詢中引用的表的結構或schema
2. 改變了查詢中用到的索引
3. 刪除了查詢中用到的索引
4. 更新了查詢用到的統(tǒng)計數(shù)據(jù)
5. 調用了函數(shù)sp_recompile
6. 對查詢用到的表的keys進行了大量insert或delete操作
7. 對帶有觸發(fā)器的表，因inserted和deleted導致的明顯增長
8. 一個查詢中混合了ddl和dml
9. 查詢執(zhí)行中改變了SET選項
10. 改變了查詢使用的臨時表的結構或schema
11. 改變了查詢中用到的動態(tài)試圖(dynamic views)
12. 改變了查詢中的游標選項
13. 改變了遠程行集，就像在分布式分割試圖(distributed partitioned view)里邊
14. 使用客戶端游標時，改變了FOR BROWSE選項

1.6為何預估和實際的執(zhí)行計劃可能不同

一般情況下，你看到的預估執(zhí)行計劃和實際執(zhí)行計劃很可能是一樣的，然而當環(huán)境改變時可能會導致二者的不同。

• 陳舊的統(tǒng)計數(shù)據(jù)
  • 統(tǒng)計數(shù)據(jù)和實際數(shù)據(jù)間的差異是導致兩個執(zhí)行計劃不同的主要原因。通常發(fā)生在有數(shù)據(jù)插入和刪除，改變了索引的鍵值以及分布。
  • 為了降低操作成本，原子性的統(tǒng)計數(shù)據(jù)操作是取樣于數(shù)據(jù)的子集。這就意味著，隨著時間推移，統(tǒng)計數(shù)據(jù)就越來越不能準確反映實際數(shù)據(jù)。
  • 這不僅會導致兩個執(zhí)行計劃間的差異，還會導致產生“壞”的執(zhí)行計劃。
• 非法的預估執(zhí)行計劃
  • 某些情況下，預估的執(zhí)行計劃根本無法工作，比如下邊的例子：

  CREATE TABLE TempTable 
  ( 
  Id INT IDENTITY(1, 1) 
  ,Dsc NVARCHAR(50) 
  ); 
  INSERT INTO TempTable ( Dsc ) 
  SELECT [Name] 
  FROM [Sales].[Store]; 
  SELECT * 
  FROM TempTable; 
  DROP TABLE TempTable; 

  • 你會得到一個錯誤

  Msg 208, Level 16, State 1, Line 7 
  Invalid object name 'TempTable'. 

  • 優(yōu)化器用于產生預估的執(zhí)行計劃，并不執(zhí)行那個T-Sql。當通過algebrizer來運行聲明的時候，由于查詢并沒有被執(zhí)行，臨時表并不存在，這就會導致錯誤。
• 請求并行計算
  • 計劃遇到并行計算的瓶頸時，會創(chuàng)建兩個計劃，實際執(zhí)行哪個取決于查詢引擎。所以你可能在預估執(zhí)行計劃里看到有(或沒有)并行操作符。當計劃實際被執(zhí)行時，
  • 查詢引擎決定了要么它不支持并行計算或者調用并行查詢后，你可能看到一個完全不同的計劃。

1.7執(zhí)行計劃的格式

1. 圖形方式
2. 文本方式

SHOWPLAN_ALL 
SHOWPLAN_TEXT 
STATISTICS PROFILE 

3. Xml方式

SHOWPLAN_XML 
STATISTICS_XML 

原文鏈接：