執行計劃

　一直想找一些關于SQL語句性能調試的權威參考，但是有參考未必就能夠做好調試的工作。我深信實踐中得到的經驗是最珍貴的，書本知識隻是一個引導。本篇來源于《Inside Microsoft SQL Server 2008》，有經驗的高手盡管拍磚把。

這個部分将講解一些性能分析工具，這些性能分許主要關注在執行計劃。

緩存執行計劃 

SQL Server 2008提供了一些伺服器對象來分析執行計劃

Sys.dm_exec_cached_plans：    包含緩存的執行計劃，每個執行計劃對應一行。

Sys.dm_exec_plan_attributes： 這是一個系統函數，每一個執行計劃都對應着一些屬性，在這個系統函數中包含着這些屬性。

Sys.dm_exec_sql_text：             這是一個系統函數，傳回文字格式的執行計劃。

Sys.dm_exec_query_plan：        這是一個系統函數，傳回xml格式的執行計劃。

SQL Server 2008還提供了一個相容性的視圖sys.syscacheobject，這個視圖中儲存了所有的執行計劃的資訊。

　　

清除緩存 

在進行性能分析的時候有時候需要清除緩存以便進行下一次分析。SQL Server提供了一些工具來清除緩存的性能資料。使用下面的語句來完成這些任務。 

清除全局緩存使用下面的語句： 

DBCC DROPCLEANBUFFERS;

從全局緩存中清除執行計劃，使用下面的語句： 

DBCC FREEPROCCACHE;

清除某一個資料庫中的執行計劃，使用下面的語句： 

DBCC FLUSHPROCINDB(<db_id>);

清除一個特定的執行計劃使用下面的語句： 

DBCC FREESYSTEMCACHE(<cachestore>);

可以使用’ALL’，pool_name，’Object Plan’，’SQL Plans’，’Bound Trees’作為輸入參數。’ALL’參數标明要清除所有的緩存，pool_name的值表明要清除的一個緩存池的名字。’Object Plans’清除對象計劃（例如存儲過程，觸發器，使用者定義函數等等）。’SQL Plans’用來清除要立即執行的語句。’Bound Trees’定義清除視圖，限制等的緩存。

注意：在使用這些語句清除緩存之前要想清楚，特别是在生産環境。這些對性能有很大的影響。清除這些緩存之後SQL Server需要從資料頁中重新讀取資料。并且SQL Server需要重新生成新的執行計劃。是以在清除之前要想清楚這些對生産或者測試環境的影響。

 動态的管理對象

SQL Server 2005引入了動态管理對象，例如DMV，DMF。SQL Server 2008中添加了新的對象，新的屬性。這些飽含非常有用的資訊，利用這些資訊可以監視SQL Server，診斷問題，進行性能監視。要仔細研究這些對象會很耗時。這裡隻是列舉一些常用的。

統計IO

統計IO是是一個session選項。它傳回域目前執行的語句相關的I/O資訊。要使用這個選項首選清除資料緩存：

DBCC DROPCLEANBUFFERS; 

然後運作下面的代碼來打開這個選項：

SET STATISTICS IO ON;

SELECT orderid, custid, empid, shipperid, orderdate, filler

FROM dbo.Orders

WHERE orderdate >= '20060101'

AND orderdate < '20060201';

最後可以得到類似下面的資訊：

(21226 row(s) affected)

Table 'Orders'. Scan count 1, logical reads 537, physical reads 3, read-ahead reads 549, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. 

從輸出資訊中我們可以看到在執行計劃中有多少次擷取表（Scan count）；多少次讀取緩存（logical reads）；多少次讀取硬碟（physical reads 俺的read-ahead reads）；多少次讀取大的對象（lob physical reads ， log read-ahead reads）。

使用下面的語句來關閉這個選項：

SET STATISTICS IO OFF;  

統計運作時間 

STATISTICS TIME是一個用來傳回CPU時鐘時間的session選項。它傳回文法分析，編譯，執行的時間。要使用這個選項首選要清除執行計劃緩存。 

DBCC FREEPROCCACHE; 

運作下面的語句來打開相應的選項： 

SET STATISTICS TIME ON; 

運作下面的語句：

SELECT orderid, custid, empid, shipperid, orderdate, filler FROM dbo.Orders WHERE orderdate >= '20060101' AND orderdate < '20060201';  

得到下面的資訊： 

SQL Server parse and compile time: 

   CPU time = 0 ms, elapsed time = 4 ms. 

SQL Server Execution Times: 

   CPU time = 46 ms,  elapsed time = 544 ms. 

