Buffer Pool Extension SQL 2014

We all are aware of Buffer Pool and its importance while querying database.
When ever there is request for data , data and index pages are read from disk into the buffer pool
Also from buffer pool modified pages are written to disk during checkpoints. These pages are then read again from disk.All this random I/O activities putting pressure on disk.In general to resolve this issues we have to add RAM or high performance disks. All this leads to cost escalation and more Disk and Ram cause hardware failure.

So how to scale up the system ?

Now in SQL 2014  Buffer Pool Extension helps , where we can expand buffer pool to SSD, which is managed by buffer pool manager.As SSDs perform  better than regular hard disks, its effectively reduce load from hard disk, which helps to  gain I/O performance
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.Also buffer pool size grows without adding additional RAM , helps in querying larger tables with lower cost.


Now lets see how to enable buffer pool extension


We have two system tables to get buffer pool information

sys.dm_os_buffer_pool_extension_configuration
sys.dm_os_buffer_descriptors

First we need to check either buffer pool is enabled or not

select * from sys.dm_os_buffer_pool_extension_configuration

As we can see the buffer pool extension is disabled

Now we will enable it

My current RAM is 8192  MB

So if I specify its less than 8192 MB it will raise error

alter server configuration

set buffer pool extension on

(filename = 'c:\buffercache.bpe',size= 6 gb)

Now we will set it to  more than 8192 MB

alter server configuration

set buffer pool extension on

(filename = 'c:\buffercache.bpe',size= 8 gb)

Now we will see status of Buffer Pool Extension

select * from sys.dm_os_buffer_pool_extension_configuration

Now we will off the buffer pool extension

alter server configuration

set buffer pool extension off

Now we will look into other system tables
This has stored information all pages which are stored in buffer pool

select * from sys.dm_os_buffer_descriptors

Here we can see a new column [is_in_bpool_extension] added which shows either the cached page is stored in buffer pool extension or not