Debunking the Myths: Cloud HA and DR common misconceptions

Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips


























































   Got a SQL tip?
            We want to know!

Increasing Buffer Pool in SQL Server 2014

MSSQLTips author Daniel Farina By:   |   Read Comments (7)   |   Related Tips: More > SQL Server 2014
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:

Incorrect Path

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


Last Update: 1/31/2014


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.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Comments and Feedback:
Friday, February 14, 2014 - 10:38:25 AM - FMalik Read The Tip

Thanks for sharing your excellent work.


Saturday, February 15, 2014 - 11:57:21 AM - Mohd Faisal Farouqi Read The Tip

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.


Monday, February 17, 2014 - 3:29:20 PM - Mark Read The Tip

Too bad that this is an enterprise edition only feature (http://msdn.microsoft.com/en-us/library/cc645993(v=sql.120).aspx#Scalability). But hey, if you can afford SQL Server 2014 Enterprise Edition, then what's a few extra bucks for an SSD, right Mohd? :-)


Tuesday, February 18, 2014 - 11:48:41 PM - Tim Read The Tip

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


Wednesday, February 19, 2014 - 2:53:13 PM - sree Read The Tip

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


Friday, May 09, 2014 - 10:23:39 PM - Mike_TeeVee Read The Tip

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

Check for yourself:  http://msdn.microsoft.com/en-us/library/cc645993%28v=sql.120%29.aspx

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!


Monday, June 09, 2014 - 9:48:44 AM - Aaron Bertrand Read The Tip

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



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.