從這些資訊中可以獲得執行這個語句時候的CPU時鐘時間，編譯時間，運作時間。運作下面的語句可以關閉這個選項： 

SET STATISTICS TIME OFF;  

當需要分析一個單獨的語句的性能的時候這個選項非常有用。當需要使用批處理的模式來運作語句的時候需要度量會有所不同。在查詢之前儲存SYSDATETIME函數的值，并寫入到一個表中。注意這個函數傳回的時間格式是DATETIME2，可以精确到100納秒。這個函數的準确性取決于計算機硬體和作業系統版本。因為這個函數會調用GetSystemTimeAsFileTime()這個WindowsAPI。需要統計時間的時候可以重複地運作請求語句，然後記錄下需要的時間。  　

　

分析執行計劃 

執行計劃是SQL優化器生成的如何處理給定的請求的一個工作計劃。它包含這個請求中藥用到的操作符。有一些操作可能會執行多次。一些計劃分支可能會并行執行。在這個工作計劃中，優化器決定擷取語句中涉及到的表的順序，要使用到那些索引，要使用那些查詢方法，要使用那些算法等等。事實上，優化器會在多個執行計劃中選擇出一個最優的，資源耗費最少的。頻繁地生成執行計劃也會耗費時間，是以SQL Server也會根據資料量的大小估算生成執行計劃所需要的閥值時間。生成執行計劃的時間不會超過這個估算的閥值時間。還有一個閥值是根據耗費的資源計算得到的。如果一個工作計劃的資源耗費低于這個閥值，就認為它是足夠好的，優化器就會停止優化使用這個計劃。

圖形執行計劃

SSMS允許我們檢視一個圖形化的執行計劃（快捷鍵Ctrl+L）。注意當檢視一個執行計劃的時候，查詢并沒有運作。一些路徑成本隻能在運作完之後才能得到（實際查詢得到的行的數目）。

使用下面的語句來檢視執行計劃：

SELECT custid, empid, shipperid, COUNT(*) AS numorders FROM dbo.Orders WHERE orderdate >= '20080201' AND orderdate < '20080301'

GROUP BY CUBE(custid, empid, shipperid);

這個語句查詢得到所有可能的聚合值，聚合屬性是custid，empid，shipperid。如圖1

圖1

注意當這個執行計劃占用很大的螢幕空間的時候可以點選右下方的按鈕“+”不放，然後拖動滑鼠可以檢視想要檢視的區域。

執行計劃是由一些操作組成的樹狀結構圖。資料從子運算流向父運算。這個結構的順序是從右到左，從上到下。在這個例子中，運算首選從聚集索引開始，然後是後面的操作纏繞運算-Table Spool

注意每個運算符旁邊有一個百分比，這個值表值這個運算在整個執行過程中所占的資源百分比，這個值隻是優化器估計的值。SQL語句的優化工作應該放在那些所占的百分比比較大的操作上面。當把滑鼠放上去的時候，會有一個換色的提示框。有一個值是Estimated Subtree Cost。最上方，最作坊的運算時整個運算的資源開銷。如圖2

圖2

注意這些值隻是優化器估計出的值，優化器會使用這個值來和其他的估計值作比較進而選擇出一個最優的執行計劃。

另外一個比較好的地方時你可以同時生成多個語句的執行計劃，進而對他們進行比較。例如下面的語句：

他們的 查詢結果是一樣的，但是執行計劃是不同的。在每個執行計劃的開頭有一個百分比訓示這個語句在所有的語句所占的開銷的百分比。在這個例子中我們可以看到第一個語句的比例是37%，第二個語句的比例是19%，第三個是30%，第四個是14%。從這個結果我們可以粗略的認定第四個語句的效率要高一些。

當把滑鼠放在運算符上面的時候會有一個×××的提示框如圖4

圖4

在這個提示框中有下面的一些度量資訊：

操作符的名字和簡單的介紹

實體運算：計算機内部的實體運算

邏輯運算：與實體運算符比對的邏輯運算符，如 Inner Join 運算符。邏輯運算符列在實體運算符之後，兩者均位于工具提示的頂部。

傳回的行數： 運算傳回的資料行數

估計I/O開銷，估計CPU開銷： 這個資料可以用來估算這個操作是不是造成很大的CPU或者I/O開銷，一般Sort操作都會造成很大的I/O開銷

估計執行行數和執行行數：估計該操作執行的次數和實際執行的次數。這個資料可以幫助你找到更好的執行語句

估計執行開銷：用于執行此操作的查詢優化器的開銷

估計子樹開銷：查詢優化器執行此操作及同一子樹内位于此操作之前的所有操作的總開銷

