SQL 2025 master class LAB 06 Buffer Pool Extensions

--Step 1 
use MASTER
IF NOT EXISTS (SELECT * FROM sys.databases WHERE NAME = 'SoftwareSmithBufferPoolExtensions')
CREATE DATABASE SoftwareSmithBufferPoolExtensions;
GO
use  SoftwareSmithBufferPoolExtensions

CREATE TABLE TestTable1
(col1 INT IDENTITY PRIMARY KEY, 
col2 INT,
col3 nvarchar (4000),
col4 nvarchar (4000),
col5 nvarchar (4000),
col6 nvarchar (4000),
col7 nvarchar (4000),
col8 nvarchar (4000),
col9 nvarchar (4000),
col10 nvarchar (4000)
 ) 

USE TestTable1; 

SET NOCOUNT ON
DECLARE @i int = 0
WHILE @i < 100000
BEGIN
    SET @i = @i + 1
INSERT INTO TestTable1 (col2, col3, col4, col5, col6, col7, col8, col9, col10)
VALUES (RAND() * 2147483647,
CONVERT(nvarchar(4000), RAND() * 2147483647),
CONVERT(nvarchar(4000), RAND() * 2147483647),
CONVERT(nvarchar(4000), RAND() * 2147483647),
CONVERT(nvarchar(4000), RAND() * 2147483647),
CONVERT(nvarchar(4000), RAND() * 2147483647),
CONVERT(nvarchar(4000), RAND() * 2147483647),
CONVERT(nvarchar(4000), RAND() * 2147483647),
CONVERT(nvarchar(4000), RAND() * 2147483647)
)
END

CHECKPOINT -- writes dirty pages to disk, cleans the buffers
DBCC DROPCLEANBUFFERS -- removes all buffers
 
--Step 2 
 
 --check MEMORYCLERK_SQLBUFFERPOOL allocation
SELECT TOP 10 [type], SUM(pages_kb) / 1024 AS SizeMb
FROM sys.dm_os_memory_clerks
GROUP BY [type]
ORDER BY SUM(pages_kb) / 1024 DESC

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

SET STATISTICS IO ON
SELECT TOP 100000 * FROM TestTable1

 

 

ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION

ON (FILENAME='C:\Software\BPEFile.bpe',SIZE=30GB)

 

--Housekeeping 

use master
IF EXISTS (SELECT * FROM sys.databases WHERE NAME = 'SoftwareSmithBufferPoolExtensions')
DROP DATABASE SoftwareSmithBufferPoolExtensions;
GO

 ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION OFF

--with thanks and acknowledgement to  https://www.sqlshack.com/sql-server-buffer-pool-action/