Category: Query Store

Perfect Crime

As much as I’d love to think that the normal set of results in sp_QuickieStore is sufficient, sometimes you need a little bit more to figure out what’s going on.

That’s where Expert Mode comes in. Or, as I lovingly call it, @expert_mode.

Quality engineering, there.

Most normal people don’t like a flood of information all at once. That’s why I tend to write shorter blog posts, and I write short sentences in small paragraphs.

In case you were wondering.

More Better

To summon @expert_mode all you have to do is ask nicely.

EXEC sp_QuickieStore
    @expert_mode = 1;

What you get back is stuff that wouldn’t be useful when you’re just trying to find some queries to tune, but might be really useful when you’re trying to dig deeper into why a specific query was slow.

• Compilation Statistics: Here you get stuff like how many times, how long, how much memory, and other details around plan compilation.
• Resource Statistics: This data comes from the plan cache and is largely for additional memory grant details that aren’t available in Query Store, like the actual grant, and not just what was used.
• Query Store Wait Stats By Query: Up top, you get the three most prolific waits that a query was hit with; down here you get all of them ordered from highest to lowest
• Query Store Wait Stats Total: At the database level, all of the wait stats that queries have generated
• Query Store Options: How you set up Query Store, because sometimes you might wanna tweak those

Like I said, you won’t always need that stuff, but it can be useful at times in some scenarios.

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.

Scaling

I am not great at numbers. Especially big numbers, or numbers that need to get converted, like going from KB to GB.

Not KGB. I don’t wanna ever end up there.

Being but a mere mortal, I always find it a whole lot easier to figure out what I’m looking at when there are some separators in there.

For me in all my American Glory, that’s properly placed commas.

🫡🇺🇸

BigNumber4U

Some queries can rack up some pretty impressive resource consumption numbers, especially in Query Store where historical data is held for much longer times than the plan cache.

Making matters worse is that it makes sense to scale things to precise numbers that can look really confusing when they hit anything more than eight or nine digits.

That’s why I wanted to make sure sp_QuickieStore had a way to make things easier on us numerically-challenged public school kids.

EXEC sp_QuickieStore
    @format_output = 1;

Any number that meets the prerequisites for comma insertion will get one. here’s a small example:

Isn’t that nice?

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.

Stinky And Gross

Query Store gives you no way to really search through it. There are knobs and you can filter to specific times and stuff, but… That’s not really helpful most of the time.

If you need to find information about a particular query, but it’s not showing up in the places that it should be showing up, you’re screwed.

Unless you wanna write a bunch of horrible queries to dive into the Query Store DMVs on your own, or you’re the kind of Awesome Blossom who uses sp_QuickieStore.

Then you can find queries in a bunch of different ways.

It’s fun. You’ll love it.

Positive ID

In query store, most of the views are related by a couple different things:

• query id
• plan id

One query id can be attached to many plan ids, and what often happened to me is wanting to filter in to a specific set of query and plan ids.

With sp_QuickieStore, you can do that really easily.

• @include_plan_ids
• @include_query_ids
• @ignore_plan_ids
• @ignore_query_ids

Note that these parameters are all pluralized, which means you can pass in a list. That’s particularly helpful when you team the plan id parameter up with the all_plan_ids column in the procedure’s output.

You can copy and paste those out and use them directly to search through Query Store with sp_QuickieStore.

EXEC sp_QuickieStore
    @include_plan_ids = '156, 157';

You can do that with any of the other parameters too, to include or ignore certain queries.

Handle Hash Mustache

More recently, I added the ability to track down queries in Query Store by different hashes and handles in Query Store, using sp_QuickieStore.

•     @include_query_hashes
•     @include_plan_hashes
•     @include_sql_handles
•     @ignore_query_hashes
•     @ignore_plan_hashes
•     @ignore_sql_handles

Just like with the ids above, these accept CSV lists of hashes and handles to include or ignore.

But why? Well… Troubleshooting blocking and deadlocks is a whole lot easier when you can see query plans. You might see something obvious like…

• A bunch of foreign keys need to be validated on modification
• Some god awful trigger fires off
• Modification queries don’t have useful indexes

The problem is that neither the blocking or deadlock XML reports give you query plans. You only get ways to identify them — you might get the full query text if you’re lucky — but no query plans to give you more information.

Here’s an XML fragment from the blocked process report:

<executionStack>
    <frame line="1" stmtstart="24" stmtend="122" sqlhandle="0x020000005925de23bc428090e9810564087d8586724c38f30000000000000000000000000000000000000000" />
    <frame line="1" stmtend="86" sqlhandle="0x020000009002241ac985854546b21510bb975e36399c7f790000000000000000000000000000000000000000" />
</executionStack>

So uh, cool! But now what? Well, get with the program:

EXEC sp_QuickieStore
    @include_sql_handles = 
    '0x020000005925de23bc428090e9810564087d8586724c38f30000000000000000000000000000000000000000,
     0x020000009002241ac985854546b21510bb975e36399c7f790000000000000000000000000000000000000000';

Now you can find query plans by handle and hash really easily in Query Store.

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.

Wide Open

I try not to make too many assumptions about what you might want to see. The only real restrictions out of the box with sp_QuickieStore are:

• It only looks at one database at a time
• It only shows you the top 10 sorted by average cpu
• It only shows you the pas 24 hours of data

These were design decisions made in order to help sp_QuickieStore live up to its name.

But of course, you can tinker with these things with the following parameters:

