SQL Server Community Tools: sp_PressureDetector Doesn’t Show You Irrelevant Waits

Posted on by Erik Darling

Which Wait Did They Go?

I tried to really focus sp_PressureDetector on things that really matter to overall SQL Server performance, and specifically related to CPU and memory pressure.

It seemed easier to include rather than exclude waits, since the list always seems to keep growing. I also wanted to include a little decoder ring to help you understand what each wait means.

  • CMEMTHREAD: Tasks waiting on memory objects
  • CXCONSUMER: Parallelism
  • CXPACKET: Parallelism
  • CXSYNC_CONSUMER: Parallelism
  • CXSYNC_PORT: Parallelism
  • PAGEIOLATCH_EX: Modifications reading pages from disk into memory
  • PAGEIOLATCH_SH: Selects reading pages from disk into memory
  • RESOURCE_SEMAPHORE: Queries waiting to get memory to run
  • RESOURCE_SEMAPHORE_QUERY_COMPILE: Queries waiting to get memory to compile
  • SOS_SCHEDULER_YIELD: Query scheduling
  • THREADPOOL: Worker thread exhaustion

I know this seems really limited, but… Uh. What else would you want, here? I could show locking waits, but they aren’t really related to CPU or memory pressure.

Sure, if enough (probably parallel) queries get blocked, it can lead to THREADPOOL waits, but if that’s the cause then you’ll see all the blocking going on in the running queries results.

Seeing a bunch of lock waits up front is just a distraction from the CPU and memory pressure issues I’m trying to surface, and there’s really nothing specifically hardware related to fixing locking problems.

But anyway, if you feel like there are relevant waits missing from the list, feel free to leave a comment or open an issue on GitHub.

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 database performance problems quickly. You can also get a quick, low cost health check with no phone time required.

Related Posts