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.

Local Variable in Stored Procedure

First, create the stored procedure as follows:

CREATE OR ALTER PROC GetCustomerOrders (@CustomerID INT)
SET @CID = @CustomerID
FROM WideWorldImporters.Sales.Orders
WHERE CustomerID = @CID

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)

Exit mobile version