sys.dm_os_buffer_descriptors - Usage & Examples

sys.dm_os_buffer_descriptors provides a row-level view of SQL Server's buffer pool, the primary memory cache where data and index pages reside before being written to disk or after being read from storage. Each row represents a single 8KB page in memory with metadata including database_id, file_id, page_id, allocation_unit_id, and page_type.

The buffer pool manager maintains these descriptors within buffer pool memory structures. When SQL Server reads data pages from disk, the storage engine allocates buffer pool slots and creates corresponding descriptors. The page_type column is a descriptive string identifying the page contents with values such as DATA_PAGE, INDEX_PAGE, TEXT_MIX_PAGE, GAM_PAGE, SGAM_PAGE, IAM_PAGE, and PFS_PAGE.

Buffer descriptors transition through states: free pages become allocated when needed, allocated pages can be marked dirty when modified, and dirty pages eventually get written to disk by the checkpoint process or lazy writer. The is_modified bit tracks dirty pages requiring write-back to storage.

Starting with SQL Server 2016, Microsoft added the is_in_bpool_extension column to track pages residing in buffer pool extension files on SSD storage. SQL Server 2019 introduced better NUMA topology awareness in buffer pool allocation, affecting how descriptors distribute across NUMA nodes.

The DMV queries internal buffer pool hash tables and linked lists, requiring shared locks that can impact performance. On systems with large buffer pools (256GB+), this DMV can return millions of rows, potentially causing memory pressure and blocking other operations.

-- Buffer pool utilization by database
SELECT 
    DB_NAME(database_id) AS database_name,
    COUNT(*) AS page_count,
    COUNT(*) * 8 / 1024 AS buffer_mb,
    COUNT(*) * 8.0 / 1024 / 1024 AS buffer_gb
FROM sys.dm_os_buffer_descriptors
WHERE database_id > 4  -- Exclude system databases
GROUP BY database_id
ORDER BY page_count DESC;

-- Top objects consuming buffer pool memory
SELECT TOP 20
    OBJECT_SCHEMA_NAME(p.object_id) AS schema_name,
    OBJECT_NAME(p.object_id) AS object_name,
    p.index_id,
    i.name AS index_name,
    COUNT(*) AS page_count,
    COUNT(*) * 8 / 1024 AS buffer_mb
FROM sys.dm_os_buffer_descriptors bd
    INNER JOIN sys.allocation_units au ON bd.allocation_unit_id = au.allocation_unit_id
    INNER JOIN sys.partitions p ON au.container_id = p.hobt_id
    LEFT JOIN sys.indexes i ON p.object_id = i.object_id AND p.index_id = i.index_id
WHERE bd.database_id = DB_ID()
    AND p.object_id > 100
GROUP BY p.object_id, p.index_id, i.name
ORDER BY page_count DESC;

-- Dirty page analysis for checkpoint timing
SELECT 
    database_id,
    DB_NAME(database_id) AS database_name,
    COUNT(*) AS total_pages,
    SUM(CASE WHEN is_modified = 1 THEN 1 ELSE 0 END) AS dirty_pages,
    SUM(CASE WHEN is_modified = 1 THEN 1 ELSE 0 END) * 100.0 / COUNT(*) AS dirty_page_pct
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id
HAVING COUNT(*) > 1000
ORDER BY dirty_page_pct DESC;

-- Buffer pool extension usage (SQL Server 2014+)
SELECT 
    COUNT(*) AS total_pages,
    SUM(CASE WHEN is_in_bpool_extension = 1 THEN 1 ELSE 0 END) AS bpe_pages,
    SUM(CASE WHEN is_in_bpool_extension = 1 THEN 1 ELSE 0 END) * 8 / 1024 AS bpe_mb
FROM sys.dm_os_buffer_descriptors
WHERE is_in_bpool_extension IS NOT NULL;

-- Page type distribution analysis
-- page_type is a descriptive string column (DATA_PAGE, INDEX_PAGE, etc.)
SELECT 
    page_type,
    COUNT(*) AS page_count,
    COUNT(*) * 8 / 1024 AS buffer_mb
FROM sys.dm_os_buffer_descriptors
GROUP BY page_type
ORDER BY page_count DESC;

Selective Memory Pressure Relief Forces checkpoint on databases with high dirty page ratios to reduce memory pressure from uncommitted changes.

-- Force checkpoint on databases with >20% dirty pages
DECLARE @sql NVARCHAR(MAX) = '';

