Category: sp_PressureDetector

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:

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.

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

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.

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.

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.

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

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.

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

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.

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

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.

All Good Things

I haven’t run into a bug with this thing in just about… Well, ever. This proc has been pretty solid.

As I use it though, especially in front of clients, I find myself wanting some additional data points all in one place. With this release, I’ve added a few small things.

1. Total physical memory in the server (not just max server memory)
2. CPU details (NUMA nodes, schedulers; if they’re off line, etc.)
3. A debug mode for the dynamic SQL

The CPU details column is an XML clickable that will look something like this:

<cpu_details>
  <offline_cpus>0</offline_cpus>
  <cpu_count>8</cpu_count>
  <hyperthread_ratio>8</hyperthread_ratio>
  <softnuma_configuration_desc>OFF</softnuma_configuration_desc>
  <socket_count>1</socket_count>
  <cores_per_socket>4</cores_per_socket>
  <socket_count>1</socket_count>
</cpu_details>

This is an example from my test VM. Please don’t think less of me.

You can find out more, and download the script over here.

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.