Quickly Detecting CPU and Memory Pressure On A SQL Server

Options Ahoy

There are lots of great options out there for figuring out what’s happening on a server. If you’re the kind of person still running sp_who, or sp_who2, or sp_who8045, it’s time to stop.

You’ve got much better options, like sp_WhoIsActive, or the First Responder Kit. They’re both great, but sometimes I needed this very specific information very quickly without all the bells and whistles.

I’ve had to do some pretty weird troubleshooting in my life, where a server was totally jammed up, and these help me figure out what’s going on.

Sir-Procs-A-Lot

I call it the pressure detector. It’s four simple DMV queries that will give you different levels of detail about memory and CPU usage currently taking place on a server. Like a lot of these scripts, when a server is Baltimoring, you’re better off running them using the DAC. Otherwise, you’re kinda stuck in the same place all your other queries are.

So what does this help you with? Situations where you’re running out of worker threads, or when you’re running out of memory.

When you hit those, you’ll get smacked with a couple nasty waits:

  • THREADPOOL: Running out of worker threads
  • RESOURCE_SEMAPHORE: Running out of query memory

The thing is, sometimes it’s hard to see what’s happening, what’s causing problems, and what’s on the receiving end.

Check out this video to walk you through the proc results.

Video Killed

Thanks for reading!

13 thoughts on “Quickly Detecting CPU and Memory Pressure On A SQL Server”

  1. Hey, the column “threading waiting for cpu” stands “tasks waiting for a worker” ? Or it eally means a scheduler contention ( amount of workers waiting to run on some scheduler )

    1. I’m not exactly sure what you’re asking, but if you start the video around the 7:20 mark (when the CPU pressure demo starts), you should see that column shoot up to 110. That’s threadpool, and the number should be pretty close to the next result set down which shows everything waiting on threadpool.

      Thanks!

  2. Great video, and thanks for the script! I’ll add this one to my tools.

    Also, if you are using SSMS 18.4, there is a bug that causes some graphical issues when switching between tabs. If you disable hardware acceleration (under General in the Options menu) it might fix that weirdness.

  3. It’s like you’re reading my diary. Excited to use this next time thing start jumping into the hand basket for a quick trip south. Thanks for creating and sharing.

  4. Msg 217, Level 16, State 1, Procedure sys.sp_executesql, Line 1 [Batch Start Line 219]
    Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

Leave a Reply

Your email address will not be published. Required fields are marked *