LATCH_KP Wait Type Explained

LATCH_KP represents Keep Page latch waits. A KP (Keep) latch holds a reference to a page to prevent it from being evicted from the buffer pool while another thread still needs it. This ensures that a page remains resident in the buffer pool for as long as any thread has an outstanding reference to it.

KP latches are not the same as buffer (BUF) latches that serialize read/write access to page contents. Instead, KP latches act as reference counts that keep a page pinned in the buffer pool. Threads acquire KP latches when they need to guarantee a page remains available in memory during subsequent operations. High LATCH_KP waits typically emerge when threads are unable to quickly acquire or release these references, often under heavy buffer pool churn or concurrent access to the same pages.

SQL Server 2016 introduced improvements to buffer pool and latch handling that reduced some KP latch contention. SQL Server 2019 further optimized buffer pool management during parallel operations. SQL Server 2022 refined page lifecycle handling in the buffer pool.

The wait manifests when a thread cannot immediately acquire the requested KP latch due to incompatible concurrent requests on the same page reference. Threads enter the SUSPENDED state and register the LATCH_KP wait until the conflicting latch releases. Extended waits indicate sustained pressure on frequently accessed pages.

-- Identify current KP latch waits and blocking patterns
SELECT 
    session_id,
    blocking_session_id,
    wait_type,
    wait_time_ms,
    resource_description,
    command,
    database_id,
    DB_NAME(database_id) as database_name
FROM sys.dm_exec_requests 
WHERE wait_type = 'LATCH_KP'
ORDER BY wait_time_ms DESC;

-- Analyze KP latch contention by resource type over time
SELECT 
    latch_class,
    waiting_requests_count,
    wait_time_ms,
    max_wait_time_ms,
    wait_time_ms / NULLIF(waiting_requests_count, 0) as avg_wait_ms
FROM sys.dm_os_latch_stats 
WHERE latch_class LIKE '%ALLOC%' OR latch_class LIKE '%METADATA%'
    AND waiting_requests_count > 0
ORDER BY wait_time_ms DESC;

-- Check tempdb allocation contention patterns
SELECT 
    session_id,
    database_id,
    user_objects_alloc_page_count,
    user_objects_dealloc_page_count,
    internal_objects_alloc_page_count,
    internal_objects_dealloc_page_count
FROM sys.dm_db_session_space_usage 
WHERE database_id = 2 
    AND (user_objects_alloc_page_count + internal_objects_alloc_page_count > 1000)
ORDER BY (user_objects_alloc_page_count + internal_objects_alloc_page_count) DESC;

-- Examine concurrent schema operations causing metadata latch pressure  
SELECT 
    r.session_id,
    r.command,
    r.status,
    r.wait_type,
    r.wait_time_ms,
    t.text,
    DB_NAME(r.database_id) as database_name
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.command IN ('CREATE INDEX', 'DROP INDEX', 'ALTER TABLE', 'CREATE TABLE')
    OR r.wait_type = 'LATCH_KP'
ORDER BY r.wait_time_ms DESC;

-- Monitor allocation page latch acquisition patterns
SELECT 
    page_type_desc,
    page_level,
    avg_page_space_used_in_percent,
    record_count,
    ghost_record_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED')
WHERE alloc_unit_type_desc = 'IN_ROW_DATA' 
    AND avg_page_space_used_in_percent < 50
    AND record_count > 0
ORDER BY ghost_record_count DESC;

Reduce tempdb allocation contention

-- Add multiple tempdb data files to distribute allocation load
-- WARNING: Test file sizing in dev environment first
USE master;
GO

DECLARE @logical_cpus int = (SELECT cpu_count FROM sys.dm_os_sys_info);
DECLARE @tempdb_files int = (SELECT COUNT(*) FROM sys.master_files WHERE database_id = 2 AND type = 0);

