One of the exciting new features coming out of the SQL Server 2014 release is the Buffer Pool Extension (BPE) option. BPE allows you extend the SQL Server Buffer Pool to fast IO devices like SSDs and therefore provides the equivalent of a level 2 cache. Basically SQL Server doesn’t have to retrieve data from slower disks for frequently

used data pages that were previously flushed out due to pressure on the standard Buffer Pool memory mapped in RAM.

The diagram below describes the architecture.

The BPE only holds clean pages, any dirty pages are written back to disk and the BPE i.e this is known as a Dual Write system. Note a database page in memory is considered “Dirty” when updates have occurred on one or more records on that page and the page has not yet been written to the data file. Therefore if there is an unexpected service restart or you lose the SSD disk then the SQL Server databases are not corrupted since all updates would already exist in the data file or at the very least in the Transaction Log file if the Dirty data page hadn’t been written to disk yet.

I decided to road test the BPE feature on a Amazon Web Services (AWS) EC2 instance which has local SSD storage. I chose the C3.Large instance that has 2 x CPUs, 3.75 GB RAM, 2 x 16 GB SSD local storage. The following OLTP and Data Warehouse workload was generated by HammerDB www.hammerora.sourceforge.net :-

• Ran DW test with 1 Query set of 22 queries – compared Elapsed time.
• Ran OLTP test for 5 minutes – compared Transaction Rate/sec and orders inserted.

The following TSQL commands were used to enable a 10 GB BPE on the local SSD, review the BPE setup and then turn off the BPE feature.

OK so the results were interesting.

I got a 16.8{2ec0bbd3bfdf207d2f0779c26660c3798ccadae611e41b6dbc787103c4a85cdd} improvement in the performance of the DW query elapsed time, not bad considering all I did was execute one command.

Below is the comparison chart for the DW test.

The OLTP results showed no improvement, in fact, the performance was slightly worse with BPE enabled. The OLTP results were expected considering the bulk of the SQL statements were insert statements. In fact, the Performance Monitor counter for BPE {2ec0bbd3bfdf207d2f0779c26660c3798ccadae611e41b6dbc787103c4a85cdd} Utilisation indicated only 3{2ec0bbd3bfdf207d2f0779c26660c3798ccadae611e41b6dbc787103c4a85cdd} – 6{2ec0bbd3bfdf207d2f0779c26660c3798ccadae611e41b6dbc787103c4a85cdd} of BPE was used. In contrast the 78{2ec0bbd3bfdf207d2f0779c26660c3798ccadae611e41b6dbc787103c4a85cdd} to 80{2ec0bbd3bfdf207d2f0779c26660c3798ccadae611e41b6dbc787103c4a85cdd} of the BPE was used in the DW tests.

Below is the comparison chart for the OLTP test.

I suspect that a system that has a mixture of workload around 60/70 {2ec0bbd3bfdf207d2f0779c26660c3798ccadae611e41b6dbc787103c4a85cdd} reads vs 30/40 {2ec0bbd3bfdf207d2f0779c26660c3798ccadae611e41b6dbc787103c4a85cdd} write would be the sweet spot for the BPE feature, assuming you have limited RAM for standard Buffer Pool and a locally attached SSD.

The implications though for deploying to the AWS is bit more significant in terms of monthly hosting costs.

Firstly you can provision an EC2 C class instance with less memory and use BPE on the SSD storage that comes with this class of EC2 instance.

Secondly when the BPE was warmed up (populated with data pages) for the DW test, 50 to 60{2ec0bbd3bfdf207d2f0779c26660c3798ccadae611e41b6dbc787103c4a85cdd} of disk IOs were hitting the BPE file on the SSD. For your database files, this would allow you to either provision a Standard EBS disk and reduce the number of costed IOs generated or attach a IOPS Provisioned EBS disk will a lower IOPS setting.

In both cases, BPE could potentially reduce the monthly cost of your EC2 instance.