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.
.png)
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.
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:
.png)
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.
.png)
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.
.png)
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
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.
MoreSubject 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:
MoreIn 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.
MoreSubscribe to our blog
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.
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:
Discuss your project with us