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

[e-mail] 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

1. BPE limitations:

• Only caches clean pages (no dirty pages)

• Not a replacement for sufficient RAM

• Requires fast SSD storage (NVMe recommended)

1. 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.