Shulou(Shulou.com)05/31 Report--

Editor to share with you an example analysis of the problem of SQLSERVER parameter sniffing, I believe that most people do not know much about it, so share this article for your reference. I hope you will learn a lot after reading this article. Let's learn about it together.

The backup file of the test database below contains some tables and some test data, because the test tables I use below are from this database

Only need to restore the database, this database is the SQL2005 version, database name: AdventureWorks

Only three tables are needed below, and there are indexes in them:

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

Database download link: AdventureWorks

In fact, to put it simply, parameter sniffing is my very popular explanation: SQLSERVER can't smell the specific parameters with his nose.

So he can't choose the most appropriate execution plan to execute your query, so parameter sniffing is a bad phenomenon.

To really understand parameter sniffing, you can first create the following two stored procedures

Stored procedure 1:

USE [AdventureWorks] GODROP PROC SniffGOCREATE PROC Sniff (@ I INT) ASSELECT COUNT (b. [SalesOrderID]), SUM (p. [weight]) FROM [dbo]. [SalesOrderHeader_test] aINNER JOIN [dbo]. [SalesOrderDetail_test] bON a. [SalesOrderID] = b. [SalesOrderID] INNER JOIN [Production]. [Product] pON b. [ProductID] = p.[ Productid] WHERE a. [SalesOrderID] = @ iGO

Stored procedure 2:

The copy code is as follows: 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 @ jacuzzi 9 SELECT COUNT (b. [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

Then please do the following two tests

Test 1:

-- Test 1: USE [AdventureWorks] GODBCC freeproccacheGOEXEC [dbo]. [Sniff] @ I = 500000-- int-- compiles and inserts an execution plan using nested loops join GOEXEC [dbo]. [Sniff] @ I = 75124-- int-- occurs execution plan reuse, reuse nested loops execution plan GO above

Test 2:

-- Test 2: USE [AdventureWorks] GODBCC freeproccacheGOSET STATISTICS PROFILE ONEXEC [dbo]. [Sniff] @ I = 75124-- int-- compiles and inserts an execution plan using hash match join GOEXEC [dbo]. [Sniff] @ I = 50000-- int-- occurs execution plan reuse, reuse hash match execution plan GO above

From the above two tests, you can clearly see the side effects of execution plan reuse.

Due to the great difference in data distribution, parameters 50000 and 75124 only have good performance for self-generated execution plans.

If you use the execution plan generated by the other party, the performance will degrade. The result set returned by parameter 50000 is relatively small

So the performance degradation is not too serious. When the result set returned by parameter 75124 is large, there is a significant performance degradation, and the difference between the two execution plans is nearly 10 times.

For this phenomenon of disobedience caused by the reuse of execution plans generated by others, SQSERVERL has a proper term called "parameter sniffing parameter sniffing"

Because the execution plan of the statement is very sensitive to the value of variables, the reuse of the execution plan will encounter performance problems, which is what I said above.

"

SQLSERVER can't sniff out the specific parameters, so he can't choose the most appropriate execution plan to execute your query.

"

The influence of local variables

What happens if you use local variables for stored procedures with parameter sniffing problems?

Next, please take a look at Test 3. When using different variable values this time, the execution plan cache is cleared, forcing it to recompile

-- first USE [AdventureWorks] GODBCC freeproccacheGOSET STATISTICS TIME ONSET STATISTICS PROFILE ONEXEC [dbo]. [Sniff] @ I = 50000-- intGO

-- the second USE [AdventureWorks] GODBCC freeproccacheGOSET STATISTICS TIME ONSET STATISTICS PROFILE ONEXEC [dbo]. [Sniff] @ I = 75124-- intGO

-- the third USE [AdventureWorks] GODBCC freeproccacheGOSET STATISTICS TIME ONSET STATISTICS PROFILE ONEXEC [dbo]. [Sniff2] @ I = 50000-- intGO

-- the fourth USE [AdventureWorks] GODBCC freeproccacheGOSET STATISTICS TIME ONSET STATISTICS PROFILE ONEXEC [dbo]. [Sniff2] @ I = 75124-- intGO

Look at their implementation plan:

For the first and second sentences, because SQL knows the value of the variable when compiling, it is very accurate when doing EstimateRows and chooses the execution plan that is most suitable for them.

But for the third and fourth sentences, SQLSERVER does not know what the value of @ j is, so when doing EstimateRows, no matter what the @ I value is

All give @ j the same prediction. So the two execution plans are exactly the same (both Hash Match).

The solution of parameter sniffing

The problem of parameter sniffing does not occur frequently, it only occurs when the data in some tables are unevenly distributed, or when the parameter values brought in by the user are very uneven.

Because of the space, I won't say it in detail, just make some induction.

(1) run dynamic SQL with exec ()

If you do not run the statement directly in the stored procedure, but take the statement with variables, generate a string, and then let commands such as exec () run as dynamic statements

Then SQL will compile the dynamic statement when it runs to this sentence.

At this time, SQL already knows the value of the variable and will generate the optimized execution plan to bypass the parameter sniffing problem.

For example, the previous stored procedure Sniff can be changed to USE [AdventureWorks] GODROP PROC NOSniffGOCREATE PROC NOSniff (@ I INT) ASDECLARE @ cmd VARCHAR (1000) SET @ cmd='SELECT COUNT (b. [SalesOrderID]), SUM (p. [weight]) FROM [dbo]. [SalesOrderHeader_test] aINNER JOIN [dbo]. [SalesOrderDetail_test] bON a. [SalesOrderID] = b. [SalesOrderID] INNER JOIN [Production]. [Product] pON b. [ProductID] = p.ProductID] WHERE a.[ SalesOrderID] = 'EXEC (@ cmd+@i) GO

(2) use the local variable local variable

(3) use query hint in the statement to specify the execution plan

At the end of the select,insert,update,delete statement, you can add a clause of "option ()"

Provide guidance on the execution plan that SQLSERVER will generate. When DBA knows what the problem is, it can be guided by adding hint.

SQL generates a relatively secure execution plan that is not bad for all possible variables

USE [AdventureWorks] GODROP PROC NoSniff_QueryHint_RecompileGOCREATE PROC NoSniff_QueryHint_Recompile (@ I INT) ASSELECT COUNT (b. [SalesOrderID]), SUM (p. [weight]) FROM [dbo]. [SalesOrderHeader_test] aINNER JOIN [dbo]. [SalesOrderDetail_test] bON a. [SalesOrderID] = b. [SalesOrderID] INNER JOIN [Production]. [Product] pON b. [ProductID] = p. [Productid] WHERE a. [SalesOrderID] = @ iOPTION (RECOMPILE) GO

(4) Plan Guide

You can use the following method to solve the sniffing problem on the original stored procedure "Sniff" that has a parameter sniffing problem

USE [AdventureWorks] GOEXEC [sys]. [sp_create_plan_guide] @ name=N'Guide1',@stmt=N'SELECT COUNT (b. [weight]) FROM [dbo]. [SalesOrderHeader_test] aINNER JOIN [dbo]. [SalesOrderDetail_test] bON a. [SalesOrderID] = b. [SalesOrderID] INNER JOIN [Production]. [Product] pON b. [ProductID] = p. [ProductID] WHERE a. [SalesOrderID] = @ iride paramsfolk null @ hints=N'option (optimize for (@ iTun75124))' GO above is all the content of the article "sample Analysis of SQLSERVER Parameter sniffing problems". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.