1 Buffer Pool Extensions in SQL Server 2014

This is new feature for SQL Server 2014.  We can use SSD to extend the buffer pool size. The idea is using the faster SSD , in the event when the physical memory need more space, it will use SSD instead of physical disk.  However, for testing purpose, the SQL server can use any disk as extension, it does not limit to SSD only. Database engine would not have idea whether it is SSD or not.

--- Check buffer pool extension configuration 

select * from sys.dm_os_buffer_pool_extension_configuration

The size of the Buffer pool extension must be large than current MAX server memory otherwise, we will get the error when configure the buffer pool extension

Msg 868, Level 16, State 1, Line 6
Buffer pool extension size must be larger than the current memory allocation threshold 8191 MB. Buffer pool extension is not enabled.

--- Enable 

--- For testing purpose, reduce the MAX memory to 1GB

EXEC sys.sp_configure N'max server memory (MB)', N'256'

GO

RECONFIGURE WITH OVERRIDE

GO

Alter server configuration set buffer pool extension on ( FILENAME='C:\SSD\buffer.xd',size=2GB );

Check buffer pool extension usage

select 

        CASE is_in_bpool_extension 

        WHEN 1 THEN 'SSD'

        ELSE 'RAM'

        END AS Location,

        is_in_bpool_extension,

        COUNT(*) AS nb_pages,

        COUNT(*)*8/1024 AS size_in_mb,

        COUNT(*)*100/(SELECT COUNT(*) from sys.dm_os_buffer_descriptors) AS percent_

from sys.dm_os_buffer_descriptors

Group by is_in_bpool_extension

Disable Buffer pool extension

--- Check buffer pool extension configuration 

select * from sys.dm_os_buffer_pool_extension_configuration