Hidey-Hole
When you’re looking at the execution plan for a query that someone is griping about, one thing to keep in mind is that it’s just part of the picture. There’s a lot of things that a query plan can’t tell you about the state of the server surrounding the query.
Query plans are, rightly, quite focused on the query at hand. If every query plan sucked in a bunch of other information, it’d be a nightmare.
I do think that actual execution plans should have blocking warnings, in the same way that we have spill warnings on some operators. Or at the very least, blocking waits should register in the wait stats that query plans collect.
Action Plan
We know we can’t see blocking in execution plans, which wouldn’t make a lot of sense at all to show in cached plans. If they’re only blocked sometimes, the value of that kind of feedback diminishes.
You can sometimes figure that out by comparing CPU and duration — if CPU is far less than duration, it could be a blocking problem.
Just like blocking, it’s nearly impossible to tell if a query was slow because the server was all manner of fiery catastrophe just by looking at the execution plan.
A lot of people will jump right to parameter sniffing when a query is sometimes slow, completely discounting other things that go bump in the night. While query plans can tell us a great story, they often can’t tell us a full story.
Going back to something we talked about much earlier in the series: We need to make sure we have the right information to solve the right problem. This is, again, where having a good monitoring tool is indispensable.
What It Is, It Is
We already spent a few days talking about some wait stats, and some things you might be able to do to compensate for them.
Any sporadic performance issue can be a tough nut to crack, and not every symptom is going to be obvious in the places you might first look.
They might not show up as in wait stats, because they’re not happening constantly. They might not show up in query plans because it’s not the fault of the query.
It’s an important thing to keep in mind, though, to not get too rabbit-holed looking at the wrong thing.
Thanks for reading!
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.