PAGELATCH_DT Wait Type Explained

The PAGELATCH_DT wait type represents contention for a page-level latch in Destroy mode. When SQL Server needs to deallocate a page from the buffer pool, it must first acquire an exclusive DT (Destroy) latch to ensure no other processes are accessing the page contents. This latch mode is the most restrictive, preventing all other access types including shared reads.

The buffer pool manager coordinates these operations through the scheduler system. When a DROP TABLE, TRUNCATE TABLE, or similar deallocating operation executes, SQL Server must process each affected page through a controlled destruction sequence. First, the page is marked for deallocation in the allocation metadata. Then, a DT latch request is queued through the latch manager. If other processes hold latches on the same page (even shared latches for reads), the destroy request waits.

SQL Server 2016 introduced significant improvements to parallel page deallocation for large table operations. SQL Server 2019 and later versions optimized the latch acquisition sequence during batch deallocation operations, reducing wait times for consecutive page destruction. SQL Server 2022 added better scheduling prioritization for destroy operations to prevent excessive blocking of concurrent workloads.

The wait becomes problematic when deallocating large objects while concurrent queries access nearby pages or when multiple sessions perform DDL operations simultaneously. Heavy OLTP workloads can extend these waits if readers continuously acquire shared latches on pages scheduled for destruction.

-- Identify sessions causing PAGELATCH_DT waits with blocking details
SELECT 
    ws.session_id,
    ws.wait_type,
    ws.wait_time_ms,
    ws.resource_description,
    r.command,
    r.status,
    t.text AS query_text,
    r.blocking_session_id
FROM sys.dm_exec_requests r
INNER JOIN sys.dm_exec_session_waits ws ON r.session_id = ws.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE ws.wait_type = 'PAGELATCH_DT'
    AND ws.waiting_tasks_count > 0
ORDER BY ws.wait_time_ms DESC;

-- Analyze buffer pool pages with high latch contention
SELECT 
    bp.database_id,
    DB_NAME(bp.database_id) AS database_name,
    bp.file_id,
    bp.page_id,
    bp.page_type,
    bp.allocation_unit_id,
    bp.row_count,
    bp.free_space_in_bytes,
    bp.is_modified
FROM sys.dm_os_buffer_descriptors bp
WHERE EXISTS (
    SELECT 1 FROM sys.dm_exec_session_waits sw 
    WHERE sw.wait_type = 'PAGELATCH_DT' 
    AND sw.resource_description LIKE '%' + CAST(bp.database_id AS VARCHAR) + ':' + CAST(bp.file_id AS VARCHAR) + ':' + CAST(bp.page_id AS VARCHAR) + '%'
);

-- Monitor current DDL operations that may cause page destruction waits
SELECT 
    r.session_id,
    r.request_id,
    r.start_time,
    r.status,
    r.command,
    r.percent_complete,
    r.estimated_completion_time,
    t.text AS sql_text,
    r.wait_type,
    r.last_wait_type
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.command IN ('DROP TABLE', 'TRUNCATE TABLE', 'DROP INDEX', 'ALTER TABLE')
   OR t.text LIKE '%DROP%' 
   OR t.text LIKE '%TRUNCATE%'
ORDER BY r.start_time;

-- Historical wait statistics for PAGELATCH_DT patterns
SELECT 
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    max_wait_time_ms,
    signal_wait_time_ms,
    wait_time_ms - signal_wait_time_ms AS resource_wait_time_ms,
    CASE 
        WHEN waiting_tasks_count > 0 
        THEN wait_time_ms / waiting_tasks_count 
        ELSE 0 
    END AS avg_wait_time_ms
FROM sys.dm_os_wait_stats 
WHERE wait_type = 'PAGELATCH_DT'
    AND waiting_tasks_count > 0;

-- Identify objects involved in current deallocation operations
SELECT DISTINCT
    o.name AS object_name,
    o.type_desc,
    s.name AS schema_name,
    i.name AS index_name,
    au.allocation_unit_id,
    au.type_desc AS allocation_type
FROM sys.allocation_units au
INNER JOIN sys.partitions p ON au.container_id = p.partition_id
INNER JOIN sys.objects o ON p.object_id = o.object_id
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
LEFT JOIN sys.indexes i ON p.object_id = i.object_id AND p.index_id = i.index_id
WHERE au.allocation_unit_id IN (
    SELECT DISTINCT bp.allocation_unit_id
    FROM sys.dm_os_buffer_descriptors bp
    WHERE EXISTS (
        SELECT 1 FROM sys.dm_exec_session_waits sw 
        WHERE sw.wait_type = 'PAGELATCH_DT'
    )
);

Identify and kill blocking DDL operations causing excessive waits This script helps resolve immediate contention by identifying long-running DDL operations that may be causing extended PAGELATCH_DT waits.

-- WARNING: This kills active sessions. Test in development first.
-- Identify sessions to kill that are causing prolonged DDL blocking
DECLARE @SessionsToKill TABLE (session_id INT, kill_reason VARCHAR(200));

