合 MSSQL 2014新功能之缓存池扩展BPE (Buffer Pool Extension)

发布日期 2024年4月26日 · 已更新 2024年5月10日

0 238 1

Tags： MSSQL SQL Server 整理自官网 SQL Server 2014 缓存池扩展BPE (Buffer Pool Extension)缓冲池扩展

👉 本文共约8041个字，系统预计阅读时间或需31分钟。

简介
缓冲池扩展的优点
容量限制
扩展
buffer pool extension 实现细节
启用缓存池扩展
Perfmon counter性能监视器
buffer pool extension 性能测试
测试2
数据在磁盘上，从磁盘读取数据。
数据在内存缓存中，从内存读取数据
数据在SSD扩展盘上有缓存，从SSD缓存读取数据
结论
sys.dm_os_buffer_descriptors
A.返回每个数据库的缓存页计数
B.返回当前数据库中每个对象的缓存页计数
其它
总结
参考

简介

SQL Server 2014缓存池扩展 (Buffer Pool Extension)功能可以将缓存池扩展到较快的SSD存储上。为内存比较紧张的系统提供了新的扩展途径。

SQL Server 从磁盘中读入数据，并且存放在buffer pool中以供读取和修改，修改完之后脏数据还是放在buffer pool中，当内存紧张执行lazy write把脏数据写入磁盘，并且释放内存页。要用时又会从磁盘中读取，这样无形中加大了对io的需求。

SQL Server 2014之前解决方法：加内存，减少对IO的需求

SQL Server 2014解决办法：使用buffer pool扩展，加入SSD硬盘，加大工作集，减少对IO的需求。

SQL Server 2014 中另一个非常好的功能是，可以将SSD虚拟成内存的一部分，来供 SQL Server 数据页缓冲区使用。通过使用SSD来扩展 Buffer-Pool，可以使得大量随机的 IOPS 由 SSD 来承载，从而大量减少对于数据页的随机 IOPS 和 PAGE-OUT。

Buffer Pool 扩展可以带来以下好处。

1．提高随机I/O的吞吐量

2．降低I/O延迟

3．提高单位时间内处理事务的吞吐量

4．显著地提高读性能

5．以软件方式为客户实现了类似于混合硬盘的效果。

总的来说缓存池扩展对于读远远高于写的OLTP系统比较有帮助，对于以下环境，则帮助不大：

1．数据仓库。您应该考虑列存储而不是缓存池扩展

2．写入比较多的OLTP。

3．拥有大于64GB内存的服务器

缓存池扩展支持以下两种模式

CW：只向SSD写入干净页。

DW：双重写，即同时向SSD和硬盘写入脏页。

下面2张图里，C: 干净页 D：脏页 R：随机 S：顺序

1) 顺序写总是直接写入硬盘，不会写入SSD
2) 干净页的随机写是写入SSD，因为既然是干净页，硬盘上已经有一份拷贝。
3) 当缓存池尚有空间时，缓存池扩展采用CW模式的，只对SSD只写入干净页。
4) 当缓存池空间已满，需要将一部分脏页刷到硬盘上的时候，它是DW模式的，脏页将同时写入SSD和硬盘。

缓冲池扩展的优点

SQL Server 数据库的主要用途是存储和检索数据，因此，大量磁盘 I/O 是该数据库引擎的一个核心特点。由于磁盘 I/O 操作可能会占用消耗很多资源并且耗时较长，所以 SQL Server 侧重于使 I/O 极为高效。缓冲池用作 SQL Server的主内存分配源。缓冲区管理是实现高效 I/O 操作的关键环节。缓冲区管理组件由下列两种机制组成：用于访问及更新数据库页的缓冲区管理器和用于减少数据库文件 I/O 的缓冲池。

数据和索引页从磁盘读入缓冲池，修改后的页（也称为脏页）写回磁盘。服务器和数据库检查点上的内存压力会造成缓冲区缓存中的热（活动）脏页被逐出缓存并写入机械磁盘，然后又读回到缓存中。这些 I/O 操作通常是 4 到 16 KB 数据的小型随机读和写操作。小型随机 I/O 模式会导致频繁搜索、机械磁盘臂争用、I/O 滞后时间延长以及系统的总 I/O 吞吐量减少。

