SLEEP_BPOOL_FLUSH

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