BPE特性

背景

在我们已有的较大的项目上，当数据库比较大且基于数据库开发定制的报表较多的情形下，业务高峰的IO波动非常大。基于这个情况，我们想从数据库的特性出发，sql server 2014开始就发布过一个新特性Buffer Pool Extension (BPE)，该特性说白就是内存不够，SSD来凑，我们生产环境使用的2017版，版本上是支持，觉的可以小范围试用。
主要收益如下

增加随机I / O吞吐量
减少I / O延迟
增加交易吞吐量
更大的混合缓冲池提高了读取性能
可以利用当前和未来的低成本内存驱动器的缓存体系结构

操作步骤

--查看当前设置
SELECT [path], state_description, current_size_in_kb,
CAST(current_size_in_kb/1048576.0 AS DECIMAL(10,2)) AS [Size (GB)]
FROM sys.dm_os_buffer_pool_extension_configuration;
 
--设置 Max memory 
EXEC sys.sp_configure 'show advanced options', '1'  RECONFIGURE WITH OVERRIDE;
GO
EXEC sys.sp_configure 'max server memory (MB)', '28672';
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sys.sp_configure 'show advanced options', '0'  RECONFIGURE WITH OVERRIDE;
GO

--启用 BPE  使用1：8比例设置
ALTER SERVER CONFIGURATION
SET BUFFER POOL EXTENSION ON (FILENAME = 'F:BP_Extension.BPE', SIZE = 224 GB);
 
--再次查看启用后的效果
SELECT [path], state_description, current_size_in_kb,
CAST(current_size_in_kb/1048576.0 AS DECIMAL(10,2)) AS [Size (GB)]
FROM sys.dm_os_buffer_pool_extension_configuration;
 
--查看有哪些语句使用了BPE
SELECT DB_NAME(database_id) AS [Database Name], COUNT(page_id) AS [Page Count],
CAST(COUNT(*)/128.0 AS DECIMAL(10, 2)) AS [Buffer size(MB)],
AVG(read_microsec) AS [Avg Read Time (microseconds)]
FROM sys.dm_os_buffer_descriptors
WHERE database_id <> 32767
AND is_in_bpool_extension = 1
GROUP BY DB_NAME(database_id)
ORDER BY [Buffer size(MB)] DESC;
 
--如果需禁用BPE
ALTER SERVER CONFIGURATION
SET BUFFER POOL EXTENSION OFF;

注意事项

BPE只支持64位的2014以及已上的版本；
标准版版BPE只支持Max Memory的1：4，企业版最大可支持1：32，设置BPE小于Max memory 会出错误提示；
BPE设置最好遵循官方的最佳实践设置，1：4或1：8是一个比较好的选择；
BPE目标是改进类似于oltp的工作负载系统，写次数非常多的系统可能无法利用这一特性。
BPE不过是一个文件，尽可能放在快的磁盘中。当然，如果条件允许你可以优先选择增加更多的内存；
一旦投入生产，请避免对文件进行配置更改或关闭功能。这些活动可能会对服务器性能产生负面影响，因为禁用该功能后，缓冲池的大小会大大减少。禁用后，在重新启动SQL Server实例之前，不会回收用于支持该功能的内存。但是，如果重新启用该功能，则将在不重新启动实例的情况下重新使用内存。