SQLSERVER parameter sniffing

What is SQLSERVER parameter sniffing? When you hear this word, you may feel that it is related to hackers. Use a tool to sniff the parameter and then intercept the parameter, and remove the pants o (_) o. In fact, I think everyone is too sensitive. In fact, this article has nothing to do with database security. In fact, it is related to database performance tuning. I believe everyone has a forum on SQLSERVER.

What is SQLSERVER parameter sniffing? When you hear this word, you may feel that it is related to hackers. Use a tool to sniff the parameter and then intercept the parameter, and remove the pants o (_) o. In fact, I think everyone is too sensitive. In fact, this article has nothing to do with database security. In fact, it is related to database performance tuning. I believe everyone has a forum on SQLSERVER.

SQLSERVER parameter sniffing

Everyone heard"Sniffing"The term should be related to hackers. Use a tool to sniff the parameters and intercept the parameter, and then take a look at it.

In fact, I think everyone is too sensitive. In fact, this article has nothing to do with database security. It is actually related to database performance tuning.

I believe that many of you may have seen the Forum"Parameter sniffing"

Three posts are about parameter sniffing.

Http://social.msdn.microsoft.com/Forums/zh-CN/sqlserverzhchs/thread/caccb7f3-8366-4954-8f8a-145eb6bca9dd

Http://msdn.microsoft.com/zh-cn/magazine/ee236412.aspx

Http://social.msdn.microsoft.com/Forums/zh-CN/sqlserverzhchs/thread/bfbe54de-ac00-49e9-a83b-f97a60bf74ef

Below I will provide a backup file for the test database, which contains some tables and some test data, you can download it, because the test tables I use below are all in this database

You only need to restore the database. The database is in SQL2005 and the database name is AdventureWorks.

The following uses three tables with indexes:

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

Database download link:AdventureWorks_Full_backup_2013-3-4.bak

In fact, the common explanation of parameter sniffing is that SQL Server cannot sniff specific parameters.

Therefore, he cannot select the most suitable execution plan to execute your query, so parameter sniffing is a bad phenomenon.

To learn more about parameter sniffing, You can first create the following two stored procedures:

Stored Procedure 1:

 1 USE [AdventureWorks] 2 GO 3 DROP PROC Sniff 4 GO 5 CREATE PROC Sniff(@i INT) 6 AS 7 SELECT COUNT(b.[SalesOrderID]),SUM(p.[Weight]) 8 FROM [dbo].[SalesOrderHeader_test] a 9 INNER JOIN [dbo].[SalesOrderDetail_test] b10 ON a.[SalesOrderID]=b.[SalesOrderID]11 INNER JOIN [Production].[Product] p12 ON b.[ProductID]=p.[ProductID]13 WHERE a.[SalesOrderID]=@i14 GO

Stored Procedure 2:

 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

Then perform the following two tests:

Test 1:

1 -- Test 1: 2 USE [AdventureWorks] 3 GO 4 DBCC freeproccache 5 GO 6 EXEC [dbo]. [Sniff] @ I = 500000 -- int 7 -- compile, insert an execution plan using the nested loops join 8 GO 9 10 EXEC [dbo]. [Sniff] @ I = 75124 -- int11 -- execution plan reuse occurs, reuse the preceding execution plan of nested loops 12 GO

Test 2:

1 -- Test 2: 2 3 USE [AdventureWorks] 4 GO 5 DBCC freeproccache 6 GO 7 set statistics profile on 8 EXEC [dbo]. [Sniff] @ I = 75124 -- int 9 -- compile, insert an execution plan using hash match join 10 GO11 12 EXEC [dbo]. [Sniff] @ I = 50000 -- int13 -- execution plan reuse occurs, reuse the preceding hash match execution plan 14 GO

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

Because the data distribution is very different, parameters 50000 and 75124 only have good performance for self-generated execution plans,

If the execution plan generated by the other party is used, the performance will decrease. The result set returned by parameter 50000 is small,

Therefore, the performance decline is not very serious. When the result set returned by parameter 75124 is large, the performance decreases significantly. The difference between the two execution plans is nearly 10 times.

SQSERVERL has a proprietary term called "parameter sniffing" for this sort of denial of service caused by reuse of execution plans generated by others"

The statement execution plan is very sensitive to variable values, which leads to performance problems when reusing the execution plan.

"

SQLSERVER cannot sniff the specific parameters with the nose, so it cannot select the most appropriate execution plan to execute your query.

"

Influence of local variables

What if a local variable is used for a stored procedure with a parameter sniffing problem?

See Test 3 below. When different variable values are used this time, the execution plan cache is cleared to force its re-compilation.

