Press "Enter" to skip to content

Using Buffer Pool Extension

William Wolf talks about Buffer Pool Extension:

With this feature, SQL Server will extend the Buffer Pool Cache to non-volatile(ssd) storage.  This will alleviate the I/O contention of mechanical disks by augmenting memory.  The BPE uses the SSD as memory extension rather than disk.  This feature can be used with standard and enterprise, but would provide noticeable benefits for Standard Edition.  According to books online, the BPE size can be up to 32 times(Enterprise) or 4 times(Standard Edition) the value of max_server_memory, but the recommended ratio is 1:16 or less.

By utilizing this option, we can alleviate some memory pressure.  To demonstrate this for me was a litte difficult at first. My laptop, as most newer laptops, has a SSD. So I plugged in a SATA hard drive externally and moved my database there for testing. If the database files are already on SSD, adding BPE may not give much benefit as the memory from BPE would write to SSD as well.

Buffer Pool Extension did end up in the Hall of Shame, but scenarios like Wolf describes exist, and in those scenarios, BPE could be a viable third-best option.