IO_COMPLETION

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

(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 sort results from/to disk during a sort spill
• 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 the results of a sort spill 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