Why is my query suddenly slower than it was yesterday? - Databases

TopAnswers Databases
Top Answer
meme
Dear [your name here]!

Oh no, I'm sorry to hear that! Let's start with some basics to get you fixed up in a jiffy.

## The thing you're running into is called Parameter Sniffing

It's a way out wiggy weird problem. The name rolls right off the tongue. Like the German word for squirrel.

And it's usually your friend. 

When a query hits your server, a plan has to be compiled. To save time and resources later, an execution plan is cached based on the estimated rows that parameter will cause your code to process and return. 

The easiest way to picture this going bad is to imagine a stored procedure that needs to count things from two lopsided populations.

For example:

 - People wearing CrossFit shirts who aren't injured: Zero

 - People wearing CrossFit shirts who wince when they wince: All

Obviously, one execution of that code would have to do a lot more work than another, and the query plans you'd want to do totally different amounts of work would look totally different.

## What am I up against?

This is a genuinely difficult problem to find, test, and fix. 

 - It's hard to find because it doesn't happen consistently
 - It's hard to test because you need to know which parameters cause different plans
 - It's hard to fix because sometimes it requires query and index tuning
 - It's hard to fix because you may not be able to change queries or indexes
 - It's hard to fix because even if you change queries or indexes, it might still come back

## Quick Fixes
Sometimes, all you need is a little clarity. Or rather, your plan cache does.

### If it's a stored procedure
Try running `EXEC sys.sp_recompile @objname = N'schema.procname'`. That'll cause the procedure to recompile a new plan the next time it runs.

What this won't fix:

 - Processes currently running it.

What this doesn't guarantee:

 - The next process that runs after recompiling will use a parameter that gives you a good plan.

You can also point `sp_recompile` at a table or view, but be forewarned that all code that touches that table or view will recompile. This could make the problem a whole lot harder.

### If it's a parameterized query

Your job is a little more difficult. You'll need to track down the SQL Handle. You don't want to free the entire plan cache -- just like using `sp_recompile` against a table or view, you could trigger (ha ha ha) a whole bunch of unintended consequences.

The easiest way to figure that command out is to run [sp_BlitzWho][1]*! There's a column called "fix parameter sniffing" that has a command to remove a single plan from the cache. This has the same drawbacks as recompile, though. 

What this won't fix:

 - Processes currently running it.

What this doesn't guarantee:

 - The next process that runs after recompiling will use a parameter that gives you a good plan.


## I still need help!

We're going to need the following things:

 - The good query plan, if possible
 - The bad query plan 
 - The parameters used
 - The query in question
 - Table and index definitions

### Getting the query plans and query

If the query is running, you can use [sp_BlitzWho][1]* or [sp_WhoIsActive][2] to capture currently executing queries.

    EXEC sp_BlitzWho;
    
    EXEC sp_WhoIsActive @get_plans = 1;

[![NUTS][3]][3]

If the query isn't currently executing, you can check for it in the plan cache, using [sp_BlitzCache][1]*. 

If you're on SQL Server 2016+, and have Query Store turned on, you can use [sp_BlitzQueryStore][1]*.

    EXEC dbo.sp_BlitzCache @StoredProcName = 'Your Mom';
    
    EXEC dbo.sp_BlitzQueryStore @StoredProcName = 'Your Mom';

These will help you track down cached version(s) of your Stored Procedure. If it's just parameterized code, your search is a little more difficult. This may help, though:

    EXEC dbo.sp_BlitzCache @QueryFilter = 'statement';

You should see fairly similar output from any of those. Again, the query plan inviting cool blue clicky column is your friend.

[![NUTS][4]][4]

The easiest way to share plans is using [Paste The Plan][5]*, or dumping the XML into pastebin. To get that, click on either one of those inviting blue clicky columns. Your query plan should appear in a new SSMS tab.

[![NUTS][6]][6]

