Sqlserver-dba.com

SQL Server – IO_COMPLETION and how to reduce it

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 ?

Source:Jack Vamvas (http://www.sqlserver-dba.com)
Author: Jack Vamvas (http://www.sqlserver-dba.com)

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact