Speed Up Reads with Buffer Pool Extension (BPE) in SQL Server 2022 on Window Server 2025 Complete Working Example

inchirags@gmail.com Chirag SQL Server DBA Tutorial https://www.chirags.in

*****************************************************************************************

Speed Up Reads with Buffer Pool Extension (BPE) in SQL Server 2022 on Window Server 2025 Complete Working Example

*****************************************************************************************

## 📌 SQL Server 2022 Buffer Pool Extension (BPE) - Complete Working Example

Step 1: Environment Preparation

-- Check if folder exists and has proper permissions

-- Create folder: C:\MSSQL\BPE\ and grant Full Control to SQL Server service account

Step 2: Verify SQL Server Version & Current Configuration

-- Check SQL Server version and edition
SELECT 
    @@VERSION AS VersionString,
    SERVERPROPERTY('Edition') AS Edition,
    SERVERPROPERTY('ProductVersion') AS ProductVersion,
    SERVERPROPERTY('ProductLevel') AS ProductLevel;

-- Enable advanced options and check current memory settings
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;

EXEC sp_configure 'max server memory (MB)';
EXEC sp_configure 'min server memory (MB)';

-- Check current buffer pool status
SELECT * FROM sys.dm_os_buffer_pool_extension_configuration;

Step 3: Create Test Database and Large Table

-- Create test database
USE master;
GO

IF DB_ID('BPE_Test') IS NOT NULL
BEGIN
    ALTER DATABASE BPE_Test SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE BPE_Test;
END
GO

CREATE DATABASE BPE_Test 
ON PRIMARY 
(NAME = N'BPE_Test', FILENAME = N'C:\MSSQL\DATA\BPE_Test.mdf', SIZE = 10GB, FILEGROWTH = 1GB)
LOG ON 
(NAME = N'BPE_Test_log', FILENAME = N'C:\MSSQL\DATA\BPE_Test_log.ldf', SIZE = 2GB, FILEGROWTH = 500MB);
GO

-- Use the database and create a large table
USE BPE_Test;
GO

-- Create a table that will be larger than available RAM
CREATE TABLE dbo.LargeTestTable (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    DataColumn1 CHAR(4000) DEFAULT REPLICATE('A', 4000),
    DataColumn2 CHAR(4000) DEFAULT REPLICATE('B', 4000),
    CreatedDate DATETIME DEFAULT GETDATE(),
    RandomValue INT DEFAULT ABS(CHECKSUM(NEWID())) % 1000
);
GO