解决这些 I/O 瓶颈的典型方法是添加更多 DRAM，或者添加高性能 SAS 主轴。虽然这些方法很有用，但它们具有明显缺点：DRAM 比数据存储驱动器成本更高，增加主轴数会增加硬件购置的资本支出，并且功耗和部件故障概率都会提高，从而增加运行成本。

缓冲池扩展功能通过非易失性存储器（通常为 SSD）来扩展缓冲池缓存。由于这种扩展，缓冲池可以容纳更大的数据库工作集，可强制在 RAM 和 SSD 之间对 I/O 分页。这会有效地将小型随机 I/O 从机械磁盘卸载到固态硬盘。由于固态硬盘滞后时间短且具有更佳随机 I/O 性能，缓冲池扩展可显著提高 I/O 吞吐量。

下面的列表说明了缓冲池扩展功能的优点。

随机 I/O 吞吐量提高
I/O 滞后时间缩短
事务吞吐量提高
由于采用更大的混合缓冲池，读取性能提高
具有一种可利用现有和将来的低成本内存驱动器的缓存体系结构

容量限制

SQL Server Enterprise 版本允许的最大缓冲池扩展大小为 max_server_memory 值的 32 倍。

SQL Server Standard 版本允许的最大缓冲池扩展大小为 max_server_memory 值的 4 倍。

扩展

SSD 是固态硬盘，不像传统的磁盘有磁头移动的部分，因此随机读写的 IOPS 远远大于传统的磁盘。将 SSD 作为 BufferPool 的延伸，就可以以非常低的成本巨量的扩充内存。而传统的模式是内存只能容纳下热点数据的一小部分，从而造成比较大的 Page-Out，如图1所示。

图1.大量随机的 IOPS 需要由磁盘阵列所承担

但如果考虑到将 SSD 加入计算机的存储体系，那么内存可以以非常低的成本扩展到约等于热点数据，不仅仅是提升了性能，还可以减少 IO 成本，如图2所示。

图2.扩展后内存几乎能 HOLD 所有热点数据

由图1和图2的对比可以看出，扩展后可以使用更便宜的 SATA 存储。此外，该特性是透明的，无需应用程序端做任何的改变。此外，该特性为了避免数据的丢失，仅仅在作为缓冲区的 SSD 中存储 Buffer Pool 的 CleanPage，即使 SSD 出现问题，也只需要从辅助存储中 PageIn 页即可。最后，该特性对于 NUMA 进行了特别优化，即使拥有超过8个 Socket 的系统，CPU 也能无障碍的访问内存。

buffer pool extension 实现细节

1.SSD添加到 buffer pool 中后，buffer pool 中内容分为两部分：

原本就处于DRAM内存中的部分，称作 L1 级别缓存
SSD扩展部分，称作 L2 级别缓存
2.L2级别缓存，仅存储干净的页面。如果L2中的页面需要修改，缓冲区管理器会处理 L1 和 L2 之间的干净页移动。

3.如果 L1 级别空间足够，数据库优先将页面缓存到 L1,只有当 L1 空间不足时，才会缓存到 L2。

SSD 存储用作内存子系统的扩展而不是磁盘存储子系统的扩展。也就是说，通过缓冲池扩展文件，缓冲池管理器可以使用 DRAM 和 NAND 闪存，在由固态硬盘支持的非易失性随机存取内存中保持一个大得多的温热页缓冲池。这会在固态硬盘上创建一个多级缓存层次结构，级别 1 (L1) 作为 DRAM，级别 2 (L2) 作为缓冲池扩展文件。仅将干净页写入 L2 缓存，以帮助确保数据安全。缓冲区管理器会处理 L1 和 L2 缓存之间的干净页移动。

下图所示为缓冲池相对于其他 SQL Server 组件的高级体系结构概览。

官网给出的 buffer pool 架构图：

