SQL Server 2014: Buffer Pool Extension to improve I/O throughput and Query Cache

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

SQL Server 2014 introduced Buffer Pool Extension to improve the performance of query cache which reduces the Disk I/O and increase the overall performance.

This buffer pool area is a global resource shared by all databases, and it stores cache related information like frequently used data, frequently executed execution plans and pages information.

The buffer pool area is a kind of memory cache which holds the frequently used result and returns the result from it rather than needing to go back to disk to retrieve the results.

The buffer pool is limited by the max server memory configuration option and the total available RAM.

Now using the concept of Buffer Pool Extension,

You can configure additional SSD disk to work as other buffer pool area. It doesn’t mean that you are increasing the RAM.

Why SSD (solid-state drive), because of it ten times faster than regular disk.

You should add SSD as buffer pool extension because with normal disk you will not get expected performance of the buffer pool.

What you should do?

Attached one SSD drive to the server and alter the server configuration parameter by adding the .bpe file. This BPE (Buffer Pool Extension) file works as an additional buffer pool area.

You just give to correct folder path of your SSD drive with .bpe filename and size, it automatically creates .bpe file with specified size.

Enable Buffer Pool Extenstion:

Disable Buffer Pool Extension: