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值。如果達到下列條件，執行計畫將會從內從中被清除：

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

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執行計畫的格式

Xml方式

 

  
SHOWPLAN_XML 
  
STATISTICS_XML