SQL—效能分析之執行計畫)

　一直想找一些關於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 DROPCLEANBUFFERS; 

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

　　

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

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

--1
SELECT custid, orderid, orderdate, empid, filler
FROM dbo.Orders AS O1
WHERE orderid =
(SELECT TOP (1) O2.orderid
FROM dbo.Orders AS O2
WHERE O2.custid = O1.custid
ORDER BY O2.orderdate DESC, O2.orderid DESC);
--2
SELECT custid, orderid, orderdate, empid, filler
FROM dbo.Orders
WHERE orderid IN
(
SELECT
(SELECT TOP (1) O.orderid
FROM dbo.Orders AS O
WHERE O.custid = C.custid
ORDER BY O.orderdate DESC, O.orderid DESC) AS oid
FROM dbo.Customers AS C
);
--3
SELECT A.*
FROM dbo.Customers AS C
CROSS APPLY
(SELECT TOP (1)
O.custid, O.orderid, O.orderdate, O.empid, O.filler
FROM dbo.Orders AS O
WHERE O.custid = C.custid
ORDER BY O.orderdate DESC, O.orderid DESC) AS A;
--4
WITH C AS
(
SELECT custid, orderid, orderdate, empid, filler,
ROW_NUMBER() OVER(PARTITION BY custid
ORDER BY orderdate DESC, orderid DESC) AS n
FROM dbo.Orders
)
SELECT custid, orderid, orderdate, empid, filler
FROM C
WHERE n = 1;

複製代碼

 

他們的 查詢結果是一樣的，但是執行計畫是不同的。在每個執行計畫的開頭有一個百分比指示這個語句在所有的語句所佔的開銷的百分比。在這個例子中我們可以看到第一個語句的比例是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;
SELECT orderid, custid, empid, shipperid, orderdate, filler
FROM dbo.Orders WHERE orderid = 280885;

查看執行計畫（CTRL+L）得到下面的結果：
(1 row(s) affected)
StmtText
-----------------------------------------------------------------------------------------------------
SELECT orderid, custid, empid, shipperid, orderdate, filler
FROM dbo.Orders WHERE orderid = 280885;

(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)

(1 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;
運行下面的語句：
SELECT orderid, custid, empid, shipperid, orderdate, filler
FROM dbo.Orders WHERE orderid = 280885;
得到的結果如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;

 

運行語句

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

 

運行結果如6：

圖6　　

點擊這個xml檔案，圖形格式的執行計畫如7：

圖7　　

使用下面的語句關閉選項：
SET SHOWPLAN_XML OFF;

為了不影響其他語句的輸出效果建議使用類似下面的代碼來查看效果：
SET STATISTICS XML ON;
GO
SELECT orderid, custid, empid, shipperid, orderdate, filler FROM dbo.Orders WHERE orderid = 280885;
GO
SET STATISTICS XML OFF;

可以看出XML格式的執行計畫提供了最友好的查看形式。