INSERT INTO @SessionsToKill
SELECT DISTINCT 
    r.session_id,
    'Long-running DDL operation causing PAGELATCH_DT waits: ' + r.command
FROM sys.dm_exec_requests r
INNER JOIN sys.dm_exec_session_waits sw ON r.session_id = sw.session_id
WHERE sw.wait_type = 'PAGELATCH_DT'
    AND r.command IN ('DROP TABLE', 'TRUNCATE TABLE', 'DROP INDEX')
    AND r.total_elapsed_time > 300000  -- 5 minutes
    AND sw.wait_time_ms > 60000;       -- 1 minute wait

-- Review sessions before killing
SELECT * FROM @SessionsToKill;

-- Uncomment to execute kills after review
-- DECLARE @sql NVARCHAR(100), @session_id INT;
-- DECLARE kill_cursor CURSOR FOR SELECT session_id FROM @SessionsToKill;
-- OPEN kill_cursor;
-- FETCH NEXT FROM kill_cursor INTO @session_id;
-- WHILE @@FETCH_STATUS = 0
-- BEGIN
--     SET @sql = 'KILL ' + CAST(@session_id AS NVARCHAR(10));
--     EXEC sp_executesql @sql;
--     FETCH NEXT FROM kill_cursor INTO @session_id;
-- END;
-- CLOSE kill_cursor;
-- DEALLOCATE kill_cursor;

Implement batch DDL operations to reduce contention Break large DROP/TRUNCATE operations into smaller batches to minimize latch contention duration.

-- Example: Batch delete large table instead of DROP to reduce latch pressure
-- Replace single DROP TABLE with batched deletion
DECLARE @BatchSize INT = 10000;
DECLARE @RowsDeleted INT = @BatchSize;
DECLARE @TableExists BIT = 1;

-- Verify table exists before starting
IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE name = 'YourLargeTable' AND type = 'U')
    SET @TableExists = 0;

WHILE @RowsDeleted = @BatchSize AND @TableExists = 1
BEGIN
    DELETE TOP(@BatchSize) FROM YourLargeTable;
    SET @RowsDeleted = @@ROWCOUNT;
    
    -- Brief pause to allow other operations to proceed
    WAITFOR DELAY '00:00:01';
    
    -- Check if other sessions are waiting
    IF EXISTS (
        SELECT 1 FROM sys.dm_exec_session_waits 
        WHERE wait_type = 'PAGELATCH_DT' AND wait_time_ms > 5000
    )
    BEGIN
        -- Longer pause if contention detected
        WAITFOR DELAY '00:00:05';
    END;
    
    PRINT 'Deleted ' + CAST(@RowsDeleted AS VARCHAR(10)) + ' rows';
END;

-- Drop the empty table structure
IF @TableExists = 1 AND NOT EXISTS (SELECT 1 FROM YourLargeTable)
    DROP TABLE YourLargeTable;

Optimize buffer pool management during large operations Adjust memory settings temporarily during planned large DDL operations to reduce page latch pressure.

-- Temporarily increase buffer pool scan frequency during large operations
-- This helps clear destroyed pages from memory faster
DECLARE @OriginalScanFreq INT;

-- Capture current setting
SELECT @OriginalScanFreq = value_in_use 
FROM sys.configurations 
WHERE name = 'recovery interval (min)';

-- Temporarily reduce recovery interval to increase checkpoint frequency
-- This clears dirty pages faster, reducing latch contention
EXEC sp_configure 'recovery interval (min)', 1;
RECONFIGURE;

-- Run your large DDL operation here
-- [Your DDL operations]

-- Restore original setting after operation completes
EXEC sp_configure 'recovery interval (min)', @OriginalScanFreq;
RECONFIGURE;

-- Force a checkpoint to clean up any remaining dirty pages
CHECKPOINT;

Schedule large DDL operations during maintenance windows when concurrent OLTP activity is minimal. Implement DDL queuing systems to serialize table drops and truncates, preventing multiple simultaneous deallocation operations that compete for the same buffer pool resources.

Configure appropriate MAXDOP settings for your workload. While parallel operations can speed up large DDL commands, excessive parallelism can increase latch contention. For dedicated DDL maintenance sessions, consider setting session-level MAXDOP to 25% of available cores.

Monitor buffer pool pressure using sys.dm_os_buffer_descriptors and sys.dm_os_memory_clerks. High buffer pool turnover increases the likelihood of latch contention during page destruction. Ensure adequate memory allocation to reduce premature page eviction that forces unnecessary deallocation cycles.

Implement proper index maintenance strategies. Fragmented indexes require more complex deallocation patterns during rebuilds. Regular maintenance reduces the number of pages requiring destruction during optimization operations.

Establish monitoring alerts for sustained PAGELATCH_DT waits exceeding 10 seconds average duration or 50+ concurrent waiting tasks. These thresholds indicate problematic DDL operations that require immediate intervention to prevent cascading performance impacts.