-- Add files up to CPU count (max 8 for most workloads)
IF @tempdb_files < @logical_cpus AND @tempdb_files < 8
BEGIN
    DECLARE @file_count int = 1;
    WHILE @file_count <= (CASE WHEN @logical_cpus > 8 THEN 8 ELSE @logical_cpus END) - @tempdb_files
    BEGIN
        DECLARE @filename nvarchar(100) = 'tempdev' + CAST(@tempdb_files + @file_count as nvarchar(10));
        DECLARE @sql nvarchar(500) = 'ALTER DATABASE tempdb ADD FILE (NAME = ''' + @filename + ''', FILENAME = ''C:\TempDB\' + @filename + '.mdf'', SIZE = 1GB, FILEGROWTH = 512MB)';
        EXEC sp_executesql @sql;
        SET @file_count += 1;
    END
END

Optimize allocation page access patterns

-- Enable trace flag 1118 globally to reduce allocation contention
-- NOTE: In SQL Server 2016+, TF 1118 behavior (uniform extent allocation) is the
-- default for tempdb. TF 1117 (proportional fill) is also default for tempdb in 2016+.
-- These trace flags are only needed for older versions or for user databases.
-- WARNING: Restart required, test impact on mixed extent allocation
DBCC TRACEON(1118, -1);

-- Enable trace flag 1117 for proportional fill
DBCC TRACEON(1117, -1);

-- Verify trace flag status
DBCC TRACESTATUS(-1);

Reduce metadata latch pressure during index operations

-- Use ONLINE operations to minimize metadata blocking
-- Example: Rebuild indexes with minimal latch impact
ALTER INDEX IX_YourIndex ON dbo.YourTable 
REBUILD WITH (ONLINE = ON, SORT_IN_TEMPDB = ON, MAXDOP = 4);

-- For non-Enterprise editions, use drop/recreate pattern
-- DROP INDEX IX_YourIndex ON dbo.YourTable;
-- CREATE INDEX IX_YourIndex ON dbo.YourTable (Column1) WITH (SORT_IN_TEMPDB = ON, MAXDOP = 4);

Configure optimal allocation settings

-- Set database autogrowth to fixed MB values to reduce allocation events
-- WARNING: Adjust sizes based on workload patterns
USE master;
GO

DECLARE @db_name sysname = 'YourDatabase';
DECLARE @sql nvarchar(max);

-- Set data file growth to 512MB increments
SET @sql = 'ALTER DATABASE [' + @db_name + '] MODIFY FILE (NAME = ''' + @db_name + ''', FILEGROWTH = 512MB)';
EXEC sp_executesql @sql;

-- Set log file growth to 256MB increments  
SET @sql = 'ALTER DATABASE [' + @db_name + '] MODIFY FILE (NAME = ''' + @db_name + '_Log'', FILEGROWTH = 256MB)';
EXEC sp_executesql @sql;

Configure multiple tempdb data files equal to logical CPU count (maximum 8) with identical initial sizes to distribute allocation workload. Set fixed growth increments rather than percentage-based autogrowth to minimize allocation latch acquisition frequency.

Implement staggered maintenance windows for schema modifications across databases to prevent concurrent metadata operations. Use ONLINE index operations in Enterprise Edition to reduce exclusive latch duration on catalog pages.

Monitor allocation page utilization through sys.dm_db_index_physical_stats and proactively rebuild fragmented indexes before they trigger excessive allocation activity. Configure appropriate file sizing to minimize autogrowth events that require extended allocation page latch holds.

On SQL Server versions prior to 2016, enable trace flags 1117 and 1118 to optimize tempdb allocation patterns and reduce contention on allocation bitmaps. In SQL Server 2016 and later, these behaviors are the default for tempdb, so the trace flags are only needed for user databases. Establish baseline measurements for LATCH_KP waits during normal operations to quickly identify allocation bottlenecks during peak periods.

Separate high-allocation workloads like staging operations and bulk inserts to dedicated filegroups with pre-allocated space, reducing competition for system allocation structures during routine OLTP operations.