SQL執行計畫解析（1）- 執行計畫基礎（下）

Getting Started

如果你不是sysadmin、dbcreator、db_owner，那麼你需要許可權查看執行計畫

GRANT SHOWPLAN TO [username]

第一個例子非常簡單，開啟management studio,鍵入以下查詢

SELECT *
FROM [dbo].[DatabaseLog];

點擊動作條上Display estimated Execution Plan按鈕，就看到了我們的第一個預估執行計畫

圖1-1

我們稍後再加以解釋，現在我們再抓一個實際的執行計畫，和預估的執行計畫不同，實際的執行計畫並不代表最佳化器的計算結果，而是代表了查詢執行時實際發生的事情，通常兩者是一樣的，但是有時也不一樣，這取決於儲存引擎對執行計畫的修改。點擊Include Actual Execution Plan按鈕使之處於啟用狀態，點擊Execute。

我們這個例子裡預估的執行計畫和實際的執行計畫是一致的。 左邊的SELECT表徵圖你將來會看到很多，通常可以忽略掉，它表示來自關聯式引擎的最終結果和格式（formatting）。

右邊的Table Scan表徵圖是第一個也是最容易尋找的表徵圖。 通常我們會從右到坐、從上到下地讀一個圖形執行計畫。表徵圖之間的意思是代表運算子（operator）之間的資料傳遞。

這個例子裡邊，我們只有一個Table Scan運算子產生了結果集。箭頭的寬度代表了資料量的大小，箭頭越寬意味這更多行的資料。這種可視的方式可能會給你錯誤的印象，你可以將滑鼠移至上方於箭頭之上來查看它所代表的資料的具體行數。 每個表徵圖下邊都有個百分數，代表了該運算子相對於整個查詢的開銷，注意，這是個比率不是實際的數字，即使是0%也會有微小的開銷。表徵圖上邊有個百分數表示查詢在批量查詢中的開銷百分比，可以使用批量查詢執行多個查詢並擷取執行計畫。

ToolTips

每個表徵圖和箭頭都有一個與之關聯的快顯視窗即ToolTips，滑鼠移至上方於其上就可以看到。預估執行計畫中滑鼠移至上方於SELECT表徵圖之上就會看到1-3.

，最佳化器產生了以下幾個數字

1. Cached plan size --  查詢產生的執行計畫將會佔用stored procedure cache的多少記憶體，當你調查cache效能的時候這是個很有用的數字，用它能夠查看哪些執行計畫佔用了更多的記憶體。
2. Estimated Operator Cost --我們在圖1-1中已經看到了這個開銷百分比。
3. Estimated Subtree Cost -- 它代表了前面的步驟到此步驟累計的開銷，記得從右到坐讀。這個數字對真實世界沒有意義，它只是查詢最佳化工具用來決定運算子開銷的算術評估值，它代表了最佳化器認為的運算子需要花費多少時間。
4. Estimated Number of Rows -- 基於統計資料計算而出
5. 上述資訊下邊還有sql聲明

Table Scan的ToolTips如1-4

圖1-4

Logical Operation 代表了最佳化器的計算結果，即最佳化器認為的查詢執行時應該發生什麼操作，而Physical Operation代表了實際發生了什麼操作，一般來說兩者是一致的，但也有例外，第二章有更多討論.

然後是I/O、CPU、Operator、Subtree的預估開銷，Subtree就是執行樹中到當前為止的部分。所有的預估都是基於列和索引的統計資料。I/O Cost 和CPU cost不是實際的運算子，而是查詢最佳化工具在計算中分配的開銷數字。這些數字有助於確定開銷是I/o密集型還是cpu密集型。你可能注意到Operator Cost 和Subtree Cost是一樣的，這是因為Table Scan是我們唯一的運算子。Ordered布爾值表示資料是否處於排序狀態。NodeID 節點序數，順序是從左至右的，儘管閱讀的最好方式是從右到坐。

運算子屬性

右鍵點擊表徵圖，選properties，可以看到更多資訊，多數我們已經遇到了，不過還有些新的。

圖1-5

