一、buffer pool extension 简介
"buffer pool extension"是 SQL Server 2014 版本引入的新功能,中文译名:缓冲池扩展。
该功能支持将SSD硬盘作为数据库缓存,利用SSD优秀的随机读写性能,提高数据库的IO吞吐量。
二、buffer pool extension 基本使用方法
1.添加缓冲池扩展
alter server configuration
set buffer pool extension on (filename = 'E:\ssd_buffer_pool.bpe', size = 10GB)
本例中E盘为SSD盘,在E盘下创建一个10GB大小的缓冲池扩展文件: ssd_buffer_pool.bpe (名字可以随便取)。
命令执行成功后观察E盘,缓存文件创建成功。
2.查看缓冲池扩展信息
select * from sys.dm_os_buffer_pool_extension_configuration
查询结果显示上一步的添加操作已生效。
3.查询哪些页面缓存到扩展缓冲池中
select * from sys.dm_os_buffer_descriptors
where is_in_bpool_extension = 1
动态管理视图 sys.dm_os_buffer_descriptors 中有一个字段:is_in_bpool_extension,如果该字段为1,表示页面缓存在 buffer pool extension 中。
4. 撤销缓冲池扩展
alter server configuration
set buffer pool extension off
执行该命令后,数据库会收回SSD缓冲池扩展,之前创建的缓冲池扩展文件,也随之消失。
三、buffer pool extension 实现细节
1.SSD添加到 buffer pool 中后,buffer pool 中内容分为两部分:
- 原本就处于DRAM内存中的部分,称作 L1 级别缓存
- SSD扩展部分,称作 L2 级别缓存
2.L2级别缓存,仅存储干净的页面。如果L2中的页面需要修改,缓冲区管理器会处理 L1 和 L2 之间的干净页移动。
3.如果 L1 级别空间足够,数据库优先将页面缓存到 L1,只有当 L1 空间不足时,才会缓存到 L2。
官网给出的 buffer pool 架构图:
四、buffer pool extension 性能测试
1.环境准备
(1) 按照上文步骤,添加好缓冲池扩展
(2) 为了模拟页面位于SSD 扩展缓存中的情况,尽量将数据库引擎的"最大服务器内存"设置为较小的值。这样当 L1 内存级别空间不足时,就会将页面缓存到 L2 级别的SSD。
(3) 用如下脚本生产数据
create table dbo.t1(
ID INT primary key,
NAME char(8000)
)
GO
declare @i int
declare @name varchar(20)
set @i = 1
begin transaction
while @i <= 100000
begin
set @name = 'aaa' + convert(varchar, @i);
insert into dbo.t1 values (@i, @name);
set @i = @i + 1;
if @i % 100 = 0
begin
commit;
begin transaction;
end
end
commit
GO
(4) 数据生产完毕后,通过 sys.dm_os_buffer_descriptors 视图,结合 %%physloc%% rowid函数算出页面ID,找出几条位于 buffer pool extensioin 即 L2 SSD 区域的数据,之后就通过这几条数据,分别测试其位于L2 SSD、位于 L1 内存、位于磁盘时的读取速度。
本例中选取了三条数据:“id=81291, 47377, 81000”,初始情况下这三条数据位于 L2 SSD 扩展缓存区的3个不同页面,且这3个页面不连续(为了更好的比对性能差异)。
select %%physloc%% as rowid,
convert(int, substring(%%physloc%%, 4, 1) + substring(%%physloc%%, 3, 1)
+ substring(%%physloc%%, 2, 1) + substring(%%physloc%%, 1, 1)) as page_id,
*
from dbo.t1
where id in (81291, 47377, 81000)
order by page_id
select database_id, file_id, page_id, page_level, is_modified, is_in_bpool_extension
from sys.dm_os_buffer_descriptors
where is_in_bpool_extension = 1
and page_id in (477406, 511158, 730)
order by page_id
(5) 打开IO性能统计开关、TIME统计开关,便于性能比对
set statistics io on
go
set statistics time on
go
2.测试从 buffer pool extension, 即 SSD 中读取数据性能
- 物理读取:0 次
- 执行时间:17 毫秒
3.测试从内存读取数据性能
为了将位于 L2 SSD 中的页面移动到 L1 内存中,直接撤销缓冲区扩展,这样缓冲区管理器就会将 L2 中的页面移动到 L1中。
-- 撤销 "extension buffer pool"
alter server configuration
set buffer pool extension off
-- 验证页面从SSD迁移到了内存
select database_id, file_id, page_id, page_level, is_modified, is_in_bpool_extension
from sys.dm_os_buffer_descriptors
where page_id in (477406, 511158, 730)
order by page_id
再次读取这三条记录
- 物理读取: 0 次
- 执行时间: 13 毫秒
4.测试从磁盘读取数据性能
清除所有干净的缓存页面,迫使从磁盘读取数据
--从缓存中清除干净的页面
dbcc dropcleanbuffers
go
--验证页面已不在缓存中
select database_id, file_id, page_id, page_level, is_modified, is_in_bpool_extension
from sys.dm_os_buffer_descriptors
where page_id in (477406, 511158, 730)
order by page_id
测试从磁盘中读取数据
- 物理读取: 7 次
- 执行时间: 100 毫秒
5.性能比对
物理读取(次) | 执行时间(毫秒) | |
---|---|---|
L1:内存, buffer pool | 0 | 13 |
L2:SSD, buffer pool extension | 0 | 17 |
磁盘 | 7 | 100 |
参考文章
[1] 官网:Buffer pool extension
[2] Buffer pool扩展简介