Have you ever come across a stored procedure that utilises parameters and performs like a dream the majority of the time but for some reason crawls along on some other occasions? Well the chances are that this is down to SQL Server Parameter Sniffing.
I was re-reading through the white paper Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005 again this week. It reminded me of the fact that Parameter Sniffing catches a lot of people out and so I wanted to share with you the details of how this issue can arise so that you may avoid it in your environments.
What is Parameter Sniffing?
“Parameter sniffing” refers to a process whereby SQL Server’s execution environment “sniffs” the current parameter values during compilation or recompilation, and passes it along to the query optimizer so that they can be used to generate potentially faster query execution plans. The word “current” refers to the parameter values present in the statement call that caused a compilation or a recompilation.
How does Parameter Sniffing work?
You can find an excellent article here by Omnibuzz providing an example walk-through of the issues of Parameter Sniffing.
Where can I find out more?
For an excellent discussion on Parameter Sniffing and other similar query tuning issues take a look at the Microsoft reference Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005.
I hope you find this information useful in both troubleshooting and more importantly, avoiding issues with SQL Server Parameter Sniffing.
