Perhaps you started out with X amount of memory when your SQL server was brought online and over time, with additional load and activity on that SQL server, users are not quite getting the type of performance they’re used to getting. Sure, you can buy more memory. What if that’s not an option?
If you’re running low on memory and need a little boost, enabling buffer pool extension can take advantage of an SSD as an “extension” for the buffer pool.
Microsoft advertises the following as benefits of implementing buffer pool extension:
- Increased random I/O throughput
- Reduced I/O latency
- Increased transaction throughput
- Improved read performance with a larger hybrid buffer pool
- A caching architecture that can take advantage of present and future low-cost memory drives
Enabling Buffer Pool Extension
We’ll want to turn on buffer pool extension with a file extension of .BPE and point to a path on a SSD that has a good chunk of space available:
ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION ON (FILENAME = 'D:\BPE\BP_Extension.BPE', SIZE = 4 GB);
Next, restart SQL Server during the next best available time to make sure your changes take effect. Following the SQL Server restart, the buffer pool extension will be in use.
To turn it back off, we simply run:
ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION OFF;
Buffer Pool Extension Status
Microsoft recommends a couple DMVs to review buffer pool extension information. The first is sys.dm_os_buffer_pool_extension_configuration:
SELECT * FROM sys.dm_os_buffer_pool_extension_configuration; GO
That will show us the following:
We won’t see much before enabling, only a note that buffer pool extension is disabled. If we set buffer pool extension on with the script from earlier in the post and run our query again, we will see information to confirm our actions:
The other DMV is sys.dm_os_buffer_descriptors. With that DMV, you can check the is_in_bpool_extension column to see what is cached in the buffer pool extension.
Thanks for reading!