SQL執行計劃解析之執行計劃基礎上篇
執行計劃，簡單的說就是查詢優化器計算的結果，表示執行被提交查詢的效率***的方式。執行計劃告訴你查詢是如何被執行的，因此它是DBA診斷低性能查詢的基礎手段。
1.查詢提交后發生了什么
當查詢被提交到Sql Server DataBase之后，很多過程就開始工作，最終目的是將數據盡快返回給用戶或者存儲起來，同時保持數據的一致性。這些過程為每個提交到服務器的查詢服務，因而有很多的不同的動作同時發生在服務器上，我們只關注T-SQL相關的事情，粗略的分為兩個階段：
- 關系引擎(relational engine)中發生的過程
- 存儲引擎(storage engine)中發生的過程
在關系引擎中，查詢優化器解析和處理查詢，產生執行計劃，然后執行計劃被送到存儲引擎(二進制方式)，存儲引擎使用執行計劃來獲取或更新數據。鎖定、索引維護、事物等都發生在存儲引擎里頭。由于執行計劃是由關系引擎產生的，我們將主要關注關系引擎。
1.1查詢解析
T-sql查詢到達服務器后去的***個地方就是關系引擎，它被傳遞給一個進程來檢查拼寫和形式，這個處理進程就是查詢解析過程。解析過程的輸出是一個解析樹。解析樹代表了執行查詢的邏輯步驟。如果T-sql語句不是一個數據操縱語言(DML)聲明就不會被優化，舉個例子，對于創建table，系統只有一種“正確”的方式，那么就沒有機會來提升性能。如果T-sql是DML聲明，解析樹就被傳遞給一個叫algebrizer的進程，algebrizer解析查詢引用到的所有的對象、表、列的名字，并且識別列的類型(varchar(50) vs nvarchar(25)等)，除此之外還要執行一個叫做聚合綁定 的過程來決定聚合的位置。algebrizer進程很重要，因為查詢里可能包或了別名、同義詞或者不存在的名字，這些需要被解析，或者查詢引用了不存在的對象。algebrizer的輸出是query processsor tree，二進制形式，然后被傳遞給了查詢優化器。
1.2查詢優化器
查詢優化器決定了數據能否訪問索引、使用哪種連接還有其他很多東西。這種決定是基于開銷的，所需的cpu、io等。查詢優化器將會產生并評估很多的計劃(除非cache里已經有了)，一般來說，選擇開銷***的那個，比如運行最快，使用最少的資源、cpu、I/O的那個。執行速度仍然是最重要的因素，如果能夠更快返回結果，優化器會選擇cpu密集型的過程。有時候優化器也會選擇效率較低的計劃，如果它認為花時間去評估很多的執行計劃還不如采用較低效率的過程。如果你提交了一個非常簡單的查詢，比方說，單表查詢、沒有索引、沒有聚合、沒有計算，那么優化器就不會花時間來計算優化，而是簡單的使用trival plan。
如果查詢是非Trival的，那么優化器就會計算開銷然后選擇一個計劃。因此它需要依賴sql server服務器維護的統計數據。統計數據是數據庫收集的關于列和索引的數據，它描述了數據的分布(distribution)、唯一性(uniqueness)和選擇性(selectivity)。構成統計數據的信息使用一個直方圖(histogram)和表格(tabulation)來表示，它是從200個平均分布的數據點(data Points)取出來的表示特定數據的出現次數。這種“關于數據的數據”給優化器提供了計算所需的必要信息。
如果列和索引相關的統計數據存在，那么優化器就會使用它們來計算。缺省地，系統會為所有索引和那些用作謂詞(predicate)、where子句的一部分、join on子句的一部分的列創建和更新統計數據。Table變量不會產生統計數據，優化器始終假定它只有一行而無視它真正的大小。臨時表有統計數據，和***表的統計數據存儲在同一個直方圖里供優化器使用。
優化器使用這些統計數據和query processor tree一起決定***的執行計劃。這就意味著，它需要測試一系列的計劃，測試不同的join類型，組織join的順序，嘗試不同的索引等等，直到達成它認為的最快的執行計劃。在這個計算中，每一步都賦予了一個數值，代表了優化器預估的時間開銷(estimated cost)，每一步的開銷加起來就是執行計劃的開銷。
有必要指出，預估的開銷畢竟是預估的，如果有無限的時間和完整的***的統計數據，優化器就能找到執行查詢的***計劃，但是優化器是試圖在最短的時間找到***的執行計劃，并且明顯的，可用的統計數據的質量也是有限的，因此，雖然這個開銷估算是個非常有用的手段，但是不能精確的反映現實。
優化器決定執行計劃后，實際的執行計劃就被創建并且存儲進內存空間plan cache，除非相同的執行計劃cache里已經存在。優化器產生可能的執行計劃(potential plans)，和cache里邊已經存在的進行比較，如果匹配就是用cache里邊的那個。
1.3查詢執行
執行計劃產生后，操作就轉移到了存儲引擎，在這里根據執行計劃實際執行查詢。這里不再詳細討論，除了一點，千辛萬苦生成的執行計劃和設計執行的可能并不一樣，比方說一下情景：
- 執行計劃超出了并行執行(parallel execution)的界限
- parallel execution 利用多處理器提高執行效率
- 統計數據過期或者發生了改變
1.4預估的和實際的執行計劃
如前所述，有兩種不同的執行計劃，***個是由優化器產生的預估的執行計劃(Estimated execution plan)，操作符和步驟被貼了Logical標簽，代表了優化器的觀點，另一個是實際的執行計劃(Actual execution plan),代表了實際發生的事情。
1.5重用執行計劃
服務器產生執行計劃開銷是昂貴的，可能的情況下Sql Server會盡量保持和重用執行計劃。執行計劃生成后就被存儲進內存Plan Cache。
執行計劃并不是***駐留內存，它們會慢慢地根據age變化從系統消失，age的計算公式為執行計劃的預估開銷*被使用的次數，例如一個計劃它的開銷是10，被引用了5次，那么它的age值就是50。延遲寫入(lazywriter)進程負責釋放所有類型的cache(包括plan cache)，它周期性地掃描cache里的對象，并每次減去一定的age值。如果達到下列條件，執行計劃將會從內從中被清除：
- 系統需要更多內存
- age值達到了0
- 執行計劃沒有被任何連接(connection)所引用
執行計劃也不是不可改變的，有些事件或動作會迫使執行計劃重新編譯。記住這些很重要，因為重新編譯執行計劃的開銷可能非常大，下面的動作會導致執行計劃重新編譯：
- 改變查詢中引用的表的結構或schema
- 改變了查詢中用到的索引
- 刪除了查詢中用到的索引
- 更新了查詢用到的統計數據
- 調用了函數sp_recompile
- 對查詢用到的表的keys進行了大量insert或delete操作
- 對帶有觸發器的表，因inserted和deleted導致的明顯增長
- 一個查詢中混合了ddl和dml
- 查詢執行中改變了SET選項
- 改變了查詢使用的臨時表的結構或schema
- 改變了查詢中用到的動態試圖(dynamic views)
- 改變了查詢中的游標選項
- 改變了遠程行集，就像在分布式分割試圖(distributed partitioned view)里邊
- 使用客戶端游標時，改變了FOR BROWSE選項
1.6為何預估和實際的執行計劃可能不同
一般情況下，你看到的預估執行計劃和實際執行計劃很可能是一樣的，然而當環境改變時可能會導致二者的不同。
- 陳舊的統計數據
- 統計數據和實際數據間的差異是導致兩個執行計劃不同的主要原因。通常發生在有數據插入和刪除，改變了索引的鍵值以及分布。
- 為了降低操作成本，原子性的統計數據操作是取樣于數據的子集。這就意味著，隨著時間推移，統計數據就越來越不能準確反映實際數據。
- 這不僅會導致兩個執行計劃間的差異，還會導致產生“壞”的執行計劃。
- 非法的預估執行計劃
- 某些情況下，預估的執行計劃根本無法工作，比如下邊的例子：
- 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'.
- 優化器用于產生預估的執行計劃，并不執行那個T-Sql。當通過algebrizer來運行聲明的時候，由于查詢并沒有被執行，臨時表并不存在，這就會導致錯誤。
- 計劃遇到并行計算的瓶頸時，會創建兩個計劃，實際執行哪個取決于查詢引擎。所以你可能在預估執行計劃里看到有(或沒有)并行操作符。當計劃實際被執行時，
- 查詢引擎決定了要么它不支持并行計算或者調用并行查詢后，你可能看到一個完全不同的計劃。
1.7執行計劃的格式
- 圖形方式
- 文本方式
- SHOWPLAN_ALL
- SHOWPLAN_TEXT
- STATISTICS PROFILE
- Xml方式
- SHOWPLAN_XML
- STATISTICS_XML
原文鏈接：
