Parameter sniff is encountered in parameterized SQL writing in SQL Server, which leads to the rapid solution of unreasonable execution plan reuse. parametersniff

Parameter sniff is encountered in parameterized SQL writing in SQL Server, which leads to the rapid solution of unreasonable execution plan reuse. parametersniff

Parameter sniff is an execution plan generated by reusing other parameters. As a result, the current parameter uses this execution plan for non-optimization. Everyone familiar with the data should know that the most typical problem of generating parameter sniff is the use of parameterized SQL statements (or the use of parameterization in the stored procedure, if the data distribution is uneven, the execution plan generated under normal circumstances is reused when many parameters with distributed data are input, the cached execution plan is not suitable for the current parameter.

In this case, in actual business, the frequency of occurrence is relatively high, because the stored procedure generally adopts the parameterized writing method. In this case, when data parameters with uneven distribution are encountered, parameter sniff occurs, which is a headache.

The specific cause of parameter sniff is not explained too much. It seems too low to explain this.

Let me give a simple example to illustrate how the parameterized stored procedure is written, what problems exist, and how to solve the parameter sniff problem,

First, create a test environment:

Create table ParameterSniffProblem (id int identity (1,1), CustomerId int, OrderId int, OrederStatus int, CreateDate Datetime, Remark varchar (200 )) declare @ I int = 0 while @ I <500000 beginINSERT INTO ParameterSniffProblem values (@ I % 10000, @ I, RAND () * 10, GETDATE ()-RAND () * 100, NEWID () set @ I = @ I + 1end -- if a customer has a large number of orders and simulates uneven data distribution, INSERT INTO ParameterSniffProblem values (6666, RAND () * 100000,1, GETDATE ()-RAND () * 100, NEWID () GO 100000 -- CREATE a normal index create clustered index IDX_CreateDate on ParameterSniffProblem (CreateDate)
Create index IDX_CustomerId ON ParameterSniffProblem (CustomerId)

Writing of parameterized stored procedures:

When writing a stored procedure, we generally recommend using parameterized writing to reduce compilation of the stored procedure and enhance reuse of execution plan cache.

This is probably the case.

CREATE PROCEDURE [dbo].ParameterSniffTest ( @p_CustomerId int,@p_Status int,@p_FromDate datetime,@p_ToDate datetime) AS BEGINSET NOCOUNT ON DECLARE@Parm NVARCHAR(MAX),@sqlcommand NVARCHAR(MAX) = N''SET @sqlcommand = 'SELECT * FROM ParameterSniffProblem WHERE 1=1'　　　　 IF(@p_CustomerId IS NOT NULL)SET @sqlcommand = CONCAT(@sqlcommand,'AND CustomerId=@p_CustomerId ')IF(@p_Status IS NOT NULL)SET @sqlcommand = CONCAT(@sqlcommand,'AND OrederStatus=@p_Status ')IF(@p_FromDate IS NOT NULL)SET @sqlcommand = CONCAT(@sqlcommand,'AND CreateDate>=@p_FromDate ')IF(@p_ToDate IS NOT NULL)SET @sqlcommand = CONCAT(@sqlcommand,'AND CreateDate<=@p_ToDate ')　　　　SET @Parm= '@p_CustomerId int,@p_Status 　　int,@p_FromDate 　datetime,@p_ToDate 　　datetime '　　　　EXEC sp_executesql @sqlcommand,@Parm,@p_CustomerId = @p_CustomerId,@p_Status = @p_Status,@p_FromDate = @p_FromDate,@p_ToDate = @p_ToDate ENDGO

Parameter Sniff:

This poses a potential parameter sniff problem,

For example, if I query the order information of user id = 100, a normal distribution of data, and the first compilation of the stored procedure, this execution plan is completely normal,

If I change the parameter to execute the query user's information of 6666, a distribution and its uneven data, but because the execution plan cached above is reused, the parameter sniff problem occurs, this execution plan is obviously unreasonable.

I/O does not look at, deliberately create an example

If I clear the execution plan cache and re-execute the preceding query, the execution plan is not like this because of the re-compilation. For the CustomerID = 6666 parameter, obviously, full table scan is cheaper.

Presumably this is a common problem in development. We parameterized SQL to reuse the execution plan for queries with different parameters, but unfortunately, when the data distribution is uneven, the reuse of the Execution Plan causes damage to the database. In this example, if the execution plan with a large distribution of data is reused as a normal parameter, for example, the index can be used for naming, and the result is a table scan, the consequences will be more serious.

So what should we do if we want to reuse the execution plan as much as possible and avoid generating the parameter sniff problem because of the reuse of the execution plan?

We know that the problem lies in @ p_CustomerId, so it is not possible to parameterize @ p_CustomerId that may generate the parameter sniff problem. It is directly pieced together in SQL. If @ p_CustomerId changes, the SQL statement will be re-compiled, that is, re-compile the incoming @ p_mermerid

If it is @ p_CustomerId, and other parameters change, for example, the time field changes here, you can also enjoy the advantages of execution plan reuse brought about by parameterization, that is, processing the parameter @ p_CustomerId, directly flatten @ p_CustomerId in the SQL statement as a string. In this way, it is equivalent to an ad hoc query, and assign a value to the CustomerId query condition field without parameterization.

IF(@p_CustomerId IS NOT NULL)SET @sqlcommand = CONCAT(@sqlcommand,'AND CustomerId= ',@p_CustomerId)

In this way, when the stored procedure is executed,

Run the index seek command of IDX_CustomerId when @ p_CustomerId = 1.

When @ p_CustomerId = 6666 is introduced, the execution plan is a full table scan to avoid reusing the execution plan generated above, resulting in unreasonable execution methods for efficiency and database server resource consumption.

This will minimize the impact of the parameter sniff problem. When the execution plan of @ p_CustomerId = 1 is cached, the @ p_CustomerId = 1 is passed in again. Other conditions have minor changes, for example, if the time field is changed, you can reuse the cache execution plan to avoid the impact of re-compilation.

Conclusion:

This method is not perfect to deal with the parameter sniff problem. It certainly has some problems. I certainly know that re-compilation is required once @ p_mermerid is different.

The value of @ p_mermerid is different. In this case, the chance of re-compilation is inevitably increased,

But it will not cause parameter sniff problems because of unreasonable execution plan reuse.

You need to know that once the parameter sniff problem occurs, a large number of queries use unreasonable execution plans, which will have a very serious impact on the entire server, for example, a large number of IO may be generated.

There is also a benefit, such as the first input @ p_mermerid = 1,

Re-input @ p_CustomerId = 1. Other conditions have minor changes. For example, if the time field is changed, the cache execution plan can still be reused, to avoid the impact of recompilation, I am just a simple example here. The actual application is far more complex than this one.

For example, the distribution of a large amount of data has two characteristics. The first distribution indicates not only one, but the second distribution is dynamic, it is possible that in the first quarter, the majority of data is A, and in the second quarter, the majority of data is B.

Therefore, it is difficult to solve the parameter sniff problem using the Plan Guide method.

This method can also reuse the cached execution plan to a certain extent, which can reduce (but inevitably) the number of recompilation times.

At the same time, compared with the ad hoc query method that pieces together an SQL string for execution, this method can also take advantages of parameterization, such as SQL injection.

Summary:

There are many ways to solve the parameter sniff problem.

The most typical is forced re-compilation,

Or execute a patchwork string using EXEC, which belongs to the Adhoc query.

Or query prompt,

Or use local variables,

Or use Plan Guide, etc,

Each method has its own limitations. at least so far, there is no perfect way to solve the parameter sniff problem.

There are many solutions to the problem, and solving the problem at the minimum cost is king.