COMMIT_TABLE

(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 is when a thread is waiting for access to the in-memory portion of the hidden commit table (sys.syscommittab) that helps underpin the Change Tracking functionality. This table contains one row for each transaction that has changed a table that is being change tracked, and is accessed from many places in the Storage Engine (e.g., when a checkpoint occurs, during periodic automatic cleanup, when a transaction commits).

(Books Online description: N/A)

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 362 in 2008 and 2008 R2, 409 in 2012, and 425 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 have heard anecdotal evidence of this wait type being a bottleneck when change tracking is enabled on many tables with a workload with many small transactions updating those tables. My guess is that the solution would be to scale back the usage of change tracking or investigate an alternative method of tracking changes.

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

1. Waiting for access to the change table (in this case, when performing an automatic checkpoint)
2. Waiting for access to the change table (in this case, when committing a transaction)
3. Waiting for access to the change table (in this case, when creating the hidden database snapshot as part of a DBCC CHECKDB)

And other similar call stacks.

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

1. SOS_Task::PostWait+9e
   EventInternal<SuspendQueueSLock>::Wait+2ca
   SOS_RWLock::GetLongWait+10b
   SOS_RWLock::GetLock+122
   CommitTableAutoLock::GetLock+a3
   HardenAndLogCheckpoint+4f0
   CheckpointRU2+3b8
   AsynchronousDiskAction::DoFlushCache+98
   AsynchronousDiskAction::ExecuteDeferredAction+246
   AsynchronousDiskPool::PerformActionsSerially+61
   AsynchronousDiskPool::WaitUntilDoneOrTimeout+71
   CheckpointDB2+2eb
   ProcessCheckpointRequest+29d
   CheckpointHelper+1d3
   CheckpointLoopWorker+da
   RegisterCheckPtWorker+133
   CheckpointLoop+d3
   CheckpointThread+55
   SOS_Task::Param::Execute+21e
   SOS_Scheduler::RunTask+ab
2. SOS_Task::PostWait+9e
   EventInternal<SuspendQueueSLock>::Wait+2ca
   SOS_RWLock::GetLongWait+10b
   CommitTableMultiAutoLock::GetExclusive+12a
   XactRM::CommitInternal+486
   FullXactImp::Commit+33f
   CMsqlXactInternalReadWrite::Commit+19
   CMsqlXactImp::Commit+1d6
   CXStmtXactEnd::XretExecute+ce
   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
3. SOS_Task::PostWait+9e
   EventInternal<SuspendQueueSLock>::Wait+2ca
   SOS_RWLock::GetLongWait+10b
   ReplicaState::GetCommitLock+1a0
   DBMgr::SyncAndLinkReplicaSetupPhase+769
   DBMgr::CreatePhasedTransientReplica+4b6
   DBMgr::CreateTransientReplica+fd
   DBDDLAgent::CreateReplica+183
   UtilDbccCreateReplica+7a
   UtilDbccCheckDatabase+9a2
   DbccCheckDB+23f
   DbccCommand::Execute+153
   CStmtDbcc::XretExecute+7cd
   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