免费观看又色又爽又黄的小说免费_美女福利视频国产片_亚洲欧美精品_美国一级大黄大色毛片

SQLSERVER參數嗅探問題的示例分析

小編給大家分享一下SQLSERVER參數嗅探問題的示例分析,相信大部分人都還不怎么了解,因此分享這篇文章給大家參考一下,希望大家閱讀完這篇文章后大有收獲,下面讓我們一起去了解一下吧!

南城ssl適用于網站、小程序/APP、API接口等需要進行數據傳輸應用場景,ssl證書未來市場廣闊!成為創新互聯建站的ssl證書銷售渠道,可以享受市場價格4-6折優惠!如果有意向歡迎電話聯系或者加微信:18980820575(備注:SSL證書合作)期待與您的合作!

下面測試數據庫的備份文件,里面有一些表和一些測試數據 ,因為我下面用的測試表都是這個數據庫里的

只需要還原數據庫就可以了,這個數據庫是SQL2005版本的,數據庫名:AdventureWorks

下面只需要用到三張表,表里面有索引:

[Production].[Product] [SalesOrderHeader_test] [SalesOrderDetail_test]

數據庫下載鏈接:AdventureWorks

SQLSERVER參數嗅探問題的示例分析

其實簡單來講,參數嗅探我的很通俗的解釋就是:SQLSERVER用鼻子嗅不到具體參數是多少

所以他不能選擇最合適的執行計劃去執行你的查詢,所以參數嗅探是一個不好的現象。

想真正了解參數嗅探,大家可以先創建下面兩個存儲過程

存儲過程一:

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

存儲過程二:

復制代碼 代碼如下: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
--發生編譯,插入一個使用nested loops聯接的執行計劃
GO

EXEC [dbo].[Sniff] @i = 75124 -- int
--發生執行計劃重用,重用上面的nested loops的執行計劃
GO

SQLSERVER參數嗅探問題的示例分析

測試二:

--測試二:

USE [AdventureWorks]
GO
DBCC freeproccache
GO
SET STATISTICS PROFILE ON
EXEC [dbo].[Sniff] @i = 75124 -- int
--發生編譯,插入一個使用hash match聯接的執行計劃
GO

EXEC [dbo].[Sniff] @i = 50000 -- int
--發生執行計劃重用,重用上面的hash match的執行計劃
GO

SQLSERVER參數嗅探問題的示例分析

從上面兩個測試可以清楚地看到執行計劃重用的副作用。

由于數據分布差別很大參數50000和75124只對自己生成的執行計劃有好的性能,

如果使用對方生成的執行計劃,性能就會下降。參數50000返回的結果集比較小,

所以性能下降不太嚴重。參數75124返回的結果集大,就有了明顯的性能下降,兩個執行計劃的差別有近10倍

對于這種因為重用他人生成的執行計劃而導致的水土不服現象,SQSERVERL有一個專有名詞,叫“參數嗅探 parameter sniffing”

因為語句的執行計劃對變量的值很敏感,而導致重用執行計劃會遇到性能問題,就是我上面說的

SQLSERVER用鼻子嗅不到具體參數是多少,所以他不能選擇最合適的執行計劃去執行你的查詢

本地變量的影響

那對于有parameter sniffing問題的存儲過程,如果使用本地變量,會怎樣呢?

下面請看測試3。這次用不同的變量值時,都清空執行計劃緩存,迫使其重編譯

--第一次
USE [AdventureWorks]
GO
DBCC freeproccache
GO
SET STATISTICS TIME ON
SET STATISTICS PROFILE ON
EXEC [dbo].[Sniff] @i = 50000 -- int
GO

SQLSERVER參數嗅探問題的示例分析

SQLSERVER參數嗅探問題的示例分析

--第二次
USE [AdventureWorks]
GO
DBCC freeproccache
GO
SET STATISTICS TIME ON
SET STATISTICS PROFILE ON
EXEC [dbo].[Sniff] @i = 75124 -- int
GO

SQLSERVER參數嗅探問題的示例分析

SQLSERVER參數嗅探問題的示例分析

--第三次
USE [AdventureWorks]
GO
DBCC freeproccache
GO
SET STATISTICS TIME ON
SET STATISTICS PROFILE ON
EXEC [dbo].[Sniff2] @i = 50000 -- int
GO

SQLSERVER參數嗅探問題的示例分析

SQLSERVER參數嗅探問題的示例分析

--第四次
USE [AdventureWorks]
GO
DBCC freeproccache
GO
SET STATISTICS TIME ON
SET STATISTICS PROFILE ON
EXEC [dbo].[Sniff2] @i = 75124 -- int
GO

SQLSERVER參數嗅探問題的示例分析

SQLSERVER參數嗅探問題的示例分析

看他們的執行計劃:

對于第一句和第二句,因為SQL在編譯的時候知道變量的值,所以在做EstimateRows的時候,做得非常準確,選擇了最適合他們的執行計劃

但是對于第三句和第四句,SQLSERVER不知道@j的值是多少,所以在做EstimateRows的時候,不管代入的@i值是多少,

一律給@j一樣的預測結果。所以兩個執行計劃是完全一樣的(都是Hash Match)。

參數嗅探的解決辦法

參數嗅探的問題發生的頻率并不高,他只會發生在一些表格里的數據分布很不均勻,或者用戶帶入的參數值很不均勻的情況下。

由于篇幅原因我就不具體說了,只是做一些歸納

(1)用exec()的方式運行動態SQL

如果在存儲過程里不是直接運行語句,而是把語句帶上變量,生成一個字符串,再讓exec()這樣的命令做動態語句運行,

那SQL就會在運行到這句話的時候,對動態語句進行編譯。

這時SQL已經知道了變量的值,會根據生成優化的執行計劃,從而繞過參數嗅探問題

--例如前面的存儲過程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,指定執行計劃

在select,insert,update,delete語句的最后,可以加一個"option(<query_hint>)"的子句

對SQLSERVER將要生成的執行計劃進行指導。當DBA知道問題所在以后,可以通過加hint的方式,引導

SQL生成一個比較安全的,對所有可能的變量值都不差的執行計劃

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

可以用下面的方法,在原來那個有參數嗅探問題的存儲過程“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參數嗅探問題的示例分析”這篇文章的所有內容,感謝各位的閱讀!相信大家都有了一定的了解,希望分享的內容對大家有所幫助,如果還想學習更多知識,歡迎關注創新互聯行業資訊頻道!

本文題目:SQLSERVER參數嗅探問題的示例分析
文章網址:http://m.newbst.com/article28/gsepcp.html

成都網站建設公司_創新互聯,為您提供App開發品牌網站設計標簽優化網站維護定制開發企業網站制作

廣告

聲明:本網站發布的內容(圖片、視頻和文字)以用戶投稿、用戶轉載內容為主,如果涉及侵權請盡快告知,我們將會在第一時間刪除。文章觀點不代表本網站立場,如需處理請聯系客服。電話:028-86922220;郵箱:631063699@qq.com。內容未經允許不得轉載,或轉載時需注明來源: 創新互聯

成都網站建設