启用缓冲池扩展后，该功能会指定固态硬盘上缓冲池缓存文件的大小和文件路径。此文件是固态硬盘上的一个连续存储范围，是在 SQL Server实例启动期间静态配置的。只有在禁用了缓冲池扩展功能的情况下，才能修改此文件的配置参数。禁用缓冲池扩展后，将从注册表中删除所有相关的配置设置。 SQL Server 实例关闭时，将会删除缓冲池扩展文件。

启用缓存池扩展

以下是如何使用缓存池扩展的具体步骤：

首先检查SQL server最大内存的设置。建议将缓存池扩展的大小设为最大内存的16倍或以下。使物理内存 (max_server_memory) 的大小与缓冲池扩展的大小之比不超过 1:16。介于 1:4 至 1:8 之间的比率是最佳的。

请先做好充分测试再实施到生产环境。

USE master
GO
EXEC sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'max server memory (MB)'
GO
/*
EXEC sp_configure 'max server memory (MB)', [VALUE (MB)]
RECONFIGURE WITH OVERRIDE
GO
*/

select value 'MEM_MB' FROM sys.configurations where name  in ( 'max server memory (MB)');

 select physical_name from  sys.master_files;

 exec xp_fixeddrives;

-- 以下脚本启用了缓存池扩展到10GB：
USE master
GO
ALTER SERVER CONFIGURATION
SET BUFFER POOL EXTENSION ON
(FILENAME = 'E:\DB_Cache\SQL2014.BPE', SIZE = 10 GB);
GO

-- 以下脚本为关闭缓存池扩展：
USE master
GO
ALTER SERVER CONFIGURATION
SET BUFFER POOL EXTENSION OFF;
GO

-- 以下脚本为改变缓存池扩展的大小：
USE master
GO
ALTER SERVER CONFIGURATION
SET BUFFER POOL EXTENSION OFF;
GO
ALTER SERVER CONFIGURATION
SET BUFFER POOL EXTENSION ON
(FILENAME = 'E:\DB_Cache\SQL2014.BPE', SIZE = 20 GB);
GO

-- 以下为缓存池扩展相关的DMV和perfmon counter,可用于检查设置和监控：
select * from sys.dm_os_buffer_pool_extension_configuration(nolock);
select * from sys.dm_os_buffer_descriptors(nolock) where is_in_bpool_extension = 1
AND page_level is not null;

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;

SELECT COUNT(*)AS cached_pages_count 
    ,name ,index_id 
FROM sys.dm_os_buffer_descriptors AS bd 
    INNER JOIN 
    (
        SELECT object_name(object_id) AS name 
            ,index_id ,allocation_unit_id
        FROM sys.allocation_units AS au
            INNER JOIN sys.partitions AS p 
                ON au.container_id = p.hobt_id 
                    AND (au.type = 1 OR au.type = 3)
        UNION ALL
        SELECT object_name(object_id) AS name   
            ,index_id, allocation_unit_id
        FROM sys.allocation_units AS au
            INNER JOIN sys.partitions AS p 
                ON au.container_id = p.partition_id 
                    AND au.type = 2
    ) AS obj 
        ON bd.allocation_unit_id = obj.allocation_unit_id
WHERE database_id = db_id()
and is_in_bpool_extension = 1
GROUP BY name, index_id 
ORDER BY cached_pages_count DESC;

USE master

EXEC sp_configure 'show advanced options', 1

RECONFIGURE WITH OVERRIDE

EXEC sp_configure 'max server memory (MB)'

EXEC sp_configure 'max server memory (MB)', [VALUE (MB)]

RECONFIGURE WITH OVERRIDE

select value 'MEM_MB' FROM sys.configurations where name in ( 'max server memory (MB)');

select physical_name from sys.master_files;

exec xp_fixeddrives;

-- 以下脚本启用了缓存池扩展到10GB：

USE master

ALTER SERVER CONFIGURATION

SET BUFFER POOL EXTENSION ON

(FILENAME = 'E:\DB_Cache\SQL2014.BPE', SIZE = 10 GB);

-- 以下脚本为关闭缓存池扩展：

