Starting SQL: Things Your Execution Plan Should Warn You About

Mild Best Wishes


Microsoft has chosen some odd things to warn us about in query plans. For estimated and cached plans, I totally understand some of the limitations.

Though the DMVs for queries associated with query plans (both in the plan cache and query store) log some additional metrics about memory grants, spills, CPU, duration, reads, and more, none of that additional information ends up as feedback in the plan XML.

I’m not complaining, either. Modifying XML is stupid.

Bellyachin’


What could be in the XML is an extension of the current set of warnings, with maybe a bit more in Actual plans.

Query plans could warn if:

  • A non-SARGable predicate caused a scan
  • A non-SARGable/complex predicate caused a Filter
  • A residual predicate did a lot of reads
  • Eager Index Spools are over a certain number of rows
  • Performance Spools have an unfavorable rebind/rewind ratio
  • If modes switch between row and batch
  • Why an index wasn’t used
  • When estimated and actual rows or executions are way off
  • When joins have an OR clause
  • When parallel row distributions are uneven
  • Louder warnings for when something forces a query to run serially
  • Show multiple missing index requests when present
  • When operators execute more than once
  • When a statistic used has significant modifications
  • A roll up of per-operator I/O at the root node

Many of these things could be in both estimated and actual plans, and would really help people understand both why the things they do when writing queries can mess them up, and also when there might be a better way to do things.

A Professional With Standards


Is that a lot of stuff? Yes. But think about the warnings we get now, and the last time they were really helpful to you.

If they ever were, I can guarantee that they’ve been misleading more often.

All in all, people need a more informative query plan, and building this sort of instrumentation for end users also gives the robots in Azure, and future Intelligent Query Processing features some nice feedback.

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.



3 thoughts on “Starting SQL: Things Your Execution Plan Should Warn You About

Leave a Reply

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