[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*]
Best Answer
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.
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:
What this doesn't guarantee:
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:
What this doesn't guarantee:
I still need help!
We're going to need the following things:
Getting the query plans and query
If the query is running, you can use sp_BlitzWho* or sp_WhoIsActive to capture currently executing queries.
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*.
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:
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.
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:
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:
Also look for different operators that make your code sensitive to parameter sniffing:
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? - Erland Sommarskog
Troubleshooting Parameter Sniffing 1, 2, 3 - Tara Kizer
Why You’re Tuning Stored Procedures Wrong (the Problem with Local Variables) - Kendra Little
How to Use Parameters Like a Pro and Boost Performance - Guy Glantser
Parameter Sniffing, Embedding, and the RECOMPILE Options - 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.