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

WRITE_COMPLETION

(Back to main page…)

Description:

This wait type represents a variety of synchronous write operations for allocation pages, non-log block log writes, and database snapshots. Examples uses include:

  • 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

(Books Online description: “Occurs when a write operation is in progress.”)

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:

2008

Removed in SQL Server version:

N/A

Extended Events wait_type value:

The map_key value in sys.dm_xe_map_values is 577 in 2008 and 2008 R2, 628 in 2012, and 644 in 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. Pushing a page into a database snapshot before it is changed in the snapshot’s source database (in this case, while changing a text page that stores part of the statistics for a table, while updating statistics on a table
  2. Writing a VLF header to the log file (in this case, while growing the log while inserting a log record for an insert of a new record in a table)
  3. Writing a file header page to disk (in this case, while creating for the files during a CREATE DATABASE operation)
  4. Fixing up the log tail (i.e. making the log writeable) (in this case, while completing crash recovery on a new log file created during a CREATE DATABASE operation)
  5. Directly writing a PFS to disk (in this case, while growing a file to make space for an allocation for a page that’s splitting)

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

  1. SOS_Task::PostWait+90
    FCB::SyncWritePreemptive+306
    FCB::CopyPageToReplicas+4b2
    BUF::CopyOnWrite+60
    BPool::PrepareToDirty+180
    TextPageRef::ModifyTextNode+11f
    BlobBase::ModifyDataFragment+2cc
    BlobBase::ModifyDataNode+963
    Blob::RWBlob+478
    Blob::RWBlob+5fb
    Blob::ReadWriteAt+139
    BlobManager::ReplaceAtWithILB+570
    PrepareIlbForSetData+60d
    LongRecord::PrepareNewColumns+9e4
    LongRecord::SetDataInternal+28e
    LongRecord::SetData+2ba
    IndexDataSetSession::SetDataInternal+94b1
    DatasetSession::SetData+16d
    RowsetNewSS::SetData+6a
    CMEDScanBase::FUpdateSingleRow+be
    CMEDScan::FUpdateRow+61f
    CMEDCatYukonObject::SetStatsBlob+485
  2. SOS_Task::PostWait+9e
    EventInternal<SuspendQueueSLock>::Wait+1fb
    FCB::SyncWrite+10a
    SQLServerLogMgr::FormatVirtualLogFile+175
    SQLServerLogMgr::FormatLogFile+e5
    FileMgr::InternalGrowFile+770
    SQLServerLogMgr::GrowAFile+6eb
    SQLServerLogMgr::ReserveAndAppend+292
    XdesRMReadWrite::GenerateLogRec+615
    PageRef::InsertRows+2594
    IndexPageRef::Insert+502
    InsertRecordOnPage+61b
    IndexDataSetSession::InsertSmallRecord+10ca
    IndexDataSetSession::InsertRowInternal+2de1
    DatasetSession::InsertRow+163
    RowsetNewSS::InsertRow+26
    CValRow::SetDataX+5b
    CQScanScalarInsertNew::GetRow+94
    CQueryScan::GetRow+81
    CXStmtQuery::ErsqExecuteQuery+36d
    CXStmtDML::XretDMLExecute+31c
  3. SOS_Task::PostWait+9e
    EventInternal<SuspendQueueSLock>::Wait+1fb
    FCB::SyncWrite+10a
    FCB::PageWriteInternal+108
    GlobalFileHeader::CreateInitialPage+3b3
    GlobalFileHeader::WriteInitialPage+50
    FCB::InitHeaderPage+25c
    DBMgr::CreateAndFormatFiles+b5a
    CStmtCreateDB::CreateLocalDatabaseFragment+712
    DBDDLAgent::CreateDatabase+f7
    CStmtCreateDB::XretExecute+dc1
    CMsqlExecContext::ExecuteStmts<1,1>+427
    CMsqlExecContext::FExecute+a43
    CSQLSource::Execute+86c
    process_request+a57
    process_commands+4a3
    SOS_Task::Param::Execute+21e
    SOS_Scheduler::RunTask+ab
  4. SOS_Task::PostWait+9e
    EventInternal<SuspendQueueSLock>::Wait+1fb
    FCB::SyncWrite+10a
    SQLServerLogMgr::FixupLogTail+427
    SQLServerLogMgr::MakeLogRecordsPossible+92
    SQLServerLogMgr::MakeLogWriteable+122
    RecoveryMgr::PhysicalCompletion+231
    RecoveryUnit::CompletePhysical+e9
    RecoveryUnit::PhaseEnd+a6
    StartupCoordinator::NotifyPhaseEnd+3b
    DBTABLE::Startup+1167
    DBMgr::StartupDB+6c6
    CStmtCreateDB::CreateLocalDatabaseFragment+81a
    DBDDLAgent::CreateDatabase+f7
    CStmtCreateDB::XretExecute+dc1
    CMsqlExecContext::ExecuteStmts<1,1>+427
    CMsqlExecContext::FExecute+a43
    CSQLSource::Execute+86c
    process_request+a57
    process_commands+4a3
    SOS_Task::Param::Execute+21e
    SOS_Scheduler::RunTask+ab
  5. SOS_Task::PostWait+90
    EventInternal<SuspendQueueSLock>::Wait+1f9
    FCB::SyncWrite+104
    FCB::PageWriteInternal+55
    InitPFSPages+3d8
    InitDBAllocPages+96
    FileMgr::InternalGrowFile+a7b
    FGCB::GrowAFile+8a6
    MakeSpaceInFileGroup+4a
    AllocationReq::AllocatePages+2514
    AllocationReq::Allocate+f3
    AllocateHoBtDataPage+5fd
    IndexPageManager::AllocatePage+1b8
    SplitPage+b59
    IndexDataSetSession::InsertSmallRecord+1b5c
    IndexDataSetSession::InsertRowInternal+2d11
    DatasetSession::InsertRow+163
    RowsetNewSS::InsertRow+26
    CValRow::SetDataX+5b
    CQScanUpdateNew::GetRow+3f7
    CQScanSortNew::BuildSortTable+2a6