Implementing Buffer Pool Extension

Buffer Pool Extension was released in SQL 2014 so it's not new.  It is also not advertised very much, but that's not for lack of usefulness.  RAM is far faster than SSD and certainly arm based disks, but most databases are too big to cache into RAM.  BPE allows us to extend the buffer cache by storing clean pages on an SSD drive.

In this video I show how easy it is to implement Buffer Pool Extension.  You don't even have to reboot or take an outage to turn it on, but remember to always test!  You can find the TEST code below the video.

https://www.youtube.com/watch?v=iFUsk\_8Y\_-E

[code lang="sql"]

--Review current BPE configuration
SELECT [path], state_description, current_size_in_kb,
CAST(current_size_in_kb/1048576.0 AS DECIMAL(10,2)) AS [Size (GB)]
FROM sys.dm_os_buffer_pool_extension_configuration;

--Reduce SQL Server Max memory to restrict the BP and force the use of BPE
EXEC sys.sp_configure 'show advanced options', '1' RECONFIGURE WITH OVERRIDE;
GO
EXEC sys.sp_configure 'max server memory (MB)', '2000';
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sys.sp_configure 'show advanced options', '0' RECONFIGURE WITH OVERRIDE;
GO
/*********** WE HAVE TO RESTART SQL SERVER FOR BPE TO SEE THE RAM CHANGE ***************/

--Enable BPE
--Go look at the file size on disk right after you run this
ALTER SERVER CONFIGURATION
SET BUFFER POOL EXTENSION ON (FILENAME = 'C:\Temp\BP_Extension.BPE', SIZE = 2 GB);

--Now that it is enabled we'll inspect the configuration again
SELECT [path], state_description, current_size_in_kb,
CAST(current_size_in_kb/1048576.0 AS DECIMAL(10,2)) AS [Size (GB)]
FROM sys.dm_os_buffer_pool_extension_configuration;

--Try to read enough data to fill BP and start using BPE
USE AdventureworksDW2016CTP3;
GO
SELECT * FROM dbo.FactResellerSalesXL_PageCompressed;
--If the above didn't do the trick then query this table as well
--SELECT * FROM dbo.FactResellerSalesXL_CCI;

--Let's see what went to BPE. If there are no results then go query more data.
SELECT DB_NAME(database_id) AS [Database Name], COUNT(page_id) AS [Page Count],
CAST(COUNT(*)/128.0 AS DECIMAL(10, 2)) AS [Buffer size(MB)],
AVG(read_microsec) AS [Avg Read Time (microseconds)]
FROM sys.dm_os_buffer_descriptors
WHERE database_id <> 32767
AND is_in_bpool_extension = 1
GROUP BY DB_NAME(database_id)
ORDER BY [Buffer size(MB)] DESC;

--Turn BPE off. Go look in c:\temp to see what happens to the physical data file
ALTER SERVER CONFIGURATION
SET BUFFER POOL EXTENSION OFF;

--Put Max Server Memory back where it was
EXEC sys.sp_configure 'show advanced options', '1' RECONFIGURE WITH OVERRIDE;
GO
EXEC sys.sp_configure 'max server memory (MB)', '3500';
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sys.sp_configure 'show advanced options', '0' RECONFIGURE WITH OVERRIDE;
GO