Let’s take a closer look at how we can speed up the system’s performance using the Buffer Pool Extension. For testing I will use a virtual machine with 4 GB RAM. I created a separate database with one table. The table has 2 columns [id] and [n]. The first column contains integer data type. This column is the primary key with a clustered index on it. The second column simply presents any data in the system.

use [master];

go

create database [BufferPoolExtensions_test];

go

use [BufferPoolExtensions_test];

go

create table [dbo].[TestTable] (        

[id] int identity(1,1) not null,       

[n] [char](1000) not null,        

constraint [PK_TestTable] primary key clustered ([id]) );

go

insert into [dbo].[TestTable] ([n])

values (replicate('A', 1000));

go 1000000

The table size is slightly more than 1 GB and it will easily fit in the memory while running the 1^st test. This is so-called ideal situation when the RAM is enough for all operational data.

For the test I will carry out 10 000 random requests to select one row of data. This is a good example of OLTP read load. I am going to perform the mentioned request several times and then calculate the arithmetic average of the results:

select [n] from [dbo].[TestTable] where [id] = cast(rand(checksum(newid())) * 1000000 as int) + 1;

go 10000

The test described above (when all the data are in memory and BPE is off) shows the result of 1.4 seconds. I’d like to remind that it is the ideal situation, when we have sufficient amount of memory and all data are in the cache.

For the 2^nd test I will slightly change a query and add a command to remove all buffers from the Buffer Pool with the aim to get the worst possibility when there is no cache data.

dbcc dropcleanbuffers;

go

select [n] from [dbo].[TestTable] where [id] = cast(rand(checksum(newid())) * 1000000 as int) + 1;

go 10000

The result is 2 min and 3 sec.

In the 3^rd test I will limit the maximum size of memory that SQL Server can use to 256 MB which is almost 4 times less than the size of the data. Then I try to repeat the test. It is worth noting that during the test not more than 90 MB of data (that is only about 8% of all data in the table) are in the cache. And the result is expected – 1 minute 56 seconds.

In the 4th test I will enable Buffer Pool Extension of 4 GB and perform the whole table scan to be sure that all the data are either in memory or in the BPE. It is important to note here that almost all data are stored in the BPE, in other words SQL Server tries to put all clean pages in the BPE and not to keep them in memory. Test result is 4.6 seconds, which is 3 times worse than in test ?1 (all data are in memory) , but 25 times better than in test ?3 (when RAM is not enough).

And for the last test I will store the whole database on a SSD, disable the BPE and clean the cache before each start. The result is 6.3 seconds, that is even slower than using BPE.

The final table:

Based on the tests results I conclude that using BPE can significantly speed up random selection of data from a table in case the memory is not enough for caching the entire table. The results of the last two tests are of especial interest – it turns out that the use of BPE can be better than database storing on a SSD. But I should note here again that my tests do not reflect your situation and results can vary significantly. Therefore, you’d better test the option for your particular load and only then make a decision.

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