SQL Server Parameter Sniffing

In this post, I will explain you Parameter Sniffing in SQL Server. I will discuss, why the query optimizer sometime fails to choose a proper query plan.

One day I experienced that one of my production stored procedure was not returning expected performance, which same procedure returning good performance before two days.

At that time, I didn’t know about Parameter Sniffing and started to look into this problem.

I found that before two days there were total 25000 records and during performance issue there were total 10,00,00 records.

I understand that record was increased suddenly, but not increase 10 times more this was increased just 3 times and even I had already tested this stored procedure for 10,00,00,00 records.

Now time to know about Parameter Sniffing:

The Many time query optimizer chooses old query plan for execution because it stores this plan into the cache for frequently running queries.

Now what happened when old query plan has table scan parameter which is require to change for index scanning after increasing records.

I found that in my situation query optimizer use old query plan instead of to create a new query plan.

The query optimizer was using an old query plan from the query cache.

This called as a Parameter Sniffing issue. 

For you guys, this is very important to know why this happening.

The Query optimizer is not always chooses the old query plan, but many times due to traffic or load,query optimizer choose a plan from the query cache.

As a DBA, you should monitor all indexes and its usages to identify this kind of problem.
SQL Server Solution for Parameter Sniffing :

Basically three options for this solution.



RECOMPILING,  means forcing SQL Server to recompile stored procedure or statement at every execution. You can set recompile hint on stored procedure or statement.

I advise to use this hint for only infrequent queries because re-compilation process takes too much CPU I/O process.

Sample code for Stored procedure and statements.



As a DBA, you can find a particular set of values which require to execute under the best execution plan so you can set OPTIMIZE for hint for this value. But values and data rapidly change in database system so this only for those values or data which are not changed frequently.

Sample statement with OPTIMIZE for Hint:



Basically, you can set UNKNOWN hint for the query optimizer to create a generic execution plan base on statistical data. This may be again generated parameter sniffing issue, but still you can use this for infrequently running queries.

This is basic about SQL Server parameter sniffing and solutions to resolve this issue.

Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.

If you like this post, then please share it with others.
Please follow dbrnd.com, I will share my experience towards the success of Database Research and Development Activity.

I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.

More from dbrnd.com

Leave a Reply

4 Comments on "SQL Server Parameter Sniffing"

Notify of
Sort by:   newest | oldest | most voted
Yogesh Shind

I think there is one more way to avoid it. You can declare variable inside and assign the variable of SP to these variables

amit bansal

Just happen to come across the post and gave a quick read. You are missing an important point that the optimizer sniffs the parameter value nd creates a plan optimized for that value, which gets reused subsequently. Good effort though…