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: Why Does sp_PressureDetector Make A Big Deal About The Remote DAC?

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

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 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: sp_PressureDetector Shows You Running Queries Taking Up CPU And Memory

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:

SQ: Server CPU Query
fivequery

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


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: Detecting Memory Pressure In SQL Server With sp_PressureDetector

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.

SQL Server Wait Stats
lucky strike

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.

SQL Server Wait Stats
human mind

Stolen


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

SQL Server Buffer Pool
gulf

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:

SQL Server Semaphores
indeed

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 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: Detecting CPU Pressure In SQL Server With sp_PressureDetector

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:

SQL Server Query Results
1861

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.

SQL Server Query Results
open world

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.

SQL Server THREADPOOL Waits
dreadful

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

Updates To sp_PressureDetector

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 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_PressureDetector: Crucial Wait Stats, More Consistent Column Arrangement, And More!

A Mother’s Guilt


GitHub had been making me feel guilty about taking some time off over the holidays to not monkey about with T-SQL, and since my brain finally seems to be working after a hectic travel schedule, I decided to tweak a few things in sp_PressureDetector.

Paris, Cali, Cabo, London

What’d I change? Great question! There’s a preview in yesterday’s post, but here’s the change log:

  • Formatting, of course
  • Clarify memory rollup column names
  • Add a section with wait stats overview for related cpu/memory/disk waits
  • Add query text column to memory grant results
  • Make query txt columns xml clickables
  • Rearranged columns for readability and consistency
  • Added a couple comments to clarify code intent
  • Add Max Server Memory to the total/available/granted line

You can learn more about how to use sp_PressureDetector here. Happy troubleshooting.

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.

Some Updates to sp_QuickieStore and sp_PressureDetector

In Trunks


I’ve made a few changes and improvements to these scripts. Here’s the changelog:

sp_PressureDetector:

  • Improved introduction
  • Added help section
  • Changed column order to put DOP information next to thread usage information
  • Added reserved threads to the CPU overview (2016+)

sp_QuickieStore:

  • Added a filter to the “maintenance” section to screen out automatic statistics updates (SELECT StatMan…)

A new function!

You can grab updated versions and get support over 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 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, sp_PressureDetector, and sp_HumanEvents

A Thing Of Things


sp_QuickieStore:

  • Added the ability to search query store wait stats for queries that generated a large amount of a specific wait category.
  • Noticed a couple TOP queries were missing DESC for the ordering
  • Increased length of all_plan_ids column to max after someone said they hit a truncation error with a length of 1000
  • Updated documentation to reflect more specific version support: 2016 SP2+, SQL Server 2017 CU3+, 2019+, and Probably Azure

sp_PressureDetector:

  • Added a parameter to skip getting execution plans when server is really hammered
  • Added database name to memory query
  • Added a missing isolation level set
  • Increased decimal length for servers with larger amounts of memory

sp_HumanEvents:

  • Updated proc to format queries to suit current formatting memes I enjoy
  • Organized the proc a little bit better, putting all the declared variables and temp table creation at the beginning
  • Fixed some inevitable bugs that come with formatting and reorganizing really long stored procedures
  • Cleaned up error handling
  • Added a check for the signed query hash action; I found the proc failed on some earlier builds of 2014 and 2012

So uh, go get those. Use’em. Have fun.

Do a SQL.

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.