Erik Darling – Page 58

SQL Server Community Tools: Why Does sp_PressureDetector Make A Big Deal About The Remote DAC?

Posted on September 16, 2022August 25, 2022 by Erik Darling

Subject Matter

I’m gonna throw this at you fast, because it’s Friday. If you’re using sp_PressureDetector in a SQL Server performance crisis, there are two things you don’t want:

Slow results from running diagnostic queries
Diagnostic queries waiting in the same CPU and memory lines as user queries

That’s why one of the first things does is see if the Remote DAC is enabled, and urge you to turn it on, like so:

EXEC sp_configure 
    'remote admin connections', 
    1; 

RECONFIGURE;

Why bother? Well, the Remote DAC is sort of like a VIP entrance to Club SQL Server. You get your own little connection and set of resources that are unfettered by lowly user queries that are most-definitely-not-on-the-guest-list.

There are a couple caveats to being on the DAC list:

You have to use an admin account to access it
Only one person can use it at a time

So what is it, and how do you use it?

Connectivity

To use the DAC, all you have to do is add the prefix “ADMIN:” to your SSMS connection:

SQL Server DAC Connection SSSMS — i disconnect from you

Once that’s done, you just connect as normal and you’re good to go. There was a longstanding bug fixed recently in SSMS, where an error message would show up that said you’d not connected using the Remote DAC, even though you were.

This makes running diagnostic queries when your server is Having A Bad Day© much less prone to either adding to or being trampled by performance issues.

And also, uh, it’s just generally a good thing to have enabled for situations where you can’t connect to SQL Server in any of the normal ways, and you maybe want to try to see what the problem is rather that just crawl out of bed to restart the server.

So yeah. Good, that. Enable it.

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: sp_PressureDetector Doesn’t Show You Irrelevant Waits

Posted on September 15, 2022September 15, 2022 by Erik Darling

Which Wait Did They Go?

I tried to really focus sp_PressureDetector on things that really matter to overall SQL Server performance, and specifically related to CPU and memory pressure.

It seemed easier to include rather than exclude waits, since the list always seems to keep growing. I also wanted to include a little decoder ring to help you understand what each wait means.

CMEMTHREAD: Tasks waiting on memory objects
CXCONSUMER: Parallelism
CXPACKET: Parallelism
CXSYNC_CONSUMER: Parallelism
CXSYNC_PORT: Parallelism
PAGEIOLATCH_EX: Modifications reading pages from disk into memory
PAGEIOLATCH_SH: Selects reading pages from disk into memory
RESOURCE_SEMAPHORE: Queries waiting to get memory to run
RESOURCE_SEMAPHORE_QUERY_COMPILE: Queries waiting to get memory to compile
SOS_SCHEDULER_YIELD: Query scheduling
THREADPOOL: Worker thread exhaustion

I know this seems really limited, but… Uh. What else would you want, here? I could show locking waits, but they aren’t really related to CPU or memory pressure.

Sure, if enough (probably parallel) queries get blocked, it can lead to THREADPOOL waits, but if that’s the cause then you’ll see all the blocking going on in the running queries results.

Seeing a bunch of lock waits up front is just a distraction from the CPU and memory pressure issues I’m trying to surface, and there’s really nothing specifically hardware related to fixing locking problems.

But anyway, if you feel like there are relevant waits missing from the list, feel free to leave a comment or open an issue on GitHub.

Going Further

SQL Server Community Tools: sp_PressureDetector Shows You Running Queries Taking Up CPU And Memory

Posted on September 14, 2022August 25, 2022 by Erik Darling

One For The Money

This post describes a little more of the philosophy behind sp_PressureDetector more than anything technical about it. Maybe.

I write this at the outset, but who knows where it’ll end up. Might even delete the whole thing and look at expensive cheese.

ANYWAY! If you’ve been following my posts this week, you’ll be painfully aware that sp_PressureDetector looks at things in your SQL Server that could indicate CPU or memory pressure.

Of course, very few servers will experience either of those issues without queries running, and so my dear friend sp_PressureDetector will show you which queries are in the danger zone.

And since it may be different queries causing problems, I show you two different sets of queries in two different ways.

Two For The Memory

The memory grant section shows you queries ordered by highest current memory grant. It will show you stuff you’re accustomed to, like session id, database, query text, query plan, and all that stuff, but it’ll also break down important stuff about what’s going on with memory.

SQL Server Memory Grant Query — fourquery

There are four queries running on my demo VM. Three of them asked for and received memory grants, but one of them didn’t!

The first three queries have all requested and been granted memory and are slowly using it
The fourth query hasn’t gotten a grant yet, but is sitting in a queue waiting for memory to free up

While that fourth query waits for memory, it’s sitting around on RESOURCE_SEMAPHORE.

You may not always see this happening, but even without hitting the global limit for memory grants, you can still get pretty valuable data about queries running asking for memory.

Three To Get CPU Ready

Just like the memory grant query output, the CPU section will show you familiar and helpful columns to identify and analyze the query, but the section that will show you which ones are chewing up CPU is here:

