Starting SQL: Things Your Execution Plan Will Warn You About

Oh look, XML

One of my favorite pieces of SQL Server documentation is the XML schema for query plans.

Whenever I’ve needed to figure something out to query the plan cache, or when I’ve been looking for stuff that might be interesting to pull out of it, this is where I’ve gone.

Especially when plan XML, it helps me rest a bit easier to know I’ve covered all the documented possibilities. Of course, there’s no distinction in the documentation between what can be in the Estimated plan, and what’s reserved for Actual plans.

Such Document

While that’s inconvenient, you can figure most things out by separating plan elements into two groups:

  • Optimization time metrics
  • Runtime metrics

Here are the documented warnings available in query plans.

what was the robot’s name?

Things like spills and memory usage can only be known at runtime, when the optimizer’s estimates are put into play.

For many other things, the optimizer likely knows about and has to account for the warnings while it’s coming up with a query plan.

Warnings About Warnings

One thing to keep in mind about many of these warnings, whether they’re optimization-time or run-time, is that they’re trigger for things that might not matter, or even necessarily be true.

I’ve written in the past about silly execution plan warnings. I’m not going to re-write all those here.

But I do want you to reinforce a couple points I’ve made over the course of the series:

  • Estimated and cached plans miss details that make troubleshooting easier
  • Not every metric and warning is a critical datapoint

For example, I’ve seen people focus on small spills in execution plans many times only to have them not be the cause of a performance problem.

Operator times make distinguishing this easier, of course. Prior to that, even actual plans could be misleading and unforgiving.

Thanks for reading!

A Word From Our Sponsors

Following on the GREAT SUCCESS of previous online events, I’m kicking things back off with another offering of my Premium Performance Tuning class, before I retire and remix the material.

To buy tickets, head over here. It’s going to be taking place Friday, October 23rd from 9am-5pm EST.

The normal ticket price is $300, but all September I’m running a 50% off sale with the coupon code “stillonline”.

Every purchase comes with access to my recorded video training for life, too.

Leave a Reply

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