Introduced in SQL Server 2014 this feature increases the size of the SQL Server Buffer Pool by allowing the Buffer Pool to be extended above the amount of physical memory allocated to SQL Server. When you have reached the maximum physical memory, the Buffer Pool extension allows you to use solid state drives to increase the size of the Buffer Pool and get the performance gains that solid state drives offer over traditional disks.
The Buffer Pool is the place in system memory that holds data and index pages read from disk. SQL Server uses its Buffer Pool to cache pages which allows queries that share the same pages to perform faster as they can pull the data from the Buffer Pool rather than needing to go to the physical disk to retrieve the data.
When performing a simple SELECT statement, SQL Server reads the data from disk into memory. At this point, the data is clean. Once the data is changed by performing an INSERT / UPDATE statement, it is marked as dirty. Eventually when a CHECKPOINT is performed the dirty pages are written to disk and marked as clean.
Clean pages may be flushed from memory when the Buffer Pool comes under pressure. At this point the data is gone from memory and SQL Server has to read it from disk the next time a user runs a query that requires the data.
Buffer Pool size is determined by server memory and the target server memory specified in the 'Max Server Memory' parameter. When that threshold is reached and SQL Server needs to read more pages, previous cached pages are discarded.
So in a nutshell SQL Server Buffer Pool Extension uses a Flash or DRAM memory device like an SSD disk as a cache to the Buffer Pool by creating a file on disk.
Well you can enable Buffer Pool Extension on your server by using the script below:
USE master GO ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION ON (FILENAME = 'C:\Databases\Test.BPE', SIZE = 2 GB) GO
Be careful because in order to change the size of the Buffer Pool Extension you need to disable and then re-enable this option with its new size. The Buffer Pool can be disabled with the following script:
USE master GO ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION OFF GO
Some recommendations that might be useful and taken into consideration: