PAGELATCH_KP Wait Type Explained

PAGELATCH_KP waits happen within the buffer manager when SQL Server needs to ensure a data page remains in memory during a read operation. The Keep mode latch is a lightweight synchronization primitive that allows multiple concurrent readers while preventing the lazywriter or checkpoint process from flushing the page to disk and deallocating its buffer pool slot.

When a worker thread requests data from a page, it first acquires a buffer reference through the buffer hash table. If the page is already in memory, the thread attempts to acquire a Keep mode latch. Multiple threads can hold Keep mode latches simultaneously, but the buffer manager cannot evict the page while any Keep latches exist.

The buffer pool manager uses a clock algorithm for page replacement. When memory pressure occurs, the lazywriter scans buffer headers looking for pages to flush. Pages with active latches, including Keep latches, are skipped during this scan. High PAGELATCH_KP waits indicate contention for specific pages where many threads simultaneously need Keep mode access.

SQL Server 2016 introduced improvements to the buffer pool scanning algorithm that reduced some latch contention. SQL Server 2019 further optimized buffer pool management with better NUMA-aware allocation patterns. SQL Server 2022 added buffer pool parallel scan capabilities that can reduce overall latch pressure during memory pressure scenarios.

The scheduler assigns worker threads to handle these latch requests through the SOS (SQL Server Operating System) layer. When a Keep latch cannot be immediately granted due to an exclusive latch holder, the requesting thread yields its quantum and enters the PAGELATCH_KP wait state until the latch becomes available.

-- Identify currently waiting tasks on PAGELATCH_KP and the pages they're waiting on
-- resource_description format is "<dbid>:<fileid>:<pageid>"
SELECT 
    wt.session_id,
    wt.wait_type,
    wt.wait_duration_ms,
    wt.resource_description,
    CAST(PARSENAME(REPLACE(wt.resource_description, ':', '.'), 3) AS INT) AS database_id,
    CAST(PARSENAME(REPLACE(wt.resource_description, ':', '.'), 2) AS INT) AS file_id,
    CAST(PARSENAME(REPLACE(wt.resource_description, ':', '.'), 1) AS INT) AS page_id,
    DB_NAME(CAST(PARSENAME(REPLACE(wt.resource_description, ':', '.'), 3) AS INT)) AS database_name
FROM sys.dm_os_waiting_tasks wt
WHERE wt.wait_type = 'PAGELATCH_KP'
ORDER BY wt.wait_duration_ms DESC;

-- Analyze buffer pool pressure and latch wait patterns
SELECT 
    cntr_value AS buffer_cache_hit_ratio
FROM sys.dm_os_performance_counters 
WHERE counter_name = 'Buffer cache hit ratio'
AND object_name LIKE '%Buffer Manager%';

SELECT 
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    max_wait_time_ms,
    signal_wait_time_ms
FROM sys.dm_os_wait_stats 
WHERE wait_type LIKE 'PAGELATCH%'
ORDER BY wait_time_ms DESC;

-- Check for memory pressure indicators
SELECT 
    type,
    pages_kb,
    virtual_memory_committed_kb,
    virtual_memory_reserved_kb
FROM sys.dm_os_memory_clerks
WHERE type IN ('MEMORYCLERK_SQLBUFFERPOOL', 'MEMORYCLERK_SQLGENERAL')
ORDER BY pages_kb DESC;

SELECT 
    counter_name,
    cntr_value,
    cntr_type
FROM sys.dm_os_performance_counters
WHERE object_name LIKE '%Memory Manager%'
AND counter_name IN ('Memory Grants Pending', 'Free Memory (KB)', 'Target Server Memory (KB)', 'Total Server Memory (KB)');

-- Identify specific tables and indexes with high latch contention
SELECT TOP 20
    OBJECT_SCHEMA_NAME(ius.object_id) AS schema_name,
    OBJECT_NAME(ius.object_id) AS table_name,
    i.name AS index_name,
    ius.user_seeks + ius.user_scans + ius.user_lookups AS total_reads,
    ius.user_updates AS total_writes,
    p.rows AS row_count
FROM sys.dm_db_index_usage_stats ius
JOIN sys.indexes i ON ius.object_id = i.object_id AND ius.index_id = i.index_id
JOIN sys.partitions p ON ius.object_id = p.object_id AND ius.index_id = p.index_id
WHERE ius.database_id = DB_ID()
AND (ius.user_seeks + ius.user_scans + ius.user_lookups) > 1000
ORDER BY total_reads DESC;

