When a query is sometimes fast and sometimes slow, for the same input parameters, and you swear nothing else in the environment is changing, that’s often a case of parameter sniffing.
After SQL Server starts up and you run a stored procedure, SQL Server builds an execution plan for that proc based on the first set of parameters that are passed in.
Say we’ve got a stored procedure that runs a report based on country sales data:
- EXEC rpt_Sales @Country = ‘China’ — SQL Server builds an execution plan optimized for big countries with big amounts of sales, and it runs in about 750 milliseconds.
- EXEC rpt_Sales @Country = ‘Monaco’ — it reuses China’s cached execution plan for big data. It’s not great for small countries, but who cares – it’s a small amount of data, so it still runs in 500 milliseconds.
Now we restart the SQL Server, and someone queries for Monaco first:
- EXEC rpt_Sales @Country = ‘Monaco’ — SQL Server builds a plan optimized for tiny countries with tiny amounts of data, and it runs in just 50 milliseconds – way better than when Monaco reused China’s plan. Yay!
- EXEC rpt_Sales @Country = ‘China’ — it reuses Monaco’s cached plan for tiny data. It takes 30 seconds, and suddenly our server starts falling over because several people are running queries at a time.
SQL Server sniffs the first set of parameters that get used. Even though nothing in our environment has changed, suddenly the SQL Server runs much more slowly, and query times for China are horrific.
How to Fix Parameter Sniffing Temporarily
Parameter sniffing fixes are based on your career progression with databases, and they go like this:
1. Reboot the server! – Junior folks panic and freak out, and just restart the server. Sure enough, that erases all cached execution plans. As soon as the box comes back up, they run rpt_Sales for China because that’s the one that was having problems. Because it’s called first, it gets a great plan for big data – and the junior admin believes they’ve fixed the problem.
2. Restart the SQL Server instance – Eventually, as these folks’ careers progress, they realize they can’t go rebooting Windows all the time, so they try this instead. It has the same effect.
3. Run DBCC FREEPROCCACHE – This command erases all execution plans from cache, but doesn’t clear out many of SQL Server’s other caches and statistics. It’s a little bit more lightweight than restarting the instance, and can be done online.
4. Rebuild indexes – Doing this has an interesting side effect: when SQL Server reads an entire index to rebuild it, it gives you double the bang for the buck, and also updates your index’s statistics at the same time. This fixes the parameter sniffing issue because when SQL Server sees updated stats on an object used by an incoming query, it’ll build a new execution plan for that query.
5. Update statistics – As folks learn the above juicy tidbit, they realize they could get by with just updating stats. That’s a much lighter-weight operation than rebuilding indexes, so they switch over to just updating stats. However, I didn’t say it was lightweight.
6. Run sp_recompile for one table or proc – This system stored procedure accepts a table or a stored procedure name as a parameter, and marks all related execution plans as needing a recompile the next time they run. This doesn’t change the stats – but if you run it for China first this time, you may just get a more accurate query plan using the same stats.
7. Run DBCC FREEPROCCACHE for a single query – This one’s my favorite! Run sp_BlitzCache @ExpertMode = 1 and scroll all the way to the right hand side. You’ll see plan handles and sql handles for your most resource-intensive queries, along with a DBCC FREEPROCCACHE command for it. Find the one experiencing the parameter sniffing issue, and save its execution plan to disk first. (You’ll want this later.) Then pass its sql or plan handle into DBCC FREEPROCCACHE. Presto, just one plan gets evicted from the cache. It’s like a targeted missile strike rather than a big nuclear bomb.
How to Prepare to Fix Parameter Sniffing Permanently
Assuming you saved the execution plan like we just discussed, you’ve got a huge head start! That plan includes:
- A set of calling parameters for the stored proc
- An example of what the execution plan looks like when it’s slow
Now you need:
- At least one other set of calling parameters that produce a different execution plan (like our Monaco vs China example)
- An example of what the execution plan looks like when it’s speedy for each set of parameters
To get that info, check out How to Start Troubleshooting a Slow Stored Procedure.
Once you have all that, you’re ready to build a single version of the stored procedure that is consistently fast across all calling parameters. Start digging into Erland Sommarskog’s epic post, Slow in the Application, Fast in SSMS. Even though that title may not describe your exact problem, trust me, it’s relevant.
17 Comments. Leave new
Why not:
1. Build TWO execution plans for the same query: one for China and one for Monaco,
2. Cache them both, and
3. Look at the parameter value at runtime to decide which plan to use
Oh, wait – Oracle’s already been doing exactly that for the past 10 years!
COME ON CONOR, WHY IS IT TAKING SO LONG FOR MICROSOFT TO CATCH UP???
P.S. – In Oracle’s parlance, parameter sniffing is called “bind variable peeking”, but you get the idea
SET PARAMETERIZATION FORCED
Or trace flag 4199 might also help in some cases… (small problem in 2014 query engine..)
Pedro – can you think of any issues that might arise from turning on forced parameterization?
If server has low memory it can be a problem…. it queries every execution with different values…. But in a controlled environment it works fine…
OPTION(RECOMPILE) can also be a solution but it recompiles every time the procedure is executed….
Pedro – ah, I think you’ve got some fun journeys ahead of you. Check these out:
http://sqlblog.com/blogs/hugo_kornelis/archive/2014/01/19/parameterization-and-filtered-indexes-part-1.aspx
https://msdn.microsoft.com/en-us/library/ms175037.aspx
Basically there’s no “out of the box” solution to avoid parameter sniffing in SQL?!
I use the forced parameterization in some databases and always adhoc workloads, since our databases are from an ERP software where every query has different parameters and filtered columns (have to use dynamic SQL with sp_executesql to avoid using “@param IS NULL Or col = @param”).
In some tests we made using forced parameterization was 40% faster inserting data (with MERGE statement – if exists update else insert) and dynamic SQL with different columns used to filter data….
Will 2016 have a solution for this problem?!?!
Serge, please help me, in case you create a plan for each and every variable-value you receive at runtime, where is the advantage of caching and re-using plans gone?
For SQL Server 2016:
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = OFF;
More info:
https://www.mssqltips.com/sqlservertip/4286/sql-server-2016-parameter-sniffing-as-a-database-scoped-configuration/
Saeid, I think when doing that, you are creating another problem. https://www.brentozar.com/archive/2020/06/you-can-disable-parameter-sniffing-you-probably-shouldnt/
Please give an ORM version of this article
Randall – the same concepts apply with ORMs. Just read the post and substitute parameterized queries for stored procedures.
Thanks. We suffer from parameter sniffing and poor cardinality estimates due to highly skewed data. Our app queries are ORM-generated. In the shorter run, option(recompile) is the only thing I’ve found that will get reasonable performance from some of these queries. We turned on forced parameterization years ago, probably in a misguided attempt to reduce single-use plans. The app queries are parameterized. In QA I tried returning to simple parameterization and tested with filtered statistics and a new suggested index on one long-running query. Option(recompile) seems to be the only reliable way to get it to run quickly and use the filtered stats and new index. Unfortunately this limits my options on the database side since development will now have to figure out how to use the ORM with query hints. We’re going to be putting everything on SSDs soon to buy development some time. By the way, love all of your tools and use them regularly.
One of dozens of articles I’ve read recently:
Skewed Data, Poor Cardinality Estimates, and Plans Gone Bad (Kimberly Tripp)
https://youtu.be/li5HwaZF8tc?list=PLoGAcXKPcRvbTr23ujEN953pLP_nDyZJC
Brent,
when you wrote: “Run sp_BlitzCache @results = ‘expert’ “, did you mean “Run sp_BlitzCache @ExpertMode = 1 “? That is because PROCEDURE dbo.sp_BlitzCache (@Version = ‘4.1’, @VersionDate = ‘20161210’), does not have the @results parameter.
Thanks.
Yuri – yeah, this is an older post. The current version calls it ExpertMode as you suspected. Keep up the good work!
Brent,
I had a SPROC, which was impacted by parameter sniffing issue.
It did not have a large list of parameters, so I re-initialize all of them via local variables and then I used those vars within data queries.
The effect was the same as using OPTION (RECOMPILE) at the statement level.
Is that somewhat expected behavior of the SQL server (2014)?
Thanks.
Yuri – go ahead and read the post carefully, included the related reading links that I recommend.
Thank you Brent. Sure, I will do that.