Quickly Detecting CPU and Memory Pressure On A SQL Server With sp_PressureDetector

Posted on by Erik Darling

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!

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.

Related Posts

38 thoughts on “Quickly Detecting CPU and Memory Pressure On A SQL Server With sp_PressureDetector”

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

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

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

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

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

  8. Pingback: Using Erik Darling’s sp_pressure_detector to resolve CPU issues | The Desert DBA
  9. Pingback: Diagnosing and Resolving CPU Issues – Curated SQL

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

    1. Great question! I don’t have a good answer without knowing more. If it happens again try to get some more details on what was waiting on threadpool and what was running.

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

  15. One thing I like about working with SQL Server is the fine individuals who offer the fruits of their labors for little or no return except a bit of kudos.
    And this is fine example of that.
    Thank you.

  17. Hello Erik. Thank you for sharing your work.
    When I run sp_PressureDetector on my server the procedure will get blocked and not return results while a user is running a SELECT xxxx INTO #temp.
    Is there any way around this problem?
    Thank you

  18. HI Erik. I am starting to use the Stored Proc on multiple servers and I am finding it very useful.
    I have a question regarding the second section of output. I consistently see “max_target_memory_mb” is only about 75% of the SQL server’s Max Server Memory. When I factor in or account for Plan Cache memory it still seems low. For example I have a Server 215GB on the OS and SQL Server is set to 195GB MAX server Memory and when I run sp_PressureDetector my “max_target_memory_mb” has the value 145959.585937.
    If I take into account Max Server size 195GB minus roughly 16GB for plan cache I should have close to 180GB for Max target?.
    What am I missing?
    Thanks

Comments are closed.