運算生成的行數：估計運算符生成的行數。有些情況下可以通過實際行數和估計行數之間的差異來判斷一個SQL語句的優劣

估計資料大小：操作符生成的行的估計大小（位元組）。可能你會疑惑為什麼這個實際行數沒有顯示在執行計劃裡面，那是因為資料行裡面有可變長度的資料類型

實際的重綁和重繞： 這個資料之和一些特定的操作有關（非聚集的纏繞，遠端請求，行數纏繞，排序，表纏繞，表值函數，斷言，過濾等）。隻有在内層嵌套查詢的時候這才會統計個度量資訊，否則Rebinds是1，Rewinds是0。這些資料表示内層的Init方法被調用。重綁和重繞的綜合應該是外連接配接得到的行數之和。重綁意味着一個或者多個相關的連接配接參數改變了，需要重新估算。重繞意思是相關的參數沒有改變，可以重用先前得到的内部結果集

底部的資訊：顯示相關的對象名，輸出，參數等等

選中一個操作符，按下F4鍵，可以檢視更加詳細的資訊。

文本格式的執行計劃 

可以通過設定以文本格式檢視執行計劃。設定SHOWPLAN_TEXT選項可以達到這個目的，如下：

SET SHOWPLAN_TEXT ON;

FROM dbo.Orders WHERE orderid = 280885;

檢視執行計劃（CTRL+L）得到下面的結果：

(1 row(s) affected)

StmtText

-----------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

  |--Nested Loops(Inner Join, OUTER REFERENCES:([Uniq1002], [Performance].[dbo].[Orders].[orderdate]))

       |--Index Seek(OBJECT:([Performance].[dbo].[Orders].[PK_Orders]), SEEK:([Performance].[dbo].[Orders].[orderid]=[@1]) ORDERED FORWARD)

       |--Clustered Index Seek(OBJECT:([Performance].[dbo].[Orders].[idx_cl_od]), SEEK:([Performance].[dbo].[Orders].[orderdate]=[Performance].[dbo].[Orders].[orderdate] AND [Uniq1002]=[Uniq1002]) LOOKUP ORDERED FORWARD)

(3 row(s) affected)

分析這個執行計劃，從内層的分支到外層分支，從上到下。但是在這裡我們隻能看到運算符的名字和參數。運作下面的語句關閉這個選項：

SET SHOWPLAN_TEXT OFF;

如果想得到更加詳細的執行計劃資訊，使用SHOWPLAN_ALL選項檢視執行計劃，STATISTICS PROFILE選項檢視具體的某一個執行計劃。SHOWPLAN_ALL将執行計劃的資訊寫入到一個表中，其中包含的一些估計的值有：StmtText, StmtId, NodeId, Parent, PhysicalOp, LogicalOp, Argument, Defi nedValues,EstimateRows, EstimateIO, EstimateCPU, AvgRowSize, TotalSubtreeCost, OutputList,Warnings, Type, Parallel, and EstimateExecutions。

通過下面的語句打開這個選項：

SET SHOWPLAN_ALL ON;

得到的結果如下圖5：

圖5

運作下面的語句關閉選項：

SET SHOWPLAN_ALL OFF;

STATISTICS PROFILE選項會産生一個實際的計劃。設定這個選項為ON的時候顯示的結果和設定SHOWPLAN_ALL為ON差不多，不過多了兩個屬性Rosw和Executes，表示實際的行數和運作行數。

語句如下：

SET STATISTICS PROFILE ON;

SELECT orderid, custid, empid, shipperid, orderdate, filler FROM dbo.Orders WHERE orderid = 280885;

取消設定：

SET STATISTICS PROFILE OFF;

XML格式的執行計劃

如果想用自己的代碼來描述執行計劃或者把執行計劃發送給客戶或者同僚，你會發現使用文本格式的資訊很不友善。SQL Server 2008允許允許傳回XML格式的執行計劃内容，這非常利于使用應用程式代碼處理。打開使用SQL Server 2008産生的xml格式的執行計劃會顯示成圖形結果，字尾是.sqlplan。

打開這個選項的代碼如下：

SET SHOWPLAN_XML ON;

運作語句

運作結果如下圖6：

圖6　　

點選這個xml檔案，圖形格式的執行計劃如下圖7：

圖7　　

使用下面的語句關閉選項：

SET SHOWPLAN_XML OFF;

為了不影響其他語句的輸出效果建議使用類似下面的代碼來檢視效果：

SET STATISTICS XML ON;

GO

SET STATISTICS XML OFF;

可以看出XML格式的執行計劃提供了最友好的檢視形式。