sp_PressureDetector: Update Roundup

Busy Bee

---

I’ve been spending a lot of time lately working on my free scripts. And, I know, I’ve been recording videos about them, but some folks out there like to read things.

In this post, I’ll be talking about some additions and changes to sp_PressureDetector, my script to quickly detect server bottlenecks like CPU, memory, disk, locking, and more. Well, maybe not more. I think that’s all of them.

Disk Metrics

---

I added  high-level disk metrics similar to what’s available in other popular scripts to mine. Why? Sometimes it’s worth looking at, to prove you should add more memory to a server so you’re less reliant on disk.

Especially in the cloud, where everything is an absolute hellscape of garbage performance that’s really expensive.

By default, I’ll show you results where either read or write latency is over 100ms, but you can change that with the following parameter:

EXEC dbo.sp_PressureDetector
    @minimum_disk_latency_ms = 5;

Results may vary. Mine look like this.

CPU Time

---

This only works for SQL Server Enterprise Edition right now, because it uses a DMV related to Resource Governor.

In the wait stats output, you’ll see how many hours of CPU time queries have consumed since server startup. I know, someone could clear out the Resource Governor stuff, but I’m willing to embrace that as an incredible rarity.

I’m also aware of the fact that I could get similar information from sys.dm_os_schedulers, but that’s only available in SQL Server 2016+, and I sometimes have to support older versions.

On the fence a bit about doing some checks, but right now it’s like…

• Are we on Enterprise Edition? Use the Resource Governor thing
• Are we on Standard Edition? Is it 2016 or better? Use the other thing
• If not, then what?

I wrote a similar bit of code into sp_BlitzFirst, and the fallback is to sum all the CPU time from queries in the plan cache, but that’s awfully iffy. Most plan caches I see, all the plans are less than 24 hours old.

If I figure something else out, I’ll work on it, but for now I’m sticking with this.

New Columns

---

Down in the CPU details section, there are some new columns that detail things like

These are useful, especially during THREADPOOL demos, ha ha ha.

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