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 and OPTIMIZE FOR UNKNOWN optimizeforunknown0-800x119

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.

SQL SERVER - Parameter Sniffing and OPTIMIZE FOR UNKNOWN optimizeforunknown

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)

OPTIMIZE FOR UNKNOWN, Parameter Sniffing, SQL Performance, SQL Scripts, SQL Server, SQL Stored Procedure
Previous Post
SQL SERVER – Parameter Sniffing and Local Variable in SP
Next Post
Learn MariaDB – New Technology Week

Related Posts

5 Comments. Leave new

  • Can we specify optimization for an unknown at server level or db level?

    Reply
  • Hi Pinal,

    Can the memory grant feedback introduced as part of Intelligent Query Processing in 2017 for batch mode and in 2019 for rowstore mode eliminate the parameter sniffing problem to an extent. Would appreciate your insights on this subject.

    Regards,

    Sravan Pappu.

    Reply
  • As you mention above, using “OPTIMIZE FOR UNKNOWN” gives a result identical to when we had declared the local parameter, which in turn, as you said in the earlier post, is no guarantee of an optimal execution plan, and can also backfire in some cases. So effectively, what we have only achieved is avoided the use of local parameter(s), but I still do not understand how we have been successful in creating an optimal execution plan?

    Reply
  • Both.
    From SQL 2016 if you right click database on SSMS and go to options you can turn off Parameter sniffing on the database.
    For Server wide, enable trace flag 4136.
    Microsoft recommend full testing before you do this though.

    Reply
  • Thanks Pinal Dave, this helped me out so my Stored Procedure now runs in 1s rather than 15minutes!

    Reply

Leave a Reply