SQL點(diǎn)滴27—性能分析之執(zhí)行計(jì)劃
一直想找一些關(guān)于SQL語(yǔ)句性能調(diào)試的權(quán)威參考，但是有參考未必就能夠做好調(diào)試的工作。我深信實(shí)踐中得到的經(jīng)驗(yàn)是最珍貴的，書本知識(shí)只是一個(gè)引導(dǎo)。本篇來(lái)源于《Inside Microsoft SQL Server 2008》，有經(jīng)驗(yàn)的高手盡管拍磚把。
這個(gè)部分將講解一些性能分析工具，這些性能分許主要關(guān)注在執(zhí)行計(jì)劃。
緩存執(zhí)行計(jì)劃
SQL Server 2008提供了一些服務(wù)器對(duì)象來(lái)分析執(zhí)行計(jì)劃
Sys.dm_exec_cached_plans： 包含緩存的執(zhí)行計(jì)劃，每個(gè)執(zhí)行計(jì)劃對(duì)應(yīng)一行。
Sys.dm_exec_plan_attributes： 這是一個(gè)系統(tǒng)函數(shù)，每一個(gè)執(zhí)行計(jì)劃都對(duì)應(yīng)著一些屬性，在這個(gè)系統(tǒng)函數(shù)中包含著這些屬性。
Sys.dm_exec_sql_text： 這是一個(gè)系統(tǒng)函數(shù)，返回文字格式的執(zhí)行計(jì)劃。
Sys.dm_exec_query_plan： 這是一個(gè)系統(tǒng)函數(shù)，返回xml格式的執(zhí)行計(jì)劃。
SQL Server 2008還提供了一個(gè)兼容性的視圖sys.syscacheobject，這個(gè)視圖中保存了所有的執(zhí)行計(jì)劃的信息。
清除緩存
在進(jìn)行性能分析的時(shí)候有時(shí)候需要清除緩存以便進(jìn)行下一次分析。SQL Server提供了一些工具來(lái)清除緩存的性能數(shù)據(jù)。使用下面的語(yǔ)句來(lái)完成這些任務(wù)。
清除全局緩存使用下面的語(yǔ)句：
DBCC DROPCLEANBUFFERS;
從全局緩存中清除執(zhí)行計(jì)劃，使用下面的語(yǔ)句：
DBCC FREEPROCCACHE;
清除某一個(gè)數(shù)據(jù)庫(kù)中的執(zhí)行計(jì)劃，使用下面的語(yǔ)句：
DBCC FLUSHPROCINDB(<db_id>);
清除一個(gè)特定的執(zhí)行計(jì)劃使用下面的語(yǔ)句：
DBCC FREESYSTEMCACHE(<cachestore>);
可以使用’ALL’，pool_name，’Object Plan’，’SQL Plans’，’Bound Trees’作為輸入?yún)?shù)。’ALL’參數(shù)標(biāo)明要清除所有的緩存，pool_name的值表明要清除的一個(gè)緩存池的名字。’Object Plans’清除對(duì)象計(jì)劃（例如存儲(chǔ)過(guò)程，觸發(fā)器，用戶定義函數(shù)等等）。’SQL Plans’用來(lái)清除要立即執(zhí)行的語(yǔ)句。’Bound Trees’定義清除視圖，約束等的緩存。
注意：在使用這些語(yǔ)句清除緩存之前要想清楚，特別是在生產(chǎn)環(huán)境。這些對(duì)性能有很大的影響。清除這些緩存之后SQL Server需要從數(shù)據(jù)頁(yè)中重新讀取數(shù)據(jù)。并且SQL Server需要重新生成新的執(zhí)行計(jì)劃。因此在清除之前要想清楚這些對(duì)生產(chǎn)或者測(cè)試環(huán)境的影響。
動(dòng)態(tài)的管理對(duì)象
SQL Server 2005引入了動(dòng)態(tài)管理對(duì)象，例如DMV，DMF。SQL Server 2008中添加了新的對(duì)象，新的屬性。這些飽含非常有用的信息，利用這些信息可以監(jiān)視SQL Server，診斷問(wèn)題，進(jìn)行性能監(jiān)視。要仔細(xì)研究這些對(duì)象會(huì)很耗時(shí)。這里只是列舉一些常用的。
統(tǒng)計(jì)IO
統(tǒng)計(jì)IO是是一個(gè)session選項(xiàng)。它返回域當(dāng)前執(zhí)行的語(yǔ)句相關(guān)的I/O信息。要使用這個(gè)選項(xiàng)首選清除數(shù)據(jù)緩存：
DBCC DROPCLEANBUFFERS;
然后運(yùn)行下面的代碼來(lái)打開(kāi)這個(gè)選項(xiàng)：
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.
從輸出信息中我們可以看到在執(zhí)行計(jì)劃中有多少次獲取表（Scan count）；多少次讀取緩存（logical reads）；多少次讀取硬盤（physical reads 俺的read-ahead reads）；多少次讀取大的對(duì)象（lob physical reads ， log read-ahead reads）。
使用下面的語(yǔ)句來(lái)關(guān)閉這個(gè)選項(xiàng)：
SET STATISTICS IO OFF;
統(tǒng)計(jì)運(yùn)行時(shí)間
STATISTICS TIME是一個(gè)用來(lái)返回CPU時(shí)鐘時(shí)間的session選項(xiàng)。它返回語(yǔ)法分析，編譯，執(zhí)行的時(shí)間。要使用這個(gè)選項(xiàng)首選要清除執(zhí)行計(jì)劃緩存。
DBCC DROPCLEANBUFFERS;
DBCC FREEPROCCACHE;
運(yùn)行下面的語(yǔ)句來(lái)打開(kāi)相應(yīng)的選項(xiàng)：
SET STATISTICS TIME ON;
運(yùn)行下面的語(yǔ)句：
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.
從這些信息中可以獲得執(zhí)行這個(gè)語(yǔ)句時(shí)候的CPU時(shí)鐘時(shí)間，編譯時(shí)間，運(yùn)行時(shí)間。運(yùn)行下面的語(yǔ)句可以關(guān)閉這個(gè)選項(xiàng)：
SET STATISTICS TIME OFF;
當(dāng)需要分析一個(gè)單獨(dú)的語(yǔ)句的性能的時(shí)候這個(gè)選項(xiàng)非常有用。當(dāng)需要使用批處理的模式來(lái)運(yùn)行語(yǔ)句的時(shí)候需要度量會(huì)有所不同。在查詢之前保存SYSDATETIME函數(shù)的值，并寫入到一個(gè)表中。注意這個(gè)函數(shù)返回的時(shí)間格式是DATETIME2，可以精確到100納秒。這個(gè)函數(shù)的準(zhǔn)確性取決于計(jì)算機(jī)硬件和操作系統(tǒng)版本。因?yàn)檫@個(gè)函數(shù)會(huì)調(diào)用GetSystemTimeAsFileTime()這個(gè)WindowsAPI。需要統(tǒng)計(jì)時(shí)間的時(shí)候可以重復(fù)地運(yùn)行請(qǐng)求語(yǔ)句，然后記錄下需要的時(shí)間。
分析執(zhí)行計(jì)劃
執(zhí)行計(jì)劃是SQL優(yōu)化器生成的如何處理給定的請(qǐng)求的一個(gè)工作計(jì)劃。它包含這個(gè)請(qǐng)求中藥用到的操作符。有一些操作可能會(huì)執(zhí)行多次。一些計(jì)劃分支可能會(huì)并行執(zhí)行。在這個(gè)工作計(jì)劃中，優(yōu)化器決定獲取語(yǔ)句中涉及到的表的順序，要使用到那些索引，要使用那些查詢方法，要使用那些算法等等。事實(shí)上，優(yōu)化器會(huì)在多個(gè)執(zhí)行計(jì)劃中選擇出一個(gè)最優(yōu)的，資源耗費(fèi)最少的。頻繁地生成執(zhí)行計(jì)劃也會(huì)耗費(fèi)時(shí)間，所以SQL Server也會(huì)根據(jù)數(shù)據(jù)量的大小估算生成執(zhí)行計(jì)劃所需要的閥值時(shí)間。生成執(zhí)行計(jì)劃的時(shí)間不會(huì)超過(guò)這個(gè)估算的閥值時(shí)間。還有一個(gè)閥值是根據(jù)耗費(fèi)的資源計(jì)算得到的。如果一個(gè)工作計(jì)劃的資源耗費(fèi)低于這個(gè)閥值，就認(rèn)為它是足夠好的，優(yōu)化器就會(huì)停止優(yōu)化使用這個(gè)計(jì)劃。
圖形執(zhí)行計(jì)劃
SSMS允許我們查看一個(gè)圖形化的執(zhí)行計(jì)劃（快捷鍵Ctrl+L）。注意當(dāng)查看一個(gè)執(zhí)行計(jì)劃的時(shí)候，查詢并沒(méi)有運(yùn)行。一些度量值只能在運(yùn)行完之后才能得到（實(shí)際查詢得到的行的數(shù)目）。
使用下面的語(yǔ)句來(lái)查看執(zhí)行計(jì)劃：
SELECT custid, empid, shipperid, COUNT(*) AS numorders FROM dbo.Orders WHERE orderdate >= '20080201' AND orderdate < '20080301'
GROUP BY CUBE(custid, empid, shipperid);
這個(gè)語(yǔ)句查詢得到所有可能的聚合值，聚合屬性是custid，empid，shipperid。如圖1
圖1
注意當(dāng)這個(gè)執(zhí)行計(jì)劃占用很大的屏幕空間的時(shí)候可以點(diǎn)擊右下方的按鈕“+”不放，然后拖動(dòng)鼠標(biāo)可以查看想要查看的區(qū)域。
執(zhí)行計(jì)劃是由一些操作組成的樹狀結(jié)構(gòu)圖。數(shù)據(jù)從子運(yùn)算流向父運(yùn)算。這個(gè)結(jié)構(gòu)的順序是從右到左，從上到下。在這個(gè)例子中，運(yùn)算首選從聚集索引開(kāi)始，然后是后面的操作纏繞運(yùn)算-Table Spool
注意每個(gè)運(yùn)算符旁邊有一個(gè)百分比，這個(gè)值表值這個(gè)運(yùn)算在整個(gè)執(zhí)行過(guò)程中所占的資源百分比，這個(gè)值只是優(yōu)化器估計(jì)的值。SQL語(yǔ)句的優(yōu)化工作應(yīng)該放在那些所占的百分比比較大的操作上面。當(dāng)把鼠標(biāo)放上去的時(shí)候，會(huì)有一個(gè)換色的提示框。有一個(gè)值是Estimated Subtree Cost。最上方，最作坊的運(yùn)算時(shí)整個(gè)運(yùn)算的資源開(kāi)銷。如圖2
圖2
注意這些值只是優(yōu)化器估計(jì)出的值，優(yōu)化器會(huì)使用這個(gè)值來(lái)和其他的估計(jì)值作比較進(jìn)而選擇出一個(gè)最優(yōu)的執(zhí)行計(jì)劃。
另外一個(gè)比較好的地方時(shí)你可以同時(shí)生成多個(gè)語(yǔ)句的執(zhí)行計(jì)劃，進(jìn)而對(duì)他們進(jìn)行比較。例如下面的語(yǔ)句：
--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;
他們的 查詢結(jié)果是一樣的，但是執(zhí)行計(jì)劃是不同的。在每個(gè)執(zhí)行計(jì)劃的開(kāi)頭有一個(gè)百分比指示這個(gè)語(yǔ)句在所有的語(yǔ)句所占的開(kāi)銷的百分比。在這個(gè)例子中我們可以看到第一個(gè)語(yǔ)句的比例是37%，第二個(gè)語(yǔ)句的比例是19%，第三個(gè)是30%，第四個(gè)是14%。從這個(gè)結(jié)果我們可以粗略的認(rèn)定第四個(gè)語(yǔ)句的效率要高一些。
當(dāng)把鼠標(biāo)放在運(yùn)算符上面的時(shí)候會(huì)有一個(gè)黃色的提示框如圖4
圖4
在這個(gè)提示框中有下面的一些度量信息：
- 操作符的名字和簡(jiǎn)單的介紹
- 物理運(yùn)算：計(jì)算機(jī)內(nèi)部的物理運(yùn)算
- 邏輯運(yùn)算：與物理運(yùn)算符匹配的邏輯運(yùn)算符，如 Inner Join 運(yùn)算符。邏輯運(yùn)算符列在物理運(yùn)算符之后，兩者均位于工具提示的頂部。
- 返回的行數(shù)： 運(yùn)算返回的數(shù)據(jù)行數(shù)
- 估計(jì)I/O開(kāi)銷，估計(jì)CPU開(kāi)銷： 這個(gè)數(shù)據(jù)可以用來(lái)估算這個(gè)操作是不是造成很大的CPU或者I/O開(kāi)銷，一般Sort操作都會(huì)造成很大的I/O開(kāi)銷
- 估計(jì)執(zhí)行行數(shù)和執(zhí)行行數(shù)：估計(jì)該操作執(zhí)行的次數(shù)和實(shí)際執(zhí)行的次數(shù)。這個(gè)數(shù)據(jù)可以幫助你找到更好的執(zhí)行語(yǔ)句
- 估計(jì)執(zhí)行開(kāi)銷：用于執(zhí)行此操作的查詢優(yōu)化器的開(kāi)銷
- 估計(jì)子樹開(kāi)銷：查詢優(yōu)化器執(zhí)行此操作及同一子樹內(nèi)位于此操作之前的所有操作的總開(kāi)銷
- 運(yùn)算生成的行數(shù)：估計(jì)運(yùn)算符生成的行數(shù)。有些情況下可以通過(guò)實(shí)際行數(shù)和估計(jì)行數(shù)之間的差異來(lái)判斷一個(gè)SQL語(yǔ)句的優(yōu)劣
- 估計(jì)數(shù)據(jù)大小：操作符生成的行的估計(jì)大小（字節(jié)）。可能你會(huì)疑惑為什么這個(gè)實(shí)際行數(shù)沒(méi)有顯示在執(zhí)行計(jì)劃里面，那是因?yàn)閿?shù)據(jù)行里面有可變長(zhǎng)度的數(shù)據(jù)類型
- 實(shí)際的重綁和重繞： 這個(gè)數(shù)據(jù)之和一些特定的操作有關(guān)（非聚集的纏繞，遠(yuǎn)程請(qǐng)求，行數(shù)纏繞，排序，表纏繞，表值函數(shù)，斷言，過(guò)濾等）。只有在內(nèi)層嵌套查詢的時(shí)候這才會(huì)統(tǒng)計(jì)個(gè)度量信息，否則Rebinds是1，Rewinds是0。這些數(shù)據(jù)表示內(nèi)層的Init方法被調(diào)用。重綁和重繞的綜合應(yīng)該是外連接得到的行數(shù)之和。重綁意味著一個(gè)或者多個(gè)相關(guān)的連接參數(shù)改變了，需要重新估算。重繞意思是相關(guān)的參數(shù)沒(méi)有改變，可以重用先前得到的內(nèi)部結(jié)果集
- 底部的信息：顯示相關(guān)的對(duì)象名，輸出，參數(shù)等等
選中一個(gè)操作符，按下F4鍵，可以查看更加詳細(xì)的信息。
文本格式的執(zhí)行計(jì)劃
可以通過(guò)設(shè)置以文本格式查看執(zhí)行計(jì)劃。設(shè)置SHOWPLAN_TEXT選項(xiàng)可以達(dá)到這個(gè)目的，如下：
SET SHOWPLAN_TEXT ON;
SELECT orderid, custid, empid, shipperid, orderdate, filler
FROM dbo.Orders WHERE orderid = 280885;
查看執(zhí)行計(jì)劃（CTRL+L）得到下面的結(jié)果：
(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)
分析這個(gè)執(zhí)行計(jì)劃，從內(nèi)層的分支到外層分支，從上到下。但是在這里我們只能看到運(yùn)算符的名字和參數(shù)。運(yùn)行下面的語(yǔ)句關(guān)閉這個(gè)選項(xiàng)：
SET SHOWPLAN_TEXT OFF;
如果想得到更加詳細(xì)的執(zhí)行計(jì)劃信息，使用SHOWPLAN_ALL選項(xiàng)查看執(zhí)行計(jì)劃，STATISTICS PROFILE選項(xiàng)查看具體的某一個(gè)執(zhí)行計(jì)劃。SHOWPLAN_ALL將執(zhí)行計(jì)劃的信息寫入到一個(gè)表中，其中包含的一些估計(jì)的值有：StmtText, StmtId, NodeId, Parent, PhysicalOp, LogicalOp, Argument, Defi nedValues,EstimateRows, EstimateIO, EstimateCPU, AvgRowSize, TotalSubtreeCost, OutputList,Warnings, Type, Parallel, and EstimateExecutions。
通過(guò)下面的語(yǔ)句打開(kāi)這個(gè)選項(xiàng)：
SET SHOWPLAN_ALL ON;
運(yùn)行下面的語(yǔ)句：
SELECT orderid, custid, empid, shipperid, orderdate, filler
FROM dbo.Orders WHERE orderid = 280885;
得到的結(jié)果如下圖5：
圖5
運(yùn)行下面的語(yǔ)句關(guān)閉選項(xiàng)：
SET SHOWPLAN_ALL OFF;
STATISTICS PROFILE選項(xiàng)會(huì)產(chǎn)生一個(gè)實(shí)際的計(jì)劃。設(shè)置這個(gè)選項(xiàng)為ON的時(shí)候顯示的結(jié)果和設(shè)置SHOWPLAN_ALL為ON差不多，不過(guò)多了兩個(gè)屬性Rosw和Executes，表示實(shí)際的行數(shù)和運(yùn)行行數(shù)。
語(yǔ)句如下：
SET STATISTICS PROFILE ON;
SELECT orderid, custid, empid, shipperid, orderdate, filler FROM dbo.Orders WHERE orderid = 280885;
取消設(shè)置：
SET STATISTICS PROFILE OFF;
XML格式的執(zhí)行計(jì)劃
如果想用自己的代碼來(lái)描述執(zhí)行計(jì)劃或者把執(zhí)行計(jì)劃發(fā)送給客戶或者同事，你會(huì)發(fā)現(xiàn)使用文本格式的信息很不方便。SQL Server 2008允許允許返回XML格式的執(zhí)行計(jì)劃內(nèi)容，這非常利于使用應(yīng)用程序代碼處理。打開(kāi)使用SQL Server 2008產(chǎn)生的xml格式的執(zhí)行計(jì)劃會(huì)顯示成圖形結(jié)果，后綴是.sqlplan。
打開(kāi)這個(gè)選項(xiàng)的代碼如下：
SET SHOWPLAN_XML ON;
運(yùn)行語(yǔ)句
SELECT orderid, custid, empid, shipperid, orderdate, filler FROM dbo.Orders WHERE orderid = 280885;
運(yùn)行結(jié)果如下圖6：
圖6
點(diǎn)擊這個(gè)xml文件，圖形格式的執(zhí)行計(jì)劃如下圖7：
圖7
使用下面的語(yǔ)句關(guān)閉選項(xiàng)：
SET SHOWPLAN_XML OFF;
為了不影響其他語(yǔ)句的輸出效果建議使用類似下面的代碼來(lái)查看效果：
SET STATISTICS XML ON;
GO
SELECT orderid, custid, empid, shipperid, orderdate, filler FROM dbo.Orders WHERE orderid = 280885;
GO
SET STATISTICS XML OFF;
可以看出XML格式的執(zhí)行計(jì)劃提供了最友好的查看形式。
作者：Tyler Ning
出處：http://www.rzrgm.cn/tylerdonet/
本文版權(quán)歸作者和博客園共有，歡迎轉(zhuǎn)載，但未經(jīng)作者同意必須保留此段聲明，且在文章頁(yè)面明顯位置給出原文連接，如有問(wèn)題，請(qǐng)微信聯(lián)系冬天里的一把火
浙公網(wǎng)安備 33010602011771號(hào)