Rajendra Gupta

Hybrid Buffer Pool in SQL Server 2019

November 23, 2021 by

This article explores the Hybrid Buffer Pool feature available in the SQL Server 2019.

Introduction

SQL Server uses Dynamic Random Access Memory (D-RAM) buffer pool for caching data pages retrieved from the disk. The buffer cache stores the page and writes back to disk only if it is modified. If a page is modified in the buffer cache, it is known as a dirty page. SQL Server considers a page as clean if its equivalent copy exists on the disk. The checkpoint writes the dirty pages and T-log information from memory to disk. The buffer pool is volatile, and every time you restart SQL Services, it gets flushed out.

The buffer pool is available for all databases for storing their cached data pages. Its size is dynamic, and you can configure it using the sp_configure. Usually, for an optimized SQL Server performance, the page should exist in the buffer cache instead of fetching it from the disk.

In the following high-level diagram, we can see that once a user submits a select query, the query processor optimizes the query plan and request the required pages from the buffer pool. If the page is not available in the buffer pool, it fetches data from the data file to the buffer pool to satisfy the user requests (DMLs).

Buffer Pool

Similarly, the data modification requires pages to be modified in the buffer cache. SQL Server records the modification in the transaction logs using the Write-Ahead-Logging (WAL) and writes dirty pages (modified) back to the disk.

Writing_Pages

You can refer to Microsoft documentation here to understand the architecture in detail.

Overview of the Hybrid buffer pool

SQL Server 2019 has many enhancements for improving query performance, reducing system bottlenecks. SQL Server 2019 introduces a new concept – Hyper Buffer Pool (HPB) that enables the buffer pool objects to utilize persistent memory devices (PMEM) for referencing the data pages. It avoids copying those data pages to the volatile DRAM.

The PMEM is a high-performance solid-state device that uses a byte-addressable memory device. It provides the following benefits.

  • It enables quick data access with minimum latency for large datasets
  • It is ultra-fast and persistent, meaning data remains in memory even after system restart, Power interruption, or system crash. Therefore, SQL Server does not require caching pages again in the buffer pool and reduces IO requests from storage
  • It increases the throughput more than the SSD and NVMe
  • It is cost-effective and cheaper than the DRAM

The PEMM device can directly access the clean pages only. If a page is not present in the buffer pool or hybrid buffer pool, it still loads the data page from the disk to the buffer pool. Therefore, once a page is dirty, SQL Server copies it to the DRAM buffer pool and writes a clean copy into the PMEM device.

Hybrid buffer pool

Therefore, the hybrid buffer pool avoids copying a data page to the buffer pool and improves query performances by reducing the IO. It can be helpful in the case of bulk transactions that require more memory for data processing.

The hybrid buffer pool is available on both Windows and Linux SQL Server. We need to format the disk with direct access supported file system. The supported file systems are as below.

  • EXT4
  • NTFS (New Technology File System)
  • Extended File system (EFS)

SQL Server 2019 has the functionality to detect whether the data file resides on the supported PMEM device. It performs the memory mappings appropriately. You need to restart SQL Service for this mapping to occur upon startup.

The following image highlights the difference in the buffer pool with and without the hybrid buffer pool.

Hybrid buffer pool architecture

Image Reference: Microsoft docs

Pre-requisites

You require SQL Server 2019 for the demonstration in this article. You can navigate to Microsoft website and download the evaluation or development version.

Download SQL Server 2019

  • Note: I recommend you install SQL Server 2019 developer edition for learning purposes. It provides all enterprise-level features for learning and performing demonstrations

Requirements of the Hybrid Buffer Pool

To use this feature in SQL Server, you need to have the following environment.

  • Windows Server 2019
  • SQL Server 2019
  • PMEM device
  • Volume on the PMEM device with supported DAX file system

Enable hybrid buffer pool in SQL Server 2019

You can use dynamic data language (DDL) to control the hybrid buffer pool. By default, this feature is disabled on the instance level.

To validate it, we can use the query system view as shown below.

Check hybrid memory configuration

It is enabled for all databases in an instance of SQL Server 2019. You can query sys.databases system catalog view and check value for the is_memory_optimized_enabled as shown below.

Check buffer pool configuration for databases

The following t-SQL script enables a hybrid buffer pool on a SQL instance.

  • Note: It requires a restart of the SQL Server instance for this setting to take effect. It allocates hash pages as per the total PMEM capacity. Once you enable the feature at the instance level, you can view the following message in the error log:

    The hybrid buffer pool memory-optimized configuration has been set to ‘enable’. Restart SQL server for the new setting to take effect

After SQL Service restart, the is_enabled column changes to 1, which shows the hybrid buffer pool is enabled for this instance.

Enable Hybrid pool

Check the error log, and it gets the following highlighted entries.

Check SQL Server error logs

  • Message 1: In this message, SQL Server informs that hyper buffer pool is enabled for this SQL instance

    2021-07-27 04:43:47.060 Server Hybrid buffer pool started with memory-optimized metadata

  • Message 2: Here, it gives allocation details of the hash pages

    Buffer Pool: Allocating 16777216 bytes for 10484610 hash Pages

  • Message 3: Here, it gives information on total persistent memory

    Hybrid buffer Pool: proposing 8387688 hash Pages for 64 GiB of total Persistent Memory

  • Note: If you have enabled a hybrid buffer pool for a SQL instance, you do not need to enable it for the individual database

Disable hybrid buffer pool for SQL Server instance

You can disable the HBP configuration using a similar dynamic data language (DDL) statement. Here, we specify the value of HYBRID_BUFFER_POOL to OFF. You also need to restart SQL Service after disabling the hybrid buffer pool.

  • Note: If you disable the feature at the instance level, it won’t be used by any database, even if it is enabled at the database level

Disable hybrid buffer pool for a specific SQL Server database

You can enable or disable a hybrid buffer pool for a specific SQL Server database using the Alter database statement. Suppose the HBP is enabled at the instance level, and you want to use it for a specific database. Therefore, you can enable it for a specific database.

  • Note: Even if you enable the HBD feature for a specific database, it takes effect once you restart SQL Service

Best Practices for hybrid buffer pool

Microsoft documentation gives few best practices for using the hybrid buffer pool feature.

  • You should use the largest allocation unit size for NTFS ((2 MB in Windows Server® 2019) along with the device configured for DAX ( Direct Access)
  • We require to enable Locked pages in Memory configuration in Windows
  • The data file size should be in multiples of 2 MB

Conclusion

The Hybrid Buffer Pool improves database performance using direct data page access from the PMEM device. In this case, SQL Server does not need to copy the page to DRAM and reduces IO, especially for bulk transactions.

Rajendra Gupta
Latest posts by Rajendra Gupta (see all)

Related posts:

  1. Buffer Pool Extension (BPE) – How it works?
  2. Buffer Pool Extension (BPE) – Introduction to the Buffer Pool
  3. Insight into the SQL Server buffer cache
  4. Buffer Pool Extension (BPE) – Implementing another level of cache
  5. Dedicated SQL pools in Azure Synapse Analytics
168 Views