Supercharging Buffer Pool Using Solid-State Drive -Buffer pool extension in SQL Server 2014 CPT2

1. Problem

Have you ever been memory constrained due to physical hardware constraints or budget constraints? Flash solid-state drives (SSDs) are changing the I/O landscape, which has largely been dominated by traditional hard disk drives (HDDs).

Ideally all database servers must have enough memory to hold every last byte of data we would need to store so that we wouldn’t be worrying about disk latency, PageIOLatch waits etc. Once a server’s memory is maxed out, there isn’t a whole lot you can do. You rewrite your code to reduce overall IO, you made sure your disk layout were as optimal as possible. But all of this was really just a way to compensate for a lack of memory.

2. Solution

Buffer Pool Extension(BPE):

Expand the Buffer Pool without adding any additional expensive memory by extending your buffer pool onto Non-Volatile storage like SSD (Solid-State drives). Buffer pool extension feature in SQL Server 2014 CPT2 enables this by taking advantage of non-volatile storage use them to extend the buffer pool space.

Buffer Pool: SQL Server stores database data and index pages in memory in an area known as Buffer Pool.

o Solid-State drives (SSD): SSD is a solid state disk is a high-performance plug-and-play storage device that contains no moving parts and uses non-volatile memory as a means of storing and accessing data. SSDs are most effective for server applications and server systems, where I/O response time is crucial.

3. Benefits:

The following list describes the benefits of the buffer pool extension feature.

o Increased random I/O throughput

o Reduced I/O latency

o Increased transaction throughput

o Improved read performance with a larger hybrid buffer pool

o A caching architecture that can take advantage of present and future low-cost memory drives

o Performance gains on OLTP workloads, mostly on the read-heavy OLTP.

o No risk of data loss. BPE only deals with clean pages.

o No application modification required. Just enable the feature and you’re ready to go.

o Simple feature syntax

4. Steps: How-To

o The Buffer Pool Extension (BPE) feature provides the seamless integration of solid-state drives (SSD) as a non-volatile random access memory (NvRAM) extension to the Database Engine buffer pool to significantly improve I/O throughput. The addition of the Buffer Pool Extension, adds the ability to extend buffer pool to nonvolatile random access memory for instance an SSD. The non-volatile storage will usually take the form of fast SSD, but technically it could be any disk!

Following Figure gives a High-level architectural overview of the Buffer Manager mechanism. When the buffer pool extension is enabled, SQL Server divides the buffer pool into 2 tiers. The level 1 (L1) tier is in RAM and can hold clean or dirty pages. The level 2 (L2) tier is on disk, and will only hold clean pages. The movement of pages between L1 and L2 is managed by the buffer manager.

Fig: High-level architectural overview.

o With the buffer pool extension enabled, SQL Server 2014 uses the non-volatile storage for data pages, making them faster to retrieve than if they had been paged out of the buffer to their disk storage location.

o The buffer pool extension feature extends the buffer pool cache by accommodating a larger database working set, which forces the paging of I/Os between RAM and the SSDs. This effectively offloads small random I/Os from mechanical disks to SSDs. Because of the lower latency and better random I/O performance of SSDs, the buffer pool extension significantly improves I/O throughput.

How many data pages exists inside cache:

Following figure tells how to check number of database pages currently being occupied in the data cache. The higher the buffer manager database pages are, the less room there is for SQL Server to cache more data pages. This means that SQL Server may have to free up data cache pages order to make room for pages being moved in from disk to the data cache, which can increase disk IO and hurt performance.

Fig: Amount of pages are currently being occupied in the data cache

Set the Buffer Pool Extension:

Let us see how we could set the buffer pool extension.

First check if there is already a buffer pool extension exists. The following figure shows how to check existing Buffer pool extension for the database: [LocalEngagement]

Fig: Check if Buffer Pool Extension enabled

Now let’s create the buffer pool extension using the following script on the specified path:

--Turn on Buffer Pool Extension

ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION ON

( FILENAME = 'E:\Program Files\SQLBufferExtension\dbandbiBufferCache.bpe',

size = 12GB )

Fig : Create Buffer Pool Extension

Now we have created and enabled Buffer Pool Extension. Let’s see the folder properties where we created the buffer pool extension:

Fig : Buffer Pool Extension folder properties

To disable the buffer pool extension, we use ALTER SERVER CONFIGURATION:

Fig : Disable Buffer Pool Extension

5. Metadata:

1. sys.dm_os_buffer_pool_extension_configuration

Returns configuration information about the buffer pool extension in SQL Server. Returns one row for each buffer pool extension file.

2. sys.dm_os_buffer_descriptors

Returns information about all the data pages that are currently in the SQL Server buffer pool. The output of this view can be used to determine the distribution of database pages in the buffer pool according to database, object, or type.

Following Fig shows what is there inside buffer pool.

Fig 4: What is there inside buffer pool?

6. Best Practices

v Determine the size for buffer pool extension:

The buffer pool extension size can be up 32 times of RAM size. Microsoft recommends a ratio between the size of the server memory and the size of the buffer pool extension of 1:16 or less in ratio.

v Avoid disabling or changing configuration settings:

Once in production, avoid making configuration changes to the file Or turning the feature off. These may affect server performance because the buffer pool is significantly reduced in size when the feature is disabled.

TSQL Masters