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.
You know what you’d call 10,000 instances of SQL Server at the bottom of the ocean?
Still cheaper than 1 instance of Oracle!
/ba-dum-ching /here all week /try the veal
Nice!
Tried this on a couple of our older servers. Looks like no one has put it in before my time here. I guess their ignorance was bliss in this case.
Ha!
I actually just fixed an issue by “enabling” parameter sniffing. Ad hoc SQL in a job declared a variable then populated it with getdate()-2. After taking the variable out the estimated number of rows returned from the table dropped from over 3 milling to 18 thousand. Compared to the 10 thousand rows actually returned it was much better.
Chris,
Nice example there of how by giving SQL Server a little nudge in the right direction everything can work well.
I try to avoid clearing the plan cache whenever possible. I have a couple scripts that identify the most expensive queries by polling the sys.dm_exec_query_stats DMV. Clearing the plan cache not only gets rid of the data that would alert me to potential performance issues in the database, but also puts the instance back in warm-up mode as all the stored procedures need to be compiled again.
I run Ola Hallengren’s index defrag and maintenance scripts on our servers, so the statistics aren’t my primary suspect when I see symptoms of parameter sniffing. My first instinct is to pull up the execution plan and compare the compile time and run time values for input parameters, thanks to Grant Fritchey’s session on parameter sniffing at SqlConnections last year.
In a couple instances I’ve added OPTION (RECOMPILE) to a stored procedure, or OPTION (OPTIMIZE FOR @Parameter = N’Some Value’) and had pretty good luck with both of those. Does anybody else have some tricks that work?
Andre Ranieri