How to Start Troubleshooting Parameter Sniffing Issues

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:

  1. 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.
  2. 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:

  1. 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!
  2. 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.

Previous Post
Query Tuning Week: How to Start Troubleshooting a Slow Stored Procedure
Next Post
Question From Office Hours: SQL Handle vs. Plan Handle

16 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

    Reply
    • Pedro Oliveira
      August 19, 2016 8:35 am

      SET PARAMETERIZATION FORCED

      Or trace flag 4199 might also help in some cases… (small problem in 2014 query engine..)

      Reply
    • Markus Pöhler
      November 27, 2017 9:27 am

      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?

      Reply
  • 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/

    Reply
  • Please give an ORM version of this article

    Reply
  • 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

    Reply
  • Yuri Leventman
    December 22, 2016 2:49 pm

    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.

    Reply
  • Yuri Leventman
    December 22, 2016 3:02 pm

    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.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}