0

I have some BlitzFirst Wait Stats that look as follows: enter image description here

Is this the proper way to interpret this? During the 30 second sample, there were a total of 627.1 seconds of IO_Completion waiting, an average of .5 waits per core per second and a total of 20,723 of these waits, each averaging 30.3 MS?

Also, I am not using the Since Startup option.

  • Also run sp_BlitzIndex to see what if there are any high-impact missing indexes. Mode=0 for the high priority stuff. Run sp_Blitz too to see if there's anything critical in there. – Tara Kizer Jun 6 '17 at 20:30
5

So let's do a quick rundown of your top two waits:

  • IO_COMPLETION - waiting to write to storage
  • PAGEIOLATCH* - waiting to read from storage

In 30 seconds, your queries spent 1,655 seconds waiting on storage.

Your storage is probably slow - if you skip down to the next section in sp_BlitzFirst's output, it will show which data and log files SQL Server was waiting on, and for how long. However, before you leap to playing Pin The Blame On The SAN Administrator, consider:

  • Tuning indexes & queries to reduce the amount of data read from disk (I'd suggest using sp_BlitzIndex and sp_BlitzCache for that, although I'm biased (disclaimer: I'm one of the coauthors))
  • Adding memory to cache data, which will reduce PAGEIOLATCH, thereby easing pressure on the storage, and will likely reduce the IO_COMPLETION waits too
  • Spending a whole lot of money on storage (you might guess that the above two fixes are above this one for a reason)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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