Optimize Parameter Driven Queries with the OPTIMIZE FOR Hint in SQL Server
Written By: Greg Robidoux -- 10/18/2007
-- read/post comments
-- print --
Rating:
(not rated yet)
Rate
Problem
SQL Server doesn't always select the best execution plan for your queries and thankfully there are several different hints that can be used to force SQL Server into using one execution plan over another. One issue that you may be faced with is when using parameters in your WHERE clause, sometimes the query runs great and other times it runs really slow. I recently had a situation where the hard coded values in the WHERE clause worked great, but when I changed the values to parameters and used the exact same values for the parameters the execution plan drastically changed and the overall time it took to run the query increased by about 5 times. This situation is referred to as parameter sniffing where SQL Server stores the values used as part of the execution plan and therefore other queries with different values may act totally different. So what options are there to get around this.
Solution
As mentioned above, SQL Server offers many hints that can be used to force how an execution plan is put together. The option that we are interested in is the OPTIMIZE FOR option. This will allow us to specify what parameter value we want SQL Server to use when creating the execution plan. This is a SQL Server 2005 hint.
Let's take a look at a few examples. These were all done against the AdventureWorks database.
Example 1
This first example is a straight query using a parameter without the OPTIMIZE FOR hint.
DECLARE @Country VARCHAR(20)
SET @Country = 'US'
SELECT *
FROM Sales.SalesOrderHeader h, Sales.Customer c,
Sales.SalesTerritory t
WHERE h.CustomerID = c.CustomerID
AND c.TerritoryID = t.TerritoryID
AND CountryRegionCode = @Country |
The following is the actual execution plan that is generated to execute this query.
The overall cost for this query is 1.31871.
Example 2
In this example we are specifying the OPTIMIZE FOR hint which is shown in the last line of this query. The first part is identical to the query in Example 1. In this example we are telling SQL Server to optimize this execution plan for this query using "CA" as the parameter value.
DECLARE @Country VARCHAR(20)
SET @Country = 'US'
SELECT *
FROM Sales.SalesOrderHeader h, Sales.Customer c,
Sales.SalesTerritory t
WHERE h.CustomerID = c.CustomerID
AND c.TerritoryID = t.TerritoryID
AND CountryRegionCode = @Country
OPTION (OPTIMIZE FOR (@Country = 'CA')) |
The following is the actual execution plan that is generated to execute this query. For the most part this query plan looks the same as the query plan above, except that the percentages in some tasks are a bit different.
The overall cost for this query is 1.1805 which is better then example 1.
Example 3
In this example we have changed the OPTIMIZE FOR value to be "US" instead of "CA", everything else is the same.
DECLARE @Country VARCHAR(20)
SET @Country = 'US'
SELECT *
FROM Sales.SalesOrderHeader h, Sales.Customer c,
Sales.SalesTerritory t
WHERE h.CustomerID = c.CustomerID
AND c.TerritoryID = t.TerritoryID
AND CountryRegionCode = @Country
OPTION (OPTIMIZE FOR (@Country = 'US')) |
The following is the actual execution plan that is generated to execute this query. As you can see SQL Server has changed this quite a bit from examples 1 and 2.
The overall cost for this query is 1.160652 which is not as good as the first two examples.
Client Stats
In addition to the above, we also captured the client statistics with each execution. The trial # corresponds with the example #. In the below grid we can see the Total Execution time increased with each run. For example1 the total time was 328, for example2 was 406 and for example3 it was 468. This shows that for this query SQL Server seems to be picking the best query plan.
Note: the procedure cache was not cleared between each run.
Summary
As you can see from this simple test when using parameters, using the OPTIMIZE FOR hint can change the query plan. This may have a positive impact or a negative impact, but this gives you another option to adjust how your queries execute especially if things start performing poorly when using parameters in your queries.
As I mentioned above, the dataset I was working on had tables with 20+ million rows. Here are the query plan costs that I had for each option:
- hard coded values - 4.44821
- parameters with same values - 2722.9
- using OPTIMIZE hint - 4.44649
Without the OPTIMIZE hint some executions were fast and others took a long time. So depending on your dataset and the task at hand this option may or may not help.
Next Steps
- Take the time to run some tests with the OPTIMIZE FOR hint to see if it boosts query performance
- Also, use the Client Statistics to give you some comparisons from one query run to the next
- If you want to clear the procedure cache between runs you can use the DBCC FREEPROCCACHE command
Readers Who Read This Tip Also Read
Free Live Webcast
Comment or Ask Questions About This Tip
|
