Buffer Pool Extension in SQL Server 2014

Forrards
March 31, 2023

I guess many have heard the phrase «640K ought to be enough for anybody», which Bill Gates is mistakenly thought to be the author of. Although RAM is constantly becoming cheaper, still its capacity is always not enough for database server. SQL Server features intelligent caching of frequently requested data, but the data size often exceeds the available amount of memory, which sometimes is difficult to expand. One of the interesting options appeared in SQL Server 2014 is Buffer Pool Extension, which enables cache of data pages on a state solid drive (SSD) in case the RAM is not enough. In this article I present a thorough overview of this technology using SQL Server 2014 CTP1.

Buffer Pool is the largest area to store data pages in SQL Server. Any page read from the disk is placed in Buffer Pool first and later is used for other purposes (for data reading or modifying). There are two types of pages in Buffer Pool. A «clean» data page is the one that was not modified. Consequently, the modified but not written back to the disk with checkpoint or lazy writer processes data pages are termed as “dirty" ones. Buffer Pool Extension allows you to increase the size of Buffer Pool using an SSD. This will be useful if you have an opportunity to add a solid-state drive in your server, but you can not expand RAM or put all frequently requested data on the SSD due to some restrictions. For example, you can use any cheap SSD as a cache for BPE without setting any reliability requirements, while to store the whole or a part of the database more expensive and reliable systems are needed.

To enable this option you should perform ALTER SERVER CONFIGURATION and specify the location and size of the file that will be used for the BPE feature

alter server configuration
set buffer pool extension
on ( filename = 'Z:\Cache.bpe' , size = 64 gb )
go

Immediately after executing this command the file is created and used. SQL Server can use it only to cache "clean" data pages. You can’t simply change the location of the file or its size. To do this you need to disable BPE and enable it again specifying other parameters. To disable Buffer Pool Extension the following command is used:

alter server configuration
set buffer pool extension off;
go

So, in which cases this option may appear to be useful and what advantages it has:

  1. It is recommended for OLTP systems, where data reading requests prevail.
  2. There is no risk of data loss, because BPE caches only "clean" pages. When a disk fails the BPE just becomes disabled.
  3. You do not need to make any changes in the application as everything works properly as soon as you turn on the option.

But there are also some limitations that should be taken into account before starting to use this option:

  1. The option is available only in Enterprise Edition.
  2. You can not specify a file smaller than the current size of the RAM for the BPE feature. According to my tests in SQL Server 2014 CTP1 you'll get an error like this:

Msg 868, Level 16, State 1, Line 18
Buffer pool extension size must be larger than the current memory allocation threshold 2048 MB. Buffer pool extension is not enabled.

  1. 128 GB is the maximum supported memory, which means those who have systems with a larger amount of memory can not use the option. And that's pretty bad moment, because large memory is available to customers of even entry-level servers.
  2. The size of BPE can exceed the size of RAM by up to 32 times. Consequently, the maximum allowed BPE size is 32 x 128 GB = 4 TB. However, the recommended BPE size is from 4X to 10X the RAM.
  3. It goes without saying that the BPE file should be placed on a fast state solid drive, otherwise the option becomes completely useless. Despite there are no reliability requirements, I should notice that if your drive fails suddenly and you lose BPE the system performance will suffer provided that it depends heavily on this option.

All the articles about Buffer pool extension:

Buffer Pool Extension in SQL Server 2014

Buffer Pool Extension in SQL Server 2014 part 2: benchmark testing

SQL Server 2014 Buffer Pool Extension part 3: system monitoring

Buffer Pool Extension in SQL Server 2014 part 4: benchmark testing for update operations

Forrards
March 31, 2023
Share
Recent articles:
Database consultancy: SaaS database considerations
March 31, 2023

Utilizing given approach each client get his own database. In such case every client`s data stays isolated from others. It simplifies backing up the data, installing new clients but causes higher cost of maintenance.

More
Database consultancy: tree-like structure
March 31, 2023

Subject recursion is well covered in the literature, but, nevertheless, the problem of output “tree” does not mean the client and SQL Server many baffled. So, put the problem: there is a table with the name and record id field indicating the parent identifier. Immediately fill in this table, some sort of test data:

More
Database consultancy: Hierarchy in MS SQL Server
March 31, 2023

In our time of greatest prevalence of databases were relational databases, which are the main targets of the tables and the relationships between them. Tables can solve most problems for data storage and manipulation. But in the real world entity requiring storage is not always presented in a tabular form. One of these very common types of data structures other than the table is a tree structure, where each data element is the parent and the offspring. An example of such a structure may be the structure of state enterprises, which is headed by the director (the root of the tree), his deputies, heads of departments from which are subject to certain deputies, employees of departments, which are subject to the rulers.

More

Subscribe to our blog

    

Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.
Database consultancy: SaaS database considerations

Utilizing given approach each client get his own database. In such case every client`s data stays isolated from others. It simplifies backing up the data, installing new clients but causes higher cost of maintenance.

March 31, 2023
Database consultancy: tree-like structure

Subject recursion is well covered in the literature, but, nevertheless, the problem of output “tree” does not mean the client and SQL Server many baffled. So, put the problem: there is a table with the name and record id field indicating the parent identifier. Immediately fill in this table, some sort of test data:

March 31, 2023

Discuss your project with us

    

Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.