SQL Server 查詢最佳化（測試02）參數嗅探-執行計畫選擇

標籤：

最近常看到"參數嗅探"這個詞,看了幾篇文章，於是就自己摸索做個測試來加深印象!去官網下載了資料庫:AdventureWorks2012直接測試吧!找幾個熟悉的表關聯起來,用ProductID作為條件找到兩個ID返回行數相差較大的值.ProductID=870(4688行)ProductID=897(2行)

【測試一】

--先清空計畫快取DBCC FREEPROCCACHE--執行前先開啟計數器監控查看(分開執行以下查詢)select sdh.SalesOrderID,sdh.SalesOrderNumber,P.ProductID,p.Name,sod.LineTotalfrom [Sales].[SalesOrderHeader] sdhinner join [Sales].[SalesOrderDetail] sod on sdh.SalesOrderID = sod.SalesOrderIDinner join [Production].[Product] p on sod.ProductID = p.ProductIDwhere P.ProductID =870select sdh.SalesOrderID,sdh.SalesOrderNumber,P.ProductID,p.Name,sod.LineTotalfrom [Sales].[SalesOrderHeader] sdhinner join [Sales].[SalesOrderDetail] sod on sdh.SalesOrderID = sod.SalesOrderIDinner join [Production].[Product] p on sod.ProductID = p.ProductIDwhere P.ProductID =897

先看計數器,有兩個綠色的峰值為1.就是上面分別執行時發生的編譯次數.

--查看緩衝對象執行類型:Adhoc(即時查詢)SELECT cacheobjtype,objtype,refcounts,usecounts,[sql]FROM sys.syscacheobjectsWHERE [sql] LIKE '%SalesOrderID%' AND [sql] NOT LIKE '%sys%'--再用視圖查看緩衝查詢計劃和計劃大小SELECT refcounts,usecounts,cacheobjtype,size_in_bytes,[text],query_planFROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_sql_text(plan_handle)CROSS APPLY sys.dm_exec_query_plan(plan_handle)WHERE [text] LIKE '%SalesOrderID%' AND [text] NOT LIKE '%sys%'

可以看到產生了兩個不同的查詢計劃(query_plan),並且佔用了緩衝(size_in_bytes).


以上這種寫法的優缺點是:
缺點: 如果查詢條件值發生變化,每次都會作為新的查詢語句編譯第一次,不僅消耗CPU,而且產生新的查詢計劃也會佔用緩衝.
優點:每次執行計畫都是最優的

【測試二】
現在換成帶參數的形式.

--先清空計畫快取DBCC FREEPROCCACHE--ProductID=870(4688行)ProductID=897(2行)DECLARE @ProductID INTSET @ProductID = 870select sdh.SalesOrderID,sdh.SalesOrderNumber,P.ProductID,p.Name,sod.LineTotalfrom [Sales].[SalesOrderHeader] sdhinner join [Sales].[SalesOrderDetail] sod on sdh.SalesOrderID = sod.SalesOrderIDinner join [Production].[Product] p on sod.ProductID = p.ProductIDwhere P.ProductID [email protected]DECLARE @ProductID INTSET @ProductID = 897select sdh.SalesOrderID,sdh.SalesOrderNumber,P.ProductID,p.Name,sod.LineTotalfrom [Sales].[SalesOrderHeader] sdhinner join [Sales].[SalesOrderDetail] sod on sdh.SalesOrderID = sod.SalesOrderIDinner join [Production].[Product] p on sod.ProductID = p.ProductIDwhere P.ProductID [email protected]

看計數器,同樣有兩個綠色的峰值為1.發生了2次編譯

--再用視圖查看緩衝查詢計劃和計劃大小SELECT refcounts,usecounts,cacheobjtype,size_in_bytes,[text],query_planFROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_sql_text(plan_handle)CROSS APPLY sys.dm_exec_query_plan(plan_handle)WHERE [text] LIKE '%SalesOrderID%' AND [text] NOT LIKE '%sys%'

這會可以看到產生了兩個相同的查詢計劃(query_plan),緩衝大小(size_in_bytes)也就相同了.
還有另一點不同之處就是,執行計畫分兩部分執行,第一部分參數賦值,第二部分查詢語句.
因此第二部分才用了相同的查詢計劃.

以上這種寫法的優缺點是:
缺點: 如果查詢條件值發生變化,每次都會作為新的查詢語句編譯第一次,不僅消耗CPU,而且產生新的查詢計劃也會佔用緩衝.
還有就是,由於查詢計劃相同.當返回行數相差較大.有的查詢效能並不是較好的.
優點: 當返回資料量都差不多的時候是較好的,查詢最佳化工具根據參數估計一個較好的查詢計劃,有利於對查詢計劃進行控制.
(但是比較發現,這種寫法比上一種還差!最後再測試)

【測試三】

