SQL SERVER – Parameter Sniffing and OPTIMIZE FOR UNKNOWN

I consider myself fortunate that I often receive a follow-up question from my earlier blog posts. Today we will discuss Parameter Sniffing and OPTIMIZE FOR UNKNOWN. In the earlier blog post, I had discussed how one can avoid the parameter sniffing by declaring the local parameter. Here are blog posts which you should read before continuing this blog post to understand the necessary background.

• 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.

OPTIMIZE FOR UNKNOWN

We have been using a simple trick of declaring the local variable to avoid the parameter sniffing but it is not a great idea as discussed in the previous blog posts. There are many reasons, it can actually backfire on us and we can get poor performance. In any case, we will continue our discussion that declaring the local parameters can help us avoid the issue of Parameter Sniffing (as it does).

It is easy to declare one or two as a local parameter but if your stored procedure is very long and has lots of different local parameters it is impossible to declare all of the variables as a local parameter and also it is not a good idea to do that. SQL Server Team has recently introduced another feature which is called OPTIMIZE FOR UNKNOWN and we can use that to avoid declaring the local parameter. This new feature gives almost the same performance as declaring the local variable. Let us see how we can use it and also how it performs when we give different variables to the stored procedure.

Let us first create the stored procedure.

CREATE OR ALTER PROC GetCustomerOrders (@CustomerID INT)
AS
SELECT *
FROM WideWorldImporters.Sales.Orders
WHERE CustomerID = @CustomerID
OPTION (OPTIMIZE FOR UNKNOWN)
GO

Now run the above-stored procedure with different variables as following. When you run the following stored procedure, please make sure that you enable the actual execution plan for your query.

-- Sample Stored Procedure
EXEC GetCustomerOrders 1060
EXEC GetCustomerOrders 90
GO

When we compared the execution plan of both the queries it is pretty clear that it is identical to each other as well as what we have seen before when we had declared the local parameter.

When you specify optimization for an unknown value, it Instructs the query optimizer to use statistical data instead of the initial values for all local variables when the query is compiled and optimized, including parameters created with forced parameterization.

I strongly suggest that if you are going to use a local parameter, use this new feature which was introduced over 10 years ago in SQL Server 2008.

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