•     @database_name: the name of the database you want to look at query store in
•     @sort_order: the runtime metric you want to prioritize results by: cpu, logical reads, physical reads, writes, duration, memory, tempdb, executions
•     @top: the number of queries you want to pull back
•     @start_date: the begin date of your search
•     @end_date: the end date of your search    
•     @execution_count: the minimum number of executions a query must have                 
•     @duration_ms: the minimum duration a query must have                    
•     @wait_filter: wait category to search for;  cpu, lock, latch, buffer latch, buffer io, log io, network io, parallelism, memory

These can be useful things to tweak based on your situation.

Details, Details

You can do some really cool stuff with these to narrow search results to things you care about. I’m gonna highlight those here, even if they may seem obvious.

•     @database_name: some databases are more important than others
•     @sort_order: if your server has a particular bottleneck, it can be useful to find queries using the most of that bottleneck
•     @top: sometimes there’s red meat beyond the top 10, like when you’re looking at high execution counts
•     @start_date: know when you had a problem? start here.
•     @end_date: know when the problem stopped? stop here.
•     @execution_count: you may not want to see queries with low execution counts, because they might just run once at night
•     @duration_ms: low duration queries may not be tunable, and you may not want to see them
•     @wait_filter: does a particular wait stat stick out on your server? Find the queries responsible for it!

I tried to give you plenty of options to focus in on high-level things that can help lead you to queries that are causing you problems.

You can also zoom in to specific queries using a few different searchables, and we’ll talk about that tomorrow.

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.

Mind Loss

Microsoft has invested some engineering time in the plumbing behind Query Store in SQL Server 2022. Really cool stuff, like the ability to add hints to a query and force it to use the plan with that hint in place.

That’s going to solve a crazy amount of problems for me, with queries that I can’t actually touch (and not because they’re priceless works of art).

But… the front end of Query Store still hasn’t changed. It’s clunky, it’s ugly, it’s not very configurable, and I find it downright unfriendly.

It can also be really slow and, golly and gosh, the number of times I’ve seen the queries that fill in the GUI show up in there is sort of depressing.

So I wrote sp_QuickieStore to fill in the gaps. No, it doesn’t populate a GUI (I don’t have those chops), but it does get you actionable results pretty quickly.

Explain Plan

By default, sp_QuickieStore will give you the top ten queries in query store by average CPU over the last 24 hours. I’m going to talk about other things you can do with it later this week.

For now, let’s just look at the first thing you see when you run it without any additional parameters. Most folks will stick sp_QuickieStore in the master database, but Query Store can only be turned on in user databases.

Of course, sp_QuickieStore has a parameter to tell it which database you want to analyze (@database_name). It’d be utterly insane for me to ask you, dear user, to install it in every user database.

The nice thing is that if you run sp_QuickieStore from a user database context, it will assume that that’s the database you want to analyze Query Store in.

EXEC sp_QuickieStore;

Right up front, you get the stuff that helps you figure out if you want to dig any deeper:

There’s a lot more information if you keep scrolling to the right that’ll tell you about resource usage, but here’s what you get:

• query_id: how Query Store identifies the query text
• plan_id: how Query Store identifies the query plan
• all_plan_ids: if your query has generated multiple plans, you’ll get a CSV list of them here
• execution_type_desc: if you query ran successfully or not
• object_name: if your query came from a store procedure
• query_sql_text: XML clickable of the query text
• compatibility_level: uh… compatibility level
• query_plan plan_forcing_type_desc: if Query Store is forcing a plan
• top_waits: the high-level wait stats that your query has generated
• first_execution_time: um… c’mon
• last_execution_time: don’t make me say it
• count_executions: oh gosh darn it to heck.

By The Numbers

There’s plenty for you to think about up there. Most folks know if they care about something by looking at some combination of object_name and query_sql_text. Sometimes count_executions will come into play.

Other times, you might have no idea what you’re looking at or why it’s showing up here. And baby. Baby, baby, baby. I am here for you.

These results are sorted by average CPU (that’s the default, remember), but there’s plenty of other memes here like logical reads for you to nod at sagely.

Something for everyone, really.

All this stuff is nice, but… Maybe you need something more. Maybe you’re searching for something in particular, maybe you want the results to look a little different, or uh… maybe you want to be an expert.

I would also love to be an expert. I would tell people expert things like “don’t throw eggs”.

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.

Just Visiting

I made some updates to sp_QuickieStore recently, which you can download here.

The main updates to the code are to:

• Add support for SQL Server 2022 views
• Add the ability to search by query hash, plan hash, and SQL handle

The SQL Server 2022 stuff isn’t important just yet, but… Hey, maybe someday.

The new search functionality is really important though, at least for how I use sp_QuickieStore much of the time. Often, you’ll find hashes and handles in other parts of the database:

• Plan cache
• Deadlock XML
• Blocked process report
• Query plans

There’s still no good way for you to search Query Store by anything. Not plan or query id, not query text, not object names. Nothing.

Don’t worry, I’m here for you.

Some other minor updates were to:

• Improve the help section
• Improve code comments throughout
• Remove the filter to only show successful executions (sometimes you need to find queries that timed out or something)
• If you filter on any hash or handle, I’ll display that in the final output so they’re easy to identify
• Replace TRY_CONVERT with TRY_CAST, which throws errors in fewer circumstances

That’s about all the stuff you need to know about. Aside from that, all my changes were slight logical errors or plumbing to implement the new features.

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.