[SQL Server]記憶體緩存資料寫入磁碟(二)自動與間接檢查點(Auto and Indirect CheckPoint)

上一篇文章中,我們探討到CheckPoint檢查點會將記憶體(Buffer Pool)中的中途資料分頁flush到磁碟上,其中checkPoint又可以分為自動、間接、手動及系統內部觸發幾種類型,這篇我們來嘗試使用SQL Server 2012推出,SQL Server 2016正式推薦使用的間接檢查點(Indirect CheckPoint)機制。

 

在此之前,我們先回到SQL Server 2016以前版本的預設:自動檢查點(Auto CheckPoint)機制,順道確認在ldf與mdf不一致時,ldf檔案是否能回追資料(ReDo)


自動檢查點機制 (automatic checkpoints)

通常我們不會特別設定檢查點機制,因此預設就是使用自動檢查點(Automatic CheckPoint)。

Technet文章說明:

Checkpoint會在背景自動發出,以符合 recovery interval 伺服器組態選項所建議的時間上限。 自動檢查點會執行到完成為止。自動檢查點的調節是根據未完成的寫入數目以及 Database Engine 是否偵測到超過 20 毫秒的寫入延遲有增加。

如果想進一步了解自動檢查點的收集Dirty Page的機制或是舊版SQL 7.0的排列搜尋機制(elevator seek),推薦大家參以下這篇由三位SQL軟體工程師撰寫的文章。

SQL 2016 – It Just Runs Faster: Indirect Checkpoint Default

 

不過,因為伺服器層級的組態設定Recovery Interval是0,看起來只剩下儲存時的磁碟反應時間可以觸發自動檢查點!

 

這邊我們先離題一下!

如果反應時間沒有慢到達標(Auto CheckPoint)、沒有收集到足夠的Dirty Page(Auto CheckPoint)、沒有記憶體壓力(Lazy writer)、沒有手動及系統內部的檢查點被觸發,總之就是沒發生Flush到Disk的活動,已經commit的資料分頁會不會一直待在Buffer Cache?

如果答案是YES,那麼使用Storage廠商同步到異地的機制,是否能正確回追(ReDo)資料?

 

mdf與ldf不同步的實驗

1.我們先打開process monitor,按照上一篇的步驟設定Filer條件

2.然後試著執行一段迴圈:每一秒打一筆交易到資料庫內,接著Commit,依此類推執行120次,然後出去跑步10km加上回家洗澡,觀察1小時多中的磁碟活動:

USE [FlushDiskDb]
drop table t1
create table t1 
(
   c1 int identity,
   c2 varchar(30)
)

Declare @COUNT INT = 0;
WHILE (@COUNT < 120)
BEGIN
BEGIN tran tran_1
SET @COUNT = @COUNT + 1
INSERT INTO t1
	VALUES ('T' + CONVERT(VARCHAR, @COUNT))
COMMIT
WAITFOR DELAY '00:00:01'
END

神奇的是完全沒有偵測到資料檔(mdf or ndf)的寫入,在記憶體long stay 1小時了!

查詢Data Buffer也發現的確還在記憶體,尚未flush到Disk

3.模擬storage廠商的異地同步機制,因為Database在線上,沒辦法複製,我們用暴力的方式kill sqlservr process,避免觸發了系統內部的check point

taskkill /f /im sqlservr.exe

執行

4.將磁碟內mdf及ldf複製到另外一台資料庫,然後掛載(Attach)

USE [master]
GO
CREATE DATABASE [FlushDiskDb] ON 
( FILENAME = N'F:\Data\FlushDiskDb.mdf' ),
( FILENAME = N'F:\Data\FlushDiskDb_log.ldf' )
 FOR ATTACH
GO

5.接著查詢Table T1,確認Database Engine是否成功的使用Transaction log 檔案回追資料?

噹噹!120筆全員到齊!

不管使用自動與間接檢查點機制,紀錄檔(ldf)與資料檔(mdf、ndf)可能會存在時間差,但Database Engine還是能透過正確的ldf檔來確保ACID的持久性(Durability),關鍵就是ldf是否也正確同步到異地了!

 

自動檢查點發生的頻率

1.為了觀察CheckPoint發生的頻率,我們來建立擴充事件的監控。


