parameterized SQL writing in SQL Server encounters parameter sniff, resulting in a quick solution for unreasonable execution of plan reuse _mssql

The parameter sniff problem is to reuse the execution plan generated by other parameters, which causes the current parameter to adopt a phenomenon that is not optimized for the execution plan. Presumably the students who are familiar with the data should know that the most typical problem of producing parameter sniff is to use parameterized SQL (or parameterized in stored procedures), and if there is uneven data distribution, the implementation plan normally generated, The execution plan for normal parameter generation is reused in cases where there are more data in the distribution, and the cache execution plan is not a case of the current parameter.

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

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

Let me give a simple example to 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 TABLE Parametersniffproblem
(
ID int identity (1,1),
CustomerId int,
OrderId int,
Orederstatus int,
createdate Datetime,
remark varchar
)
declare @i int = 0 while
@i< 500000
begin
INSERT into Parametersniffproblem values (@i%10000,@i,rand () *10,getdate ()-rand () *100,newid ( )
set @i=@i+1 end
-If a customer has very many orders, the analog data distribution is uneven,
INSERT into Parametersniffproblem values (6666 , RAND () *100000,1,getdate ()-rand () *100,newid ()) Go
100000-
-Creating a normal index create
CLUSTERED index Idx_ CreateDate on Parametersniffproblem (CreateDate
)
CREATE INDEX Idx_customerid on Parametersniffproblem (CustomerId)

To format a parameterized stored procedure:

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

That's probably it.

 CREATE PROCEDURE [dbo].  Parametersnifftest (@p_CustomerId int, @p_Status int, @p_FromDate datetime, @p_ToDate datetime) as BEGIN SET NOCOUNT On DECLARE @Parm NVARCHAR (max), @sqlcommand NVARCHAR (max) = N ' SET @sqlcommand = ' SELECT * from Parametersniffproblem W Here 1=1 ' if (@p_CustomerId are not NULL) SET @sqlcommand = CONCAT (@sqlcommand, ' and customerid= @p_CustomerId ') IF (@p_S Tatus is not null) set @sqlcommand = CONCAT (@sqlcommand, ' and orederstatus= @p_Status ') IF (@p_FromDate are NOT null) set @sq Lcommand = CONCAT (@sqlcommand, ' and createdate>= @p_FromDate ') IF (@p_ToDate is not NULL) SET @sqlcommand = CONCAT (@sqlco Mmand, ' and createdate<= @p_ToDate ') SET @Parm = ' @p_CustomerId int, @p_Status int, @p_FromDate datetime, @p_ToDat  e datetime ' EXEC sp_executesql @sqlcommand, @Parm, @p_CustomerId = @p_CustomerId, @p_Status = @p_Status, @p_FromDate = @p_FromDate, @p_ToDate = @p_ToDate end Go 

Parameter Sniff problem:

This is potentially a parameter sniff problem,

For example, I query the user id=100 order information, a normal distribution of data, stored procedures for the first time compiled, this implementation plan is completely free of problems,

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

IO will not read, deliberately created examples of

If I empty the execution plan cache, rerun the query, because there is a recompile, the execution plan is not like this, for customerid=6666 this parameter, obviously take the whole table scan cost is less

Presumably this is a common problem in development to give, we parameterized SQL is to allow different parameters of the query reuse execution plan, but unfortunately, when the data distribution is uneven, reuse of the execution plan has caused the database damage, in the case of the normal parameters of the distribution of more data for the implementation plan, For example, the name can be used to 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 parameter sniff problems because of the execution plan reuse?

We know the problem is @p_customerid, then can be parameter sniff problem @p_customerid do not parameterize, directly pieced together in the SQL, if the @p_customerid changed to recompile SQL, Which is to recompile the incoming @p_customerid.

If it is @p_customerid unchanged, other parameters have changes, such as the change of Time field here, you can also enjoy the benefits of the implementation plan reuse brought by parameterization, which is to handle @p_CustomerId this parameter, directly @p_ CustomerID in the form of a string in the SQL statement, which is equivalent to an ad hoc query, not to CustomerID this query conditional field in a parameterized way

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

So that you can execute the stored procedure,

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

When brought into the @p_customerid=6666, recompile, the execution plan is a full table scan, avoiding reuse of the resulting execution plan, resulting in unreasonable execution of efficiency and the consumption of database server resources

This reduces the impact of the parameter sniff problem as much as possible, and when the @p_customerid=1 execution plan is cached, it is passed to @p_customerid=1 again, with minor changes to other conditions, such as changes in the Time field, Cache execution plans can still be reused to avoid the impact of recompilation

Conclusion:

This way to deal with parameter sniff problem, of course, is not perfect, certainly there are problems, I certainly know that once @p_customerid 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 parameter sniff problem

To know that once a parameter sniff problem arises, a large number of queries that use unreasonable execution plans can have a very serious impact on the entire server, such as a large number of IO

At the same time, there is a benefit, such as the first introduction of @p_customerid=1,

Passing in the @p_customerid=1 again, other conditions have minor changes, such as changes in the Time field, can still reuse the cache execution plan, to avoid the impact of recompilation of course, I am here just a simple example, the actual application is far more complex than this

For example, the distribution of special data has two characteristics, the first distribution is not only one, the second distribution of uneven data is dynamic, it is possible that the first quarter of a this part of the data occupy the majority, it is possible that the second quarter B data accounted for the vast majority of

So it is difficult to use plan guide to solve parameter sniff problem

This approach can also, to some extent, be able to reuse cached execution plans and reduce (but inevitably) the number of recompilation

At the same time, this approach can be compared to the ad hoc query method of piecing together an SQL string, while also leveraging the other benefits of parameterization, such as SQL injection and so on

Summarize:

There are many ways to solve the problem of parameter sniff, not to be long-winded.

The most typical is forced recompilation,

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

or query hints,

Or use a local variable,

or use plan guide and so on and so on,

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

Encounter problems, there are many solutions, the smallest price to solve the problem is kingly.