In this cap, there are five queries running (all of them are parallel). You can see associated waits, cpu time, elapsed time, and other resource usage.

You can also see just how parallel a query went. Because there is only one “branch” in this query plan, DOP and parallel worker count are equal. In larger plans, worker count can be a multiple of DOP.

SQL Server Parallel Query Plan — woah mama

In this query plan, there are four parallel exchanges, which means four branches, so we get four x dop 8 parallel worker threads, totaling 32. Only 8 can be active concurrently, but all 32 are reserved by the query until it completes

On really overworked servers, SQL Server will downgrade DOP to preserve worker threads. Screenshots don’t really do that justice, but I demo it at the main page for sp_PressureDetector in the video.

Thanks for reading!

Going Further

SQL Server Community Tools: Detecting Memory Pressure In SQL Server With sp_PressureDetector

Posted on September 13, 2022August 25, 2022 by Erik Darling

Sponsor

People are always surprised by how SQL Server uses, and misuses, memory. First, there are misconceptions about how much memory Standard Edition can use — everyone thinks it’s 128GB — but that’s just for the buffer pool.

Sometimes they have no idea that…

SQL Server caches data pages in memory (buffer pool)
SQL Server doesn’t work with pages on disk (except to read them into memory)
SQL Server queries can ask for TONS of memory that gets pulled from the buffer pool
SQL Server uses memory to manage other things, like locks, caching query plans, compressed backups, and more

So that’s the type of stuff that I thought would be good to expose with sp_PressureDetector.

To look at just memory stuff with it, run this:

EXEC sp_PressureDetector
    @what_to_check = 'memory';

Simple as.

Trudging

When SQL Server is under memory pressure, it can show up in a few different places, just like CPU pressure.

The arrows are pointing to two wait types that are directly related to queries waiting on memory. Read the description column for more detail ;^}

But that’s not the whole story, because like I said above, SQL Server uses memory for a bunch of stuff. If there’s memory pressure on the server, you’ll also see SQL Server having to go out to disk a whole bunch.

Stolen

SQL Server surfaces what things are consuming memory, too. I show that early on in the results.

My server isn’t very busy at the moment, so these numbers are fairly low, but this gives you a pretty good breakdown of:

How much memory is dedicated to the buffer pool
How much memory is stolen from the buffer pool
How much memory other consumers are taking up

You can match some of that up with other results that get returned that show more detail on query memory grants:

It doesn’t match exactly because there’s a little time between when each query runs, but the granted_memory and used_memory columns are close-enough to the MEMORYCLERK_SQLQERESERVATIONS and Stolen Server Memory (KB) lines in the other results shown here.

If you’re paying really close attention, you may notice that 17GB of memory has been given to three queries, and 197 queries are waiting on memory.

In tomorrow’s post, we’ll look at how sp_PressureDetector surfaces queries that are most likely involved in CPU and memory pressure.

Thanks for reading!

Going Further

SQL Server Community Tools: Detecting CPU Pressure In SQL Server With sp_PressureDetector

Posted on September 12, 2022August 25, 2022 by Erik Darling

Playing Favorites

I absolutely adore sp_PressureDetector. It’s short, it’s sweet, and it returns so many great details about what sort of pressure a SQL Server is under.

Today, we’re going to look at various ways that CPU pressure can be exposed.

You know, those expensive things that you license from Microsoft that make your database run?

They seem important.

Sizzling

There are precious few parameters to sp_PressureDetector. The only one you might use is @what_to_check.

EXEC sp_PressureDetector
    @what_to_check = 'cpu';

By default, the value is “both” — meaning you check CPU and memory — but you can choose to check one or the other.

Running that is going to show you all of the following things, as long as you’re on the latest version.

First, you might see signs in wait stats:

My demo VM hasn’t been up terribly long, and I threw a ridiculous CPU workload at it. Basically one parallel query that exhausts worker threads.

All of the waits there can be signs that your server CPU is overworked. They’re not too bad here, but if the hours_wait_time column is much greater than the hours_uptime column, that could be a pretty good indication.

Of course, because I’m throwing a horrible parallel workload at it, some of the other sections are gonna have really obvious problems.

Take this section, for instance.

The negative available_threads column, plus the high runnable columns. Having lots of runnable queries means you have a lot of queries waiting to get on/back on a CPU.

Long lines there can mean that your CPUs are way too busy.

When things are really bad, you might see a bunch of queries that are waiting a really long time to get a CPU, resulting in gobs of THREADPOOL waits.

These are the places that signs of CPU pressure can prop up. If you need help fixing that, young and good looking consultants are standing by.

Thanks for reading!

Going Further

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

Posted on September 9, 2022September 9, 2022 by Erik Darling

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

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

Posted on September 8, 2022August 23, 2022 by Erik Darling

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

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

Posted on September 7, 2022September 7, 2022 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.

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

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

Posted on September 6, 2022August 23, 2022 by Erik Darling

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

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

Posted on September 5, 2022August 23, 2022 by Erik Darling

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!