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.

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.