Defined Values顯示了該操作向過程追加的資訊，這些資訊可能是基本查詢的一部分，我們這個例子裡是被選中的列，也可能是查詢過程中內部建立的值，比方說一些確定內部參考完整性的標記，也可能是彙總函式計數的預留位置。Estimated Rebinds和Rewinds這兩個值是執行計畫裡init()被調用的次數。Foreced Index，使用query hint強制使用某個索引的時候為True。Sql Server通過query hint提供了一些功能，使使用者可以對如何執行查詢進行某些控制。NoExpandHint 此項的概念和Forced Index基本相同，只不過是用於索引檢視表。

文本執行計畫和Xml執行計畫

要想擷取文本執行計畫，在查詢開始加上一個命令就可以了

SET SHOWPLAN_ALL ON;

關閉文本計劃，使用

SET SHOWPLAN_ALL OFF;

擷取實際的文本計劃，使用

SET STATISTICS PROFILE ON
和
SET STATISTICS PROFILE OFF

擷取實際的和預估的xml執行計畫，使用

SET SHOWPLAN_XML ON
和
SET SHOWPLAN_XML OFF

ps:關於文本和Xml執行計畫的更多細節就略過了

使用Sql Server Profiler自動捕獲執行計畫

在開發階段，我們可以通過上述幾種方式來捕獲執行計畫。但是在測試伺服器或者運行中的伺服器，可能並不允許我們這樣做，Sql Server2005中可以使用Profiler在執行查詢的同時捕獲Xml執行計畫。

Profiler是一個強大的工具，使使用者可以捕獲關於事件的資料。Profiler的事件可以通過GUI介面進行手動跟蹤，也可以自己定義跟蹤，並在特定的時間自動運行。這些跟蹤資訊可以通過螢幕查看，也可以發送到檔案或資料庫的表中。

執行計畫事件

• Showplan Text : 此事件在每次查詢時均觸發，產生的計劃類型和Showplan_Text聲明相同，是Showplan Xml產生的內容的子集。
• Showplan Text (unencoded) : 同上，不過此事件是以字串形式展示資訊而不是二進位方式。
• Showplan All : 此事件在每次查詢時均觸發，產生的計劃類型和Showplan_all聲明相同.
• Showplan All for Query Compile : 此事件產生的資料和Showplan All相同，不過此事件僅在查詢編譯時間觸發。
• Showplan Statistics Profile：此事件產生實際的執行計畫，方式和T-Sql命令Statistics Profile相同。它產生的資訊是Statistics Xml命令或Showplan Xml Statistics Profile事件的子集。
• Showplan Xml：此事件在每次查詢時均觸發，產生一個預估的執行計畫，同Showplan_Xml.
• Showplan Xml for Query Compile: 同上，僅在查詢編譯時間觸發。
• Performance Statistics：和Showplan Xml for Query Compile 類似，此事件不僅捕獲執行計畫也捕獲效能測量資料。此事件僅捕獲其中定義的子事件類別的Xml輸出。此事件在第一次進入Cache時、編譯時間、重編譯時間以及從Cache裡移除時觸發。
• Showplan Xml Statistics Profile：此事件在每個查詢運行時產生其實際的執行計畫。

使用Showplan xml或者Showplan Xml Statistics Profile捕獲所有的執行計畫毫無疑問會增加伺服器的負擔，這些都不是輕量級的事件。即使使用不常用的Showplan Xml for Query compile也會導致小的效能下降。謹慎使用。

捕獲Showplan Xml跟蹤

啟動Profiler -> 建立一個跟蹤並串連到伺服器 –> 切換至Events Selection標籤頁 –> 勾選Show all events複選框 –> 點擊+號展開Performance部 –> 勾選Showplan xml事件

現在就可以在Profiler裡捕獲Showplan xml事件了，通常同時捕獲一些其他的基本事件會有用處，如:RPC:Completed, SQL:BatchStarting,SQL:BatchCompleted等。

圖1-6

當勾選了Showplan Xml事件之後，第三個標籤頁就出現了，在那裡可以指定將輸出的Xml發送往獨立的檔案。

點擊Run啟動跟蹤，當有查詢執行時就可以獲得類似的結果

圖1-7

TextData列裡就是實際的Xml執行計畫。選中的時候下邊的視窗就出現了圖形執行計畫。你可以右鍵點擊然後選擇Extract Event data來將計劃存到另外的檔案裡。