The DIRTY_PAGE_POLL is a wait type that behaves like CHECKPOINT_QUEUE. This wait type was introduced in SQL Server 2012 with an indirect checkpoint feature. In most cases, the DIRTY_PAGE_POLL wait type can be ignored, it becomes important for all DBAs to have a basic idea of this wait type when it gets generated.

What is the DIRTY_PAGE_POLL Wait Type?

The DIRTY_PAGE_POLL wait type is a background wait that is related to the recovery writer process that is used by the indirect checkpoint feature. The wait type runs continuously in the background of your SQL Server instance. 

The checkpoint process inside the SQL Server is accountable for writing modified data pages from the buffer cache to the database data files on disk. The automatic checkpoint process runs regularly at a set interval of 1 minute. However, the indirect checkpoint feature will allow you to configure a specific checkpoint interval at the database level.

The checkpoint process is very important in the recovery of a SQL Server database when any crash happens.

Case Scenario:

Suppose you were working on a SQL Server instance and suddenly your server crashed. Fortunately, with a simple restart of SQL Server service, all gets back to normal. The first thing the SQL Server does is to start a recovery process. The recovery process checks for the transaction log for uncommitted transactions when the crash occurred and performs a rollback of the transaction.

The recovery process checks whether any data pages that were modified but only at the buffer cache level. If there were any transactions in the buffer cache, then such transactions will be redone by the SQL Server from the transaction log. A busy server will have such transactions where a lot of them were trapped in the buffer cache level. In such cases, the recovery time will be more than normal.

Because of the same, from SQL Server 2012 a new feature was introduced "Target Recovery Time" which you can see in the database properties. By configuring this feature, we can tell SQL Server to write the modified data pages to disc faster.

An example of the database properties with the "Target Recovery Time" is shown below.

Feature at the database level:

A sample example of the database properties with the "Target Recovery Time" is shown below.


In the image, the Target Recovery Time option is configured to 0 by default. If the value is 0, then the indirect checkpoint is not being used. If the value is anything greater than 0, an indirect checkpoint will start to happen as per the specification.

The wait type can be queried from sys.dm_os_wait_stats wait type.


Note: Starting from SQL Server 2016, the Target Recovery Time value is set to 60 seconds by default when you create a new database inside the SQL Server instance. In case you are manually changing to a new value of its own, then do not set the option to a low value. A value of less than 60 seconds can impose an extra load on the storage subsystem. This is because the SQL Server will start writing dirty pages continuously to disk. Be very wise while setting the value in the production environment.  


The DIRTY_PAGE_POLL wait type was introduced in SQL Server 2012. It was an introduction to the indirect checkpoint feature. Starting from SQL Server 2016, the default value was set to 60 for new database creation. The wait type will still get generated in case you are using the feature. The whole point is that the wait type values are for information only and do not impose any performance overhead.


Post a Comment