Performance Improvements - Getting Started with SQL Server 2014 Administration - page 80

Data pages get modified in memory, and the changed data pages are known as dirty
pages. The checkpoint process, internal to SQL Server, writes the data pages back to
the disk. Memory pressure on the server, along with database checkpoints, will cause
dirty pages in the buffer cache to be removed from the cache and written to mechanical
disks and then read back into the cache. SQL Server I/O operations are usually small
random reads and writes of the order of 4 to 16 KB of data. Small random I/O patterns
will incur frequent seeks, which will compete for the mechanical disk arm. This can
reduce the I/O performance and reduce the aggregate I/O throughput of the system.
It is the competition for the resources to carry out the mechanical disk I/O operations
that can cause a system to become slow.
In the earlier versions of SQL Server, a reasonable approach to solving these
I/O bottlenecks was to add more RAM, or alternatively, to add additional high-
performance I/O spindles, or a combination of the two. These options are definitely
helpful and are still likely to be helpful in SQL Server 2014. However, there are some
drawbacks associated with them. These drawbacks are as follows:
• RAM is generally more expensive than data storage drives and adding
extra spindles and disk drives increases capital expenditure in hardware
acquisition. This can increase operational costs by increasing the power
consumption and the probability of component failure.
• Mechanical disk drives can and will fail eventually.
The buffer pool extension feature allows SQL Server to extend the buffer pool
cache by making use of Solid State Drives ( SSD ). This enables the buffer pool
to accommodate a larger database working set, which forces the paging of I/Os
between RAM and the SSDs instead of the mechanical disk. This effectively offloads
small random I/O operations from mechanical disks to SSDs. Because SSDs offer
better performance through lower latency and better random I/O performance, the
buffer pool extension can significantly improve the I/O throughput, thus removing
I/O bottlenecks and speeding up the performance of a system. As I/O is usually
the slowest component in SQL Server system operations, this will help increase the
database performance.
The following code will enable the buffer pool extension on your SQL Server database:
ALTER SERVER CONFIGURATION
SET BUFFER POOL EXTENSION ON
(FILENAME = 'F:\SSDCACHE\Example.BPE', SIZE = 50 GB)