-- Monitor active latch holders and waiters
SELECT 
    session_id,
    request_id,
    wait_type,
    wait_time,
    resource_description,
    blocking_session_id
FROM sys.dm_exec_requests
WHERE wait_type = 'PAGELATCH_KP'
AND wait_time > 1000; -- Only waits over 1 second

Increase Server Memory (if memory pressure detected)

-- Check current memory configuration and increase if needed
SELECT 
    name,
    value,
    value_in_use,
    description
FROM sys.configurations
WHERE name IN ('max server memory (MB)', 'min server memory (MB)');

-- Set max server memory to 80% of physical RAM (adjust 32768 MB as needed)
-- Test in dev environment first
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'max server memory (MB)', 32768;
RECONFIGURE;

Test this change during maintenance window. Monitor performance after implementation. Insufficient memory causes increased buffer pool churn and latch contention.

Enable Buffer Pool Extension (SQL Server 2014+)

-- Enable buffer pool extension to SSD storage
-- Replace path with actual SSD drive path, size in MB
ALTER SERVER CONFIGURATION 
SET BUFFER POOL EXTENSION ON 
(FILENAME = 'F:\BufferPoolExtension\BufferPool.bpe', SIZE = 32768);

-- Verify configuration
SELECT 
    path,
    state_description,
    current_size_in_kb
FROM sys.dm_os_buffer_pool_extension_configuration;

Only use on SSD storage. Test thoroughly in non-production first. Buffer pool extension can reduce memory pressure but may introduce slight latency for extended pages.

Optimize High-Contention Indexes

-- Create covering indexes to reduce page reads for frequently accessed data
-- Example based on diagnostic query results
-- Replace table_name and column names with actual values from analysis

-- Add missing indexes identified through diagnostic queries
CREATE NONCLUSTERED INDEX IX_OptimizedCovering
ON YourTable (Column1, Column2)
INCLUDE (Column3, Column4, Column5)
WITH (FILLFACTOR = 90, PAD_INDEX = ON);

-- Consider partitioning for very large tables with hot spots
-- Evaluate partition scheme based on access patterns

Analyze execution plans before creating indexes. New indexes require maintenance overhead. Consider partition alignment for partitioned tables.

Configure NUMA Settings

-- Enable soft-NUMA for better buffer pool distribution (SQL 2016+)
-- Check current NUMA configuration
SELECT 
    memory_node_id,
    pages_kb,
    virtual_address_space_reserved_kb,
    virtual_address_space_committed_kb
FROM sys.dm_os_memory_nodes
WHERE memory_node_id < 64; -- Exclude DAC node

-- Set affinity mask if needed for NUMA alignment
-- This requires instance restart - plan accordingly
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
-- Example: EXEC sp_configure 'affinity mask', 15; -- Adjust based on CPU topology

Requires careful CPU topology analysis. Incorrect affinity settings can severely impact performance. Test during planned maintenance window.

Configure max server memory to leave 4-6 GB for OS and other applications, preventing excessive buffer pool pressure that leads to increased latch contention. Monitor buffer cache hit ratio and maintain above 95% for OLTP workloads.

Implement proper index maintenance schedules using REBUILD or REORGANIZE operations to maintain optimal page density and reduce fragmentation that causes additional page reads. Target fill factor of 90-95% for frequently updated indexes.

Use partitioning strategies for large tables exceeding 10 million rows to distribute latch contention across multiple file groups and reduce hot spot scenarios. Align partition schemes with query patterns and hardware architecture.

Deploy buffer pool extension on high-performance SSD storage to extend effective memory capacity without full RAM upgrades. Size extension at 2-4 times available RAM for optimal benefit.

Monitor sys.dm_os_wait_stats regularly for PAGELATCH_KP trends and establish baselines during typical workload periods. Alert when wait times exceed 50ms average or when waiting task counts exceed normal patterns by 200%.

Consider In-Memory OLTP for frequently accessed small tables with high contention patterns. Evaluate read-scale availability groups for read-heavy workloads to distribute latch pressure across multiple replicas.

Configure trace flag 8048 on NUMA systems with more than 8 cores to enable NUMA node local buffer pool searches, reducing cross-node memory access patterns that contribute to latch waits.