[SQL Server]内存缓存数据写入磁盘(一)首部曲

很少扎实的实践SQL Server Database Engine内部的行为，最近发现某个客户的性能瓶颈可能在Transaction log的I/O，同时另外一家客户正在导入保存厂商异地备援(DR site)的解决方案(不打算用AlwaysOn传送到异地)，由于保护的是磁盘资源，我们需要确保磁盘上的mdf、ndf与ldf的一致性。

来笔记Buffer Flush To Disk读书心得以及SQL2012/2014/SQL2016相关的新古与全新功能。

维持ACID的完整性(Atomicity)及持久性(Durability)

为了维持关联式数据库A.C.I.D的重要使命，SQL Server采用事务纪录(Transaction log)来实现完整性Atomicity及持久性(Durability)，这边先简单解释完整性Atomicity及持久性(Durability):

• 完整性Atomicity:?事务必须全部完成执行，要不就是全部不执行。?
• 持久性(Durability):事务完成之后，其作用便永远存在于系统之中。

为了能拥有飞快的I/O性能，更新数据库数据时，SQL Server会先将数据档及纪录档更新在Buffer Pool(也称Buffer cache)，也就是内存缓存区内，而为了确保完整性及持久性，一个事务的Commit完成，则是将写入Buffer Pool中的Log Flush到Disk上作为确认，也就是write-ahead log (WAL)，先确保log保存磁盘正确(这与AlwaysOn的同步认可很类似)。

因为数据分页(Data Page)还在Buffer Pool上，此时若SQL Server所在的服务器发生内存异常，只要Transaction log的磁盘文件(.ldf)正确，DataBase Engine还是可以使用Transaction Log完成ReDo及UnDo将Disk上的mdf/ndf文件追回或复原正确。

*如果直接同步mdf、ndf与ldf到异地机房(DR Site)，可能会碰上log(ldf)是新的，但data file(mdf ndf)还是旧的情境。

从Buffer Pool写入Log到磁盘的时间点

前面文章理解到Transaction Log写入磁盘的其中一个时间点是事务Commit的Ending pose，整理几个写入磁盘的时间点:

• 事务commit
• log Buffer到达60K的容量
• checkpoint 检查点

这边我们先简单测试事务commit前后，DataBase Engine在磁盘上的活动。

1.建立测试数据库、测试数据表及测试数据

CREATE DATABASE [FlushDiskDb]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N‘FlushDiskDb‘, FILENAME = N‘C:tempdbFlushDiskDb.mdf‘ , SIZE = 5120KB , FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N‘FlushDiskDb_log‘, FILENAME = N‘C:tempdbFlushDiskDb_log.ldf‘ , SIZE = 2048KB , FILEGROWTH = 10%)
GO

USE [master]
GO
ALTER DATABASE [FlushDiskDb] SET RECOVERY FULL WITH NO_WAIT
GO

create table t1 
(
   c1 int identity,
   c2 varchar(30)
)

insert t1 VALUES(‘Stanley‘)
insert t1 VALUES(‘*‘)

2.打开process monitor

3.设定Filter条件

Ctrl L?热键跳出Filer选项

设定Filter条件: Path > Contaons > database file路径

4.建立一个事务，然后先不要Commit

use FlushDiskDb

BEGIN tran tran_one 
 update t1 
    set c2 = ‘France‘
 where c1 = 2 

5.果然在process monitor中: 空空如也!

6.但如果此时在事务的最后面下了commit

process monitor 出现了写入.ldf的磁盘活动! 而且只有ldf没有mdf磁盘活动喔。

write-ahead log (WAL)，先保证log写入磁盘来确保ACID的AD!

从Buffer Pool写入数据分页到磁盘的时间点

前面的简单练习可以理解到Log写入磁盘的时间点，也许聪明的工程师一定很快想进一步知道，数据分页写入磁盘的时间点? 从Technet的文章很快找到答案:

• checkpoint:检查点发生
• Lazy writer:懒惰写入器工作了(因为很懒惰，触发的机会相较checkpoint少很多)
• Eager writing:?热切的写入器被选择，然后大量载入事务发生了(nonlogged操作的一种，这边暂时不讨论)

简单把Data Page和log buffer一起划图来表示磁盘写入流程:

检查点(CheckPoint)触发时机

这边我们先study最常发生的CheckPoint，中文是检查点，CheckPoint会将目前内存中已修改的页面 (中途分页或称Dirty Page)和事务记录资讯从内存缓存(Buffer Cache)中写入磁盘上。

Database Engine 支持几种类型的检查点：

• 自动:SQL DataBase Engine决定，可在执行个体层级设定(recovery interval)。
• 间接:SQL Server 2012中的新功能，在数据库层级设定固定周期的检查点时间(TARGET_RECOVERY_TIME)。
• 手动: 人为的方式执行checkpoint命令
• 系统内部:?执行数据库备份、快照、停止 SQL Server (MSSQLSERVER) 服务、SQL Server 容错移转丛集执行个体 (FCI) 离线、使用 ALTER DATABASE 加入或移除数据库文件..

我们来试试刚刚的案例，新增查询窗口，手动输入checkpoint命令!

Process monitor多了写入.mdf的磁盘活动

Checkpoint vs Lazy writer

Checkpoint和Lazy writer很类似，但Checkpoint的设计出发点是为了缩短数据库的复原时间，但Lazy writer则是为了解决内存压力，两种执行后都会将Buffer Cache的数据分页写入磁盘，但只有Lazy Writer会释放出资源。

如果也希望checkpoint释放资源，举例来说，我们需要手动再执行DBCC DROPCLEANBUFFERS命令:

CHECKPOINT;
GO
DBCC DROPCLEANBUFFERS;
GO

查询目前Buffer Pool中的数据表

我们可以透过sys.dm_os_buffer_descriptors 这个dm查询到!

USE FlushDiskDb
SELECT
	DB_NAME(buf.database_id) AS DbName
	,o.name
	,buf.* 
FROM sys.dm_os_buffer_descriptors buf
LEFT JOIN sys.allocation_units AS au WITH (NOLOCK)
	ON buf.allocation_unit_id = au.allocation_unit_id
LEFT JOIN sys.partitions AS pt WITH (NOLOCK)
	ON au.container_id = pt.hobt_id
LEFT JOIN sys.objects AS o WITH (NOLOCK)
	ON pt.object_id = o.object_id
LEFT JOIN sys.indexes AS idx WITH (NOLOCK)
	ON o.object_id = idx.object_id
		AND pt.index_id = idx.index_id
WHERE DB_NAME(buf.database_id) = db_name()
  and o.type_desc = ‘USER_TABLE‘

ORDER by name 

?

执行结果:

1.Checkpoint执行前: Dirty Page

2.Checkpoint执行后

3.DBCC DROPCLEANBUFFERS执行后:

今天先大致理解Buffer Pool写入磁盘的架构及时间点，周末继续笔记未完成的SQL2012/2014/2016新古和全新功能。

参考:

Writing Pages

https://technet.microsoft.com/en-us/library/aa337560(v=sql.105).aspx

数据库检查点 (SQL Server)

https://msdn.microsoft.com/zh-tw/library/ms189573(v=sql.120).aspx

原文:大专栏  [SQL Server]内存缓存数据写入磁盘(一)首部曲