3

I've had sp_BlizFirst running for a few days every 12 minutes to capture the various statistics.

What I've come to realise though is that it doesn't seem to gather a lot of File Related Performance Stats apart from I/O stalls.

Is there enough info to make a decision on whether I/O is a problem or not based off Wait Statistics and I/O Stalls? I can't find in the Blitz tables any mention of Disk avg. read time, Disk avg. write time, Avg. disk queue length that I thought should be considered too?

Can anyone give some feedback on the below? My Parallelism stats definitely need to be properly looked at (CXPACKET), but I'm curious about my disk i/o performance also. OLDEDB Waits I believe are being caused by Linked Servers and Idera SQL Diagnostic Manager monitoring the instance every 1 minute.

From https://www.sqlskills.com/blogs/paul/are-io-latencies-killing-your-performance/ I read the following: Everyone has their idea of what constitutes good or bad I/O latency, and here’s my take: Excellent: < 1ms Very good: < 5ms Good: 5 – 10ms Poor: 10 – 20ms Bad: 20 – 100ms Shockingly bad: 100 – 500ms WOW!: > 500ms

If I go by that, I'm in the Bad category.

Here are my Wait Statistics:

Server 1

enter image description here

The Average Read I/O Stall is 23.91

The MAX Read I/O Stall was 1162

The Average Write I/O Stall is 0.71

The MAX Write I/O Stall was 139

and here's the full extract.

Server 2

enter image description here

The Average Read I/O Stall is 26.28

The MAX Read I/O Stall was 428

The Average Write I/O Stall is 2.60

The MAX Write I/O Stall was 667

and here's the full extract.

Improve this question

1 Answer 1

Reset to default
4

Disclaimer: I'm one of the authors of sp_BlitzFirst and the First Responder Kit.

The wait stats section is on top for a reason: you need to focus on your top wait types. It doesn't really matter how fast or slow your drives are if SQL Server isn't waiting on them. Great example: say you've got a read-only database hosted on a server with enough memory to cache the entire database, and none of your queries happen to hit TempDB. Once the data's up in cache, does it matter how slow your drives are? Kinda - for maintenance tasks like backups and index rebuilds - but not for end user queries.

Now, look at your server's top 3 waits:

  • CXPACKET 1,530 hours - related to parallelism
  • OLEDB 502 hours (typically harmless, but I leave it in there because sometimes it isn't harmless in large quantities)
  • LATCH_EX 250 hours - usually goes hand in hand with CXPACKET
  • PAGEIOLATCH_SH 191 hours - reading data pages from a data file.

Yo, man - your top wait is coming up on TEN TIMES LARGER than your storage wait.

Forget the storage: you're barking up the wrong tree. Focus on your top wait type. To tune parallelism, go to https://BrentOzar.com/go/cxpacket. The short story:

  • Set Cost Threshold for Parallelism to 50 (some folks will say 40 or 75, and that's fine too)
  • Set MAXDOP to the number of cores in each processor
  • Find the queries reading large amounts of data, and tune them or the indexes they need. My favorite way is sp_BlitzCache @SortOrder = 'reads'
  • Ask SQL Server which indexes it wishes it would have had during query execution. My favorite way is sp_BlitzIndex @GetAllDatabases = 1.
Improve this answer
3
  • Thanks Brent, let's say for example PAGEIOLATCH_SH was at a similiar level to CXPACKET, between Wait Stats and I/O Stalls, is that enough to make a decision on whether storage is indeed slow? Obviously considering indexing also through read queries. I'm just curious because even though it's 4th on the list, there's still high I/O stall values during business hours based off the scale Paul Randal gives.
    – Philip
    Oct 15, 2017 at 11:07
  • That's like asking, "If my speedometer read 200mph, but the rest of my dashboard looked exactly like it does now, what should I do?" It just doesn't make sense: if your speedometer read 200mph, the rest of the numbers would look different.
    – Brent Ozar
    Oct 15, 2017 at 12:34
  • Haha fair enough, cheers Brent.
    – Philip
    Oct 15, 2017 at 20:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.