-- Insert enough data to exceed available RAM (adjust based on your server's RAM)
-- This will create approximately 2GB of data
INSERT INTO dbo.LargeTestTable DEFAULT VALUES;
GO 500000  -- Adjust this number based on your server's RAM

-- Verify table size
EXEC sp_spaceused 'dbo.LargeTestTable';

-- Create additional indexes to increase memory pressure
CREATE NONCLUSTERED INDEX IX_LargeTestTable_RandomValue 
ON dbo.LargeTestTable(RandomValue);

CREATE NONCLUSTERED INDEX IX_LargeTestTable_CreatedDate 
ON dbo.LargeTestTable(CreatedDate);
GO

Step 4: Set Up Limited Memory for Testing

-- Set max server memory to a low value to force BPE usage
-- Adjust based on your server's total RAM (set to about 25% of total RAM)

EXEC sp_configure 'max server memory (MB)', 2048; -- 2GB
RECONFIGURE;
GO

-- Verify memory setting
EXEC sp_configure 'max server memory (MB)';

Step 5: Baseline Test (Without BPE)

-- First, ensure BPE is disabled
ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION OFF;
GO

-- Wait a moment for changes to take effect
WAITFOR DELAY '00:00:05';

-- Clear buffers and procedure cache
CHECKPOINT;
DBCC DROPCLEANBUFFERS;
DBCC FREEPROCCACHE;
GO

-- Run test query and measure performance
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO

-- Test query that will require significant memory
SELECT 
    COUNT(*) AS TotalRows,
    AVG(RandomValue) AS AvgRandomValue,
    MIN(CreatedDate) AS EarliestDate,
    MAX(CreatedDate) AS LatestDate
FROM dbo.LargeTestTable
WHERE RandomValue BETWEEN 100 AND 900
    AND CreatedDate > DATEADD(DAY, -30, GETDATE());

SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
GO

-- Record baseline performance metrics
SELECT  
    pr.cntr_value AS [Page Reads/Sec],
    ple.cntr_value AS [Page Life Expectancy],
    ple.instance_name
FROM sys.dm_os_performance_counters pr
JOIN sys.dm_os_performance_counters ple
    ON ple.object_name = 'SQLServer:Buffer Manager'
   AND ple.counter_name = 'Page life expectancy'
WHERE pr.counter_name = 'Page reads/sec'
  AND pr.object_name = 'SQLServer:Buffer Manager';

Step 6: Enable Buffer Pool Extension

-- Enable BPE with a file on SSD
ALTER SERVER CONFIGURATION 
SET BUFFER POOL EXTENSION ON
(FILENAME = 'C:\MSSQL\BPE\bpe_cache.bpe', SIZE = 8 GB); -- Adjust size as needed
GO

-- Verify BPE configuration
SELECT 
    path,
    file_id,
    state,
    state_description,
    current_size_in_kb / 1024.0 AS current_size_mb
FROM sys.dm_os_buffer_pool_extension_configuration;
GO

-- Restart SQL Server service for BPE to take full effect
-- Note: This requires service restart. For demo purposes, we'll continue.
PRINT 'Please restart SQL Server service for BPE to take full effect';
PRINT 'After restart, continue with Step 7';

Step 7: Test With Buffer Pool Extension

-- After service restart, verify BPE is enabled
SELECT * FROM sys.dm_os_buffer_pool_extension_configuration;
GO

-- Clear buffers to force reading from disk
CHECKPOINT;
DBCC DROPCLEANBUFFERS;
DBCC FREEPROCCACHE;
GO

-- Wait for BPE to start caching
WAITFOR DELAY '00:00:10';

-- Run the same test query with BPE enabled
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO

SELECT 
    COUNT(*) AS TotalRows,
    AVG(RandomValue) AS AvgRandomValue,
    MIN(CreatedDate) AS EarliestDate,
    MAX(CreatedDate) AS LatestDate
FROM dbo.LargeTestTable
WHERE RandomValue BETWEEN 100 AND 900
    AND CreatedDate > DATEADD(DAY, -30, GETDATE());

SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
GO

-- Check performance counters
SELECT  
    pr.cntr_value AS [Page Reads/Sec],
    ple.cntr_value AS [Page Life Expectancy],
    ple.instance_name
FROM sys.dm_os_performance_counters pr
JOIN sys.dm_os_performance_counters ple
    ON ple.object_name = 'SQLServer:Buffer Manager'
   AND ple.counter_name = 'Page life expectancy'
WHERE pr.counter_name = 'Page reads/sec'
  AND pr.object_name = 'SQLServer:Buffer Manager';

Step 8: Monitor BPE Usage and Effectiveness

-- Check BPE usage statistics
SELECT 
    counter_name,
    cntr_value,
    cntr_value / 1024.0 AS value_mb
FROM sys.dm_os_performance_counters
WHERE object_name LIKE '%Buffer Manager%'
    AND counter_name LIKE '%Extension%';
GO

-- See what pages are in BPE vs main buffer pool
SELECT 
    DB_NAME(database_id) AS DatabaseName,
    CASE is_in_bpool_extension 
        WHEN 1 THEN 'BPE' 
        ELSE 'Main Buffer Pool' 
    END AS Location,
    COUNT(*) AS PageCount,
    COUNT(*) * 8 / 1024.0 AS SizeMB
FROM sys.dm_os_buffer_descriptors
WHERE database_id = DB_ID('BPE_Test')
GROUP BY database_id, is_in_bpool_extension
ORDER BY Location DESC;
GO

-- Detailed page information
SELECT TOP 100
    DB_NAME(database_id) AS DatabaseName,
    CASE is_in_bpool_extension 
        WHEN 1 THEN 'BPE' 
        ELSE 'Main Buffer Pool' 
    END AS Location,
    page_type,
    COUNT(*) AS PageCount
FROM sys.dm_os_buffer_descriptors
WHERE database_id = DB_ID('BPE_Test')
GROUP BY database_id, is_in_bpool_extension, page_type
ORDER BY Location, PageCount DESC;

Step 9: Performance Comparison

-- Create a comparison table
CREATE TABLE #PerformanceComparison (
    TestRun VARCHAR(50),
    ElapsedTimeMS INT,
    CPUTimeMS INT,
    LogicalReads BIGINT,
    PhysicalReads BIGINT,
    PageLifeExpectancy INT,
    PageReadsPerSec BIGINT
);

