sp_PressureDetector

Have you ever wanted a quick and easy way to diagnose CPU or memory pressure on a SQL Server? That’s exactly what sp_PressureDetector 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 (and watching)!