How to Enable and Disable Hybrid Buffer Pool for SQL Server Databases
By: Manvendra Singh | Updated: 2021-08-24 | Comments | Related: More > SQL Server 2019
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.
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.
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.
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.
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.
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.
Read more articles on SQL Server:
- Read my other articles on multiple SQL Server topics
- You can also read more article on SQL Server Management Studio
- Explore more knowledge on SQL Server Database Administration Tips
About the author
View all my tips
Article Last Updated: 2021-08-24