您好,登錄后才能下訂單哦!
小編給大家分享一下SQLSERVER參數(shù)嗅探問題的示例分析,相信大部分人都還不怎么了解,因此分享這篇文章給大家參考一下,希望大家閱讀完這篇文章后大有收獲,下面讓我們一起去了解一下吧!
下面測試數(shù)據(jù)庫的備份文件,里面有一些表和一些測試數(shù)據(jù) ,因為我下面用的測試表都是這個數(shù)據(jù)庫里的
只需要還原數(shù)據(jù)庫就可以了,這個數(shù)據(jù)庫是SQL2005版本的,數(shù)據(jù)庫名:AdventureWorks
下面只需要用到三張表,表里面有索引:
[Production].[Product] [SalesOrderHeader_test] [SalesOrderDetail_test]
數(shù)據(jù)庫下載鏈接:AdventureWorks
其實簡單來講,參數(shù)嗅探我的很通俗的解釋就是:SQLSERVER用鼻子嗅不到具體參數(shù)是多少
所以他不能選擇最合適的執(zhí)行計劃去執(zhí)行你的查詢,所以參數(shù)嗅探是一個不好的現(xiàn)象。
想真正了解參數(shù)嗅探,大家可以先創(chuàng)建下面兩個存儲過程
存儲過程一:
USE [AdventureWorks] GO DROP PROC Sniff GO CREATE PROC Sniff(@i INT) AS SELECT COUNT(b.[SalesOrderID]),SUM(p.[Weight]) FROM [dbo].[SalesOrderHeader_test] a INNER JOIN [dbo].[SalesOrderDetail_test] b ON a.[SalesOrderID]=b.[SalesOrderID] INNER JOIN [Production].[Product] p ON b.[ProductID]=p.[ProductID] WHERE a.[SalesOrderID]=@i GO
存儲過程二:
復(fù)制代碼 代碼如下:1 USE [AdventureWorks] 2 GO 3 DROP PROC Sniff2 4 GO 5 CREATE PROC Sniff2(@i INT) 6 AS 7 DECLARE @j INT 8 SET @j=@i 9 SELECT COUNT(b.[SalesOrderID]),SUM(p.[Weight])10 FROM [dbo].[SalesOrderHeader_test] a11 INNER JOIN [dbo].[SalesOrderDetail_test] b12 ON a.[SalesOrderID]=b.[SalesOrderID]13 INNER JOIN [Production].[Product] p14 ON b.[ProductID]=p.[ProductID]15 WHERE a.[SalesOrderID]=@j16 GO
然后請做下面這兩個測試
測試一:
--測試一: USE [AdventureWorks] GO DBCC freeproccache GO EXEC [dbo].[Sniff] @i = 500000 -- int --發(fā)生編譯,插入一個使用nested loops聯(lián)接的執(zhí)行計劃 GO EXEC [dbo].[Sniff] @i = 75124 -- int --發(fā)生執(zhí)行計劃重用,重用上面的nested loops的執(zhí)行計劃 GO
測試二:
--測試二: USE [AdventureWorks] GO DBCC freeproccache GO SET STATISTICS PROFILE ON EXEC [dbo].[Sniff] @i = 75124 -- int --發(fā)生編譯,插入一個使用hash match聯(lián)接的執(zhí)行計劃 GO EXEC [dbo].[Sniff] @i = 50000 -- int --發(fā)生執(zhí)行計劃重用,重用上面的hash match的執(zhí)行計劃 GO
從上面兩個測試可以清楚地看到執(zhí)行計劃重用的副作用。
由于數(shù)據(jù)分布差別很大參數(shù)50000和75124只對自己生成的執(zhí)行計劃有好的性能,
如果使用對方生成的執(zhí)行計劃,性能就會下降。參數(shù)50000返回的結(jié)果集比較小,
所以性能下降不太嚴重。參數(shù)75124返回的結(jié)果集大,就有了明顯的性能下降,兩個執(zhí)行計劃的差別有近10倍
對于這種因為重用他人生成的執(zhí)行計劃而導(dǎo)致的水土不服現(xiàn)象,SQSERVERL有一個專有名詞,叫“參數(shù)嗅探 parameter sniffing”
因為語句的執(zhí)行計劃對變量的值很敏感,而導(dǎo)致重用執(zhí)行計劃會遇到性能問題,就是我上面說的
“
SQLSERVER用鼻子嗅不到具體參數(shù)是多少,所以他不能選擇最合適的執(zhí)行計劃去執(zhí)行你的查詢
”
本地變量的影響
那對于有parameter sniffing問題的存儲過程,如果使用本地變量,會怎樣呢?
下面請看測試3。這次用不同的變量值時,都清空執(zhí)行計劃緩存,迫使其重編譯
--第一次 USE [AdventureWorks] GO DBCC freeproccache GO SET STATISTICS TIME ON SET STATISTICS PROFILE ON EXEC [dbo].[Sniff] @i = 50000 -- int GO
--第二次 USE [AdventureWorks] GO DBCC freeproccache GO SET STATISTICS TIME ON SET STATISTICS PROFILE ON EXEC [dbo].[Sniff] @i = 75124 -- int GO
--第三次 USE [AdventureWorks] GO DBCC freeproccache GO SET STATISTICS TIME ON SET STATISTICS PROFILE ON EXEC [dbo].[Sniff2] @i = 50000 -- int GO
--第四次 USE [AdventureWorks] GO DBCC freeproccache GO SET STATISTICS TIME ON SET STATISTICS PROFILE ON EXEC [dbo].[Sniff2] @i = 75124 -- int GO
看他們的執(zhí)行計劃:
對于第一句和第二句,因為SQL在編譯的時候知道變量的值,所以在做EstimateRows的時候,做得非常準確,選擇了最適合他們的執(zhí)行計劃
但是對于第三句和第四句,SQLSERVER不知道@j的值是多少,所以在做EstimateRows的時候,不管代入的@i值是多少,
一律給@j一樣的預(yù)測結(jié)果。所以兩個執(zhí)行計劃是完全一樣的(都是Hash Match)。
參數(shù)嗅探的解決辦法
參數(shù)嗅探的問題發(fā)生的頻率并不高,他只會發(fā)生在一些表格里的數(shù)據(jù)分布很不均勻,或者用戶帶入的參數(shù)值很不均勻的情況下。
由于篇幅原因我就不具體說了,只是做一些歸納
(1)用exec()的方式運行動態(tài)SQL
如果在存儲過程里不是直接運行語句,而是把語句帶上變量,生成一個字符串,再讓exec()這樣的命令做動態(tài)語句運行,
那SQL就會在運行到這句話的時候,對動態(tài)語句進行編譯。
這時SQL已經(jīng)知道了變量的值,會根據(jù)生成優(yōu)化的執(zhí)行計劃,從而繞過參數(shù)嗅探問題
--例如前面的存儲過程Sniff,就可以改成這樣 USE [AdventureWorks] GO DROP PROC NOSniff GO CREATE PROC NOSniff(@i INT) AS DECLARE @cmd VARCHAR(1000) SET @cmd='SELECT COUNT(b.[SalesOrderID]),SUM(p.[Weight]) FROM [dbo].[SalesOrderHeader_test] a INNER JOIN [dbo].[SalesOrderDetail_test] b ON a.[SalesOrderID]=b.[SalesOrderID] INNER JOIN [Production].[Product] p ON b.[ProductID]=p.[ProductID] WHERE a.[SalesOrderID]=' EXEC(@cmd+@i) GO
(2)使用本地變量local variable
(3)在語句里使用query hint,指定執(zhí)行計劃
在select,insert,update,delete語句的最后,可以加一個"option(<query_hint>)"的子句
對SQLSERVER將要生成的執(zhí)行計劃進行指導(dǎo)。當DBA知道問題所在以后,可以通過加hint的方式,引導(dǎo)
SQL生成一個比較安全的,對所有可能的變量值都不差的執(zhí)行計劃
USE [AdventureWorks] GO DROP PROC NoSniff_QueryHint_Recompile GO CREATE PROC NoSniff_QueryHint_Recompile(@i INT) AS SELECT COUNT(b.[SalesOrderID]),SUM(p.[Weight]) FROM [dbo].[SalesOrderHeader_test] a INNER JOIN [dbo].[SalesOrderDetail_test] b ON a.[SalesOrderID]=b.[SalesOrderID] INNER JOIN [Production].[Product] p ON b.[ProductID]=p.[ProductID] WHERE a.[SalesOrderID]=@i OPTION(RECOMPILE) GO
(4)Plan Guide
可以用下面的方法,在原來那個有參數(shù)嗅探問題的存儲過程“Sniff”上,解決sniffing問題
USE [AdventureWorks] GO EXEC [sys].[sp_create_plan_guide] @name=N'Guide1', @stmt=N'SELECT COUNT(b.[SalesOrderID]),SUM(p.[Weight]) FROM [dbo].[SalesOrderHeader_test] a INNER JOIN [dbo].[SalesOrderDetail_test] b ON a.[SalesOrderID]=b.[SalesOrderID] INNER JOIN [Production].[Product] p ON b.[ProductID]=p.[ProductID] WHERE a.[SalesOrderID]=@i', @type=N'OBJECT', @module_or_batch=N'Sniff', @params=NULL, @hints=N'option(optimize for(@i=75124))'; GO
以上是“SQLSERVER參數(shù)嗅探問題的示例分析”這篇文章的所有內(nèi)容,感謝各位的閱讀!相信大家都有了一定的了解,希望分享的內(nèi)容對大家有所幫助,如果還想學習更多知識,歡迎關(guān)注億速云行業(yè)資訊頻道!
免責聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點不代表本網(wǎng)站立場,如果涉及侵權(quán)請聯(lián)系站長郵箱:is@yisu.com進行舉報,并提供相關(guān)證據(jù),一經(jīng)查實,將立刻刪除涉嫌侵權(quán)內(nèi)容。