If you're touchy about sharing your company's code and query, you can use [Sentry One's free Plan Explorer tool][7] to anonymize your plan. Keep in mind, this makes getting help harder -- anonymized code is a lot harder to read and figure out.

All of these tools we talked about should return the Query Text. You don't need to do anything else here.

Getting the parameter(s) is a little more difficult. If you're using [Plan Explorer][7], there's a tab at the bottom that lists them all out for you.

[![NUTS][8]][8]

If you're using [sp_BlitzCache][1]*, there's a clickable column which gives you the execution statement for stored procedures.

[![NUTS][9]][9]


## Getting the table and index definitions

You can easily right click in SSMS to script things out.

[![NUTS][10]][10]

If you want to get everything in one shot, [sp_BlitzIndex][1]* can help if you point it directly at a table.

    EXEC dbo.sp_BlitzIndex @DatabaseName = 'StackOverflow2010',
                           @SchemaName = 'dbo',
                           @TableName = 'Users';

This will give you the table definition (though not as a create statement), and create statements for all your indexes. 

Collecting and adding this information to your question should get people enough information to help, or point you in the right direction.


## I wanna do it myself!

Well, cool. I'm happy for you. You crazy person.

There are a lot of ways people think they "fix" parameter sniffing:

 - [Recompile hints][11]
 - [Optimize for unknown][12]
 - Optimize for a value

But these really just disable parameter sniffing in different ways. That's not to say they can't solve the problem, they just don't really get to the root cause.

That's because getting to the root cause is usually kind of difficult. You have to look for those pesky "plan quality issues".

Starting with the fast vs slow plans, look for differences like:

 - Indexes used
 - Join order
 - Serial vs Parallel

Also look for different operators that make your code sensitive to parameter sniffing:

 - Lookups
 - Sorts
 - Join type
 - Memory grants (and by extension, spills)
 - Spools

Don't get too wrapped up in seek vs scan, index fragmentation, or any of the cargo cult-y stuff people hem and haw about. 

Usually, there's a pretty basic indexing problem. Sometimes the code needs a little rewriting. 

If you want to learn more about parameter sniffing:

 - [Slow in the Application, Fast in SSMS?][13] - Erland Sommarskog

 - Troubleshooting Parameter Sniffing [1][14], [2][15], [3][16] - Tara Kizer

 - [Why You’re Tuning Stored Procedures Wrong (the Problem with Local Variables)][17] - Kendra Little

 - [How to Use Parameters Like a Pro and Boost Performance][18] - Guy Glantser

 - [Parameter Sniffing, Embedding, and the RECOMPILE Options][19] - Paul White

**If you're reading this, and you think I missed a link or helpful tool, leave a comment. I'll do my best to keep this up to date.**

---

  [1]: http://firstresponderkit.org
  [2]: http://whoisactive.com
  [3]: https://i.stack.imgur.com/p1PZL.png
  [4]: https://i.stack.imgur.com/6PXH4.png
  [5]: https://www.brentozar.com/pastetheplan/
  [6]: https://i.stack.imgur.com/nNUOP.png
  [7]: https://www.sentryone.com/plan-explorer
  [8]: https://i.stack.imgur.com/B5Et9.png
  [9]: https://i.stack.imgur.com/i8Xgw.png
  [10]: https://i.stack.imgur.com/BoXN8.png
  [11]: https://www.brentozar.com/archive/2013/12/recompile-hints-and-execution-plan-caching/
  [12]: https://www.brentozar.com/archive/2013/06/optimize-for-unknown-sql-server-parameter-sniffing/
  [13]: http://www.sommarskog.se/query-plan-mysteries.html
  [14]: https://www.brentozar.com/archive/2018/03/troubleshooting-parameter-sniffing-issues-the-right-way-part-1/
  [15]: https://www.brentozar.com/archive/2018/03/troubleshooting-parameter-sniffing-issues-right-way-part-2/
  [16]: https://www.brentozar.com/archive/2018/03/troubleshooting-parameter-sniffing-issues-the-right-way-part-3/
  [17]: https://www.brentozar.com/archive/2014/06/tuning-stored-procedures-local-variables-problems/
  [18]: https://groupby.org/conference-session-abstracts/how-to-use-parameters-like-a-pro-and-boost-performance/
  [19]: https://sqlperformance.com/2013/08/t-sql-queries/parameter-sniffing-embedding-and-the-recompile-options
1 Answer
