sp_BlitzFirst – How to Interpret Wait Stats

sp-blitzsp-blitzfirst

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.

Best Answer

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)
Related Question