1 -- first 2 USE [AdventureWorks] 3 GO4 DBCC freeproccache5 GO6 set statistics time ON7 set statistics profile ON8 EXEC [dbo]. [Sniff] @ I = 50000 -- int9 GO

1 -- 2 USE [AdventureWorks] 3 GO4 DBCC freeproccache5 GO6 set statistics time ON7 set statistics profile ON8 EXEC [dbo]. [Sniff] @ I = 75124 -- int9 GO

1 -- 2 USE [AdventureWorks] 3 GO4 DBCC freeproccache5 GO6 set statistics time ON7 set statistics profile ON8 EXEC [dbo]. [Sniff2] @ I = 50000 -- int9 GO

1 -- fourth 2 USE [AdventureWorks] 3 GO4 DBCC freeproccache5 GO6 set statistics time ON7 set statistics profile ON8 EXEC [dbo]. [Sniff2] @ I = 75124 -- int9 GO

View their execution plan:

For the first and second sentences, because SQL knows the value of the variable during compilation, It is very accurate when doing EstimateRows and selects the execution plan most suitable for them.

However, for the third and fourth sentences, SQLSERVER does not know the value of @ j. Therefore, no matter what the value of @ I is during EstimateRows,

All prediction results are the same as those of @ j. So the two execution plans are exactly the same (bothHash Match).

Parameter sniffing Solution

The frequency of parameter sniffing problems is not high. It only occurs when the data distribution in some tables is uneven or the parameter values brought by the user are uneven.

I will not elaborate on it because of the length, but I will just make some Induction

(1) run dynamic SQL statements in exec () Mode

If you do not directly run the statement in the stored procedure, but put the statement with a variable, generate a string, and then run the exec () command as a dynamic statement,

Then, the SQL statement is compiled when the statement is run.

At this time, SQL knows the value of the variable and generates an optimized execution plan to bypass parameter sniffing.

1 -- for example, the previous Stored Procedure Sniff can be changed to 2 USE [AdventureWorks] 3 GO 4 drop proc NOSniff 5 GO 6 create proc NOSniff (@ I INT) 7 AS 8 DECLARE @ cmd VARCHAR (1000) 9 SET @ cmd = 'select COUNT (B. [SalesOrderID]), SUM (p. [Weight]) 10 FROM [dbo]. [SalesOrderHeader_test] a11 inner join [dbo]. [SalesOrderDetail_test] b12 ON. [SalesOrderID] = B. [SalesOrderID] 13 inner join [Production]. [Product] p14 ON B. [ProductID] = p. [ProductID] 15 WHERE. [SalesOrderID] = '16 EXEC (@ cmd + @ I) 17 GO

(2) Use the local variable

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

At the end of the select, insert, update, and delete statements, you can add an "option ( ) "Clause

Provides guidance on the execution plan to be generated by SQLSERVER. After the DBA knows the problem, it can be guided by hint.

SQL generates a safer execution plan with no variable values.

 1 USE [AdventureWorks] 2 GO 3 DROP PROC NoSniff_QueryHint_Recompile 4 GO 5 CREATE PROC NoSniff_QueryHint_Recompile(@i INT)  6 AS 7 SELECT COUNT(b.[SalesOrderID]),SUM(p.[Weight]) 8 FROM [dbo].[SalesOrderHeader_test] a 9 INNER JOIN [dbo].[SalesOrderDetail_test] b10 ON a.[SalesOrderID]=b.[SalesOrderID]11 INNER JOIN [Production].[Product] p12 ON b.[ProductID]=p.[ProductID]13 WHERE a.[SalesOrderID]=@i14 OPTION(RECOMPILE)15 GO

(4) Plan Guide

The following method can be used to solve the sniffing problem in the stored procedure "Sniff" with the parameter sniffing problem.

 1 USE [AdventureWorks] 2 GO 3 EXEC [sys].[sp_create_plan_guide] 4 @name=N'Guide1', 5 @stmt=N'SELECT COUNT(b.[SalesOrderID]),SUM(p.[Weight]) 6 FROM [dbo].[SalesOrderHeader_test] a 7 INNER JOIN [dbo].[SalesOrderDetail_test] b 8 ON a.[SalesOrderID]=b.[SalesOrderID] 9 INNER JOIN [Production].[Product] p10 ON b.[ProductID]=p.[ProductID]11 WHERE a.[SalesOrderID]=@i',12 @type=N'OBJECT',13 @module_or_batch=N'Sniff',14 @params=NULL,15 @hints=N'option(optimize for(@i=75124))';16 GO

For Plan Guide, it can also be used in general statement optimization.

I finally got it done. It took a long time to test the data ~~