Home > Articles > Data > SQL Server

  • Print
  • + Share This
This chapter is from the book

This chapter is from the book

The Buffer Pool Extension

The Buffer Pool Extension feature in SQL Server 2014 is a form of memory optimization, but is separate and distinct from in-memory optimization. With the Buffer Pool Extension feature, SQL Server provides the SQL Server instance the ability to use locally attached solid-state drives (SSD) as non-volatile random access memory for buffering. This is a server-level configuration setting that allows a buffer pool to be extended beyond available physical RAM in the server to accommodate larger OLTP workloads. This helps to resolve I/O bottlenecks while improving overall I/O throughput, due to lower latency and better random I/O performance of SSDs. The larger hybrid buffer pool provides improved read performance.

The Buffer Pool Extension guarantees no risk of data loss, as it only holds clean pages (i.e., no pages with uncommitted modifications are stored in the Buffer Pool Extension).

With the Buffer Pool Extension, a file residing on SSD storage is used as an extension to the memory subsystem rather than the disk storage subsystem. When implementing the Buffer Pool Extension, it creates a multilevel caching hierarchy with level 1 (L1) as the DRAM and level 2 (L2) as the Buffer Pool Extension file on the SSD. Only clean pages are written to the L2 cache, which helps maintain data safety. The buffer manager automatically handles the movement of clean pages between the L1 and L2 caches.

The following is the syntax for enabling/disabling the Buffer Pool Extension feature in a SQL Server instance:

ALTER SERVER CONFIGURATION
 SET Buffer Pool Extension
 { ON ( FILENAME = 'os_file_path_and_name'
     ,SIZE = <size> [ KB | MB | GB ] )
 | OFF }

When running the ALTER SERVER CONFIGURATION command to create the Buffer Pool Extension, you specify the size and file path of the buffer pool caching file on the SSD. This file is a contiguous extent of storage on the SSD and is statically configured during startup of the instance of SQL Server. You also specify the size of the Buffer Pool Extension, which can be up to 32 times the value of max_server_memory.

If you wish to modify any of the properties of the Buffer Pool Extension, you must first disable it. When the Buffer Pool Extension is disabled, all related configuration settings are removed from the registry. Additionally, the Buffer Pool Extension file is deleted upon shutdown of the instance of SQL Server.

To view the current Buffer Pool Extension configuration settings, you can query the sys.dm_os_buffer_pool_extension_configuration DMV.

  • + Share This
  • 🔖 Save To Your Account