OPTIMIZE for UNKNOWN


In this article I will discuss about the ‘Optimize for Unknown’ query hint that was introduced in SQL Server 2008 and how we can use it.

So what is ‘OPTIMIZE for UNKNOWN’?
‘OPTIMIZE for UNKNOWN’ directs the query optimizer to use the standard rules it uses if no parameter values have been passed.The optimizer will use statistical data instead of the initial values for all variables when the query is compiled and optimized.

Now the big question is how does this help? Well ‘OPTIMIZE for UNKNOWN’ is one of the mechanisms which helps us to avoid parameter sniffing.

So what is parameter sniffing?
I will explain this with an example. I will use the Adventureworks2012 database for demonstration purpose on SQL Server 2012. However you can use any other version but not lower than SQL Server 2008.

Let us run the below query on the database and observe the output and execution plan

USE AdventureWorks2012
GO
SELECT * FROM sales.SalesOrderDetail
WHERE ProductID = 744

Row Count: 13
ofu1Now lets take a look at one of the aspects of the execution plan of the above query. Make a note that the actual number of rows is 13 and the estimated number of rows is 44.
ofu2Let us have a look at the plan xml for the run time values and compiled time values:
ofu3Now let us run another query with a different value.

USE AdventureWorks2012
GO
SELECT * FROM Sales.SalesOrderDetail
Where ProductID = 707

Let us observe the row count, execution plan and plan xml values as we did for the previous query
Row Count:
ofu4Execution plan: Make a note that the estimated number of rows is still 44.5 which was for the parameter used in the previous query i.e 744
ofu5Plan XML for Compiled and Runtime values: Make a note here that the compiled value used by the query optimizer here to create the plan is 744 which was the value that the previous query used.
ofu6This is called parameter sniffing where the optimizer sniffs the current parameter value during compilation.

Can this cause trouble?
Yes, it can. When a non-similar parameter is passed when a plan is compiled for the first time, the plan that the optimizer will find in the memory may not be the optimal one for that parameter passed. This will can result in a plan that is suboptimal and can cause a devastating effect on performance.

So how can we use ‘OPTIMIZE for UNKNOWN’ to avoid parameter sniffing. let us understand that with the below example.

We are going to run the above 2 queries here also but with the Optimize query hint in the second query:

USE AdventureWorks2012
GO
SELECT * FROM sales.SalesOrderDetail
WHERE ProductID = 744
go
SELECT * FROM sales.SalesOrderDetail
WHERE ProductID = 707
OPTION (OPTIMIZE FOR UNKNOWN)--Query Hint

Observations post execution of the two queries:
1)  It creates 2 different plans:
ofu72) Estimated and Actual row calculation for the two queries respectively:
Query 1:
ofu8Query 2:
ofu93) Compiled value and RunTime value from Plan xml:
Query 1: With parameter value 744
ofu10  Query 2: With parameter value 707 and query hint (Optimize for Unknown)
ofu11 So with the use of the query hint the optimizer does not use the value that it got from the cache but generates a new plan based on the available stats in the database.

What are the kind of stored procedures that can be a victim of Parameter Sniffing?
A)  SP’s which has optional parameters
B)  SP’s which has parameters in range operators.

Advertisements

Tagged: , , , , , , , , , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: