Skip to content

Latest commit

 

History

History
101 lines (64 loc) · 11.4 KB

buffer-pool-extension.md

File metadata and controls

101 lines (64 loc) · 11.4 KB
title description author ms.author ms.date ms.service ms.subservice ms.topic
Buffer Pool Extension
Learn about buffer pool extension and its benefits, which include improved I/O throughput. View best practices to follow when turning on this feature.
rwestMSFT
randolphwest
01/28/2025
sql
configuration
conceptual

Buffer pool extension

[!INCLUDE SQL Server]

Introduced in [!INCLUDE ssSQL14], the buffer pool extension provides the seamless integration of a nonvolatile random access memory (that is, solid-state drive) extension to the [!INCLUDE ssDE] buffer pool to significantly improve I/O throughput. The buffer pool extension isn't available in every [!INCLUDE ssNoVersion] edition. For more information, see Features Supported by the Editions of SQL Server 2016.

Benefits of the buffer pool extension

The primary purpose of a [!INCLUDE ssNoVersion] database is to store and retrieve data, so intensive disk I/O is a core characteristic of the Database Engine. Because disk I/O operations can consume many resources and take a relatively long time to finish, [!INCLUDE ssNoVersion] focuses on making I/O highly efficient. The buffer pool serves as a primary memory allocation source of [!INCLUDE ssNoVersion]. Buffer management is a key component in achieving this efficiency. The buffer management component consists of two mechanisms: the buffer manager to access and update database pages, and the buffer pool, to reduce database file I/O.

Data and index pages are read from disk into the buffer pool and modified pages (also known as dirty pages) are written back to disk. Memory pressure on the server and database checkpoints cause hot (active) dirty pages in the buffer cache to be evicted from the cache and written to mechanical disks and then read back into the cache. These I/O operations are typically small random reads and writes on the order of 4 KB to 16 KB of data. Small random I/O patterns incur frequent seeks, competing for the mechanical disk arm, increasing I/O latency, and reducing aggregate I/O throughput of the system.

The typical approach to resolving these I/O bottlenecks is to add more DRAM, or alternatively, added high-performance SAS spindles. While these options are helpful, they have significant drawbacks: DRAM is more expensive than data storage drives, and adding spindles increases capital expenditure in hardware acquisition and increases operational costs by increased power consumption and increased probability of component failure.

The buffer pool extension feature extends the buffer pool cache with nonvolatile storage (usually SSD). Because of this extension, the buffer pool can accommodate a larger database working set, which forces the paging of I/Os between RAM and the SSDs. This effectively offloads small random I/Os from mechanical disks to SSDs. Because of the lower latency and better random I/O performance of SSDs, the buffer pool extension significantly improves I/O throughput.

The following list describes the benefits of the buffer pool extension feature.

  • Increased random I/O throughput

  • Reduced I/O latency

  • Increased transaction throughput

  • Improved read performance with a larger hybrid buffer pool

  • A caching architecture that can take advantage of present and future low-cost memory drives

Concepts

The following terms are applicable to the buffer pool extension feature.

Term Description
Solid-state drive (SSD) Solid-state drives store data in memory (RAM) in a persistent manner. For more information, see the Wikipedia definition.
Buffer In [!INCLUDE ssNoVersion], a buffer is an 8-KB page in memory, the same size as a data or index page. Thus, the buffer cache is divided into 8-KB pages. A page remains in the buffer cache until the buffer manager needs the buffer area to read in more data. Data is written back to disk only if it's modified. These in-memory modified pages are known as dirty pages. A page is clean when it's equivalent to its database image on disk. Data in the buffer cache can be modified multiple times before being written back to disk.
Buffer pool Also called buffer cache. The buffer pool is a global resource shared by all databases for their cached data pages. The maximum and minimum size of the buffer pool cache is determined during startup or when the instance of [!INCLUDE ssnoversion-md] is dynamically reconfigured with sp_configure. This size determines the maximum number of pages that can be cached in the buffer pool at any time in the running instance.

