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: optimize for, OPTION (Optimize FOR), parameter sniffing, query plan, statistics, stored procedure
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 GOThe 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
good one.
LikeLike
Awesome article.
LikeLike
[…] 2. https://furrukhbaig.wordpress.com/2007/08/22/parameter-sniffing/ […]
LikeLike
Very Good Info
LikeLike
Just make sure the variable is the same size as the parm.
LikeLike
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.
LikeLike
I would like to see a continuation of the topic
LikeLike
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
LikeLike
[…] https://furrukhbaig.wordpress.com/2007/08/22/parameter-sniffing/ […]
LikeLike
now I’ll be tuned..
LikeLike
[…] here to read full […]
LikeLike
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
LikeLike
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
LikeLike
Awesome
LikeLike