--這時把執行語句放到預存程序CREATE PROCEDURE P_Test(@ProductID INT)ASBEGINselect sdh.SalesOrderID,sdh.SalesOrderNumber,P.ProductID,p.Name,sod.LineTotalfrom [Sales].[SalesOrderHeader] sdhinner join [Sales].[SalesOrderDetail] sod on sdh.SalesOrderID = sod.SalesOrderIDinner join [Production].[Product] p on sod.ProductID = p.ProductIDwhere P.ProductID [email protected]END--ProductID=870(4688行)ProductID=897(2行)--執行預存程序DBCC FREEPROCCACHEEXEC P_Test @ProductID = 870EXEC P_Test @ProductID = 897--查看緩衝對象執行類型:Proc(預存程序)SELECT cacheobjtype,objtype,refcounts,usecounts,[sql]FROM sys.syscacheobjectsWHERE [sql] LIKE '%SalesOrderID%' AND [sql] NOT LIKE '%sys%'SELECT refcounts,usecounts,cacheobjtype,size_in_bytes,[text],query_planFROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_sql_text(plan_handle)CROSS APPLY sys.dm_exec_query_plan(plan_handle)WHERE [text] LIKE '%SalesOrderID%' AND [text] NOT LIKE '%sys%'

這時發現,只有1個緩衝計劃!無論參數怎麼改變都是只緩衝一個查詢計劃,這樣就省去了記憶體的佔用.
但是這個方法的優缺點就更明顯了.


這種寫法的優缺點是:
缺點: 如果查詢條件值發生變化,每次都會編譯1次,消耗CPU.
最重要的缺點是,查詢計劃的產生,是以第一次執行預存程序所傳遞的參數值來確定的!
也就是說,在預存程序建立後,傳遞參數首次執行預存程序,該參數返回的行數或多或少都會影響到執行計畫的永久確定.

DBCC FREEPROCCACHE--情況計畫快取
EXEC P_Test @ProductID = 870--現在換870先執行
EXEC P_Test @ProductID = 897--剛才為897首次執行預存程序

執行後再看查詢計劃,又是不一樣了!
所以這點要注意,為什麼同樣的預存程序,表統計資訊沒問題,但是有的查詢快,有的慢.
跟蹤把具體語句查出來運行又正常,就如同上面【測試一】一樣。



優點: 省下了記憶體!(不過記憶體一般用不了多少)

【測試四】

--這時把執行語句放到預存程序CREATE PROCEDURE P_Test2(@ProductID INT)ASBEGINDECLARE @ID INTSET @ID = @ProductID --區別在這裡select sdh.SalesOrderID,sdh.SalesOrderNumber,P.ProductID,p.Name,sod.LineTotalfrom [Sales].[SalesOrderHeader] sdhinner join [Sales].[SalesOrderDetail] sod on sdh.SalesOrderID = sod.SalesOrderIDinner join [Production].[Product] p on sod.ProductID = p.ProductIDwhere P.ProductID [email protected] END--ProductID=870(4688行)ProductID=897(2行)DBCC FREEPROCCACHEEXEC P_Test2 @ProductID = 870EXEC P_Test2 @ProductID = 897SELECT refcounts,usecounts,cacheobjtype,size_in_bytes,[text],query_planFROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_sql_text(plan_handle)CROSS APPLY sys.dm_exec_query_plan(plan_handle)WHERE [text] LIKE '%SalesOrderID%' AND [text] NOT LIKE '%sys%'

這種方法優缺點與【測試三】幾乎一樣，唯一不同的是，首次產生的執行計畫不受參數影響。
如下兩個預存程序，剛建立完預存程序後，不管誰先執行，查詢計劃都是一樣的！
EXEC P_Test2 @ProductID = 870
EXEC P_Test2 @ProductID = 897


這裡就真正用到了所謂的“參數嗅探”！以為最佳化引擎首次確定查詢計劃時，並不知道執行的參數值是什麼。
因此只嗅探到傳遞的參數，系統就是根據參數確定了預存程序的查詢計劃。
這裡也有不好的一點，就是參數返回多少也可能影響到效能。

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

總結: 

以上幾種都有優缺點，最不好的就是【測試二】那種。
還有一個現象，就是上面的所有測試，個人在效能監控器中都沒有發現“重編譯”的情況，每次都只有“編譯”。
雖然編譯包括重編譯，但是重編譯都沒出現過一次。除非顯示讓語句重編譯（如 option(recompile)）才出現。

測試一：最佳，每次都會產生新的計畫快取

測試二：不好，同樣緩衝計劃，返回結果集較大時效能不一樣

測試三：省緩衝，隨著資料量增長，預存程序最好重新編譯

測試四：省緩衝，查詢計劃固定，更改不了。

最後總體測試對錶以上這四種情況：
資料較多，不了，總結如下：

ProductID	查詢類型	格式	總邏輯讀	CPU	記憶體	時間	每次編譯	緩衝大小	查詢開銷
870(4688行)	即時查詢	where P.ProductID =870	1305	20	952	20	是	56 KB	26%
870(4688行)	即時參數查詢	where P.ProductID [email protected]	1305	16	1016	16	是	56 KB	24%
870(4688行)	預存程序	proc ：@ProductID = 870	1305	17	928	17	是	56 KB	26%
870(4688行)	預存程序內聲明	proc ：where P.ProductID [email protected]	1305	18	984	18	是	56 KB	24%
897(2行)	即時查詢	where P.ProductID =897	20	10	792	10	是	48 KB	13%
897(2行)	即時參數查詢	where P.ProductID [email protected]	1305	17	1016	17	是	56 KB	37%
897(2行)	預存程序	proc ：P_Test @ProductID = 897	20	8	760	8	是	56 KB	13%
897(2行)	預存程序內聲明	proc ：where P.ProductID [email protected]	1305	18	984	18	是	56 KB	37%

SQL Server 查詢最佳化（測試02）參數嗅探-執行計畫選擇