Optimize Parameter Driven Queries with SQL Server OPTIMIZE FOR Hint

By: Greg Robidoux | Read Comments (3) | Print Greg is the President of Edgewood Solutions and a co-founder of MSSQLTips.com. Related Tips: More

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

 

Related Tips: More | Become a paid author

Last Update: 10/18/2007

Share:

Share

Tweet

Connect with MSSQLTips.com Follow us on twitter... Let's connect on facebook! Join us on Google+

Comments and Feedback:

Friday, September 04, 2009 - 4:33:02 AM - jcelko	Read The Tip
A decade ago WATCOM SQL (now Sybase SQL Anywhere)  had a hint whereyou wrote (<search condition> <estimate 0-100>) where you guessed the percentage of the time that the search condition was true.  The optimizer then used that guess instead of its stats to build a query.  This worked much better than a forced plan.

Sunday, February 13, 2011 - 1:50:40 AM - Elicx	Read The Tip
are you use clear buffer for get the real time of the query?

Thursday, February 17, 2011 - 2:46:02 PM - Greg Robidoux	Read The Tip
No I did not use DBCC FREEPROCCACHE to clear the cache.  The issue is related to having different stats for different values and therefore SQL Server would use differnt query plans.  By using the OPTIMIZE hint this allow SQL to use the same plan regardless if the parameter value changed.

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code

 

Sponsor Information

Find and fix SQL Server problems before they happen - SQL diagnostic manager now with predictive analysis! SQL Monitor – For database professionals who need results on Day One. Try it online. Need SQL Server help and not sure where to turn? Reach out to expert consultants for a Health Check. Find and Fix SQL issues with Foglight Performance Analysis. Get a free copy. Solving SQL Server problems for millions of DBAs and Devs since 2006. Join now. Valuable SQL Server web casts on Performance Tuning, Development, Administration, SSIS and more...