Parameter sniffing is an aspect of SQL Server that is typically viewed as a bad thing. The simple fact is that SQL Server uses parameter sniffing all the time, that’s how SQL Server works. It is only those times when parameter sniffing results in performance issues that people (naturally) complain.
It’s a lot like lawyers. It only takes one bad lawyer to make us think all lawyers are bad. Then again, I wouldn’t ever call 10,000 SQL Server instances at the bottom of an ocean “a good start”.
Over the years there have been many, many articles and blog posts regarding parameter sniffing. I’m going to point you to an article by Erland Sommarskog. It is by far one of the best possible resources anyone could reference when it comes to various performance issues and the effects of parameter sniffing.
But that’s not why I am writing this today.
I’m writing this because I have found a way, I believe, to quickly and easily identify instances of SQL Server that are having issues with parameter sniffing.
The symptoms of bad parameter sniffing go like this: everything works, until it doesn’t. When things stop working the DBA gets a phone call and is asked to “make it go”. The typical band-aid applied is an update of the statistics on the underlying tables. Updating the statistics then forces all plans that would use those tables to be recompiled and as a result the very next plan that gets compiled and cached will “win” by being the one every other similar query will use. Depending upon your data distribution this may or may not be a good enough plan for all users and all queries. The likely result of this band-aid approach is to find yourself needing to update statistics again at some point in the future.
After doing a manual update of statistics a few times you may find a DBA tempted to use what I consider to be the shotgun approach: using DBCC FREEPROCCACHE. There’s a good chance the DBA doesn’t want to keep running this manually, either, and as a result it is likely to end up inside of a SQL Agent job. I was recently working with a client and they were describing the symptoms of parameter sniffing perfectly. When we went to look at the instance the DBA even went out of their way to show me the current solution:
Yeah…it’s doing what you think…and set to run every two hours, just to be safe.
So here is what I have decided to do. I am adding the following simple script to my toolbox immediately. It will help to flag any instance that has any job step with the DBCC FREEPROCCACHE command. This way I can quickly identify the server and ask the simple question “hey, have you ever had sporadic performance issues with this instance”? Here is the script I plan to use:
SELECT sj.name, sjs.command FROM msdb.dbo.sysjobsteps sjs INNER JOIN msdb.dbo.sysjobs sj ON sjs.job_id = sj.job_id WHERE sjs.command LIKE '%DBCC FREEPROCCACHE%'
I am 98% confident that if you find a job with that command then the reason it is there is because the server was suffering from bad parameter sniffing issues. Run that script against your servers and see if anything comes back.
The results may surprise you.