Why is my query suddenly slower than it was yesterday?

[Salutations]

(check one)

[ ] Well trained professional, [ ] Casual reader, [ ] Hapless wanderer,

I have a (check all that apply)

[ ] query [ ] stored procedure [ ] database thing maybe

that was running fine (if applicable)

[ ] yesterday [ ] in recent memory [ ] at some point

but is suddenly slower now.

I've already checked to make sure it's not being blocked, and that it's not the victim of some long running maintenance task, report, or other out of band process.

What is the problem, what should I do, and what information can I provide to get some help?

[*Insert appropriate closing remarks*]

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*! 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* or sp_WhoIsActive to capture currently executing queries.

EXEC sp_BlitzWho;

EXEC sp_WhoIsActive @get_plans = 1;

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

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

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.

The easiest way to share plans is using Paste The Plan*, 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.

If you're touchy about sharing your company's code and query, you can use Sentry One's free Plan Explorer tool 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, there's a tab at the bottom that lists them all out for you.

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

Getting the table and index definitions

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

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

```
EXEC dbo.sp_BlitzIndex @DatabaseName = 'StackOverflow2010',

StackExchange Database Administrators Q#204565, answer score: 102