By: Daniel Farina | Comments (14) | Related: > SQL Server Configurations
Problem
Under heavy load your SQL Server may not work as intended due to lack of resources. To handle this situation SQL Server 2014 gives us the possibility to use an SSD or Ram device to dynamically increase the Buffer Pool. Keep reading to see how you can take advantage of this new wonderful feature.
Solution
Every day in the life of a DBA is a challenge. You don't know what you will face until you are seated at your desk. Sometimes there are nightly ETL processes that didn't run and you have to re-run them during work hours when the database is under more pressure.
As another example, suppose that you are a DBA at a company like Amazon. It is expected that this kind of business has seasonal demand. For example, before holidays such as Mother's Day, Christmas, etc. there will be more transactions than on normal days.
Now, considering Murphy's Law who states "anything that can go wrong will go wrong"; just imagine that all of this happens at the same time. Will you be ready?
What is the SQL Server Buffer Pool?
Starting from square one, if you are new to SQL Server you may be asking yourself: What is the buffer pool?
To make it simple, the Buffer Pool is the place in system memory that holds data and index pages read from disk. This has two purposes:
- To modify pages according to INSERT and UPDATE statements. Those pages are marked as "dirty" and are flushed to disk when a checkpoint is performed.
- To increase response time when retrieving the same data.
Buffer Pool size is determined amongst other things by server memory and the target server memory specified in the "Max Server Memory" parameter. When that threshold is reached and SQL Server needs to read more pages, previous cached pages are discarded.
With this in mind, we can deduce that the purpose of the Buffer Pool is to improve performance by reducing IO operations. As such, the Buffer Pool size can correlate to improved performance during a heavy workload.
Advantages of the SQL Server Buffer Pool on SSDs
Remember that SQL Server is designed to use the maximum available memory regardless of other system processes and that includes other instances you may have on the same server. You don't want to have an instance using for example, 80% of RAM and leaving the rest to other instances.
Here is when Buffer Pool Extension comes into play. You can limit the instance's memory by setting "Max Server Memory" parameter and enable Buffer Pool Extension to compensate from a performance perspective.
If you have configured an instance per sever also you can take advantage of this feature by enabling Buffer Pool Extension during heavy load without restarting the instance.
How does the SQL Server Buffer Pool Extension work?
This feature uses a Flash or DRAM memory device like an SSD disk as a cache to the Buffer Pool by creating a file on disk. At first I thought that the disk would be accessed as a RAW device but no, it has to be formatted like any other Windows drive. Considering this, I noticed that even a standard disk can be used to enable Buffer Pool Extension, but this won't be as useful from a performance perspective.
Since enabling this feature creates a file and relies on the file system, for best benefits you should use a non-fragmented drive so Windows doesn't split IO requests. You can monitor this with the following performance counter: "\LogicalDisk\Split IO/sec".
Microsoft Recommendations for the SQL Server Buffer Pool Extension
According to Books Online you can set the Buffer Pool Extension size up to 32 times the size of RAM, but they recommend adjusting its value in concordance with the "Max Server Memory" parameter. They say that a ratio of 1:16 or less, I mean 16 times "Max Server Memory" size would be enough. But even a lower ratio could be beneficial.
Amongst their recommendations is the proper testing prior to implementation on production environments and to avoid performing configuration changes to the file.
Steps to Enable the SQL Server Buffer Pool Extension
Now I will guide you though the process of setting up the SQL Server Buffer Pool Extension.
First, as Microsoft recommends we should check the value of "Max Server Memory" parameter.
USE master GO EXEC sp_configure 'show advanced options', 1 RECONFIGURE WITH OVERRIDE GO EXEC sp_configure 'max server memory (MB)' GO /* EXEC sp_configure 'max server memory (MB)', [VALUE (MB)] RECONFIGURE WITH OVERRIDE GO */
Finally here is the script to enable Buffer Pool Extension. I created a 10GB file in this example, but you can change this value as needed.
USE master
GO
ALTER SERVER CONFIGURATION
SET BUFFER POOL EXTENSION ON
(FILENAME = 'E:\DB_Cache\SQL2014.BPE', SIZE = 10 GB);
GO
In order to specify a filename for the Buffer Pool Extension we must ensure that the path exists, otherwise you will face the following error:
Disable the SQL Server 2014 Buffer Pool Extension
To disable this feature, simply execute the following script.
USE master
GO
ALTER SERVER CONFIGURATION
SET BUFFER POOL EXTENSION OFF;
GO
Altering the SQL Server 2014 Buffer Pool Extension Size
In order to modify Buffer Pool Extension Size, first you need to disable and then re-enable this option with its new size.
USE master
GO
ALTER SERVER CONFIGURATION
SET BUFFER POOL EXTENSION OFF;
GO
ALTER SERVER CONFIGURATION
SET BUFFER POOL EXTENSION ON
(FILENAME = 'F:\SSDCACHE\Example.BPE', SIZE = 10 GB);
GO
Next Steps
- If you still don't have a version of SQL Server 2014, download a trial version here.
- To monitor Buffer Pool Extension you can use the New SQL Server 2014 Dynamic Management Views.
About the author
Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
