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
Now 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.
Let us have a look at the plan xml for the run time values and compiled time values:
Now 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:
Execution 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
Plan 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.
This 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:
2) Estimated and Actual row calculation for the two queries respectively:
Query 1:
Query 2:
3) Compiled value and RunTime value from Plan xml:
Query 1: With parameter value 744
  Query 2: With parameter value 707 and query hint (Optimize for Unknown)
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.

Tagged: Microsoft SQL Server, Optimisation, OPTIMIZE FOR UNKNOWN, parameter sniffing, ParameterRuntimeValue, Plan Cache, plan xml, query optimizer, SQL server 2008, Troubleshooting Parameter Sniffing, xml