Data and index pages are read from disk into the buffer pool and modified pages (also known as dirty pages) are written back to disk. Memory pressure on the server and database checkpoints cause hot (active) dirty pages in the buffer cache to be evicted from the cache and written to mechanical disks and then read back into the cache. These I/O operations are typically small random reads and writes on the order of 4 to 16 KB of data. Small random I/O patterns incur frequent seeks, competing for the mechanical disk arm, increasing I/O latency, and reducing aggregate I/O throughput of the system. The typical approach to resolving these I/O bottlenecks is to add more DRAM, or alternatively, added high-performance SAS spindles.
The following list describes the benefits of the BPE feature.
- Increased random I/O throughput
- Reduced I/O latency
- Increased transaction throughput
- Improved read performance with a larger hybrid buffer pool
- A caching architecture that can take advantage of present and future low-cost memory drives
The following illustration provides a high-level architectural overview of the buffer pool relative to other SQL Server components.
Below steps demonstrate how to enable BPE:
1. Check the currently configured value of the MAX SERVER MEMORY.
USE master
GO
EXEC sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'max server memory (MB)'
GO
2. Configure the BPE buffer cache file. The minimum size is the size of MAX SERVER MEMORY. You should put this file in a SSD drive, e.g.
USE master
GO
ALTER SERVER CONFIGURATION
SET BUFFER POOL EXTENSION ON
(FILENAME = '<SSD>:\SSDCACHE\SqlServerCache.BPE', SIZE = 32 GB);
GO
Best Practices:
Microsoft says that the BPE can be up to 32 times of the MAX SERVER MEMORY with SQL Server Enterprise, and up to 4 times with Standard Edition. But it recommends a ratio of 1:16 or less between the amount of memory and the size of the extension and cautions that a ratio of 1:4 to 1:8 could be optimal as a starting point.
