Parameter sniffing and performance in SQL Server 2005

Recently, I’ve been working on an database store procedure which was having too many SQL statements and that perform some data operation on multiple tables. Until the filter values were hardcoded everything was working fine but once I supplied those hardcoded values from parameter, the performance of the SP goes down drastically. Then I researched why this happening and did some analysis and found the issue. Below is some information which will help you understanding the Issue with a feature of SQL Server Optimizer called Parameter Sniffing and possible workaround for that. First let me tell you what parameter sniffing is,

“SQL Server uses the histogram of statistics objects to estimate the cardinality of query, and then use this information to try to produce an optimal execution plan. The query optimizer accomplishes this by first inspecting the values of the query parameters. This is called parameter sniffing.”

This helps to get an execution plan tailored to the current parameters of the query naturally improves the performance. These execution plans get cached in the plan cache so that they can be reused the next time the same query needs to be executed. This save optimization cost(CPU resource) and time as the query don’t get optimized again.

But some times some performance problem can be seen occasionally. Syntactically identical queries can generated different execution plan by Optimizer, depending on the parameters supplied and one of them can cause the performance issue. This is a known problem using explicit parameterization for e.g. StoredProcedures.

As we discussed there can be different execution plans generated for different values of the parameter so if you are aware about the values and want to optimize your query for that specific value you can use below method known as

“Optimizing for a typical parameter”

There is an OPTIMIZE FOR option hint available in sql server 2005 which can do this for you.

ALTER PROCEDURE demoSP ( @pid int)

AS

SELECT * FROM Sales.SalesOrderDetail

WHERE ProductID = @pid

OPTION ( OPTIMIZE FOR ( @pid = 192) )

Use this option only when you know that this is frequently used and can generate the best plan for your query.

“Optimize for every execution”

To do this use the RECOMPILE hint with your query.

ALTER PROCEDURE demoSP ( @pid int)

AS

SELECT * FROM Sales.SalesOrderDetail

WHERE ProductID = @pid

OPTION ( RECOMPILE )

But this give you best optimized plan every time your query gets executed but this will cost you the Optimization cost every time. So choose this option if you are ready to compromise with this option.

“Optimize for Local Variables”

If you have local variables used in the query then the Sql Server Optimize won’t be able to know about them on compilation time as they’ll appear only at execution time. So using Local variable is like Disabling the Parameter sniffing which is not good so far. So you optimized query plan will only use run time statistics and can show unpredictable results. There’s a hint available to enable the option to avoid this behavior.

OPTION ( OPTIMIZE FOR UNKNOWN )

This solution will ignore the parameter values and use the same execution plan for all the executions but at least you’ll get a consistent plan every time.

“Forced Parameterization”

A new feature introduced in Sql Server 2005 is to parameterize the query more aggressively. This feature is disabled by default and can be enabled on database level. But this is not recommended to do so. But you can enable it while execution your query to get the benefits from this feature.

ALTER DATABASE Adventureworks SET PARAMETERIZATION FORCED

and after completion of query don’t forget to revert it to previous state.

ALTER DATABASE Adventureworks SET PARAMETERIZATION SIMPLE

However this approach requires a lot of research and analysis on your query performance and should be applied after a dense testing of your query.