(Check out my Pluralsight online training course: SQL Server: Performance Troubleshooting Using Wait Statistics and my comprehensive library of all wait types and latch classes.)
The official definition of these wait types are:
- IO_COMPLETION: Occurs while waiting for I/O operations to complete. This wait type generally represents non-data page I/Os. Data page I/O completion waits appear as PAGEIOLATCH_* waits.
- WRITE_COMPLETION: Occurs when a write operation is in progress.
I promised many of the people who sent me wait statistics recently that I would write a blog post giving more detailed information on when these wait types occur, so here it is.
I used the Extended Events code in my post How to determine what causes a particular wait type to watch for these wait types occurring and then ran a variety of operations and analyzed the call stacks. There are way too many occurrences to document them all here, so I’ll summarize my findings below.
Note that these are not lists are not exhaustive, but you get the idea of the kinds of operation where these wait types occur.
- Reading log blocks from the transaction log (during any operation that causes the log to be read from disk – e.g. recovery)
- Reading allocation bitmaps from disk (e.g. GAM, SGAM, PFS pages) during many operations (e.g. recovery, DB startup, restore)
- Writing intermediate sort buffers to disk (these are called ‘Bobs’)
- Reading and writing merge results from/to disk during a merge join
- Reading and writing eager spools to disk
- Reading VLF headers from the transaction log
- Writing any page to a database snapshot (e.g. while running DBCC CHECK*, which is often the most common cause of this wait type)
- Writing VLF headers while creating or growing a transaction log file
- Writing a file’s header page to disk
- Writing portions of the transaction log during database startup
- Writing allocation pages to disk when creating or growing a data file
These aren’t waits that I’d generally be concerned about, and I’d expect the individual resource wait times to be in line with those of the read and write latencies of the instance.