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
Thanks for sharing this informative content , Great work.
ReplyDeleterasmussen student portal