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

SLEEP_BPOOL_FLUSH

(Back to main page…)

Description:

This wait type is when the checkpoint process realizes that it’s saturating the I/O subsystem (average 20ms write latency, or 100ms during shutdown) and pauses starting more writes for a few milliseconds to let the in-flight writes complete. It could also be from a checkpoint that is blocked for some reason.

(Books Online description: “Occurs when a checkpoint is throttling the issuance of new I/Os in order to avoid flooding the disk subsystem.”)

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 100 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 have not seen this wait type be a noticeable contention point. Checkpoint writes themselves will manifest as ASYNC_IO_COMPLETION waits.

This wait type is one that I usually filter out as a benign wait when doing wait statistics analysis.

However, you may see this wait type be one of the top waits if a checkpoint is blocked for some reason. An example of this would be trying to start a data backup (which performs a checkpoint) while a database is being encrypted for Transparent Data Encryption.

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

  1. Pausing during a checkpoint (in this case, as part of running a manual CHECKPOINT operation)
  2. Pausing during a checkpoint (in this case, as part of a background checkpoint)
  3. Pausing during a checkpoint (in this case, as part of running a BACKUP LOG operation)
  4. Pausing during a checkpoint (in this case, while waiting to start a data backup)

And other similar call stacks.

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

  1. SOS_Task::PostWait+9e
    EventInternal<SuspendQueueSLock>::Wait+1fb
    BPool::FlushCache+707
    HardenAndLogCheckpoint+a67
    CheckpointRU2+23f
    AsynchronousDiskAction::DoFlushCache+98
    AsynchronousDiskAction::ExecuteDeferredAction+246
    AsynchronousDiskPool::PerformActionsSerially+61
    AsynchronousDiskPool::WaitUntilDoneOrTimeout+71
    CheckpointDB2+2eb
    CheckpointWithOptionalTruncate+c5
    DBOpAgent::CheckpointDBHandler+1e
    CStmtCheckpoint::XretExecute+e7
    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
  2. SOS_Task::PostWait+9e
    EventInternal<SuspendQueueSLock>::Wait+1fb
    BPool::FlushCache+707
    HardenAndLogCheckpoint+a67
    SafeCheckpoint+16b
    PerformLogTruncation+373
    ProcessLogTruncRequest+e6
    CheckpointHelper+1c2
    CheckpointLoopWorker+da
    RegisterCheckPtWorker+133
    CheckpointLoop+d3
    CheckpointThread+55
    SOS_Task::Param::Execute+21e
    SOS_Scheduler::RunTask+ab
  3. SOS_Task::PostWait+9e
    EventInternal<SuspendQueueSLock>::Wait+1fb
    BPool::FlushCache+707
    BackupLogOperation::PerformDataCopySteps+c0
    BackupEntry::BackupLog+33d
    CStmtDumpXact::XretExecute+ef
    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
    BPool::FlushCache+707
    HardenAndLogCheckpoint+a67
    CheckpointRU2+278
    AsynchronousDiskAction::DoFlushCache+98
    AsynchronousDiskAction::ExecuteDeferredAction+246
    AsynchronousDiskPool::PerformActionsSerially+61
    AsynchronousDiskPool::WaitUntilDoneOrTimeout+71
    CheckpointDB2+2eb
    BackupDatabaseOperation::PerformDataCopySteps+c2
    BackupEntry::BackupDatabase+314
    CStmtDumpDb::XretExecute+ef
    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+a8