LATCH_DT Wait Type Explained

LATCH_DT waits represent contention for destroy latches that SQL Server uses to coordinate the cleanup of internal structures. When objects like tables, indexes, or memory allocations need destruction, SQL Server must acquire exclusive DT latches to ensure no other threads access the structure during deallocation.

The scheduler assigns destroy operations to worker threads, which must obtain DT latches before proceeding with cleanup. Unlike buffer latches that protect data pages, DT latches guard system catalog entries, allocation structures, and in-memory objects during their destruction phase. The buffer pool manager, lock manager, and metadata cache all participate in this coordination.

SQL Server 2016 introduced improved latch partitioning that reduced DT latch contention compared to earlier versions. SQL Server 2019 added accelerated database recovery (ADR), which changed cleanup patterns by deferring some destruction operations to background processes. SQL Server 2022 further optimized memory allocation patterns, reducing the frequency of certain DT latch acquisitions during bulk operations.

Common triggers include dropping large tables with many partitions, rebuilding clustered indexes on busy systems, memory pressure forcing aggressive cleanup, and tempdb object churn during complex queries. High DT latch waits often correlate with DDL operations executing while heavy DML workloads access related structures.

-- Current LATCH_DT wait statistics
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
FROM sys.dm_os_wait_stats 
WHERE wait_type = 'LATCH_DT';

-- Active sessions waiting on DT latches
SELECT 
    s.session_id,
    s.status,
    s.command,
    r.wait_type,
    r.wait_time,
    r.wait_resource,
    t.text AS current_sql,
    s.program_name,
    s.host_name
FROM sys.dm_exec_sessions s
INNER JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.wait_type = 'LATCH_DT'
ORDER BY r.wait_time DESC;

-- Latch statistics breakdown showing DT latch patterns
SELECT 
    latch_class,
    waiting_requests_count,
    wait_time_ms,
    max_wait_time_ms
FROM sys.dm_os_latch_stats
WHERE latch_class LIKE '%DESTROY%' OR latch_class LIKE '%DT%'
ORDER BY wait_time_ms DESC;

-- Recent DDL operations that may cause DT latch contention
SELECT 
    t.object_id,
    OBJECT_NAME(t.object_id) AS table_name,
    t.lock_escalation_desc,
    i.name AS index_name,
    i.type_desc,
    s.user_scans,
    s.user_seeks,
    s.user_updates
FROM sys.tables t
LEFT JOIN sys.indexes i ON t.object_id = i.object_id
LEFT JOIN sys.dm_db_index_usage_stats s ON i.object_id = s.object_id AND i.index_id = s.index_id
WHERE t.modify_date > DATEADD(hour, -2, GETDATE())
ORDER BY t.modify_date DESC;

-- Memory pressure indicators affecting cleanup operations
SELECT 
    type,
    pages_kb,
    virtual_memory_reserved_kb,
    virtual_memory_committed_kb
FROM sys.dm_os_memory_clerks
WHERE type IN ('MEMORYCLERK_SQLBUFFERPOOL', 'MEMORYCLERK_SQLGENERAL')
ORDER BY pages_kb DESC;

Identify and terminate blocking DDL operations:

-- Find long-running DDL operations causing DT latch waits
-- WARNING: Review sessions carefully before killing
SELECT 
    s.session_id,
    s.status,
    r.command,
    r.percent_complete,
    r.estimated_completion_time,
    t.text,
    r.start_time
FROM sys.dm_exec_sessions s
INNER JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.command IN ('DROP TABLE', 'DROP INDEX', 'ALTER INDEX', 'TRUNCATE TABLE')
    AND r.total_elapsed_time > 300000; -- 5 minutes

-- Uncomment to kill specific session after verification
-- KILL [session_id];

Enable accelerated database recovery to reduce cleanup waits:

-- Enable ADR to minimize long cleanup operations
-- Test in development first, requires SQL Server 2019+
ALTER DATABASE [YourDatabaseName] 
SET ACCELERATED_DATABASE_RECOVERY = ON;

-- Verify ADR status
SELECT 
    name,
    is_accelerated_database_recovery_on
FROM sys.databases
WHERE name = 'YourDatabaseName';

Optimize tempdb to reduce allocation contention:

-- Check current tempdb file configuration
SELECT 
    f.name,
    f.size * 8 / 1024 AS size_mb,
    f.growth * 8 / 1024 AS growth_mb,
    f.is_percent_growth
FROM sys.database_files f
WHERE DB_NAME() = 'tempdb';

-- Add tempdb data files if fewer than CPU cores (up to 8)
-- Adjust file count and sizing based on your system
ALTER DATABASE tempdb
ADD FILE (
    NAME = 'tempdev2',
    FILENAME = 'C:\YourTempPath\tempdev2.ndf',
    SIZE = 1024MB,
    FILEGROWTH = 256MB
);

Schedule index maintenance during low activity periods:

-- Create maintenance window for index operations
-- Replace with your actual maintenance schedule
IF NOT EXISTS (SELECT 1 FROM msdb.dbo.sysjobs WHERE name = 'Index_Maintenance_Low_DT_Latch')
BEGIN
    EXEC msdb.dbo.sp_add_job
        @job_name = 'Index_Maintenance_Low_DT_Latch',
        @enabled = 1,
        @description = 'Rebuild indexes during low activity to minimize DT latch waits';
    
    EXEC msdb.dbo.sp_add_jobstep
        @job_name = 'Index_Maintenance_Low_DT_Latch',
        @step_name = 'Rebuild_Fragmented_Indexes',
        @command = 'EXEC sp_MSforeachdb ''USE [?]; ALTER INDEX ALL ON [?] REBUILD WITH (ONLINE = ON, MAXDOP = 2)''';
    
    EXEC msdb.dbo.sp_add_schedule
        @schedule_name = 'Weekly_Maintenance',
        @freq_type = 8, -- Weekly
        @freq_interval = 1, -- Sunday
        @freq_subday_type = 1,
        @active_start_time = 020000; -- 2:00 AM
END;

Configure tempdb with multiple data files equal to CPU cores (maximum 8) with identical sizing to reduce allocation contention that triggers DT latch waits. Enable accelerated database recovery on SQL Server 2019+ to minimize cleanup operations during transaction rollbacks.

Schedule DDL operations like index rebuilds and table maintenance during low-activity windows. Avoid dropping large partitioned tables during peak hours. Implement gradual data archival instead of large DELETE operations that create extensive cleanup requirements.

Monitor memory pressure using sys.dm_os_memory_clerks and configure appropriate max server memory settings to prevent aggressive memory cleanup. Set up alerts for LATCH_DT wait times exceeding 100ms average to catch contention early.

Use online index operations where possible to reduce exclusive latch requirements. Consider implementing table partitioning strategies that allow partition switching instead of large table drops. Configure appropriate growth settings for user databases to minimize auto-growth operations that can trigger DT latch contention.