--先查詢DB_ID
SELECT DB_ID(N'FlushDiskDb')

--建立擴充事件
CREATE EVENT SESSION [CheckPointEvent] ON SERVER
ADD EVENT sqlserver.checkpoint_begin(
    ACTION(sqlserver.database_id,sqlserver.database_name,sqlserver.is_system)
    WHERE ([sqlserver].[database_id]=(20))
    )
--,ADD EVENT sqlserver.checkpoint_end(   
--ACTION(sqlserver.database_id,sqlserver.database_name,sqlserver.is_system)
--    WHERE ([sqlserver].[database_id]=(20))
--    )
ADD TARGET package0.event_file
(
    SET FILENAME = N'C:\temp\db\CheckPointEvent.xet'
)
WITH (MAX_DISPATCH_LATENCY = 1 SECONDS)  

--啟用擴充事件
ALTER EVENT SESSION [CheckPointEvent] ON SERVER STATE=START

建立完畢!

2.執行20萬筆資料寫入,觀測自動檢查點的執行次數!

USE [FlushDiskDb]
--建立資料表t01
drop table t01
create table t01 
(
   c1 int identity,
   c2 varchar(30)
)

--執行20萬筆寫入
Declare @COUNT INT = 0;
WHILE (@COUNT < 200000)
BEGIN
SET @COUNT = @COUNT + 1
INSERT INTO t01
	VALUES ('T' + CONVERT(VARCHAR, @COUNT))
END

 

只錄到1次CheckPoint

 


間接檢查點(Indirect CheckPoint)

為了降低資料庫復原的時間,SQL Server 2012在資料庫層級新增了一組TARGET_RECOVERY_TIME,她是一個復原時間的目標,可以讓我們自行決定是否採用間接簡單點機制,在SQL 2012-2014間預設還是使用自動檢查點機制,但到了SQL Server 2016之後,如果在UI新增資料庫,新的預設值就是採用間接CheckPoint機制,Target Recovery Time預設為60秒,SQL Server會在內部計算多少Dirty Page作為觸發check point的門檻來符合目標復原時間的目標

 

1.把資料庫目標復原時間設定成10秒

ALTER DATABASE [FlushDiskDb] SET TARGET_RECOVERY_TIME = 10 SECONDS;

2.建立Table:T02,然後打20萬筆交易,計算發生CheckPoint的頻率

USE [FlushDiskDb]

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

Declare @COUNT INT = 0;
WHILE (@COUNT < 2000)
BEGIN
BEGIN tran tran_1
SET @COUNT = @COUNT + 1
INSERT INTO t1
	VALUES ('T' + CONVERT(VARCHAR, @COUNT))
COMMIT
WAITFOR DELAY '00:00:00.01'
END

相較自動檢查點機制,間接檢查點總共增加了5次CheckPoint活動

3.查詢Buffer Pool內的Data Page

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'

 

大部分的Dirty Page已經Flush到磁碟上,一旦資料庫需要復原,就只需要回追幾個Data Page的資料了。

 


自動檢查點機制(Auto CheckPoint) vs 間接檢查點機制(Indirect CheckPoint)

可以參考以下Mike Ruthruff這篇文章的比較,在SQL 2016的版本中,若使用間接檢查點機制,可以讓磁碟的寫入活動更為平均。

另外也可以注意到把2012或2014資料庫搬到SQL 2016使用自動檢查點機制時的效能影響。

https://blogs.msdn.microsoft.com/sqlcat/2016/08/03/changes-in-sql-server-2016-checkpoint-behavior/

 


小結:

1.是否使用間接檢查點機制: 降低復原時間的考量

2.伺服器配置的記憶體越大,代表掃描記憶體找出Dirty Page的時間越久,這時候就需要更勤勞的CheckPoint活動

3.也可以用Trace flag 3502/3504監控CheckPoint活動,但建議使用擴充事件(Extended Event)監控

 


參考:

Database Checkpoints (SQL Server)

Configure the recovery interval Server Configuration Option

Changes in SQL Server 2016 Checkpoint Behavior

SQL 2016 – It Just Runs Faster: Indirect Checkpoint Default

Change the Target Recovery Time of a Database (SQL Server)