Uncover new features of SQL Server 2014 (2)-SSD Buffer Pool (buffer Pool) expansion, 2014 Buffer

Uncover new features of SQL Server 2014 (2)-SSD Buffer Pool (buffer Pool) expansion, 2014 Buffer

Introduction

Another good feature in SQL Server 2014 is that SSD can be virtualized into a portion of the memory for the SQL Server data page buffer. By using SSD to expand the Buffer-Pool, a large number of random IOPS can be carried by SSD, thus greatly reducing random IOPS and PAGE-OUT for data pages.

Ssd as Buffer Pool

SSD is a solid state drive. Unlike traditional disks, SSD has a head moving part. Therefore, random read/write IOPS is much larger than traditional disks. Using SSD as an extension of the Buffer Pool can greatly expand the memory at a very low cost. In the traditional mode, the memory can only accommodate a small part of the hotspot data, resulting in a relatively large Page-Out, as shown in 1.

Figure 1. A large number of random IOPS must be borne by the disk array

However, if SSD is added to the computer's storage system, the memory can be extended to approximately the same as hotspot data at a very low cost, not only improving performance, but also reducing IO costs, as shown in figure 2.

Figure 2. Memory can HOLD almost all hotspot data after expansion

The Comparison Between figure 1 and figure 2 shows that the extended SATA storage can be used. In addition, this feature is transparent and does not require any changes on the application side.

In addition, to avoid data loss, this feature only stores the Clean Page of the Buffer Pool in the SSD serving as the Buffer, you only need to access the Page In Page from the secondary storage.

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

Enable BUFFER Pool Extension

In SQL Server 2014, enabling Buffer Pool Extension is simple, just having SysAdmin permissions and entering a T-SQL statement, as shown in 3.

Figure 3. Enable Buffer Pool Extension

Correspondingly, we can see this extension file in the physical disk. The performance of this 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 smaller than the physical memory or threshold value. Otherwise, an error is reported, as shown in Figure 5.

Figure 5. error message

For this function, SQL Server introduces a brand new DMV and adds a column to the original DMV to describe the Buffer Pool Extention, as shown in 6.

Figure 6. New DMV and updates to the original DMV

In addition, SQL Server introduces a large number of related counters for this feature monitoring, as shown in 7.

Figure 7. Related counters

Summary

The SQL Server Buffer Pool Extension provides us with the possibility of meeting higher enterprise-level requirements at a lower cost. Combined with memory databases, the future possibilities will be extended infinitely.

What is the difference between DB2 and SQL Server?

SQL is Microsoft, db2 is not