SQL Server Books online defines IO_COMPLETION as “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”
As with ASYNC_IO_COMPLETION it is a symptom of an IO bottleneck. Cross check by identifying other wait stat indicators such as: ASYNC_IO_COMPLETION, WRITELOG, PAGEIOLATCH_X. The appearance of at least one of these along with IO_COMPLETION – near the top of the wait stats indicates a serious IO bottleneck
It’s useful to differentiate between WRITELOG – which occurs when waiting for a log flush to occur – and IO_COMPLETION. IO_COMPLETION reports on non-data pages and essentially is a SQL Server thread awaiting the kernel to return the IO request status.
The complexity in diagnosing the source of the problem, is to understand the OS, SQL server, IO configurations which could include drive profile, controllers etc.Tools such as SQL Profiler & Perfmon assist
Any SQL Server task waits for IO completion – this is completely normal part of SQL Server processing. But when the wait for IO completion is slow there is a problem
A few issues to consider and approached to minimising IO_COMPLETION.
1) Maintain Random access \ Sequential access files on different drives. Create an io profile of the database server and balance drive appropriately .
2) Commit some analysis of poorly running queries. Is excessive table and index scanning occurring ? This could be causing many round-trips to the disk
3) End to end analysis – for example, is IO being throttled ?
Republished from http://www.sqlserver-dba.com.
Republished from SQL Server DBA [65 clicks].
Read the original version here [32134 clicks].