Building SQL ConstantCare®: Collecting Query Plans

Coming Soon

In an upcoming release, we’ll start collecting query plans from your servers. Since this may result in the transmission of PII, it will be opt-in only. We won’t be getting this by default, because we don’t want anyone to be uncomfortable with the level of data we’re collecting from their servers.  This is an important goal.

Like Brent said in another post, not enough subscribers are using Query Store yet to build any tooling around it. I have a feeling that this is the refrain from a lot of vendors. In the years since Query Store was announced, I haven’t seen any big vendor announcements that they’re supporting or analyzing it.

Free markets are wonderful for market research, just ask Extended Events.

How We’re Doing It

We’re departing a bit from our usual data gathering method, where we have a single query that does a simple select from a DMV. In this case, it makes sense to pre-assemble some things to make sure we get relevant details in a snapshot. For that, we already have a great query in sp_BlitzCache. It gets the top N plans by different important metrics: CPU, reads, writes, duration, executions, etc. and does some Highly Advanced Maths (multiplication and division, and even summing) to calculate resource usage of queries.

Where things change, though, is what happens next. In a normal run of sp_BlitzCache, we’d get the plans by a metric, then do a bunch of XML parsing and processing, generate and roll up warnings, and return a bunch of information to you. That makes sense for a single user running a single script on a single server to look at, but it makes far less sense when we need to just gather data from (up to 60!) servers from a single user, and then look at the data later. We can’t afford to lose time with a bunch of XQuery Hanky Panky®.

Instead, we’re running the gathering query in a simple loop to sort your plan cache by different important measures, and spitting a limited number of high value offenders out.


Another important goal is to introduce as little overhead as possible. We’ve seen your servers — many of them are in tough, tough shape. Sometimes when I’m looking at server data, that Sarah McLachlan song from the animal shelter commercial starts playing. So if we’re going to help, we can’t start by hurting.

That’s why all of the analysis is going to be done on our end, not on yours — we’ve got a tough end. It’s not that running sp_BlitzCache is unsafe, or will drive your server to its penitent knees, but let’s face it: XML has never been SQL Server’s friend. And besides, if we’re just going to take XML out of the database, we might as well take it all the way out.

We try to be kind to all databases, which means we’re not going to heap abuse on Postgres by introducing a bunch of XML processing there, either. I have no idea if Postgres is good, bad, or indifferent to XML, and I don’t want to find out. I want to use something that I know is good at it, and will put as little strain on our server as possible.

Pick and Fuss

We’re still deciding between C# and Python for the XML parsing. Which one we go with will ultimately be decided by what makes Richie’s life the least difficult around writing unit tests for, etc. Your favorite language may not win, and I’m sorry for that. You can talk about how awful we are on Hacker News, if you want. I’ll understand completely.

By taking this part of the processing out of the database and making it asynchronous, it allows us to embrace fuller linguistic support of XML and do even deeper trending analysis of your query plans. I’ll talk about that in a future post.

I’m really excited to be working on this piece of SQL ConstantCare®, and I think you’ll like getting the analysis you’re used to from sp_BlitzCache, and a whole lot more.

Thanks for reading!

Brent says: I’m excited about doing big-picture analysis in query plans, too. Often, when I’m looking at a client’s sp_BlitzCache output, I say things like, “I see we really like using table variables, and we really like using scalar functions as filters. Let’s start with an hour’s worth of training on those two topics, and then let’s look at your query plans, and you’ll see things in a fresh new light.” I’m looking forward to taking the same kind of approach with SQL ConstantCare® mentoring.

Previous Post
[Video] What to Do When SQL Server is Slow
Next Post
Management Studio Hides Missing Indexes From You.

4 Comments. Leave new

  • Michael J Swart
    July 2, 2018 8:47 am

    I’ve seen first hand the value of collecting top query plans regularly and doing the big picture analysis you’re talking about. I really really like this direction and the boost in value to Constant Care.

    • Erik Darling
      July 2, 2018 10:37 am

      Thanks, Michael. I’m really psyched to work on this part of the application. It’s got all my favorite stuff!

  • André Cardoso
    July 2, 2018 11:07 am

    In C# (.NET) you can query XML with Linq to XML 🙂

  • “Sometimes when I’m looking at server data, that Sarah McLachlan song from the animal shelter commercial starts playing.”



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.