The maximum memory that can be committed by buffer pool extension can be limited by the other applications running on the machine, in case those applications create significant memory pressure.
Checkpoint A checkpoint creates a known good point from which the [!INCLUDE ssDE] can start applying changes contained in the transaction log during recovery after an unexpected shutdown or crash. A checkpoint writes the dirty pages and transaction log information from memory to disk and, also, records information about the transaction log. For more information, see Database checkpoints (SQL Server).

Details

SSD storage is used as an extension to the memory subsystem rather than the disk storage subsystem. That is, the buffer pool extension file allows the buffer pool manager to use both DRAM and NAND-Flash memory to maintain a much larger buffer pool of lukewarm pages in nonvolatile random access memory backed by SSDs. This 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 handles the movement of clean pages between the L1 and L2 caches.

The following illustration provides a high-level architectural overview of the buffer pool relative to other [!INCLUDE ssNoVersion] components.

:::image type="content" source="media/buffer-pool-extension/solid-state-drive-architecture.png" alt-text="Diagram of SSD buffer pool extension architecture.":::

When enabled, the buffer pool extension specifies 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 [!INCLUDE ssNoVersion]. Alterations to the file configuration parameters can only be done when the buffer pool extension feature is disabled. When the buffer pool extension is disabled, all related configuration settings are removed from the registry. The buffer pool extension file is deleted upon shutdown of the instance of SQL Server.

Capacity limitations

SQL Server Enterprise edition allows a maximum buffer pool extension size of 32 times the value of max server memory.

SQL Server Standard edition allows a maximum buffer pool extension size of four times the value of max server memory.

Best practices

We recommend that you follow these best practices.

  • After you enable buffer pool extension for the first time, you should restart the SQL Server instance to get the maximum performance benefits.

  • Set the buffer pool extension so the ratio between the size of the physical memory (max server memory) and the size of the buffer pool extension of 1:16 or less. A lower ratio in the range of 1:4 to 1:8 might be optimal. For information about setting the max server memory option, see Server memory configuration options.

  • Test the buffer pool extension thoroughly before implementing in a production environment. Once in production, avoid making configuration changes to the file or turning off the feature. These activities might have a negative effect on server performance because the buffer pool is significantly reduced in size when the feature is disabled. When disabled, the memory used to support the feature isn't reclaimed until the instance of SQL Server is restarted. However, if the feature is re-enabled, the memory is reused without restarting the instance.

Return information about the buffer pool extension

You can use the following dynamic management views to display the configuration of the buffer pool extension and return information about the data pages in the extension.

Performance counters are available in the SQL Server, Buffer Manager Object to track the data pages in the buffer pool extension file. For more information, see buffer pool extension performance counters.

The following XEvents are available.

XEvent Description Parameters
sqlserver.
buffer_pool_extension_pages_written		 Fires when a page or group of pages are evicted from the buffer pool and written to the buffer pool extension file. number_page
first_page_id
first_page_offset
initiator_numa_node_id
sqlserver.
buffer_pool_extension_pages_read		 Fires when a page is read from the buffer pool extension file to the buffer pool. number_page
first_page_id
first_page_offset
initiator_numa_node_id
sqlserver.
buffer_pool_extension_pages_evicted		 Fires when a page is evicted from the buffer pool extension file. number_page
first_page_id
first_page_offset
initiator_numa_node_id
sqlserver.
buffer_pool_eviction_thresholds_recalculated		 Fires when the eviction threshold is calculated. warm_threshold
cold_threshold
pages_bypassed_eviction
eviction_bypass_reason
eviction_bypass_reason_description

Related tasks

Task Description Article
Enable and configure the buffer pool extension. ALTER SERVER CONFIGURATION
Modify the buffer pool extension configuration ALTER SERVER CONFIGURATION
View the buffer pool extension configuration sys.dm_os_buffer_pool_extension_configuration
Monitor the buffer pool extension sys.dm_os_buffer_descriptors

SQL Server, Buffer Manager object