Uncover the new features of SQL Server 2014 (2) – SSD buffer pool expansion

Time:2021-7-13

brief introduction

Another very good function of SQL Server 2014 is that SSD can be virtualized as a part of memory for SQL server data page buffer. By using SSD to extend the buffer pool, a large number of random IOPs can be carried by SSD, thus greatly reducing the random IOPs and page-out for data pages.

SSD AS Buffer Pool

SSD is a solid state hard disk, unlike the traditional disk, which has the moving part of the head, so the random read-write IOPs is much larger than the traditional disk. Taking SSD as the extension of buffer pool, we can expand the memory at a very low cost. The traditional mode is that the memory can only hold a small part of the hot data, resulting in a relatively large page out, as shown in Figure 1.

Figure 1. A large number of random IOPs need to be undertaken by disk array

However, if SSD is added to the computer’s storage system, the memory can be expanded to about hot data at a very low cost, which not only improves the performance, but also reduces the IO cost, as shown in Figure 2.

Figure 2. The expanded memory can hold almost all hot data

From the comparison between figure 1 and Figure 2, it can be seen that after expansion, the cheaper SATA storage can be used. In addition, the feature is transparent and does not require any changes on the application side.

In addition, in order to avoid data loss, this feature only stores the clean page of the buffer pool in the SSD as a buffer. Even if the SSD has problems, it only needs to store the page in page from the secondary storage.

Finally, this feature is specially optimized for NUMA. Even if the system has more than 8 sockets, the CPU can access the memory barrier free.

Enable buffer pool extension

In SQL Server 2014, it is very simple to enable buffer pool extension. You only need to have sysadmin permission and enter a T-SQL statement, as shown in Figure 3.

   

Figure 3. Enable buffer pool extension

Correspondingly, we can see the extended file on the physical disk. The performance of the file is very similar to that of the windows virtual memory file, as shown in Figure 4.

   

Figure 4. Corresponding buffer pool extension file

However, it is worth noting that the memory expansion we enable cannot be less than the physical memory or threshold, otherwise an error will be reported, as shown in Figure 5.

   

Figure 5. Error message

For this function, SQL Server introduces a new DMV and adds a column to the original DMV to describe the buffer pool extension, as shown in Figure 6.

   

Figure 6. The new DMV introduced and the update to the original DMV

In addition, for the monitoring of this feature, SQL server also introduces a large number of counters related to it, as shown in Figure 7.

   

Figure 7. Correlation counter

Summary

SQL Server buffer pool extension provides us with the possibility of meeting higher enterprise level requirements at lower cost. Combined with in memory database, the future possibility will be extended infinitely.