The OPTIMIZE FOR
query hint was introduced in SQL Server 2005 to allow you to specify a
value the query optimizer will use when creating an execution plan for
parameterized queries. SQL Server 2008 has enhanced this query hint by
adding the UNKNOWN option, which
instructs the query optimizer to use statistical information when
creating the execution plan instead of the supplied value. The OPTIMIZE FOR
query hint is useful when different parameter values create different
execution plans. If you create a stored procedure, and the execution
plan is cached based on infrequently used values, you could suffer from a
poorly cached execution plan.
Let's look at a couple of queries where the execution plan changes based on the search value. The query in Listing 1
performs a basic join on the SalesOrderDetail and the SalesOrderHeader
tables and limits the results to only orders with a quantity of one.
Example 1. Query That Will Generate an Execution Plan That Uses a Merge Join
USE AdventureWorks2008
GO
SELECT B.CustomerID
FROM Sales.SalesOrderDetail A
JOIN Sales.SalesOrderHeader B
ON A.SalesOrderID = B.SalesOrderID
WHERE A.OrderQty = 1
|
The query optimizer chooses a merge join for the query in Listing 1 because there are almost 75,000 rows with an order quantity of one. You can view the execution plan for Listing 1 in Figure 1.
Let's execute the same query with one minor change, as shown in Listing 2. If you look closely, you will see the only difference between the queries in Listing 1 and Listing 2 is that we changed the value in the WHERE clause to limit the results to only include orders with an order quantity of ten.
Example 2. Query That Will Generate an Execution Plan That Uses a Hash Join
USE AdventureWorks2008
GO
SELECT B.CustomerID
FROM Sales.SalesOrderDetail A
JOIN Sales.SalesOrderHeader B
ON A.SalesOrderID = B.SalesOrderID
WHERE A.OrderQty = 10
|
As you can see in Figure 2, making a simple change in the WHERE
clause causes the query optimizer to use a hash match to join the
records, since there are only 768 rows with an order quantity of ten.
Now let's execute the same
query using a variable with a value of ten instead of hard coding the
value. Let's also optimize the query as if the variable being used
contained the number one, as shown in Listing 3.
Example 3. Using the OPTIMIZE FOR Query Hint to Optimize the Execution Plan for a Value of One
USE AdventureWorks2008
GO
DECLARE @ID int =10
SELECT B.CustomerID
FROM Sales.SalesOrderDetail A
JOIN Sales.SalesOrderHeader B
ON A.SalesOrderID = B.SalesOrderID
WHERE A.OrderQty = @ID
OPTION (OPTIMIZE FOR (@ID = 1))
|
As you can see in Figure 3,
the execution plan now contains a merge join instead of the hash join
that the query optimizer would have normally chosen for the value of
ten.
Let's execute the query once
again using a variable of one, which should cause the query optimizer
to choose a merge join. Let's also change the query hint to optimize for
UNKNOWN to instruct the query optimizer to use statistical information to create the query plan, as shown in Listing 4.
Example 4. Using the OPTIMIZE FOR query hint to Optimize the Execution Plan for an Unknown Value
USE AdventureWorks2008
GO
DECLARE @ID2 int =1
SELECT B.CustomerID
FROM Sales.SalesOrderDetail A
JOIN Sales.SalesOrderHeader B
ON A.SalesOrderID = B.SalesOrderID
WHERE A.OrderQty = @ID2
OPTION (OPTIMIZE FOR (@ID2 UNKNOWN))
|
As you can see in Figure 4,
the statistical information causes the query optimizer to use a hash
join instead of a merge join that the optimizer would have normally
chosen for the value of one. It chooses the hash join because, based on
the statistical information, you are more likely to pass in a variable
that will perform better using the hash join.
Make sure you know the usage patterns of the application when using the OPTIMIZE FOR
query hint. You may need to modify the optimization values from time to
time. Optimal parameters today may not be the optimal parameters a year
from now.