USE master

ALTER SERVER CONFIGURATION

SET BUFFER POOL EXTENSION OFF;

-- 以下脚本为改变缓存池扩展的大小：

USE master

ALTER SERVER CONFIGURATION

SET BUFFER POOL EXTENSION OFF;

ALTER SERVER CONFIGURATION

SET BUFFER POOL EXTENSION ON

(FILENAME = 'E:\DB_Cache\SQL2014.BPE', SIZE = 20 GB);

-- 以下为缓存池扩展相关的DMV和perfmon counter,可用于检查设置和监控：

select * from sys.dm_os_buffer_pool_extension_configuration(nolock);

select * from sys.dm_os_buffer_descriptors(nolock) where is_in_bpool_extension = 1

AND page_level is not null;

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;

SELECT COUNT(*)AS cached_pages_count

,name ,index_id

FROM sys.dm_os_buffer_descriptors AS bd

INNER JOIN

(

SELECT object_name(object_id) AS name

,index_id ,allocation_unit_id

FROM sys.allocation_units AS au

INNER JOIN sys.partitions AS p

ON au.container_id = p.hobt_id

AND (au.type = 1 OR au.type = 3)

UNION ALL

SELECT object_name(object_id) AS name

,index_id, allocation_unit_id

FROM sys.allocation_units AS au

INNER JOIN sys.partitions AS p

ON au.container_id = p.partition_id

AND au.type = 2

) AS obj

ON bd.allocation_unit_id = obj.allocation_unit_id

WHERE database_id = db_id()

and is_in_bpool_extension = 1

GROUP BY name, index_id

ORDER BY cached_pages_count DESC;

Perfmon counter性能监视器

SQL Server:Buffer Manager:Extension page reads/sec

SQL Server:Buffer Manager:Extension page writes/sec

SQL Server:Buffer Manager:Page reads/sec

SQL Server:Buffer Manager:Page writes/sec

SQL Server:Readahead pages/sec

buffer pool extension 性能测试

1.环境准备
(1) 按照上文步骤，添加好缓冲池扩展

(2) 为了模拟页面位于SSD 扩展缓存中的情况，尽量将数据库引擎的"最大服务器内存"设置为较小的值。这样当 L1 内存级别空间不足时，就会将页面缓存到 L2 级别的SSD。

(3) 用如下脚本生产数据

本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！

后续精彩内容已被站长无情隐藏，请输入验证码解锁本文！

获取验证码：请先关注本站微信公众号，然后回复“验证码”，获取验证码。在微信里搜索“AiDBA”或者“dbaup6”或者微信扫描右侧二维码都可以关注本站微信公众号。

打赏赞(1)

标签： MSSQL SQL Server 整理自官网 SQL Server 2014 缓存池扩展BPE (Buffer Pool Extension)缓冲池扩展

小麦苗

学习或考证，均可联系麦老师，请加微信db_bao或QQ646634621

发表回复取消回复

要发表评论，您必须先登录。

1、声明：本网站有部分文章整合或转载自网络，文章著作权归属原作者或原团队；若有侵权，深表歉意，请联系本站站长进行处理，谢谢！！！
2、本站提供数据库的培训和考证业务，培训包含但不限于Oracle、MySQL、PostgreSQL、SQL Server、国产数据库等，考证包括但不限于Oracle OCP、Oracle OCM、MySQL OCP、PGCA、PGCE、PGCM等，私聊QQ646634621或微信dbaup66，非诚勿扰，谢谢！！！
3、麦老师新建了《西安DBA数据架构师》微信群，有志同道合的朋友可以私聊我入群，我们一起聊技术，聊生活，空了可以喝酒约饭。
4、为了方便大家更有针对性的讨论学习数据库，故新建数据库分类群，包括Oracle群、MySQL群、SQL Server群、PG&GP群、国产&信创群。志同道合的朋友可以入群聊技术聊人生，捣乱的勿进，喷子勿进，极端人士勿进，谢谢。

合 MSSQL 2014新功能之缓存池扩展BPE (Buffer Pool Extension)