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

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

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

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

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

1.1查詢解析

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

1.2查詢優(yōu)化器

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

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

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

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

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

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

1.3查詢執(zhí)行

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

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

1.4預(yù)估的和實(shí)際的執(zhí)行計(jì)劃

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

1.5重用執(zhí)行計(jì)劃

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

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

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

執(zhí)行計(jì)劃也不是不可改變的，有些事件或動(dòng)作會(huì)迫使執(zhí)行計(jì)劃重新編譯。記住這些很重要，因?yàn)橹匦戮幾g執(zhí)行計(jì)劃的開銷可能非常大，下面的動(dòng)作會(huì)導(dǎo)致執(zhí)行計(jì)劃重新編譯：

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

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

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

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

  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; 

  • 你會(huì)得到一個(gè)錯(cuò)誤

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

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

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

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

SHOWPLAN_ALL 
SHOWPLAN_TEXT 
STATISTICS PROFILE 

3. Xml方式

SHOWPLAN_XML 
STATISTICS_XML 

原文鏈接：