SQL Server 2019 Hybrid Buffer Pool Enable and Disable

By: Manvendra Singh | Updated: 2021-08-24 | Comments | Related: > SQL Server 2019

Problem

In a previous article, we discussed the new hybrid buffer pool in SQL Server. In this article, we learned that if we enable hybrid buffer pool for a SQL Server instance, then this feature will be enabled for all databases hosted on that instance. Sometimes you need to disable this feature for some databases like for system databases or for a specific database. This article will help you achieve this objective, where I describe the hybrid buffer pool at the database level and how to enable or disable it for specific databases.

Solution

If you want to understand hybrid buffer pool, then you should read my last article where I have explained about this feature and its configuration for a SQL Server instance. This article is an extension of that tip where I will describe this feature for databases so I would recommend you first go through the previous article first.

The Hybrid Buffer Pool is a new feature introduced in SQL Server 2019 for both Windows and Linux based SQL Server instances. 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 the data pages from the buffer pool. The behavior (avoids a copy of the data page in the buffer pool and reduces IO) of the Hybrid Buffer Pool improves SQL Server performance for read heavy workloads related transactions which generally require more memory for processing.

By default, the hybrid buffer pool is disabled for SQL Server instances. We need to manually enable it to use this feature. If we enable or disable the hybrid buffer pool for a SQL Server instance then it will automatically be enabled or disabled for all databases hosted on that instance. If you have a requirement to enable or disable the hybrid buffer pool for individual databases post enabling it for a SQL Server instance, then you can do so by following the steps below.

Disable Hybrid Buffer Pool Configuration of a SQL Server Database

Let’s first check the hybrid buffer pool configuration for a SQL Server instance by running the below T-SQL statements.

--Check Hybrid Buffer Pool configuration for SQL Server instance
SELECT * FROM sys.server_memory_optimized_hybrid_buffer_pool_configuration

I ran the above command and got the below output. It shows this feature is not enabled.

Now let's go ahead and enable the hybrid buffer pool for the SQL Server instance by running the below statement. Ensure to restart the SQL Server service post executing the below T-SQL to ensure this change is applied. You can read in depth about enabling hybrid buffer pool in my last article that is linked above.

--Enable Hybrid Buffer Pool for a SQL Server instance
ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED HYBRID_BUFFER_POOL = ON;

Once you enable the hybrid buffer pool, then check whether this feature is enabled for your databases on the SQL Server instance by running the below T-SQL statements.

--Check Hybrid Buffer Pool configuration for databases
SELECT name, is_memory_optimized_enabled FROM sys.databases

You can see the output in the below image. We can see the hybrid buffer pool is enabled for all databases. This can only be possible if you have enabled the hybrid buffer pool for the SQL Server instance.

Now, if you want to disable this feature for a specific database then you can do so by running the below ALTER statement for that database.

--Disable Hybrid Buffer Pool configuration for database AdventureWorks2019
ALTER DATABASE [AdventureWorks2019] SET MEMORY_OPTIMIZED = OFF;

I have disabled the hybrid buffer pool for user database AdventureWorks2019 as shown in the below image.

disable Hybrid Buffer Pool for a database

Now, we will check the hybrid buffer pool configuration for database AdventureWorks2019 by running the below T-SQL command.

--Check Hybrid Buffer Pool configuration for database AdventureWorks2019
SELECT name, is_memory_optimized_enabled 
FROM sys.databases
WHERE name = ‘AdventureWorks2019’

You can see the hybrid buffer pool is disabled for database AdventureWorks2019 in the image below.

I removed the WHERE clause and executed the command for all databases, so we can see the hybrid buffer pool setting for each database.

Enable Hybrid Buffer Pool Configuration of a SQL Server Database

This section will describe how to enable the hybrid buffer pool for a database. There might be a possibility you disabled this feature for a database and now need to enable it for the database. Below is the command to enable it for a database.

--Enable Hybrid Buffer Pool configuration for database AdventureWorks2019
ALTER DATABASE [AdventureWorks2019] SET MEMORY_OPTIMIZED = ON;

I executed the above command for database AdventureWorks2019 successfully in the below screenshot.

enable Hybrid Buffer Pool for a database

Now, we will validate the configuration by running the same T-SQL command we executed above and we can see it is set to 1 (enabled) again.

Summary

Make sure to place your database files on PMEM devices with DAX option which is required for hybrid buffer pool in order to take benefits of this feature. Read more in the previous article.

Next Steps

If you don’t want to use the hybrid buffer pool for any database on a SQL Server instance, then it is recommended to disable this configuration at the SQL Server instance level and not for individual databases.

Also, ensure to keep all database file sizes in multiplication of 2MB as per Microsoft if you want to use this feature for a database. Now, you can go ahead and enable this feature and enable or disable for individual databases.

About the author

Manvendra Singh has over 5 years of experience with SQL Server and has focused on Database Mirroring, Replication, Log Shipping, etc.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2021-08-24

How to Enable and Disable Hybrid Buffer Pool for SQL Server Databases