Building SQL ConstantCare®: Analyzing Query Plans

XML Delenda Est

Last week, I talked about how we’re going to be collecting query plans from users who opt to have us do so (and only users who opt in! No surprises, here) by leveraging code we already have written in sp_BlitzCache, and how we’re going to do the XML analysis on our end using a Real Programming Language®.

That means we’ll be taking the ~60 some odd checks that we run on query plans, and converting them from T-SQL over to another language. For example, this is the check we use to see if your query plan is being forced to run serially.

Why So Serial?

This is a great thing to know, which was introduced in SQL Server 2012. That’s right — earlier versions don’t have this. I’m glad that 2008 and 2008 R2 are going out of support next Summer. No more guessing.

Goin’ Nowhere

There are some things I would love to do with the plan cache that aren’t possible. I started doing them in sp_BlitzQueryStore, but only in ways that I felt wouldn’t make running it counterproductive. For instance, there’s a parameter sniffing column that generates some warnings based on significant differences in plan metrics from execution to execution.


If I really wanted to abuse your expensive CPUs, I could take different plans generated by the same query and compare them, looking at the operator choices, and highlighting ones that make your code more sensitive to parameter sniffing.

To break it down:

  • The audience isn’t there for Query Store, and
  • Doing that kind of XML parsing (especially on large plans) would be painful in T-SQL
  • The plan cache doesn’t have historical data like that to analyze

What Changes In SQL ConstantCare®

We’ll have really good historical data about collected plans, and a process completely detached from the database to analyze the XML.

That means better long term analysis to help you:

  • Solve tough parameter sniffing issues
  • Monitor plans for improvement and regression
  • Chart how tuning has changed wait stats

Long term, we’ll be able to build in cool stuff:

  • Improve on existing missing index recommendations
  • Make our own index recommendations
  • Offer potential rewrites when we spot antipatterns

For example, here’s part of the XML of a plan broken down before any index tuning:


And here it is after adding some helpful indexes:


Matching queries on identifiers like Query Hash and SQL Handle allows us to validate tuning efforts over time. There are a lot of other metrics we can retrieve as well, this is just a screenshot friendly example.

Thanks for reading!

Brent says: and when you throw in things like stored procedure and function names, then long term, we’ll be able to trend your performance tuning efforts over time even as query hashes change. We’ll be able to tell you which query to tune, and why, then later on, tell you, “Your query tuning efforts for usp_SalesReport paid off in 80% less logical reads.” This isn’t going to be easy – but it’ll pay off. That’s what we mean by mentoring, not monitoring.

Previous Post
Why SQL Developers Keep Making The Same Mistakes
Next Post
#TSQL2sday: How Much Plan Cache History Do You Have?

3 Comments. Leave new

  • I really enjoy the ConstantCare posts; they don’t seem to generate huge numbers of comments on the blog, but they always prompt me to reflect on how to go about picking the right technology for the job in hand, even if that means choosing something other than comfortable-as-old-slippers T-SQL

    • Yeah, I don’t expect ’em to generate a lot of comments – they’re just fun to read, especially in the database business. It’s kinda like used to be – insight from the inside of building stuff, real-world hands-on stuff, not armchair architecture. It’s easy to find people who are just reading the manual and guessing how something valuable might be built – it’s so much harder to find real-world insight from people who are really doing the work.

  • Tom Uellner
    July 27, 2018 4:34 pm

    I agree with Ross. I really enjoy the ConstantCare post. Thank you!


Leave a Reply

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

Fill out this field
Fill out this field
Please enter a valid email address.