SQL Server数据库之必不可少的分页文件
-
Windows 分页文件作用
分页文件是在硬盘上可选的隐藏系统文件。分页文件由Windows将虚拟的内存地址自动转换为真实的内存地址，功能如下：
RAM的物理扩展，系统可以从物理内存中删除不经常访问的已修改页面，让系统更高效地为更频繁访问的页面更高效地使用物理内存。
应用程序要求，使用页面文件，以确保数据库缓存可以在其他服务或应用程序请求内存时释放内存。
对系统故障转储的支持，用于 "后退" （或支持）系统崩溃转储，并延长系统提交的系统所支持的内存量（也称为 "虚拟内存"）。
系统提交内存限制是 "物理内存" 和 "所有页面文件" 组合的总数，它表示系统可以支持的最大系统提交的内存，如果系统可以支持的最大系统提交的内存达到系统提交限制，系统和进程可能无法获取提交的内存。
当系统支持的最大提交内存达到系统提交内存限制的90% 时，分页文件设置为自动管理大小时，系统管理的页面文件会自动增长到物理内存或 4 GB （越大）的三倍。
分页文件的大小需要考虑包括系统的稳定性、应用程序类型、业务高峰期消耗的系统提交内存大小、页面数量访问次数多个方面。分页文件的最小需要配置为200MB才能完成系统故障转储（内核层面）。分页文件大小默认配置为自动，当系统提交内存超过限制的90%，每次增长3倍的物理内存大小或者4GB（在磁盘空间允许的情况下）。
-
SQL Server如何动态管理内存
SQL Server的内存管理围绕着两个目标：
防止缓冲池过大，从而导致操作系统层面出现内存不足。
尽量增加缓冲池大小，尽量减少产生数据库文件的物理I/O。
SQL Server的缓冲池是占用最多内存也是最主要的部分，由 min server memory 和 max server memory 参数控制整个SQL Server能够使用内存的上限和下限。启动服务时，缓冲池不立即获取 min server memory 中指定的内存量，只需要小部分的内存进行初始化，随着压力增大而慢慢获取内存，不会超过 max server memory 的限制，但在达到min server memory 之前，并不会释放内存。
如果为 min server memory 和 max server memory 指定相同的值，则一旦分配给数据库引擎的内存达到该值，数据库引擎将停止为缓冲池动态释放和获取内存。
2 测试环境
-
测试服务器配置
OS版本：Windows Server 2008 R2
DB版本：SQL Server 2008 R2
CPU：4 vCPU，每CPU 4核，共16个CPU
内存大小：8GB
-
测试数据库配置
最大内存限制：0MB
测试数据库：tpcc，simple模式
-
压测工具及性能统计信息
压测工具：HummerDB 3.3，压测一小时
性能数据收集方式：性能计数器，15秒收集一次，收集一个半小时。使用PAL分析结果
本次测试不关注TPC-C的事务计数，只关注Windows虚拟内存和SQL Server服务及内存使用情况
-
测试参数
使用 HummerDB 创建压测数据，并进行压测
-
Schema Build
Number of Warehouses：160 #仓库数量，建议为每个 CPU 创建 10 到 100 个仓库
Virtual User to Build Schema：10 #创建方案的虚拟用户数，介于客户端 vCPU 的 1 到 2 倍之间
-
Driver Script
TPC-Driver Script：Timed Test Driver Script #定时测试驱动程序脚本
Minutes of Rampup Time：5 #预热5分钟
Minutes for Test Duration：60 #测试持续时间60分钟
-
Virtual User
Virtual Users：320 #用于压力测试的虚拟用户数，TPC-C 建议采用 10 倍比例以防止行锁定
压测界面：
3 结果对比
| 计数器 | 测试场景一（无分页文件） | 测试场景二（分页文件自动管理） |
|---|---|---|
|
Memory % Committed Bytes In Use |
压测期间使用率达到100% |
压测期间使用率平均在60%以下 |
| Memory Pages Output/sec |
无分页文件，没有换页 |
最高一秒换页1635次，平均每秒换页19次 |
| Memory Pages Input/sec |
没有分页文件，但却有读入接近600 |
读入在250以下 |
| Paging File % Usage | 无 |
在2%左右，按照分配8GB，使用1.2GB |
|
SQLServer:Buffer Manager\ Lazy writes/sec |
压测期间，最高每秒写120次 |
在测试结束后，最高每秒写200次 |
|
SQLServer:Buffer Manager\ Free pages |
压测期间最低388个可用页 |
压测开始直线下降，可用也最低187个 |
计数器说明：
- Memory % Committed Bytes In Use：提交内存指的是需要在分页文件中为其保留的空间（RAM和所有页面文件），如果提交内存超过了提交内存限制（持续100%），说明可能发生了内存泄露。
- Memory Pages Output/sec：当物理内存不足时，Windows将更多页面写到分页文件以释放空间。
- Memory Pages Input/sec：当进程引用无法在RAM中找到虚拟内存中的页时，需要从磁盘读取多个页面到RAM中。
- Paging File % Usage：页面文件使用率
- SQLServer:Buffer Manager\Lazy writes/sec：Lazy write进程释放缓冲池中最近最少使用的页，并将脏页标识为“干净的”，从缓冲池写入到磁盘，释放缓冲池空间。如果SQL Server需要更多的内存，且缓冲池小于max server memory，Lazy write进程会频繁地释放缓冲池中的页，通常它都是在20秒以下，最好接近0。
- SQLServer:Buffer Manager\Free pages：指的是缓冲池中所有可用地页数量，每个页8KB，一般不小于640个页。
4 结果分析
4.1 测试场景一（无分页文件）
间隔1分钟，从数据库上查询会话请求信息：
执行SQL语句报错：System.OutOfMemoryException，已经没有足够的内存可以继续执行查询语句
-------------------------------------------------------------观察测试过程----------------------------------------------------------
从12:14开始，系统类型的事件中，频繁出现事件ID 2004的报警：Windows 成功诊断出虚拟内存不足的情况。
直到12:42，SQL Server服务已经被意外停止。
结果：SQL Server数据库持续长时间高负载，如果遇到高峰期，SQL Server一直需要更多的内存，Lazy write进程频繁地释放内存，但操作系统没有允许换页，使操作系统层面提交内存长时间使用率达到100%。压测了1个小时，在第30分钟的时候，操作系统出现内存泄漏，导致SQL Server服务被终止。
4.2 测试场景二（分页文件自动管理）
会话数一直稳定可查询
操作系统日志没有任何报错，之前测试场景的报错（ID 2004）没有再发生了。
结果：SQL Server数据库持续长时间高负载，Lazy write进程频繁地释放内存，操作系统允许SQL Server进行换页，使操作系统层面提交内存长时间使用率保持在60%以下，操作系统没有出现内存泄漏。压测一个小时，SQL Server服务运行正常。当然，除非你使用了锁定内存页，否则在SQL Server数据库中，必须要配置上分页文件！
