parameterized SQL syntax in SQL Server encounters parameter sniff, a solution that leads to unreasonable execution of plan reuse

The parameter sniff problem is to reuse the execution plan generated by other parameters, leading to the non-optimization of the execution plan by the current parameters. Presumably familiar with the data of the students should know that the most typical problem of producing parameter sniff is the use of parameterized SQL (or stored procedures using parameterized), if there is uneven distribution of data, the normal situation of the resulting execution plan, In the case of passing in parameters with more distributed data, the execution plan generated by the normal parameters is reused, and the cached execution plan is not a case for the current parameter.

This situation, in the actual business, the frequency is still relatively high, because the stored procedures are generally used in parametric notation, at this time, when encountering uneven distribution of data parameters, parameter sniff phenomenon appeared, this problem is still more annoying.

Specific parameter sniff cause, I don't do too much explanation, explain this is too low

I give a simple example, simulate this phenomenon, explain how the parameterized storage process is written, what problems exist, and how to solve the problem of parameter sniff,

Create a test environment first:

Create TableParametersniffproblem (IDint Identity(1,1), CustomerIdint, OrderIdint, Orederstatusint, CreateDateDatetime, Remarkvarchar( $))Declare @i int = 0 while @i<500000begin    INSERT  intoParametersniffproblemValues(@i%10000,@i,RAND()*Ten,GETDATE()-RAND()* -,NEWID())    Set @i=@i+1End --If a customer has a lot of orders, the simulation data distribution is not uniform situationINSERT  intoParametersniffproblemValues(6666,RAND()*100000,1,GETDATE()-RAND()* -,NEWID())GO 100000--Create a normal indexCREATE CLUSTERED INDEXIdx_createdate onParametersniffproblem (createdate)CREATE INDEXIdx_customerid onParametersniffproblem (CUSTOMERID)

How to parameterize a stored procedure:

When writing stored procedures, we generally recommend using parameterized notation to reduce the compilation of stored procedures and to enhance the reuse of execution plan caches.

That's about it.

CREATE PROCEDURE [dbo]. Parametersnifftest (@p_CustomerId int,    @p_Status int,    @p_FromDate datetime,    @p_ToDate datetime)  as   BEGIN    SETNOCOUNT 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 [email Protected]_customerid')        IF(@p_Status  is  not NULL)            SET @sqlcommand =CONCAT (@sqlcommand,'and [email protected]_status')        IF(@p_FromDate  is  not NULL)            SET @sqlcommand =CONCAT (@sqlcommand,'and Createdate>[email protected]_fromdate')        IF(@p_ToDate  is  not NULL)            SET @sqlcommand =CONCAT (@sqlcommand,'and Createdate<[email protected]_todate')
SET @Parm= '@p_CustomerId int, @p_Status int, @p_FromDate datetime, @p_To Date datetime'        
EXECsp_executesql@sqlcommand,@Parm,                          @p_CustomerId    =    @p_CustomerId,                          @p_Status        =    @p_Status,                          @p_FromDate      =    @p_FromDate,                          @p_ToDate        =    @p_ToDate ENDGO

Parameter Sniff Questions:

This is potentially a parameter sniff problem,

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

If I continue to change the parameters of the query user 6666 information, a distribution and uneven data, but because the reuse of the above cached execution plan, there is parameter sniff problem, this execution plan is obviously unreasonable

Io is not looked at, deliberately made examples of

If I empty the execution plan cache,

Re-execute the above query, because there is a recompile, the execution plan is not like this, for customerid=6666 this parameter, obviously go full table scan cost is smaller

Presumably this is a common problem in development to give,

We have parameterized SQL to reuse the execution plan for queries of different parameters,

Unfortunately, when the data is unevenly distributed, the reuse of the execution plan is just as damaging to the database,

In the above example, if the normal parameters reuse the execution plan with more data distributed, such as the name can be used in the index, the result is a table scan, the consequences will be more serious.

So, what if you want to reuse the execution plan as much as possible and avoid the parameter sniff problem caused by the reuse of the execution plan?

We know that the problem is @p_customerid body, then can have parameter sniff problem may produce @p_customerid do not parameterize, directly pieced together in SQL,

If the @p_customerid changes, recompile the SQL, that is, the incoming @p_customerid recompile

If the @p_customerid is not changed, changes to other parameters, such as the change of time fields Here, can also benefit from the reuse of execution plans brought by Parameterization

This is how to deal with @p_CustomerId this parameter, directly @p_customerid in the form of a string in the SQL statement,

In this case, it is equivalent to the ad hoc query, not by the parameterized way to CustomerID This query condition field assignment

IF (@p_CustomerId is not NULL)
SET @sqlcommand = CONCAT (@sqlcommand, ' and customerid= ', @p_CustomerId)

So that when you execute the stored procedure again,

When brought into the @p_customerid=1, execute Idx_customerid's index Seek

when brought into the @p_customerid=6666, recompile, the execution plan is full-table scan, avoid reusing the execution plan generated above, resulting in unreasonable execution of efficiency and the consumption of database server resources

This will minimize the impact of the parameter sniff problem, when the @p_customerid=1 execution plan is cached,

Re-passing @p_customerid=1, other conditions have minor changes, such as changes in the Time field, can still reuse the cached execution plan, to avoid the impact of recompilation

Conclusion:

This way to deal with parameter sniff problem, of course, is not perfect, there must be problems, I certainly know that once the @p_customerid is different will be recompiled

Certainly because the @p_customerid parameter values are different, this inevitably increases the chance of recompilation,

But not because of unreasonable execution plan reuse, bring the parameter sniff problem

To know that once the parameter sniff problem is generated, a large number of queries with unreasonable execution plans will have a very serious impact on the entire server, such as the possibility of generating a large number of IO, etc.

There is also a benefit,

For example, the first time you pass in @p_customerid=1

Re-passing @p_customerid=1, other conditions have minor changes, such as changes in the Time field, can still reuse the cached execution plan, to avoid the impact of recompilation

Of course I'm just a simple example here, and the actual application is much more complicated than this

For example, the distribution of the special multi-data has two characteristics, the first distribution of the indicator is not only one, the second distribution of uneven data is dynamic,

It is possible that the first quarter is a this part of the data occupy the majority, possibly the second quarter B data accounted for the overwhelming majority

So it's hard to use plan guide to solve the parameter sniff problem.

This approach can also be used to a certain extent to reuse the cached execution plan, which reduces (but inevitably) the number of recompilation

At the same time, this approach can also take advantage of the additional benefits of parameterization, such as SQL injection, compared to an ad hoc query that is executed with a SQL string.

Summarize:

There are many ways to solve the problem of parameter sniff.

The most typical is forced recompilation,

or use exec to execute a cobbled string, which belongs to the Adhoc query

or query hints,

or using a local variable,

or use plan guide and so on,

Every way has his limitations, at least so far, there is no perfect way to solve the problem of parameter sniff

There are many ways to solve a problem, and to solve a problem at the lowest cost is the kingly way.

parameterized SQL syntax in SQL Server encounters parameter sniff, a solution that leads to unreasonable execution of plan reuse