Looking at wait types in SQL Server is a great way to get some insight into how SQL Server is operating under the current load. Although wait types in SQL Server are around from the days of SQL Server 2000 (may be earlier), it has improved significantly from SQL Server 2005. New wait types are being added almost in every new SQL Server versions, providing us with great insight to how SQL Server is performing. This post is to understand the DIRTY_PAGE_POLL wait type in SQL Server 2012.
DIRTY_PAGE_POLL is a new wait type added in SQL Server 2012. If you see high values for this wait type on your server, should you be concerned? What is this wait type referring to? Let’s get into the details of what functionality of SQL Server does this refer to.
Before SQL Server 2012, the recovery time of the database(s) after an unexpected shutdown or crash used to depend on the ‘recovery interval’ sp_configure option which in turn was responsible for issuing “automatic checkpoints”. The timing of these automatic checkpoints used to vary a lot depending on the activity on the database and the algorithm had some issues as well to determine how frequently the checkpoint should be run to support the recovery time accurately. There is a myth that checkpoint will run every minute and that is NOT true and its a story for another post.
In SQL Server 2012, a new concept known as “Indirect checkpoints” is added which allows to control the recovery time much more accurately compared to previous versions. This is controlled at the database level unlike the old option in previous versions was at server level. The option is called “TARGET_RECOVERY_TIME” and is set using ALTER DATABASE command. For more details refer BOL at http://msdn.microsoft.com/en-us/library/hh403416(v=sql.110).aspx.
When you use “Indirect Checkpoint”, the background thread constantly looks for dirty pages to be written to disk at a much more aggressive rate (compared to automatic checkpoints) i.e to support the recovery time interval that is set using the “TARGET_RECOVERY_TIME”. Even if you are NOT using the “Indirect checkpoint”, the background thread may be waiting to poll for dirty pages. So, it is possible to see high values for this wait type especially when you aren’t using “Indirect checkpoint” feature. It is a benign wait type and shouldn’t be any concern if you happen to see high values for this wait type on your systems.
So the next question is should we change all databases to use “Indirect checkpoint” when upgraded to SQL Server 2012? While “Indirect checkpoint” has great promise, you should rely on extensive testing for your workload before using this feature as it changes the how much data is being written to the disk constantly.