Understanding Hybrid Buffer Pool in SQL Server
Microsoft introduces a new feature called Hybrid Buffer Pool in SQL Server 2019 CTP 2.1. This feature allows direct access of data pages in database files stored in persistent memory (PMEM) devices.
Before going ahead, you might be thinking what is Persistent Memory devices and how it is related to SQL Server. Let me first explain about persistent memory.
What is Persistent Memory?
Persistent memory generally termed as PMEM, is a solid-state high performance byte-addressable memory device that resides on the memory bus. It has many benefits over other storage devices.
The main advantages of persistent memory include:
- It provides fast data access near real-time without any latency than flash SSDs for large data sets as well.
- PMEM increases throughput more than flash storage.
- Biggest benefit is its cost. It’s cheaper than DRAM.
- PMEM is cacheable. This is a huge advantage over PCIe interconnect, which cannot be cached in the CPU.
- It retains Data in memory after power interruption or shut off.
Persistent memory can be used in a variety of ways to deliver lower latency for applications where you need fast data access with enormous amount of data such as IoT applications, Cyber threat analysis, financial trading etc.
What is Hybrid Buffer Pool?
In a traditional system, SQL Server caches data pages in the buffer pool. With Hybrid Buffer Pool, SQL Server skips performing a copy of the page into the DRAM-based portion of the buffer pool, and instead references the page directly on the database file that lives on a PMEM device. Access to data files in PMEM for Hybrid Buffer Pool is performed using memory-mapped I/O, also known as enlightenment. This brings performance benefits from avoiding a copy of the page to DRAM, and from the avoidance of the I/O stack of the operating system to access the page on persistent storage.
Only clean pages can be referenced directly on a PMEM device. When a page becomes dirty it is kept in DRAM, and then eventually written back to the PMEM device.
This feature will be available in both Windows and Linux version of SQL Server 2019.
How to Enable Hybrid Buffer Pool?
We can enable Hybrid Buffer Pool for SQL Server instance by enabling trace flag during SQL Server startup. As of now, the current process is, we must enable the startup trace flag 809 in order to use Hybrid Buffer Pool.
Microsoft suggests a best practice to keep in mind while enabling
Hybrid Buffer Pool in SQL Server. We should use largest allocation size available for NTFS and ensure the device has been enabled for DAX (DirectAccess) while formatting the PMEM device on Windows.
I hope you liked this article. Please write your comments if you have any questions.
Read More:
- SQL Server 2017 New Features
- New T-SQL Functions introduced in SQL Server 2017
- SQL Server DBA Interview Questions & Answers
- How to Fix SQL Error 1005: A Comprehensive Guide - April 9, 2023
- How to Fix SQL Server Error 207 – Invalid Column Name - April 9, 2023
- How to Fix SQL Error 1045: Resolving Access Denied Issues - April 8, 2023
