Have you ever wanted a quick and easy way to diagnose CPU or memory pressure on a SQL Server? That’s exactly what sp_pressure_detector does.
Designed to fill in some blank spots in sp_WhoIsActive and other monitoring scripts, this focuses on two key areas:
- What’s happening with your CPUs
- What’s happening with your RAM
For CPU, we look at worker thread usage and potential starvation — the dreaded THREADPOOL waits, which means queries are sitting around waiting on worker threads to run — along with CPU intensive queries.
For RAM, we look at how much is available, in use, and what it’s being used by: either the buffer pool, where SQL Server caches data, or query memory grants that SQL Server uses to write things down in while queries Sort or Hash data. The primary wait we care about in this scenario is RESOURCE_SEMAPHORE, which is queries hanging out waiting to get memory to start running.
Running the script is as simple as downloading and installing it and pressing F5.
For more detailed information, check out the below video on how to use the script and interpret the results.
Thanks for reading!