How To Find Poorly Performing SQL Server Queries To Tune Using Query Store

Stick And Move


If you take performance for your SQL Servers seriously, you should be using Query Store for your business critical databases.

I used to say this about third party monitoring tools, but the landscape for those has really tanked over the last few years. I used to really love SQL Sentry, but it has essentially become abandonware since SolarWinds bought SentryOne.

At this point, I’m happier to enable query store, and then use a couple extended events to capture blocking and deadlocks. While it would be stellar if Query Store also did that, for now life is easy enough.

To analyze blocking and deadlock Extended Events, I use:

This won’t capture absolutely everything, but that’s okay. We can usually get enough to go on with those three things. If you have bad blocking and deadlocking problems, you should start there.

But once you turn on Query Store, where do you go?

Gooey


If you’re okay with all the limitations of the GUI, you can tweak a few things to get more useful information out of it.

I usually start with the Top Resource Consuming Queries view, since, uh… those are usually good things to tune.

SQL Server Query Store
top resource consuming plans

But the crappy bar graph that Query Store defaults to is not what you want to see. There’s way too much jumping around and mousing over things to figure out what’s in front of you.

I like switching to the grid format with additional details view, by clicking the blue button like so:

SQL Server Query Store
additional details!

But we’re not done yet! Not by a long shot. The next thing we wanna do is hit the Configure button, and change what we’re looking at. See, the other crappy thing is that Query Store defaults to showing you queries by total duration.

What ends up being in here is a bunch of stuff that runs a lot, but tends to run quickly. You might get lucky and find some quick wins here, but it’s usually not where the real bangers live.

To get to those, we need to hit the Configure button and make a couple tweaks to look at queries that use a lot of CPU on average, and push the time back from only showing the last hour to the last week or so.

You can go back further, but usually the further you go back, the longer it takes to get you results.

SQL Server Query Store
configurator

The problem here is that you can often get back quite a bit of noise that you can’t filter out or ignore. Here’s what mine looks like:

SQL Server Query Store
noise noise noise

We don’t really need to know that creating indexes took a long time. Substitute those with queries you don’t necessarily care about fixing, and you get the point.

You can sort of control this by only asking for queries with a certain number of plans to come back, but if your queries aren’t parameterized and you have a lot of “single use” execution plans, you’ll miss out on those in the results.

SQL Server Query Store
min-maxing

This filter is available under the Configuration settings where we changes the CPU/Average/Dates before.

The major limitation of Query Store’s GUI is that you can’t search through it for specific problems. It totally could and should be in there, but as of this writing, it’s not in there.

That’s where my stored procedure sp_QuickieStore comes in.

Scripted, For Your Pleasure


The nice thing about sp_QuickieStore is that it gets rid of a lot of the click-clacking around to get things set up. You can’t save your Query Store GUI layout to open up and show you what you want every time, you have to redo it.

To get us to where we were with the settings above, all we have to do is this:

EXEC sp_QuickieStore
    @execution_count = 5;

By default, sp_QuickieStore will already sort results by average CPU for queries executed over the last week of Query Store data. It will also filter out plans for stuff we can’t really tune, like creating indexes, updating statistics, and waste of time index maintenance.

You’ll get results that look somewhat like so:

sp_QuickieStore
to the rescue!

There are a number of things you can do with  to include or ignore only certain information, too:

  • @execution_count: the minimum number of executions a query must have
  • @duration_ms: the minimum duration a query must have
  • @execution_type_desc: the type of execution you want to filter
  • @procedure_schema: the schema of the procedure you’re searching for
  • @procedure_name: the name of the programmable object you’re searching for
  • @include_plan_ids: a list of plan ids to search for
  • @include_query_ids: a list of query ids to search for
  • @ignore_plan_ids: a list of plan ids to ignore
  • @ignore_query_ids: a list of query ids to ignore
  • @include_query_hashes: a list of query hashes to search for
  • @include_plan_hashes: a list of query plan hashes to search for
  • @include_sql_handles: a list of sql handles to search for
  • @ignore_query_hashes: a list of query hashes to ignore
  • @ignore_plan_hashes: a list of query plan hashes to ignore
  • @ignore_sql_handles: a list of sql handles to ignore
  • @query_text_search: query text to search for

You straight up can’t do any of that with Query Store’s GUI. I love being able to focus in on all the plans for a specific stored procedure.

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.

SQL Server Community Tools: The Wrap Up And Combined Link

I am a heading


Over the past month (plus or minus a couple days), I’ve shown you in a series of quick posts how I use different SQL Server Community Tools that are free and open source to troubleshoot SQL Server issues.

Here’s the full list of posts.

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.

SQL Server Community Tools: How To Dig Deeper With Expert Mode With sp_QuickieStore

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.

SQL Server Community Tools: Formatting sp_QuickieStore Output So It’s Easier To Understand

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:

SQL Server Query Results
commacastic

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.

SQL Server Community Tools: How To Find Specific Queries With sp_QuickieStore

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.

SQL Server Query Results
bang on

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.

SQL Server Community Tools: How To Filter What sp_QuickieStore Shows You

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.

SQL Server Community Tools: Using sp_QuickieStore To Find Your Worst Performing Queries

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:

SQL Server Query Results
big machine

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.

SQL Server Query Results
bingo

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.

Correlating Data From sp_WhoIsActive to Query Store Or The Plan Cache

sp_QuickiePost


If you’re the type of person who logs sp_WhoIsActive to a table to capture executing queries, you may want to find some additional details about the statements that end up there.

Out of the box, it’s arduous, tedious, and cumbersome to click around on a bunch of columns and grab handles and hashes and blah blah.

Now, these two queries depend on you grabbing a couple specific columns in your output. If you’re not getting these, you’re kinda screwed:

From query plans, you can get the plan handle and plan hash:

WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT
   session_id,
   query_plan,
   additional_info,
   query_hash = 
       q.n.value('@QueryHash', 'varchar(18)'),
   query_plan_hash = 
       q.n.value('@QueryPlanHash', 'varchar(18)')
FROM dbo.WhoIsActive AS w
CROSS APPLY w.query_plan.nodes('//StmtSimple') AS q(n);

From additional info, you can get the SQL Handle and Plan Handle:

SELECT
  session_id,
  query_plan,
  additional_info,
  sql_handle =
      w.additional_info.value('(//additional_info/sql_handle)[1]', 'varchar(131)'),
  plan_handle = 
      w.additional_info.value('(//additional_info/plan_handle)[1]', 'varchar(131)')
FROM dbo.WhoIsActive AS w;

Causation


For the plan cache, you can use your favorite script. Mine is, of course, sp_BlitzCache.

You you can use the @OnlyQueryHashes or @OnlySqlHandles parameters to filter down to queries you’re interested in.

For Query Store, you can use my script sp_QuickieStore to do the same thing.

It has parameters for @include_query_hashes, @include_plan_hashes or @include_sql_handles.

You might want to add some other filtering or sorting to the queries up there to find what you’re interested in, but this should get you started.

I couldn’t find a quick or easy way to combine the two queries, since we’re dealing with two different columns of XML data, and the query plan XML needs a little special treatment to be queried.

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.

How SQL Server 2022’s Parameter Sensitive Plan Feature Can Make Query Store Confusing

Psssssp



Thanks for watching!

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.

Updates to sp_QuickieStore

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.