The Buffer Pool Extension in SQL

With CPU speeds topping out and I/O rates maximized using solid state drives (SSDs), the next available strategy for increasing OLTP performance is through memory optimization. Databases, because of their size, typically reside on disk. Historically, main memory was significantly more expensive than disk, so typically the memory available for caching data was only a fraction of the size of the database. However, with the significantly reduced cost of system memory over the past 20 years, it’s become more financially feasible to install large amounts of memory in the server. It is now possible for most OLTP databases, or at least the most critical tables, to fit entirely into memory which reduces the performance impact of disk-based I/O, which in turn increases transaction speed performance.

To take maximum advantage of the performance improvements that can be achieved from having your critical OLTP tables memory resident, Microsoft developed the In-Memory Optimization feature for SQL Server. In-Memory Optimization, more commonly referred to as In-Memory OLTP, is the primary and most important new feature introduced in SQL Server 2014. This new feature (which you may sometimes hear referred to by its project code name Hekaton) is fully integrated into the SQL Server database engine.

Another feature introduced in SQL Server 2014 to take advantage of the lower costs and increased sizes of SSDs, is the Buffer Pool Extension feature. The Buffer Pool Extension feature provides the ability for SQL Server to use solid-state drives (SSD) as a non-volatile random access memory (NvRAM) to extend the size of the buffer pool. By offloading buffer cache I/Os from mechanical disk to SSDs, the Buffer Pool Extension feature can significantly improve I/O throughput because of the lower latency and better random I/O performance of SSDs.

These exciting new features for SQL Server are discussed in this chapter.


Overview of In-Memory OLTP

In-Memory OLTP allows OLTP workloads to achieve significant improvements in performance, and reduction in processing time. The In-Memory OLTP engine is completely integrated with the SQL Server database engine and can be accessed transparently via your SQL Server applications. However, the In-Memory OLTP components’ internal behavior and capabilities are different and distinct from the standard database engine components as shown in Figure 33.1