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

Posted on by Erik Darling

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.

Related Posts