{"id":1041,"date":"2025-06-06T08:00:00","date_gmt":"2025-06-06T13:00:00","guid":{"rendered":"https:\/\/www.sqltabletalk.com\/?p=1041"},"modified":"2025-06-10T09:58:35","modified_gmt":"2025-06-10T14:58:35","slug":"sql-server-indirect-checkpoints-target-recovery-time","status":"publish","type":"post","link":"https:\/\/www.sqltabletalk.com\/?p=1041","title":{"rendered":"Optimizing SQL Server with Indirect Checkpoints and Target Recovery Time"},"content":{"rendered":"

Introduction<\/h2>\n

A checkpoint is a background process that writes dirty pages to disk. A checkpoint performs a full scan of the pages in the buffer pool, lists all the dirty pages that are yet to be written to disk, and finally writes those pages to disk. In SQL instances that do not have many dirty pages in the buffer pool, this is a trivial operation. However, with SQL instances that have OLTP databases, use more memory and\/or involve sequential scanning of all pages, the performance of the system could be impacted.<\/p>\n

With SQL Server 2012, indirect checkpoints<\/strong> were introduced. In this case, the dirty page manager manages the dirty page list and keeps track of all the dirty page modifiers of the database. By default, it runs every 60 seconds and tracks the dirty pages that need to be flushed.<\/p>\n

For example, if an indirect checkpoint is set to 120 seconds, then individual pages would be flushed around 120 seconds after they were made dirty. This will impact I\/O and may lead to I\/O spikes. I\/O also depends on the number of dirty modifiers lists of the database. We may even see a significant spike in the set target interval and see small hits as the pages change. This eliminates overhead from tracing and flushing the dirty pages, which results in a significant improvement in backup performance.<\/p>\n

To view your current target recovery time, you can query sys.databases<\/code>.<\/p>\n

SELECT name, target_recovery_time_in_seconds\nFROM sys.databases;<\/code><\/pre>\n
On a database where an automatic checkpoint mode is set, tracing the entire buffer pool is necessary to evaluate for possible dirty pages. On the other hand, with an indirect checkpoint, only those pages of the buffer pool that are dirty must be dealt with.<\/p>\n
For example, let\u2019s consider a system with 2 TB of memory that contains approximately 250 million buffer units and has 100 dirty pages. Assume that it requires 5 CPU cycles to trace and identify the status of each buffer unit. To traverse 250 million buffer units, it would require 1.25 trillion CPU cycles\u2014and to what? Simply to find 100 positive dirty pages in the automatic checkpoint mode! However, 100 dirty pages are hardly anything with the indirect checkpoint mode. The entire buffer pool scanning time is eliminated.<\/p>\n
How it Works<\/h2>\n
When target_recovery_time<\/code> is set at the database level, indirect checkpoint is activated, and a dirty page manager is created for the database. The dirty page manager maintains the dirty page lists (DPList) for the database. Partitioned DPLists help improve concurrency and avoid any contention, with each worker thread running on a scheduler writing to its corresponding dirty page list. Because of this, when the transactional activity on the database is low (<3 k\u20135 k transactions per second), the overhead of tracking dirty pages in DPList is minimal and doesn’t impact the overall throughput of the workload running on the database with indirect checkpoint.<\/p>\n
When the total number of dirty pages exceeds the dirty page threshold (calculated internally from target_recovery_time<\/code>), the recovery writer wakes up, swaps the list, and starts iterating through the DPList in a loop\u2014starting with the longest DPList. It collects the page IDs, sorts the pages in the list to optimize and bundle writes together, and finally posts asynchronous write requests to write the pages.<\/p>\n
These are good improvements for the most part; however, in some situations on a busy system, you can still see contention in this process. The recovery writer handles dirty pages by organizing, sorting, and posting writes to optimize database performance. When transactional activity is high, contention can occur between the recovery writer and I\/O threads, leading to slower processing. To maintain recovery time targets, worker threads may step in to assist the recovery writer, ensuring efficient handling of dirty pages despite increased workload. While this strategy helps meet recovery goals, it can temporarily impact overall system performance.<\/p>\n
This performance impact may occur because, in some situations, indirect checkpoint can cause uneven distribution of dirty pages\u2014especially in databases like tempdb<\/code>\u2014leading to long dirty page lists. When these lists grow too large, the recovery writer struggles to handle them efficiently, causing contention with I\/O threads and consuming additional CPU resources. In severe cases, this can lead to non-yielding scheduler errors, where the system encounters performance issues due to excessive spinlock contention and delays in processing.<\/p>\n
If you see non-yielding schedulers in your environment due to this, you will see the following symptoms in the error log.<\/p>\n
Process 0:0:0 (0x34c0) Worker 0x000000FCD89BC160 appears to be non-yielding on Scheduler 9. Thread creation time: 13140343858116. Approx Thread CPU Used: kernel 0 ms, user 60968 ms. Process Utilization 13%. System Idle 86%. Interval: 70234 ms.\n\nA time-out occurred while waiting for buffer latch -- type 3, bp 0000010C1900C6C0, page 4:323520, stat 0x10f, database id: 2, allocation unit Id: 6488064, task 0x000000FE6076D088 : 0, waittime 300 seconds, flags 0x19, owning task 0x00000102ED0C8108. Not continuing to wait.<\/code><\/pre>\n
To detect if your SQL Server instance is experiencing excessive spinlock contention on DPList\u2014even if there is no non-yielding scheduler detected\u2014you can use the following diagnostics available in SQL Server.<\/p>\n
Monitoring<\/h2>\n
Use sys.dm_os_spinlock_stats<\/code> DMV<\/strong> to look for a high number of spins and backoff events over periods of time. Run the following query multiple times at one-minute intervals. Calculate the difference between snapshots to identify the number of spins and backoffs between consecutive snapshots.<\/p>\n
Note:<\/strong> It is expected to see some spins and backoffs due to healthy spinlock contention between the recovery writers and I\/O threads, so you will need to compare different snapshots to see if exponential backoff continues to increase.<\/p>\n
SELECT GETDATE() AS [Date Captured], *\nFROM sys.dm_os_spinlock_stats\nWHERE name = 'DP_LIST';<\/code><\/pre>\n
You can use the following script to capture an Extended Event for spinlock backoff for DPList in your environment. The call stack captured from the XEvents can be analyzed using SQLCallStackResolver<\/a> developed by Arvind Shyamsundar.<\/p>\n
Interpretation Guidelines<\/h3>\n\n\n\n\n\n\n\n\n
Metric<\/th>\nWhat it means<\/th>\nWhen to worry<\/th>\n<\/tr>\n<\/thead>\n
collisions<\/strong><\/td>\nNumber of times threads tried to acquire the spinlock simultaneously<\/td>\nHigh values are normal under load<\/td>\n<\/tr>\n
spins<\/strong><\/td>\nTotal spin attempts<\/td>\nHigh with low collisions = inefficiency<\/td>\n<\/tr>\n
spins_per_collision<\/strong><\/td>\nAvg spins per collision<\/td>\n>1000 may indicate contention<\/td>\n<\/tr>\n
backoffs<\/strong><\/td>\nTimes a thread gave up spinning<\/td>\nHigh and increasing = bad contention<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Azure SQL<\/h2>\n
All of this is applicable to Azure SQL: SQL Server on Azure VM, Azure SQL Managed Instance (SQL MI), and Azure SQL Database (SQL DB).<\/p>\n
Azure SQL Database does support indirect checkpoints, but with some important distinctions compared to SQL Server and Azure SQL Managed Instance.<\/p>\n