Discover what new features are in SQL Server 2014 (2)-Solid-State disk buffer pool (buffer pools) extension _mssql

Brief introduction

Another very good feature of SQL Server 2014 is that SSD can be virtualized as part of memory for use by SQL Server data page buffers. By using SSD to extend Buffer-pool, a large number of random IOPS can be hosted by SSD, which reduces the random IOPS and page-out for data pages.

SSD as Buffer Pool

SSD is a solid-state drive, unlike a traditional disk with a magnetic head moving part, so random read and write ioPS far larger than the traditional disk. Using SSDs as an extension of buffer pool, you can expand memory at a very low cost. The traditional pattern is that memory can only hold a small portion of the hot data, resulting in a relatively large page-out, as shown in Figure 1.

Figure 1. A large number of random iops needs to be borne by the disk array

But if you take into account the storage system that adds SSD to your computer, the memory can be extended at very low cost to approximately equal to hot data, not only to improve performance, but also to reduce IO costs, as shown in Figure 2.

Figure 2. After extended memory can almost hold all hot data

The comparison between Figure 1 and Figure 2 shows that you can use a cheaper SATA storage after the expansion. In addition, the feature is transparent and does not require any changes to be made by the application side.

In addition, in order to avoid the loss of data, only the clean Page of buffer pool is stored in the SSD as buffers, even if there is a problem with SSD, only page in page from secondary storage.

Finally, this feature is specially optimized for NUMA, and the CPU can access memory without hindrance even with systems with more than 8 sockets.

Enable buffer Pool Extension

In SQL Server 2014, enabling buffer Pool extension is simple enough to enter a T-SQL statement, as shown in Figure 3, just after you have sysadmin permissions.

Figure 3. Enable Buffer Pool Extension

Correspondingly, we can see this extension file on the physical disk, which is very similar to the Windows virtual memory file, as shown in Figure 4.

Figure 4. Corresponding buffer pool extension file

It is worth noting, however, that the memory extensions that we have enabled cannot be less than physical memory or thresholds, or they will be an error, as shown in Figure 5.

Figure 5. Error message

For this feature, SQL Server introduced a new DMV and added a column to the original DMV to describe the buffer Pool extention, as shown in Figure 6.

Figure 6. Introduction of the new DMV and updates to the original DMV

In addition, for monitoring the feature, SQL Server introduces a number of counters associated with it, as shown in Figure 7.

Figure 7. Related counters

Summary

    SQL Server Buffer Pool extension gives us the possibility to meet higher enterprise-level requirements at a lower cost, combining the memory database, the possibilities for the future will extend indefinitely.