(Republishing, or using this info in a commercial product/website, is prohibited without permission. All other uses are permitted. If in doubt, please ask.)

IO_COMPLETION

(Back to main page…)

Description:

This wait type represents a variety of synchronous read and write operations in data files are not related to tables, plus reads from the transaction log. Examples uses include:

  • 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

Note that hash spills do not cause this wait – they cause SLEEP_TASK waits.

(Books Online description: “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.”)

Questions/comments on this wait type? Click here to send Paul an email, especially if you have any information to add to this topic.

Added in SQL Server version:

Pre-2005/2005

Removed in SQL Server version:

N/A

Extended Events wait_type value:

The map_key value in sys.dm_xe_map_values is 97 in all versions through 2014 RTM. After 2014 RTM, you must check the DMV to get the latest value as some map_key values have changed in later builds.

Other information:

I don’t have any specific troubleshooting guidance for this wait type, but as general guidance for I/O wait types, if the wait time is higher than acceptable I/O latencies for your environment, investigate whether the I/O subsystem is overloaded or has a configuration problem. See the PAGEIOLATCH_SH wait type for more information.

Known occurrences in SQL Server (list number matches call stack list):

  1. Reading a log block from the log (in this case, while rolling back a transaction)
  2. Reading a PFS page from disk (in this case, while initializing the various per-database allocation bitmaps in a newly created data file)
  3. Reading a sort buffer from disk (in this case, while sorting the results of a scan)
  4. Reading the sorted input to a merge join from disk
  5. Reading a VLF header from the log (in this case, while growing the log while inserting a log record to mark changes in a PFS while bulk-loading into a heap)

And many more similar stacks.

Abbreviated call stacks (list number matches known occurrences list):

  1. SOS_Task::PostWait+90
    EventInternal<SuspendQueueSLock>::Wait+1f9
    SQLServerLogMgr::CheckLogBlockReadComplete+1e8
    LogConsumer::GetNextLogBlock+b91
    SQLServerLogIterBackLink::GetNext+1b7
    XdesRMReadWrite::RollbackToLsn+36f
    XdesRMFull::RollbackNested+28b
    XactRM::RollbackNestedXact+ff
    FullXactImpBase::RollbackNestedXact+53
    CMsqlXactInternalReadWrite::RollbackNestedXact+5d
    CMsqlXactImp::RollbackNestedXact+19
    CAutoMsqlXact::RollbackNestedXact+5b
    CXStmtDML::RollbackNestedXact+1b
    CXStmtDML::FinishOnExceptionImp+1a4
  2. SOS_Task::PostWait+9e
    EventInternal<SuspendQueueSLock>::Wait+1fb
    FCB::PageRead+130
    DirectlyMarkPFSPage+107
    InitGAMIntervalPages+52a
    InitDBAllocPages+d0
    FileMgr::CreateNewFile+177
    AsynchronousDiskAction::ExecuteDeferredAction+8f
    AsynchronousDiskWorker::ThreadRoutine+15c
    SubprocEntrypoint+a7f
    SOS_Task::Param::Execute+21e
    SOS_Scheduler::RunTask+ab
  3. SOS_Task::PostWait+90
    EventInternal<SuspendQueueSLock>::Wait+1f9
    BobMgr::GetBuf+1d3
    get_bob_buf+21
    addrun+169
    soAllocRecBuf+399
    RowsetSorted::InsertRow+2b97
    RowsetChangeSort::InsertRow+19
    CValRowNoHrow::SetDataX+48
    CQScanSortNew::PushRow+34
    CQScanSortNew::BuildSortTable+28f
    CQScanSortNew::OpenHelper+c0
    CQScanNew::OpenHelper+41
    CQScanUpdateNew::Open+179
    CQueryScan::StartupQuery+240
    CXStmtQuery::SetupQueryScanAndExpression+2bd
    CXStmtQuery::InitForExecute+34
    CXStmtQuery::ErsqExecuteQuery+36a
    CXStmtDML::XretDMLExecute+2f3
    CXStmtDML::XretExecute+ad
    CMsqlExecContext::ExecuteStmts<1,1>+400
  4. SOS_Task::PostWait+90
    EventInternal<SuspendQueueSLock>::Wait+1f9
    merge_getnext+3f3
    mergenext+12
    RowsetSorted::GetNextRows+e3
    CQScanRowsetNew::GetRowWithPrefetch+9b
    CQScanRowsetNew::GetRowHelper+51
    CQScanSortNew::GetRowHelper+84
    CQScanMergeJoinMMNew::GetRow+547
    CQScanMergeJoinNew::GetRow+401
    CQScanSortNew::BuildSortTable+2a6
    CQScanSortNew::OpenHelper+c0
    CQScanMergeJoinNew::Open+ca
    CQScanSortNew::BuildSortTable+3a
    CQScanSortNew::OpenHelper+c0
    CQScanNew::OpenHelper+41
    CQScanRowsetNew::OpenHelper+21
    CQScanRangePrefetchDelayNew::Open+60
    CQScanNLJoinNew::Open+24
    CQScanNew::OpenHelper+41
    CQScanRowsetNew::OpenHelper+21
    CQScanRangePrefetchDelayNew::Open+60
  5. SOS_Task::PostWait+9e
    EventInternal<SuspendQueueSLock>::Wait+1fb
    FCB::SyncRead+134
    SQLServerLogMgr::ReadFileHdr+53
    SQLServerLogMgr::DiscoverVirtualLogFiles+83
    SQLServerLogMgr::PrepareGrowFile+69
    FileMgr::InternalGrowFile+984
    SQLServerLogMgr::GrowAFile+6eb
    SQLServerLogMgr::ReserveAndAppend+292
    XDES::GenerateNonXactLogRec+f8
    PageRef::SetFreeSpaceInPFS+336
    ChangeFreeSpaceValue+20d
    CHeapBuild::InsertRow+13a
    RowsetBulk::InsertRow+2e5f
    CValRow::SetDataX+5b
    CQScanUpdateNew::GetRow+43d
    CQScanXProducerNew::GetRowHelper+366
    CQScanXProducerNew::GetRow+15
    FnProducerOpen+57
    FnProducerThread+8c3
    SubprocEntrypoint+a7f