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