jump to navigation

Parameter Sniffing and OPTIMIZE FOR August 22, 2007

Posted by furrukhbaig in Optimize, Parameter Sniffing, Performance, Query hint, SQL Server 2005, stored procedure, TSQL.
Tags: , , , , ,
trackback

Parameter Sniffing refers to a process whereby SQL Server’s execution environment “sniffs” the parameter values during first invocation, and passes it along to the query optimizer so that they can be used to generate optimal query execution plans.

“First invocation” also refers to the first invocation after a plan was removed from cache for lack of reuse or for any other reason. The optimizer “knows” what the values of the input parameters are, and it generates an adequate plan for those inputs parameters. SQL Server internally maintains the statistics and distribution of the values in the columns used for filtering.

While parameter sniffing is certainly a powerful feature, it can cause problems when a procedure’s plan happens to have been kicked out of the procedure cache (or was never in it) just prior to the procedure being called with atypical parameter values. This can result in a plan that is skewed toward atypical use, one that is not optimal when called with typical values. Since, once cached, a query plan can be reused for parameter values that vary widely, the ideal situation is to have a plan in the cache that covers the typical usage of the procedure as much as possible. If a plan makes it into the cache that is oriented toward atypical parameter values, it can have a devastating effect on performance when executed with typical values.

An example would probably help here. Suppose we had a stored procedure that returns sales data by country. In our case, three-fourths of our sales is in the UK. The procedure takes a single parameter, @country, indicating the country for which to return sales info. It uses this parameter to filter a simple SELECT statement that returns the requested sales data.

CREATE PROCEDURE uspGetCountrySale
(@Country Varchar(50))
AS 
SELECT OrderID, CustomerID, EmployeeID, OrderDate
FROM dbo.SaleOrders
WHERE Country = @Country 
GO

 The optimizer would most likely to choose to do a clustered index scan when creating execution plan for this query because (given that “UK” would normally be passed in for @country) so much of the table would be traversed anyway that scanning it would require less I/O and be faster than repeated nonclustered index lookups. However, what happens if the plan happens to have been kicked out of the cache (let’s say due to an auto-statistics update) just prior to a user calling it with, say, “Spain”, where we have almost no sales? Assuming a suitable index exists, the optimizer may decide to use a nonclustered index seek in the new query plan. Subsequent executions of the procedure would reuse this plan, even if they passed in “UK” for @country. This could result in performance that is very slower than the scan-based plan.

As a workaround prior to SQL Server 2005, local variables can be used instead of stored procedure parameters. Please note SQL Server can not sniff the value of local variable. This will lead SQL Server to use statistics on filter column and create a plan which is best for average values in that column. This can also lead to serious performance when same procedure called with atypical value but will do best for typical values.

CREATE PROCEDURE uspGetCountrySale
(@Country Varchar(50))
AS 
DECLARE @_Country Varchar(20)
SET @_Country = @Country 
SELECT OrderID, CustomerID, EmployeeID, OrderDate
FROM dbo.SaleOrders
WHERE Country = @_Country 
GO
 
 

There’s a new query hint provided in SQL Server 2005 to tackle the problem—the OPTIMIZE FOR query hint. This hint allows you to provide SQL Server with a literal that reflects the selectivity of the variable, in case the input is typical. For example, if you know that the variable will typically end up with a highly selective value, you can provide the literal which reflects the typical value. for example.

 
CREATE PROCEDURE uspGetCountrySale (@Country Varchar(50))
AS 
DECLARE @_Country Varchar(20)
SET @_Country = @Country 
SELECT OrderID, CustomerID, EmployeeID, OrderDate
FROM dbo.SaleOrders
WHERE Country = @_Country
OPTION (OPTIMIZE FOR(@Country = ‘UK’)); 
GO

 

 

Advertisements

Comments»

1. Ryan - August 22, 2007

good one.

Like

2. Chirag Darji - September 27, 2007

Awesome article.

Like

3. Parameter Sniffing and SQL Server « Tech Treasure - September 27, 2007
4. Will - December 13, 2007

Very Good Info

Like

5. Will - December 13, 2007

Just make sure the variable is the same size as the parm.

Like

6. djs - December 19, 2007

Shouldn’t the proc be:

CREATE PROCEDURE uspGetCountrySale (@Country Varchar(50))
AS
DECLARE @_Country Varchar(50)
SET @_Country = @Country
SELECT OrderID, CustomerID, EmployeeID, OrderDate
FROM dbo.SaleOrders
WHERE Country = @_Country
OPTION (OPTIMIZE FOR(@_Country = ‘UK’));
GO

Since the query is using the @_Country variable in the Where clause the Optimize hint should also use @_Country, right?
Then again if you are using the Optimize hint then you probably don’t need the local @_Country variable and can just use the @Country parameter in the Where clause and in the Optimize hint.

Like

7. Maximus - December 20, 2007

I would like to see a continuation of the topic

Like

8. furrukhbaig - December 20, 2007

Thanks djs for pointing out copy / paste error.

you are right that variable in WHERE clause and in OPTIMIZE FOR hint should be same. In our scenario we dont need local variable here and I forgot to change the code from last example.

so sproc should look like

CREATE PROCEDURE uspGetCountrySale (@Country Varchar(50))
AS

SELECT OrderID, CustomerID, EmployeeID, OrderDate
FROM dbo.SaleOrders
WHERE Country = @Country
OPTION (OPTIMIZE FOR(@Country = ‘UK’));
GO

I hope this will help.

Thanks,
Furrukh Baig

Like

9. Parameter Sniffing & Stored Procedures Execution Plan | SQL Server Articles and Tutorials - April 3, 2009
10. Drunk Milf Pussy - August 4, 2009

now I’ll be tuned..

Like

11. Parameter Sniffing and OPTIMZE FOR « Yogesh's Blog - December 2, 2009

[…] here to read full […]

Like

12. Steve - November 26, 2010

Hi

I am having problem with varchar(max) and optimizing in SS2005. It works fine for VARCHAR(8000) but we pass in a comma separated string to our actual sps and can’t get them to compile. So here’s an example:

CREATE PROCEDURE test (@programme_code VARCHAR(MAX))
AS

BEGIN

SELECT
programme_title
FROM
programme_table
WHERE
programme_code = @programme_code
option (OPTIMIZE FOR (@programme_code = ‘PROG CODE’))

END

On compile generates error:

Msg 4132, Level 16, State 1, Procedure test, Line 7
The value specified for the variable “@programme_code” in the OPTIMIZE FOR clause could not be implicitly converted to that variable’s type.

I’ve tried CAST, CONVERT but with no luck.

Ta

Steve

Like

13. Rohit Paliwal - July 11, 2011

Guys my where clause is something like below

where
x.col1 = isnull(@var1, x.col1)

Now this where clause is doing a scan even when the input variable is not null…. Can I use this optimize clause here???
If Yes then how??
Any help will be appreciated.
Rohit

Like

14. Gulin - August 28, 2012

Awesome

Like


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: