SQL SERVER – Parameter Sniffing and Local Variable in SP

After reading yesterday’s blog post about SQL SERVER – Parameter Sniffing Simplest Example, I received a follow-up question from my client with whom I have worked earlier in a recent consulting engagement of Comprehensive Database Performance Health Check. The question I had received was about Parameter Sniffing and Local Variable in Stored Procedure.

Before you continue reading this blog post, I suggest you read my earlier blog post where I have demonstrated an example of parameter sniffing. In the example, when the query is executed the very first time, it compiles the plan with that parameter and all the time keep on using that parameter. This may not be good if the first query which was executed had a parameter that was not frequently used.

One of the alternatives is to use local parameters to the stored procedure which will not cache for the future stored procedure execution. Let us see the example.

• SQL SERVER – Parameter Sniffing Simplest Example
  In this blog post, we discuss what actually is parameter sniffing and how we can overcome it with the help of recompiling the stored procedure.
• SQL SERVER – Parameter Sniffing and Local Variable in SP
  It is easy to overcome the parameter sniffing for the stored procedure by declaring the local variable inside the stored procedure.
• SQL SERVER – Parameter Sniffing and OPTIMIZE FOR UNKNOWN
  You can take advantage of the new query hint of Optimize For Unknown to simulate the local variable in the stored procedure. A very underutilized technique indeed.
• SQL SERVER – DATABASE SCOPED CONFIGURATION – PARAMETER SNIFFING
  This new database level enhancement was introduced recently which can help you overcome any issue with the parameter sniffing.
• SQL SERVER – Parameter Sniffing and OPTION (RECOMPILE)
  The oldest and most traditional technique to not cache the query plans and compile your stored procedure or queries every single time to get optimal performance.
• Performance and Recompiling Query – Summary
  This post summarizes the entire series of parameter sniffing, performance and recompiling query.

Local Variable in Stored Procedure

First, create the stored procedure as follows:

CREATE OR ALTER PROC GetCustomerOrders (@CustomerID INT)
AS
DECLARE @CID INT
SET @CID = @CustomerID
SELECT *
FROM WideWorldImporters.Sales.Orders
WHERE CustomerID = @CID
GO

Please note that this stored procedure has a local parameter which is actually assigned by the parameter which is passed from outside. Now let us run the stored procedure with one parameter.

EXEC GetCustomerOrders 1060

Let us observe the execution plan.

Next, let us run it one more time with different

EXEC GetCustomerOrders 90

Once again let us observe the execution plan.

When we see the execution plan for both the queries and also inspect the property it is very clear that this time, the query is not caching any particular value for the execution plan. The matter of fact, the query is taking a generic average value from the statistics of the index and using it for the estimation and that is why the approximation count is 111.

Now here are two very important question –

Have you removed the issue of Parameter Sniffing?

Yes, we have no more issue with the parameter sniffing as the query is no longer remembering the parameter passed to the stored procedure and using it to build an execution plan.

Have we improved the performance of the query by removing the issue of Parameter Sniffing?

Actually, no. Now the query is no longer taking the first passed parameter to build an execution plan. However, instead of that, it is using average statistics details to build the execution plan. While in general, it is better but again in many scenarios, it can be the worst possible plan as well.

In Summary, it is a good idea to use local parameters in the stored procedure to overcome parameter sniffing issue, however, there is no guarantee of an optimal execution plan.

If you recompile your stored procedure or recompile the statement in your stored procedure for sure you will get the optimal plan but the cost of the recompilation can be very high in many scenarios.

Reference: Pinal Dave (https://blog.sqlauthority.com)