How to overcome parameter sniffing in SQL Server?

How to overcome parameter sniffing in SQL Server?

It is easy to overcome the parameter sniffing for the stored procedure by declaring the local variable inside the stored procedure. You can take advantage of the new query hint of Optimize For Unknown to simulate the local variable in the stored procedure.

What happens when query is using all indexes in SQL Server?

You have a SQL Server stored procedure that has been performing fairly well, but suddenly its execution times skyrocket. After looking at the cached execution plan you see that the query is using all indexes as should be.

Is it bad to recompile a stored procedure in SQL?

Furthermore, recompilation will increase CPU load and in heavy concurrent systems it could be as problematic as the issue we are trying to solve. As an example, below is the sample code to create our previous stored procedure with the recompile option.

What are the parameters of a stored procedure?

A stored procedure has many parameters and/or complex business logic enumerated within it. A stored procedure uses extensive branching logic. Playing around with the TSQL appears to fix it temporarily. Of the many areas of SQL Server where performance problems rear their head, few are handled as poorly as parameter sniffing.

Where is the parameter stored in SQL Server?

When the procedure is compiled or recompiled, the value passed into the parameter is evaluated and used to create an execution plan. That value is then stored with the execution plan in the plan cache. On subsequent executions, that same value – and same plan – is used. This is a normal, expected behavior in SQL Server.

How to execute stored procedure in SQL Server?

Execute the stored procedure. On the script we first execute the DBCC FREEPROCCACHE to clean the plan cache and then run the stored procedure. Look at the image below and you will see how the second call to the stored procedure uses the same plan without taking into consideration the parameters given.