-- Insert baseline results (manually enter values from your tests)
INSERT INTO #PerformanceComparison VALUES
('Without BPE', 8560, 4320, 1250000, 98000, 120, 4500);

-- Insert BPE results (manually enter values from your tests)
INSERT INTO #PerformanceComparison VALUES
('With BPE', 4230, 2850, 1250000, 12000, 450, 1200);

-- Display comparison
SELECT * FROM #PerformanceComparison;

-- Calculate improvement percentages
SELECT 
    TestRun,
    ElapsedTimeMS,
    CPUTimeMS,
    PhysicalReads,
    PageLifeExpectancy,
    CAST((1 - (ElapsedTimeMS * 1.0 / FIRST_VALUE(ElapsedTimeMS) OVER (ORDER BY TestRun DESC))) * 100 AS DECIMAL(5,2)) AS ElapsedTimeImprovementPct,
    CAST((1 - (PhysicalReads * 1.0 / FIRST_VALUE(PhysicalReads) OVER (ORDER BY TestRun DESC))) * 100 AS DECIMAL(5,2)) AS PhysicalReadsImprovementPct
FROM #PerformanceComparison;

Step 10: Cleanup and Restoration

-- Disable BPE
ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION OFF;
GO

-- Restore original memory settings
EXEC sp_configure 'max server memory (MB)', 0; -- 0 = use all available memory
RECONFIGURE;
GO

-- Clean up test database
USE master;
GO

IF DB_ID('BPE_Test') IS NOT NULL
BEGIN
    ALTER DATABASE BPE_Test SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE BPE_Test;
END
GO

-- Remove BPE file (manually delete from filesystem)
EXEC xp_cmdshell 'DEL "C:\MSSQL\BPE\bpe_cache.bpe"';
GO

## 📊 Expected Results Example:

| Metric | Without BPE | With BPE | Improvement |
|--------|-------------|----------|-------------|
| Elapsed Time | 8560 ms | 4230 ms | 50.6% faster |
| CPU Time | 4320 ms | 2850 ms | 34.0% less CPU |
| Physical Reads | 98,000 | 12,000 | 87.8% reduction |
| Page Life Expectancy | 120 sec | 450 sec | 275% increase |

## Key Points:

1. BPE is most effective when:

- You have limited RAM but fast SSDs available

- Workload is read-intensive

- Data size significantly exceeds available RAM

2. BPE limitations:

- Only caches clean pages (no dirty pages)

- Not a replacement for sufficient RAM

- Requires fast SSD storage (NVMe recommended)

3. Best practices:

- Monitor BPE hit rates regularly

- Use BPE as temporary solution, not permanent fix

- Consider adding more RAM if BPE usage is consistently high

This complete example provides a working implementation of Buffer Pool Extension with proper testing methodology and performance comparison.

For any doubts and query, please write on YouTube video 📽️ comments section.

Note : Flow the Process shown in video 📽️.

😉Please Subscribe for more videos:

https://www.youtube.com/@chiragtutorial

💛Don't forget to, 💘Follow, 💝Like, Share 💙&, Comment

Thanks & Regards,

Chitt Ranjan Mahto "Chirag"

____________________________________________________________________

Note: All scripts used in this demo will be available in our website.

Link will be available in description.

EndFragment