SQL Server Parameter Sniffing and its improvement method, parametersniffing

SQL Server Parameter Sniffing and its improvement method, parametersniffing

When processing stored procedures, SQL Server implements one compilation and reuse multiple times to save Compilation Time. The parameter sniffing problem occurs when the execution plan generated by the input value is not applicable to subsequent parameters. Create procedure Sniff1 (@ I int) as SELECT count (B. SalesOrderID), sum (p. weight) from [Sale
When processing stored procedures, SQL Server implements one compilation and reuse multiple times to save Compilation Time. The parameter sniffing problem occurs when the execution plan generated by the input value is not applicable to subsequent parameters.

create procedure Sniff1(@i int) as SELECT count(b.SalesOrderID),sum(p.weight) from [Sales].[SalesOrderHeader] ainner join [Sales].[SalesOrderDetail] bon a.SalesOrderID = b.SalesOrderIDinner join Production.Product pon b.ProductID = p.ProductIDwhere a.SalesOrderID =@i;goDBCC FREEPROCCACHEexec Sniff1 50000;exec Sniff1 75124;go

Parameter Sniffing occurs occasionally, but only when the data distribution is uneven or the value of the input Parameter is uneven. Now, let's discuss how to solve these problems.

1. Run dynamic SQL in Exec () Mode

create procedure Nosniff1(@i int) as declare @cmd varchar(1000);set @cmd = 'SELECT count(b.SalesOrderID),sum(p.weight) from [Sales].[SalesOrderHeader] ainner join [Sales].[SalesOrderDetail] bon a.SalesOrderID = b.SalesOrderIDinner join Production.Product pon b.ProductID = p.ProductIDwhere a.SalesOrderID ='; exec(@cmd+@i); go

Exec nosniff2 50000;

Exec nosniff2 75124;

From the trace above, we can see that SP: CacheInsert events exist before the query statement is executed. SQL Server performs dynamic compilation and correctly estimates the result set based on the variable values, different execution plans are provided.

2. Use local variables

create procedure Nosniff2(@i int) as declare @iin int;set @iin=@iSELECT count(b.SalesOrderID),sum(p.weight) from [Sales].[SalesOrderHeader] ainner join [Sales].[SalesOrderDetail] bon a.SalesOrderID = b.SalesOrderIDinner join Production.Product pon b.ProductID = p.ProductIDwhere a.SalesOrderID =@iin;go

Exec Nosniff2 50000;

Exec Nosniff2 75124;

As described in the previous article, when using local variables, the parameter values are obtained during the execution of stored procedure statements, and SQL Server does not know the value of the variable at runtime, it will compile according to a preestimate, A compromise execution plan is provided.

3. Use Query Hint to specify the execution plan

Add OPTION ([,... n]) to SELECT, DELETE, UPDATE, and MERGE statements to guide the execution plan. When the database administrator knows the problem, he can use hint to guide SQL Server to generate an execution plan that is not very bad for all variables.

The above section describes the SQL Server Parameter Sniffing and its improvement methods. I hope it will help you. If you have any questions, please leave a message and I will reply to you in a timely manner. Thank you very much for your support for the help House website!