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 on 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.

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 on 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.

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 on 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.

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 on 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.

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 on 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.

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 on 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.

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 on 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.

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 on 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 on 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.

SQL Server 2022: Cardinality Estimation Feedback

Quiet As Kept


I’ve been trying to take the general temperature when it comes to SQL Server 2022. At least from a performance perspective, some interesting things have been introduced so far.

There have been a few neat things:

  • Parameter Sensitive Plan optimizations
  • Query Store Hints
  • Memory Grant Feedback improvements
  • DOP Feedback
  • Cardinality Estimation Feedback

I’m not seeing a whole lot out there. I’m not sure why. I follow quite a few SQL bloggers via Feedly.

Perhaps it’s just¬†too new. Maybe everyone is waiting for CTP SP1.

Well, anyway. In this post I want to talk a little bit about what Cardinality Estimation Feedback can do, and what it can’t do.

What It Do


First, you need Query Store enabled to get this to work. It relies on the Query Store Plan hints also introduced for SQL Server 2022.

For queries that execute frequently and retain cached plans, the optimizer will look at some of the assumptions that get made under different Cardinality Estimation models.

Things like:

  • Row Goals
  • Predicate independence/correlation
  • Join containment being simple or base

What each of those things means isn’t terribly important to the post, but all of them are things that are influenced by using the legacy or default cardinality estimators.

As I understand it, this is a bit like Memory Grant Feedback. If estimation issues are detected, a different plan will be attempted. If that plan corrects a performance issue, then the hint will get persisted in Query Store.

Pretty cool, but…

What It Don’t Do


It doesn’t fix things while they’re running, like Adaptive Joins can do. That’s sort of unfortunate! Hear me out on why.

Often, when model errors are incorrect, queries run for a long time. Particularly when row goals are introduced, query plans are quite sensitive to those goals not being met quickly.

It’d be really unfortunate for you to sit around waiting for 15-16 executions of a poor performing query to finish executing before an intervention happens.

I would have either:

  • Reduced, or made this threshold configurable
  • Been more aggressive about introducing Adaptive Joins when CE models influence plan choices

After all, Adaptive Joins help queries at runtime rather than waiting for an arbitrary number of executions and then stepping in.

Perhaps there was a good reason for not doing this, but those were the first two things to cross my mind when looking into the feature.

How It Do


I was able to get the feature to kick in using a familiar query.

Here’s the setup script:

DBCC FREEPROCCACHE;
ALTER DATABASE 
    StackOverflow2010 
SET 
    QUERY_STORE CLEAR;
GO

    CREATE INDEX whatever 
        ON dbo.Votes(CreationDate, VoteTypeId, PostId);
    
    CREATE INDEX apathy
        ON dbo.Posts (PostTypeId)
            INCLUDE (OwnerUserId, Score, Title);
GO

    SELECT TOP (2500) 
        p.OwnerUserId, 
        p.Score, 
        p.Title, 
        v.CreationDate,
        ISNULL(v.BountyAmount, 0) AS BountyAmount
    FROM dbo.Posts AS p
    JOIN dbo.Votes AS v
        ON  p.Id = v.PostId
    WHERE v.VoteTypeId = 1
    AND   p.PostTypeId = 1
    ORDER BY v.CreationDate DESC;
    GO 17

SELECT qspf.* FROM sys.query_store_plan_feedback AS qspf;

SELECT qsqh.* FROM sys.query_store_query_hints AS qsqh;

For the first 16 runs, we get the same query plan that takes about 2 seconds.

SQL Server Query Plan
if you got a problem

Then, magically, on run #17, we get a different query plan!

SQL Server Query Plan
yo i’ll solve it

Pretty cool! The plan totally changed, and clearly got better. I am happy about this. Not so happy that it would have taken 16 executions of a Potentially Painful© query to get here, but you know.

Here we are.

In Query Store


There are a couple views that will detail where hints came from and which were applied:

SQL Server Query Results
clowny clown clown

Since I just cleared out query store prior to this running, we can infer some things:

  • CE Feedback kicked in and gave us a new plan with a hint to disable row goals
  • The second plan generated was identified by the engine as needing memory grant feedback

I suppose this is a good reason to do some work on sp_QuickieStore so y’all can see this stuff in action.

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 on 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.