Increasing Buffer Pool in SQL Server 2014

By:   |   Comments (14)   |   Related: > SQL Server Configurations


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.


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

EXEC sp_configure 'show advanced options', 1

EXEC sp_configure 'max server memory (MB)'

EXEC sp_configure 'max server memory (MB)', [VALUE (MB)]


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 

    (FILENAME = 'E:\DB_Cache\SQL2014.BPE', SIZE = 10 GB);

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:

Incorrect Path

Disable the SQL Server 2014 Buffer Pool Extension

To disable this feature, simply execute the following script.

USE master


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


    (FILENAME = 'F:\SSDCACHE\Example.BPE', SIZE = 10 GB);
Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Daniel Farina 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

Comments For This Article

Thursday, December 1, 2016 - 8:57:12 AM - aj Back To Top (44872)

I don't understand what this means for SQL Server 2014 Standard Edition (x64). We have a box with 256GB of RAM. The memory limit of standard edition is 128GB. Suppose I'd create a buffer pool extension of 10GB on SSD. Would that mean, SQL Server would use 138GB (128GB RAM + 10GB SSD) of "memory" or just put 10GB of the 128GB to SSD -> "free" 10GB of RAM?

Friday, November 13, 2015 - 4:44:28 AM - PREETHA Back To Top (39065)

VERY NICE TIPS REGARDS TO SDD DISK as a cache to the Buffer Pool by creating a file on disk.

Saturday, January 17, 2015 - 10:36:28 AM - Matt Back To Top (35976)

Do you know if the BPE size can be dynamic?  The scenario I'm trying to use is scaling down VM's over the weekend in Azure. The problem being if I'm using BPE it's tied to the RAM of the VM. If that suddenly changes the ratio is off and SQL won't start. Any ideas?

Monday, November 10, 2014 - 1:06:23 PM - John Q Martin Back To Top (35242)


If you want to see the configuration state of the Buffer Pool Extension then you can query sys.dm_os_buffer_pool_extension_configuration, additionally you can query sys.dm_os_buffer_descriptors and filter on is_in_bpool_extension to get details of the content of the BPE file.



Wednesday, October 8, 2014 - 5:58:51 PM - Junior Back To Top (34898)

Hi, Unfortuntely this returns me 0 rows. select * from sys.configurations where name like '%buffer%'

Also - in which table would the current value for filename stored?  that is = (FILENAME = 'F:\SSDCACHE\Example.BPE' 

Tuesday, October 7, 2014 - 7:19:19 PM - Daniel Farina Back To Top (34873)

Hi Junior,

You can query the sys.configurations DMV

Best Regards

Monday, October 6, 2014 - 2:36:21 PM - Junior Back To Top (34849)

Is there a tsql query that lets me see what the current configuration is for the Server Configuration.

Monday, June 9, 2014 - 9:48:44 AM - Aaron Bertrand Back To Top (32152)

I believe that in Standard Edition the limit is 4X RAM, not 32X like in Enterprise.

Friday, May 9, 2014 - 10:23:39 PM - Mike_TeeVee Back To Top (30715)

This is available in the Standard Edition (the person who said it was Enterprise-Only was wrong).

Check for yourself:

And others complaining about the cost of SSD to non-SSD - I don't get it.  SSD is still way cheaper than Memory, easier to add, and you can extend your server's memory limit with SSD using this new "Buffer Pool Extension" feature.  Obviously it won't be as fast as Memory, but it'll still be faster than having to push cache out of memory (that you may still want to hit) to load in newer cache when your memory is under pressure.

I wonder if SQL Server 2014 is smart enough to move Old Cache to the SSD, and keep newer (and more recently used cache) in Memory.  That would be awesome if they did.

I'd like to hear about all the Cons/Risks of using "Buffer Pool Extension" and in what scenarios is it Recommended and Discouraged.

Sounds too good to be true.  :)

Great Article BTW!

Wednesday, February 19, 2014 - 2:53:13 PM - sree Back To Top (29506)

It doesn't work on RAM Drives. Should be on a non-volatile storage device...

Tuesday, February 18, 2014 - 11:48:41 PM - Tim Back To Top (29498)

Hi,may i ask you if there is a memeory limited in memry database

Monday, February 17, 2014 - 3:29:20 PM - Mark Back To Top (29478)

Too bad that this is an enterprise edition only feature ( But hey, if you can afford SQL Server 2014 Enterprise Edition, then what's a few extra bucks for an SSD, right Mohd? :-)

Saturday, February 15, 2014 - 11:57:21 AM - Mohd Faisal Farouqi Back To Top (29464)

Thanks! for sharing. It's an awesome stuff. The only drawback I see is it would require SSD's which is quite pricey...right? Maybe should benefit in future if the cost goes down.

Friday, February 14, 2014 - 10:38:25 AM - FMalik Back To Top (29454)

Thanks for sharing your excellent work.

get free sql tips
agree to terms