If you notice that one of your usual query sometimes runs fine and the same query sometimes runs slow. It may be due to a common phenomenon called parameter sniffing.
A query becomes a victim of Parameter sniffing when SQL server caches a plan for a particular parameter. The same query when run again with a different parameter runs using the cached plan instead of getting a brand new plan. In general, this is by design and is a good thing since every recompile burns more CPU cycles.
Folks usually try the following to deal with the above scenario
- Restart windows
- Restart the SQL Server Service
- Fail over the cluster
- DBCC FREEPROCCACHE
- Rebuild indexes, which really is just same as clearing out the cache
- Update your statistics, which really is just same as above
- DBCC FREEPROCCACHE’s for specific plans
The right way to deal with parameter sniffing emergency is:
- Find the ONE bad plan in cache
- Save the ONE bad plan to disk for troubleshooting later
- Free the ONE bad plan from memory
You could run the open source stored proc sp_BlitzCache to find the bad plan. This store proc lists the top 10 worst queries in the plan cache. You review each of them and pin point the query plan where the estimates versus actuals are off by more than 10x.
Can we use monitoring software to catch parameter sniffing?
Typically when we hear from developers or end users that a query is running long. We hop onto the terminal of monitoring tool and look for PLE (page life expectancy). However, the long running queries may not the root cause for flushing out the cache. It is sometimes the quick running queries with large memory grants that drop the PLE down abruptly.
When are we susceptible to parameter sniffing?
Anytime you have one piece of code that needs to handle a varying number of rows, we are probably gonna have to worry about parameter sniffing.
Database Blog 