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.
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.
Thanks for reading!
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.
38 thoughts on “Quickly Detecting CPU and Memory Pressure On A SQL Server With sp_PressureDetector”
Nice scripts. Should point out it only works in SQL2016 and >
You mean this script isn’t a reason to upgrade immediately?!
I commented out these two columns for it to work in 2014
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.
Great and very useful stuff. Thank you Erik for posting it.
My pleasure! Hope you find them useful!
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 )
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.
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!
Aw, thanks! Glad you like it!
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.
Yeah, I usually disable that. It’s been awful for ages.
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.
Page 84 in your diary should be burned, FYI.
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).
Can’t reproduce over here, sorry! Double check that you don’t have a call to the proc at the end.
probably a great procedure, except it does not work on Azure SQL Databases.
Please vote for https://feedback.azure.com/forums/908035-sql-server/suggestions/33864208-sys-dm-os-sys-info-ms-ticks-in-azure-sql-db
Nothing works in Azure. That should go without saying 😉
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?
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.
I’ve created a Notebook based on this script, I want to add it to the SQL diagnostic (Jupyter) Book (https://tsql.tech/the-sql-diagnostic-jupyter-book/), is it ok for you?
I don’t see any licensing conditions on your GitHub page, so I’m asking 🙂
(look in the script)
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.
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.
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
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.
I’m not trying to bring an argument. No problems. Peace.
Thanks. A nice simple and concise set of results to show some useful information at a glance.
Hi Erik, is the detector script still available for download? It looks like a great tool.
Yeah, head over here. I also updated the GitHub links in the post.
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.
Thanks Erik for the awesome script! Is it possible to log it’s output to a table?
That’s not currently something that the procedure supports, no.
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?
Hm, no, I haven’t run into that.
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?
That number comes from here, which is a query against dm_exec_query_resource_semaphores . That column should indicate how much memory queries can get, not how much SQL Server can get.
Comments are closed.