Using an SSD drive as a L2 cache in SQL Server buffer pool extension

If your SQL Server workload is limited by random disk I/O, buffer pool extension can use an SSD drive as an extra cache layer between memory and the database files.

The feature is called Buffer Pool Extension. It extends the SQL Server buffer pool (in RAM) by using fast non-volatile storage, usually an SSD.

It is not a replacement for adding RAM, but it can help when the active working set is larger than available memory and the server still has slow database storage behind it.

To start using the feature, first check the current max server memory setting:

EXEC sp_configure 'show advanced options', 1;
reconfigure;

EXEC sp_configure 'max server memory';

The buffer pool extension file must be placed on fast storage, preferably a dedicated SSD.

Enable buffer pool extension with:

ALTER SERVER CONFIGURATION
SET BUFFER POOL EXTENSION ON
(
    FILENAME = 'E:\SqlBufferPoolExtension\SqlServer.bpe',
    SIZE = 64 GB
);

You can check the current configuration with:

SELECT *
FROM sys.dm_os_buffer_pool_extension_configuration;

Microsoft recommends sizing the buffer pool extension in relation to max server memory. Do not place the file on the same slow disk subsystem that is already limiting SQL Server.

To disable the feature, run the following:

ALTER SERVER CONFIGURATION
SET BUFFER POOL EXTENSION OFF;

Changing the buffer pool extension file path or size requires disabling the feature first.

Remember to use Performance Monitor (PerfMon) or SQL Server counters to check whether the feature is actually helping. Useful counters are under the SQL Server Buffer Manager object and include buffer pool extension reads and writes.

At the point, the question becomes: what kind of systems are good candidates for testing? Here's a short list:

• read-heavy workloads
• databases larger than available RAM
• servers with slow database disks but available SSD storage
• older SQL Server Standard Edition environments where adding RAM is limited or expensive.

Bad candidates would be:

• servers already using fast storage for database files
• write-heavy workloads where reads are not the main bottleneck
• systems where adding RAM is still practical
• production systems without baseline performance measurements.

As always with SQL Server performance features, measure before and after. Buffer pool extension can help in the right workload, but it should not be enabled blindly.

Docs: Buffer pool extension, ALTER SERVER CONFIGURATION. Hope this helps!