Recently, I've been working on a database stored procedure which was having too many SQL statements and that performed some data operation on multiple tables. With the filter values were hardcoded everything was working fine but once I supplied those hardcoded values as parameters instead, the performance of the SP went down drastically. Then I researched why this was happening and did some analysis and found the issue. The following is some information which will help you understanding the issue with a feature of SQL Server Optimizer called Parameter Sniffing and a 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 a 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 which 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 saves optimization cost (CPU resources) and time as the query doesn't get optimized again.
But sometimes a performance problem can be seen occasionally. Syntactically identical queries can generated different execution plans 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 of the values and want to optimize your query for that specific value you can use the following 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 gives you the best optimized plan every time your query is 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 at compile time as they'll appear only at execution time. So using a Local variable is like Disabling the Parameter sniffing which is not good so far. So your 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 at the database level. But this is not recommended to do be done. But you can enable it during execution of your query to get the benefits of this feature.
AALTER DATABASE Adventureworks SET PARAMETERIZATION FORCED
and after completion of the query don't forget to revert it back to the previous state:
ALTER DATABASE Adventureworks SET PARAMETERIZATION SIMPLE
However this approach requires a lot of research and analysis of your query performance and should be applied after a dense testing of your query.