How to enable / disable Buffer Pool Extension

SQL Server is deficient of memory resources
RAM could not be extended
What should i do?

Buffer Pool Extension is your answer. This is a really useful feature which is used to extend your buffer memory on the disk if the RAM of the server is could not be extended and SQL Server is deficient of memory resources.

To use buffer pool extension you have to enable it and provide space on disk.
To enable BPE, you can use below query and specify the file location which can be used to store the buffer data on disk. the size of file  go up to 32 times the size of RAM

/************************/
USE master
GO
ALTER SERVER CONFIGURATION
SET BUFFER POOL EXTENSION ON
    (FILENAME = 'N:\adhoc\SQL2016.BPE', SIZE = 10 GB);
GO
/************************/

Ho to query the pages the buffer pool extension file?

/************************/
SELECT COUNT(*) AS cached_pages_count
FROM sys.dm_os_buffer_descriptors
WHERE is_in_bpool_extension <> 0
/************************/

How to disable BPE?

we can use below query to disable BPE, this will automatically delete the file.
/************************/
ALTER SERVER CONFIGURATION
    SET BUFFER POOL EXTENSION OFF;
GO
/************************/

You  can increase the buffer pool extension file size online but if you are planning to decrease the size we need to restart the SQL services

By Mohammed Adil