Benefits we get enabling Buffer Pool Extension

All your queries are satisfied using pages in the buffer pool (memory), not pages in the disk. SQL Server loads required pages from the disk to buffer pool for your query if they are not exist in the buffer pool and caches them for reusing, reducing IO demand, improving performance. If your SQL Server gets more and more requests with workloads and memory attached to the server is not enough, SQL Server will not be able to keep them all, and some of them need to be discarded allowing new pages to be placed. This hinders the performance and it can be solved only by adding more memory. Does it always practical?

Although attaching more memory is the best solution for more workloads, SQL Server allows us to attach a fast storage device and configure it for buffer pool. The target type is non-volatile such as SSD but it works with other types too. Once configured, SQL Server uses it for holding only clean pages that contain committed data. This is called as Buffer Pool Extension and this available only with Enterprise Edition of SQL Server 2014 64-bit installation.

Buffer Pool Extension offers three benefits;

• Improve the performance of an OLTP environment with high volume of data reading.
• Low cost for implementing as storage devices are less expensive per megabyte than physical memory per megabyte. 
• Transparent the applications connected with the database. It can be easily enabled and requires no changes to existing applications.

Here is the way of implementing Buffer Pool Extension.

-- Checking whether BFE is enabled
SELECT * FROM sys.dm_os_buffer_pool_extension_configuration;

/*
For testing, if you do not have a SSD storage,
plug a pen drive that has a capacity more than
physical memory added to the machine.
*/
-- Enable buffer pool extension
ALTER SERVER CONFIGURATION
SET BUFFER POOL EXTENSION ON
(FILENAME = 'G:\BFE.bpe', SIZE = 20GB );

-- Checking whether BFE is enabled
SELECT * FROM sys.dm_os_buffer_pool_extension_configuration;

-- Once configured, can check and see whether
-- SQL Server uses it or not
SELECT * FROM sys.dm_os_buffer_descriptors;

-- If require, it can be anytime disabled.
ALTER SERVER CONFIGURATION
SET BUFFER POOL EXTENSION OFF;

Not all SQL Server environments get advantages with Buffer Pool Extension. It is beneficial when following are true;

• OLTP system with high volume of data reads and less data writes.
• Server configured at least with 32GB of RAM but lesser than 64GB of RAM.
• Buffer Pool Extension file is configured to use a file that is between four and ten times the amount of physical memory attached.
• Used storage is high throughput SSD.