Tip of the Month: Buffer Pool Extension

By Lori Brown | SQL Administration

Oct 06

— 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)

Follow

About the Author

Lori is an avid runner, cross fitter and SQL enthusiast. She has been working for SQLRX for 15 years and has been working with SQL in general for 25 years. Yup...she is an old hand at this stuff.

>