Problem
Microsoft introduced a new feature called Hybrid Buffer Pool in SQL Server 2019 which improves system performance by reducing IO operations in the buffer pool (DRAM). I will explain this new feature and demonstrate how to enable or disable for a SQL Server instance.
Solution
Hybrid Buffer Pool is a new feature in SQL Server 2019 that allows SQL Server to directly access data pages from their respective database files that are hosted on a persistent memory device. This feature will prevent additional IO operations by not copying the data pages to the buffer pool which generally happens in traditional systems. In simple words, we are just extending the RAM based buffer pool to a much bigger size by enabling the hybrid buffer pool feature.
Before going ahead with this article, let me explain a few terms that will be helpful.
- Persistent Memory Device (PMEM) – The Hybrid Buffer Pool requires persistent memory devices (PMEM) which are non-volatile and byte addressable. The performance of such devices is near equal to traditional computer memory that we generally term as Dynamic RAM (DRAM). One of the main differences between PMEM and DRAM is that DRAM is volatile whereas PMEM devices are nonvolatile. Nonvolatile means it will not lose any information if the system is refreshed or power is lost.
- DAX – DAX stands for Direct Access. DAX is used to access files stored in Persistent memory without the need to copy the data through the page cache.
Let’s learn more about the Hybrid Buffer Pool along with how to configure for your SQL Server instance.
Understanding Hybrid Buffer Pool in SQL Server
Hybrid Buffer Pool is a new feature introduced in SQL Server 2019 for both Windows and Linux based SQL Server instances. It enables buffer pool objects to reference data pages in database files residing on persistent memory (PMEM) devices. If we don’t enable this feature, then SQL Server must cache a copy of the respective data pages in the buffer pool to fulfill the query requirement. This means the hybrid buffer pool allows SQL Server to access data pages directly from database files that are stored on a persistent memory device and not through accessing a copy of data pages from the buffer pool. This behavior (avoids copying a data page into the buffer pool and reduces IO) of the Hybrid Buffer Pool improves SQL Server performance and it is very beneficial for read heavy workloads and related transactions which generally require more memory to process.
A PMEM device must be formatted with a DAX supported filesystem (such as XFS, EXT4, or NTFS). SQL Server will automatically detect if data files reside on an appropriately formatted PMEM device and perform memory mapping of database files upon startup, when a new database is attached, restored, or created.
Let’s go ahead and learn how to check whether the Hybrid Buffer Pool is enabled for your SQL Server instance in the next section.
Check Hybrid Buffer Pool Configuration of a SQL Server Instance
We will use the below T-SQL statement to check whether hybrid buffer pool is enabled for a SQL Server instance.
--Check Hybrid Buffer Pool configuration SELECT * FROM sys.server_memory_optimized_hybrid_buffer_pool_configuration
Below is the output and we can see that for this instance it is not configured or enabled.
The above T-SQL statement tell us at instance level, but you can also enable or disable at the database level. Below is the query that we can use to see if this is enabled for each database.
--Check Hybrid Buffer Pool configuration for databases SELECT name, is_memory_optimized_enabled FROM sys.databases
You can see the output below which shows that every database is configured / enabled to use the hybrid buffer pool once it is configured and enabled at the instance level.
Enable Hybrid Buffer Pool Configuration of a SQL Server Instance
Hybrid Buffer Pool is disabled by default for SQL Server instances. If we want to use it, then we need to enable at the instance level which will allow all databases hosted on that instance to use this feature by default.
Let’s run the below T-SQL statement to configure and enable it for a SQL Server instance.
--Enable Hybrid Buffer Pool for a SQL Server instance ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED HYBRID_BUFFER_POOL = ON;
I executed the command below.
We can check the SQL Server error log to see if it has been enabled. You can see below in the log screenshot where it shows: “The hybrid buffer pool memory-optimized configuration has been set to ‘enable’. Restart SQL Server for the new setting to take effect.”
Next, we will run the same command as earlier to check the configuration. The first column “is_configured” is set to 1 which means hybrid buffer pool is configured, but “is_enabled” is 0 this is because we haven’t restarted SQL Server service yet after making the change.
After restarting the SQL Server service using SQL Server Configuration Manager, we can check again and below we can see it is now configured and enabled.
You can also compare SQL Server error log file before enabling hybrid buffer pool and after enabling and restarting SQL Server instance.
Here is before:
Here is after:
We have successfully enabled hybrid buffer pool for a SQL Server instance. Point to be noted is that once we configure and enable hybrid buffer pool for a SQL Server instance then this feature will be enabled for all databases hosted on that instance by default. We don’t need to enable it for specific databases. Although, we can enable or disable hybrid buffer pool feature for individual databases and I will discuss more about this in another article.
Disable Hybrid Buffer Pool Configuration of a SQL Server Instance
This section describes how to disable hybrid buffer pool for a SQL Server instance. We will use the same T-SQL statement as before, but will change the value of HYBRID_BUFFER_POOL to OFF for disabling this configuration.
--Disable Hybrid Buffer Pool for a SQL Server instance ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED HYBRID_BUFFER_POOL = OFF;
I executed the above T-SQL statement.
Now, we will validate whether the hybrid buffer pool is disabled. By running the below command, we can see “is_configured” is set to 0, but “is_enabled” is still 1.
You can also check the SQL Server error log file to see if the hybrid buffer pool has been disabled.
Now that we have disabled the hybrid buffer pool, let’s restart SQL Server service to complete the change.
I restarted the SQL Server service and again checked the hybrid buffer pool configuration by running the below T-SQL statement. We can now see both values are 0 again.
Also, if we check the SQL Server error log, there is not an entry for the hybrid buffer pool. See below in the error log file where we can see buffer pool allocation, but not hybrid buffer pool details.
Next Steps
- I explained Hybrid Buffer Pool and its configuration for a SQL Server instance. If you want to use this feature, then you can follow this article to enable it.
- Microsoft also recommends some best practices for hybrid buffer pool like if you want to enable it then make sure you have enabled lock pages in memory on your windows machine.
- One thing to note is that if we enable or disable the hybrid buffer pool at the SQL Server instance then it will automatically be enabled or disabled for all databases hosted on that instance. If you want to enable or disable this feature for individual databases or for system databases, then stay tuned for the next article.
