SQLServer buffer pool extension

SQL Server 缓冲池扩展

原创已于 2022-09-22 14:45:09 修改 · 1.1k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sqlserver #数据库

于 2022-09-19 14:42:26 首次发布

介绍SQL Server 2014新增功能bufferpoolextension，利用SSD提升数据库性能。包括配置方法、实现原理及性能对比测试。

一、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