Tip of the Month: Buffer Pool Extension

— By Lori Brown  @SQLSupahStah

Available in SQL Server 2014 Enterprise Edition and Standard Edition x64 only

Buffer Pool Extension is a way to use solid state disks (SSD) as nonvolatile RAM (NvRAM) instead of memory. It reduces latency. It stores clean or committed pages only so there is no risk of data loss and will give faster access to data that would have otherwise be slow because it would have to be read from disk. Buffer Pool Extension can provide performance gains for read-heavy OLTP workloads.

Turn on Buffer Pool Extension in SQL with an ALTER SERVER statement. FILENAME is the path to the bpe file that will be located on the SSD disk and SIZE is the size of the bpe file in GB.

ALTER SERVER CONFIGURATION

 SET BUFFER POOL EXTENSION ON

(FILENAME = ‘X:\BPESSD\EXTENDMEMORY.BPE’, SIZE = 50 GB)