SELECT @sql = @sql + 'CHECKPOINT ' + QUOTENAME(DB_NAME(database_id)) + ';' + CHAR(13)
FROM (
    SELECT 
        database_id,
        SUM(CASE WHEN is_modified = 1 THEN 1 ELSE 0 END) * 100.0 / COUNT(*) AS dirty_pct
    FROM sys.dm_os_buffer_descriptors
    WHERE database_id > 4
    GROUP BY database_id
    HAVING SUM(CASE WHEN is_modified = 1 THEN 1 ELSE 0 END) * 100.0 / COUNT(*) > 20
) dirty_dbs;

-- Execute with caution during low activity periods
-- EXEC sp_executesql @sql;
PRINT @sql; -- Preview commands first

Buffer Pool Memory Redistribution Clears the entire clean buffer pool when memory distribution becomes unbalanced. DBCC DROPCLEANBUFFERS is a server-wide command and takes no arguments.

-- Clear clean buffer pool pages server-wide
-- WARNING: This affects ALL databases on the instance and will impact performance temporarily
DECLARE @dbname SYSNAME = 'YourNonProdDatabase';

IF DB_ID(@dbname) IS NOT NULL
BEGIN
    -- Verify database has >10GB in buffer pool before clearing
    IF EXISTS (
        SELECT 1 FROM sys.dm_os_buffer_descriptors 
        WHERE database_id = DB_ID(@dbname)
        GROUP BY database_id
        HAVING COUNT(*) * 8 / 1024 / 1024 > 10
    )
    BEGIN
        PRINT 'Preparing to clear clean buffers (server-wide) due to heavy usage by: ' + @dbname;
        CHECKPOINT;
        -- DBCC DROPCLEANBUFFERS; -- Uncomment after testing. Server-wide, no parameters.
    END;
END;

Large Object Buffer Analysis and Cleanup Identifies and optionally clears cached large objects that may be consuming disproportionate buffer pool space.

-- Find large objects in buffer pool and generate cleanup commands
WITH LargeObjects AS (
    SELECT 
        bd.database_id,
        p.object_id,
        COUNT(*) AS page_count,
        COUNT(*) * 8 / 1024 AS buffer_mb
    FROM sys.dm_os_buffer_descriptors bd
        INNER JOIN sys.allocation_units au ON bd.allocation_unit_id = au.allocation_unit_id
        INNER JOIN sys.partitions p ON au.container_id = p.hobt_id
    WHERE bd.database_id > 4
        AND p.object_id > 100
    GROUP BY bd.database_id, p.object_id
    HAVING COUNT(*) > 10000 -- Objects using >78MB buffer space
)
SELECT 
    DB_NAME(lo.database_id) AS database_name,
    OBJECT_SCHEMA_NAME(lo.object_id, lo.database_id) AS schema_name,
    OBJECT_NAME(lo.object_id, lo.database_id) AS object_name,
    lo.buffer_mb,
    'DBCC DROPCLEANBUFFERS; -- server-wide, clears ALL clean pages' AS cleanup_command
FROM LargeObjects lo
ORDER BY lo.buffer_mb DESC;

-- Review results before executing DBCC DROPCLEANBUFFERS. It is a server-wide command
-- that takes no parameters and will clear clean buffers for every database on the instance.

Configure max server memory appropriately, leaving 2-4GB for OS on dedicated SQL Server instances, or 20-25% of total RAM on shared servers. Monitor buffer cache hit ratios through sys.dm_os_performance_counters to ensure adequate memory allocation without over-provisioning.

Implement buffer pool extension on systems with fast SSD storage when physical RAM limitations exist. Configure BPE files on dedicated SSD volumes, typically sized at 4-10x physical RAM allocation.

Schedule regular database maintenance during off-peak hours to prevent excessive dirty page accumulation. Configure checkpoint duration using the recovery interval setting, typically 1-5 minutes for OLTP systems.

Avoid querying sys.dm_os_buffer_descriptors on production systems during peak hours. Instead, capture snapshots during maintenance windows or use aggregated queries with appropriate WHERE clauses to limit result sets.

Set up alerts for buffer cache hit ratios below 95% and Page Life Expectancy drops below 300 seconds (or your baseline). Monitor dirty page percentages exceeding 20% as indicators of checkpoint pressure or insufficient I/O throughput.

For systems with large buffer pools, consider partitioning monitoring queries by database_id or implementing sampling strategies rather than full table scans of the DMV.