- Overview of In-Memory OLTP
- In-Memory Optimization Requirements
- Limitations of In-Memory OLTP
- Using In-Memory OLTP
- Using Memory-Optimized Tables
- Logging, Checkpoint, and Recovery for In-Memory OLTP
- Managing Memory for In-Memory OLTP
- Backup and Recovery of Memory-Optimized Databases
- Migrating to In-Memory OLTP
- Dynamic Management Views for In-Memory OLTP
- The Buffer Pool Extension
- Summary
This chapter is from the book
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.