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!



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

    1. I commented out these two columns for it to work in 2014
      –deqmg.reserved_worker_count,
      –deqmg.used_worker_count,

      And change this line to create procedure only at the top
      CREATE PROCEDURE dbo.sp_pressure_detector

      Erik, thanks for sharing the procedure and video on how it all works.

  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!

      1. Nice! I review this part, and this stands “tasks waiting for a worker”.

        “Thread waiting cpu” is more related when the thread already got a task and cannot run because all schedulers are busy (the thread goes to runnable state). This is “true” thread waiting for CPU.

        I this case, no threads are waiting for CPU, and “tasks are waiting for a thread (or a worker)”. This what your output shows!

        Nice job!

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

  5. I had a case where THREADPOOL waits popped out and “threads_waiting_on_cpu” were more than 15, but “available_threads” more than 500. It was confusing to see THREADPOOL waits and “available_threads” so many at the same time. THREADPOOL is when there are no available threads in the server’s thread pool. Why “available_threads” were more than 500 when the THREADPOOL waits popped out? Is there something I don’t understand correctly with this wait type?

  6. First – THANKS!!!! A Lot!

    Next a couple of tweaks to consider:

    1. To get @cool_new_columns you count with a function win the where clause. Switching to ac.object_id = object_id(N’sys.dm_exec_requests’) should get a better plan.

    2. in the dynamic SQL there are two mutually exclusive conditions for der.transaction_isolation_level = 2. Putting the condition into an outer apply you can interrogate the results when sys.dm_tran_active_snapshot_database_transactions has a record.

    1. My pleasure! Glad you like it.

      1. Do I need a better plan for that DMV query? Let me know if it’s causing any performance issues for you.
      2. Same question — that one part is the same, but there are differences elsewhere. If it’s causing a problem for you, send me over the details.

      Thanks!

      1. Short answer – no.

        That being said, it make sense (to me) that we exhibit “best practices” for what we warn other not to do.
        1. Functions in a where clause have been romper-room no-no’s for a long time.
        2. DRY principal – don’t read data twice when you can read it once.

        I have a habit of picking apart procs like this from Brent, Kendra, SSC, etc to:
        – Learn from recognized masters
        – Understand different practices/approaches to solving problems
        – Make myself better

        1. Okay, but your suggestion is for me to trade one function for another function in the where clause, and the “reading data twice” is in a case expression where one may not occur if the other condition is met. Also, one is exists, and the other is not exists. I think perhaps your focus is incorrectly applied here.

Leave a Reply

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