SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

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

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 1st test. This is so-called ideal situation when the RAM is enough for all operational data.

2_1

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 2nd 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 3rd 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:

#

Description of the test

Result, time

1

All data are stored in RAM

1.5 sec

2

All data are stored on disk (cold cache, no data in memory).

2 min 02 sec

3

The size of RAM for SQL Server is limited. About 8% of all data are stored in the cache.

1 min 56 sec

4

The size of RAM is limited as in test ?3, but now BPE is enabled and almost all data are stored in the BPE.

4.6 sec

5

The database is stored on a SSD (cold cache, no data in memory).

6.3 sec

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

Forrards Group Database Blog

I`m an SQL Server consultant experienced in areas such as database design, T-SQL advanced programming, database administration and business intelligence. I have a good understanding of a few subject domains, for example, finance, trading, telecoms and insurance. Right now I`m running a small database consultancy and a blog, though I'm also an active stock trader.

Comments

Leave a comment on the original post [www.forrards.com, opens in a new window]

Loading comments...