Odds and ends

During the precon, we asked how many people were on 2016, how many people were aware of query store, how many people were using it, and how many people were using sp_BlitzQueryStore.

About 1/3 of the hands went up at first, then for each successive question, fewer and fewer hands stayed up.

Adoption is hard, I get it.

Heck, there were plenty of folks in the crowd who weren’t using our other fabulous Blitz scripts, so I didn’t feel too slighted.

Tour de source

One of the toughest things about writing a new stored procedure isn’t figuring out what it’s going to do, but how it’s gonna do it.

I could have just dropped a copy of sp_BlitzCache for Query Store, but I wanted to do things a little bit more differenter’ly.

Given the relative instability of the plan cache, the sorting by a single metric and all that totally makes sense. It’s short term.

Query store gives you a much longer history, so we have the ability to find what was going on when your server was at it’s worst by every metric.

I also didn’t want to just rehash information you can already pull out of built in reports.

That’s no fun at all. No one’s sitting around saying “can you make this less visual? Thanks!”

At work

So how can you run this thing? Right now, these are our available parameters.

• @Help: Right now this just prints the license if set to 1. I’m going to add better documentation here as the script matures.
• @DatabaseName: This one is required. Query Store is per database, so you have to point it at one to examine.
• @Top: How many plans from each “worst” you want to get. We look at your maxes for CPU, reads, duration, writes, memory, rows, executions, and additionally tempdb and log bytes for 2017. So it’s the number of plans from each of those to gather.
• @StartDate: Fairly obvious, when you want to start looking at queries from. If NULL, we’ll only go back seven days.
• @EndDate: When you want to stop looking at queries from. If you leave it NULL, we’ll look ahead seven days.
• @MinimumExecutionCount: The minimum number of times a query has to have been executed (not just compiled) to be analyzed.
• @DurationFilter: The minimum number of seconds a query has to have been executed for to be analyzed.
• @StoredProcName: If you want to look at a single stored procedure.
• @Failed: If you want to look at failed queries, for some reason. I dunno, MS made such a big deal out of being able to look at these, I figured I’d add it.
• @PlanIdFilter: If you want to filter by a particular plan id. Remember that a query may have many different plans.
• @QueryIdFilter: If you want to filter by a particular query id. If you want to look at one specific plan for a query.
• @ExportToExcel: Leaves XML out of the input and tidies up query text so you can easily paste it into Excel.
• @HideSummary: Pulls the rolled up warnings and information out of the results.
• @SkipXML: Skips XML analysis.
• @Debug: Prints dynamic SQL and selects data from all temp tables if set to 1.

At play

A couple basic examples!

This will go into the Stack Overflow database, and grab the top three plans by all the metrics query store collects between the start and end dates.

We do de-deduplicate by plan_id here, so we’re not doing a lot of extra XML analysis. But don’t worry, we tell you each metric a plan fell into!

For those of you who already use sp_BlitzCache, this should look pretty similar to you.

Looking at the output, there’s a stored procedure up at the top. If we want to zoom in and look at all of its history, we just change our query a little bit.

Et voila!

I trimmed off some of the columns to the left, like database name and cost, but don’t worry! They’re still there. I left them out to show you the parameter sniffing symptoms column.

sp_BlitzCache only warns you about parameter sniffing. It doesn’t tell you what changed or was different between executions. I’m doing that here because I want to make it easier to identify problematic symptoms over time.

If you scroll right a little bit, you get some total and average metrics.

There’s more stuff to the right, like first and last execution times, and context settings. It just doesn’t photograph well.

Like chowder.

No one ever looks at a picture of chowder like “oh yeah, that’s the best chowder!”.

It’s always “yeah, that’s chowder.” and you’re either a chowder person or you’re not a chowder person.

Warnings and whatnot

To make your transition to Query Store more comfortable, I also roll up warnings and information as a secondary result set for you.

This is the part that gets skipped if you choose to hide the summary.

This gives you general plan warnings, and also tells you when your highest metric consuming periods of time were.

Imperial Motors

I’m going to write some other posts that look more closely at the code, and show you some more advanced examples of bad query you can track down using sp_BlitzQueryStore.

Thanks for reading!