No tags yet.
Parameter Sniffing
Not always optimal...
What is it?
Let’s say you have a parameterized proc, and it creates and caches an execution plan based on certain values for said parameters. It “sniffs” the parameters and uses that for cardinality estimation.
Then you run this stored proc again, but with different values for the parameters. SQL Server is going to use the execution plan that has been cached even though a different execution plan would be more optimal due to different values (and uneven data distribution within a table), which would normally return a different cardinality estimation, input into the parameters.
Let’s say a certain value returns most rows of a table, and then another value only returns a few rows. Wouldn’t these require different execution plan? What if returning a few rows one used the cached plan that scanned an entire table?
See how the above execution plans are different due to the where clause in this scenario? If this was a parameterized stored proc, it would only use one execution plan that was cached.
Is this bad?
Not all sniffing is as bad as this... (bad) guy.
Not always. If the results are returning in an acceptable amount of time then nobody would even notice. However, if there is a discrepancy in performance, then further investigation is necessary.
Possible Solutions?
OPTION Recompile: This can be used on individual statement. If you have a stored proc with multiple statements and you feel only a few of them need to be recompiled, this might be something to try.
WITH RECOMPILE : This forces the entire procedure to recompile upon each execution. The bad news is it's not storing a plan in the cache. But the good news is, it will force the engine to create a more optimal plan. This can be written into a proc, or executed along with the proc.
OPTIMIZE FOR: You can provide your stored procedure with known parameter values that will help the engine create queries that are optimal for most of the executions.
Filtered Indexes: I have read about, but not actually applied, the use of filtered indexes to help the procedure pick a better query plan. In depth analysis of this is beyond the scope of this post.
There are many, many more potential solutions than this. And you'll never know the best option until you test it. There is no hard fast rule to resolving these issues. It takes a little patience and good planning. Collect as many metrics as possible including the use SET STATISTICS IO ON and the execution plan.
Here is an example of things to try, I cannot guarantee that you will see the same results as I did on my server. But at least it's a start and you can start tweaking it in your own lab.