DIRTY_PAGE_TABLE_LOCK is recorded when a thread waits for access to the list of dirty pages on an Availability Group readable secondary, contended between parallel redo threads (which update it as they apply log) and read queries (which consult it to see consistent data). It is one of the structural costs of reading from a replica that is simultaneously being written by redo.
Its sibling DPT_ENTRY_LOCK is the same fight one level down, at individual entries.
Is It a Problem?
It can be, and its pattern is distinctive: heavy read activity on the secondary plus a heavy update workload on the primary. Crucially, the readers do not have to touch the same tables being updated; the contention is on the dirty page list itself, so unrelated reporting queries can stall behind redo bookkeeping.
If your readable secondary disappoints under load while the primary churns, this wait (with DPT_ENTRY_LOCK) is a prime suspect.
Common Causes
- Read-scale usage of a secondary while the primary sustains high write volume.
- Big redo bursts (index maintenance on the primary) colliding with reporting windows on the secondary.
- High parallel redo activity multiplying list updates.
What To Do
- Confirm the pairing: this wait on the secondary, sustained write rate on the primary, and reporting concurrency at the same time.
- Schedule around it: separate the primary’s heaviest write windows from the secondary’s heaviest read windows where the business allows.
- Reduce primary-side write amplification (index maintenance scope, fill factor tuning) to cut what redo must process.
- If read-scale demands outgrow one secondary, additional readable replicas spread the reader side.
How To See It
Rank it against everything else with Get-WaitStatistics on the secondary, alongside DPT_ENTRY_LOCK and the redo rate.
Part of the SQL Server Wait Types Library.
Related deep dive: HADR_SYNC_COMMIT Wait Type.
Leave a Reply