Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Optimize Parameter Driven Queries with SQL Server OPTIMIZE FOR Hint


By:   |   Read Comments (5)   |   Related Tips: More > Performance Tuning

Attend a SQL Server Conference for FREE >> click to learn 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 hint was introduced in SQL Server 2005.

Let's take a look at a few examples. These were all done using the AdventureWorks database.

Example 1 - query with no hints

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 - query using OPTIMIZE FOR hint

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 even though we are passing in US 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 - query using OPTIMIZE FOR

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.60652 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 328ms, for example2 was 406ms and for example3 it was 468ms. This shows that for this query SQL Server seems to be picking the best query plan and the OPTIMIZE FOR did not make it run any faster.

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.  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


Last Update:


signup button

next tip button



About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Tuesday, September 15, 2015 - 9:58:10 AM - Greg Robidoux Back To Top

Hi Raj, you will need to look at the query plans for each option with and without the OPTIMIZE FOR hint to see how SQL Server is parsing and executing the query.  This will help you see where there are differences and why one option runs faster.


Tuesday, September 15, 2015 - 9:12:21 AM - Raj Mathur Back To Top

Hi Greg,

 

I have been following all Posts on MSSQLTIPS.com sine quite a long time.

This Post was very Helpful and explanatory for me, and it did prove that usage of OPTIMIZE FOR helps in reducing I/O.

But i felt that inspite of Less I/O, it was taking a longer time than former Query[Without Optimize] for the Query to execute.

Could you please also advise us some hint to reduce Query Execution Time.

 

Regards,

Raj


Thursday, February 17, 2011 - 2:46:02 PM - Greg Robidoux Back To Top

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.


Sunday, February 13, 2011 - 1:50:40 AM - Elicx Back To Top

are you use clear buffer for get the real time of the query?


Friday, September 04, 2009 - 4:33:02 AM - jcelko Back To Top

 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.  


Learn more about SQL Server tools