오랜만에 Checkpoint관련 글을 포스팅합니다.
오늘 쓰려고 하는 내용은 이전에 올린 글에서 잠깐 언급 하였습니다.
Checkpoint는 인접해 있는 페이지들을 하나의 IO로 처리 할 수 있습니다.
하나의 IO로 여러 페이지를 처리하여, 보다 효율적으로 dirty page를 물리적 디스크로 플러쉬 할 수 있습니다. SQL Server 2000의 경우에는 최대 16개의 페이지 였지만 2005의 경우에는 최대 32개의 페이지를 하나의 IO로 처리 할 수 있습니다.
하지만 이렇게 32개 페이지를 하나의 IO로 플러쉬 하기 위해서는 checkpoint로 내려야 할 Dirty page가 순차적으로 존재해야 합니다. 데이터가 랜덤한 페이지상에 자주 변경되는 비즈니스라면 이러한 동작 방식이 도움을 줄 수 없을것입니다. 하지만 인접해 있는 페이지가 변경 되는 경우라면 보다 적은 Writes로 많은 페이지를 플러쉬 할 수 있을 것입니다. 하지만 인접해 있는 데이터가 변하지만 페이지 조각화로 인해 위와 같이 동작 하지 못할 수도 있습니다.
그렇다면 정말 위에서 말한것 처럼 동작하는지 테스트 하였습니다.
보다 명확하게 checkpoint의 IO양과 초당 처리속도를 확인해야 하기에 추적 플래그 3502,3504 를 사용하였으며, 데이터 변경을 하여 자동 checkpoint가 발생시에 errorlog에 있는 데이터로 비교하였습니다.
USE [master]
GO
CREATE DATABASE [test] ON PRIMARY
( NAME = N'test_Data', FILENAME = N'F:\test_Data.MDF' , SIZE = 102400KB , MAXSIZE = UNLIMITED, FILEGROWTH = 51200KB ),
FILEGROUP [test_Data1]
( NAME = N'test_Data1_01', FILENAME = N'F:\test_Data1_01.mdf' , SIZE = 5GB , MAXSIZE = UNLIMITED, FILEGROWTH = 51200KB )
,( NAME = N'test_Data1_03', FILENAME = N'G:\test_Data1_03.mdf' , SIZE = 5GB , MAXSIZE = UNLIMITED, FILEGROWTH = 51200KB )
LOG ON
( NAME = N'test_Log', FILENAME = N'E:\test_Log.LDF' , SIZE = 5GB , MAXSIZE = 2048GB , FILEGROWTH = 51200KB )
ALTER DATABASE [test] SET RECOVERY SIMPLE
use test
go
drop table tbl1
/*
테스트를 위해 하나의 페이지에 한행만 존재하도록 테이블을 만든다.
*/
SELECT
TOP 4000000
ROW_NUMBER() OVER(ORDER BY(SELECT 1)) as col1
,cast(ROW_NUMBER() OVER(ORDER BY(SELECT 1)) as char(5000)) as col2
INTO tbl1
FROM sys.sysindexes A,sys.sysindexes A1,sys.sysindexes A2,sys.sysindexes A3,sys.sysindexes A4
create unique clustered index ixa on tbl1(col1)
--sp_spaceused tbl1
정확한 테스트를 위해 기존에 BP에 있는 메모리 날리기.
*/
dbcc dropcleanbuffers
DBCC TRACEON(3504,3502,-1)
/*
순차적으로 데이터를 변경한다.
하나의 IO작업에 16개페이지(1572070/97851)를 내리는 것을 확인 할 수 있으며
초당 280메가의 Dirty page를 내리는 것을 볼수 있다.
*/
update tbl1
set col2 = newid()
where col1 < 2000000
2008-12-16 02:52:28.340 spid10s Ckpt dbid 11 started (8)
2008-12-16 02:52:28.340 spid10s About to log Checkpoint begin.
2008-12-16 02:52:28.340 spid10s Ckpt dbid 11 phase 1 ended (8)
2008-12-16 02:53:12.060 spid10s FlushCache: cleaned up 1572070 bufs with 97851 writes in 43719 ms (avoided 162901 new dirty bufs)
2008-12-16 02:53:12.060 spid10s average throughput: 280.93 MB/sec, I/O saturation: 72605
2008-12-16 02:53:12.060 spid10s last target outstanding: 1600
2008-12-16 02:53:12.060 spid10s About to log Checkpoint end.
2008-12-16 02:53:12.080 spid10s Ckpt dbid 11 complete
/*
랜덤하게 데이터를 변경한다.
하나의 IO작업에 1개페이지(147576/147573)를 내리는 것을 확인 할 수 있으며
초당 56메가의 Dirty page를 내리는 것을 볼수 있다.
*/
update tbl1
set col2 = newid()
where col1%2 = 0
2008-12-16 02:56:04.760 spid10s Ckpt dbid 11 started (8)
2008-12-16 02:56:04.760 spid10s About to log Checkpoint begin.
2008-12-16 02:56:04.760 spid10s Ckpt dbid 11 phase 1 ended (8)
2008-12-16 02:56:25.200 spid10s FlushCache: cleaned up 147579 bufs with 147573 writes in 20438 ms (avoided 1466 new dirty bufs)
2008-12-16 02:56:25.200 spid10s average throughput: 56.41 MB/sec, I/O saturation: 55153
2008-12-16 02:56:25.200 spid10s last target outstanding: 1600
2008-12-16 02:56:25.200 spid10s About to log Checkpoint end.
2008-12-16 02:56:25.200 spid10s Ckpt dbid 11 complete
위와 같은 결과를 보면, 순차적인 데이터 변경으로 발생된 dirty page가 보다 효과적으로 디스크로 플러쉬 되는 것을 확인 할 수 있습니다.
특정 테스트 환경이기에 위 테스트 결과를 절대적으로 신뢰할 수는 없습니다.
sqler.pe.kr // sqlleader.com
hyoksong.tistory.com
DBCC DROPCLEANBUFFERS는 Buffer Pool에 존재하는 데이터 페이지를 제거하는 구문입니다.
하지만 DBCC DROPCLEANBUFFERS을 수행하면 정말 Buffer Pool에 있는 데이터 페이지가 사라질까요?
간단히 결론만을 말하면 아직 disk로 플러시 되지 않는 페이지는 이 구문으로 내려갈 수 없습니다.
모든 Buffer Pool에 있는 페이지를 내리려면 checkpoint를 통해서 Dirty페이지를 마크 후에 DBCC DROPCLEANBUFFERS를 수행 하면됩니다.
아래는 테스트 예제입니다.
USE TEST
GO
--DROP TABLE tbl1
--샘플 테이블 만들기
SELECT
TOP 1000
ROW_NUMBER() over(order by (select 1)) as col1
,cast('Hyoksong' as char(5000)) as col2
INTO tbl1
from sys.sysindexes A,sys.sysindexes B,sys.sysindexes C,sys.sysindexes B1
--Buffer Pool에있는 데이터 페이지를 날리자.
EXEC sp_msforeachdb 'USE ? CHECKPOINT 1'
DBCC DROPCLEANBUFFERS
--500개 행에 대해서 업데이트를 치자.
UPDATE tbl1
SET col2 = 'Test'
WHERE col1 > 500
(500개행적용됨)
--Buffer Pool의tbl1 테이블 데이터 페이지의 수는 몇개?
select
COUNT(*)
from sys.allocation_units a
right outer join sys.dm_os_buffer_descriptors b
ON a.allocation_unit_id = b.allocation_unit_id
left outer join sys.partitions p
ON a.container_id = p.hobt_id
WHERE B.database_id = db_id('Test')
and (p.object_id IS NULL OR p.object_id > 100)
and object_name(object_id,database_id) = 'tbl1'
and page_type = 'DATA_PAGE'
/*
결과: 1001
*/
--DBCC DROPCLEANBUFFERS 를수행하고Buffer Pool에는몇개?
DBCC DROPCLEANBUFFERS
select
COUNT(*)
from sys.allocation_units a
right outer join sys.dm_os_buffer_descriptors b
ON a.allocation_unit_id = b.allocation_unit_id
left outer join sys.partitions p
ON a.container_id = p.hobt_id
WHERE B.database_id = db_id('Test')
and (p.object_id IS NULL OR p.object_id > 100)
and object_name(object_id,database_id) = 'tbl1'
and page_type = 'DATA_PAGE'
/*
결과: 500
*/
--그럼Checkpoint를후에DBCC DROPCLEANBUFFERS을수행하면?
CHECKPOINT 1
DBCC DROPCLEANBUFFERS
select
COUNT(*)
from sys.allocation_units a
right outer join sys.dm_os_buffer_descriptors b
ON a.allocation_unit_id = b.allocation_unit_id
left outer join sys.partitions p
ON a.container_id = p.hobt_id
WHERE B.database_id = db_id('Test')
and (p.object_id IS NULL OR p.object_id > 100)
and object_name(object_id,database_id) = 'tbl1'
and page_type = 'DATA_PAGE'
/*
결과: 0
*/
송 혁, SQL Server MVP
sqler.pe.kr
sqlleader.com
hyoksong.tistory.com
nexondbteam.tistory.com
