Hybrid Buffer Pool

Microsoft SQL Server 2019 introduces Hybrid Buffer Pool. This feature allows access to Persistent MEMory (PMEM) devices. These persistent memory devices add a new layer to server memory hierarchy and filling the gap between high performance / high cost of DRAM (Dynamic Random Access Memory) and the lower cost lower performance of file storage drives using SSD.

This memory architecture has been implemented by Intel as Intel® Optane™ Technology; refer to https://www.intel.com/content/www/us/en/products/docs/storage/optane-technology-brief.html for more information:

Figure 1.15: Intel memory architecture

Persistent memory is integrated at the memory controller level of the CPU chip and will retain data even when the server is powered off.

While many aspects of persistent memory devices can be realized without any software changes, features such as Hybrid Buffer Pool can take advantage of the new storage hierarchy and provide direct memory access to files.

For clean database pages, those that have not been modified, SQL server can directly access them as memory. When an update is made, and then marked as dirty, the page is copied to DRAM, changes persisted, and the page is then written back into the persistent memory area.

To enable Hybrid Buffer Pool, the feature must be enabled at the instance level of SQL Server. It is off by default. After enabling, the instance must be restarted:

ALTER SERVER CONFIGURATION 
SET MEMORY_OPTIMIZED HYBRID_BUFFER_POOL = ON;

Furthermore, the Hybrid Buffer Pool will only operate on memory-optimized databases:

ALTER DATABASE <databaseName> SET MEMORY_OPTIMIZED = ON;

Or, in order to disable, execute the following command:

ALTER DATABASE <databaseName> SET MEMORY_OPTIMIZED = OFF;

To see the Hybrid Buffer Pool configurations and memory-optimized databases on an instance, you can run the following queries:

SELECT * FROM sys.configurations WHERE name = 'hybrid_buffer_pool';
SELECT name, is_memory_optimized_enabled FROM sys.databases;

There are many considerations when configuring a server with persistent memory, including the ratio of DRAM to PMEM. You can read more here:

• https://docs.microsoft.com/en-us/windows-server/storage/storage-spaces/deploy-pmem
• https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/hybrid-buffer-pool?view=sql-server-2017