WRITE_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 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