BLOG main image
분류 전체보기 (45)
SQL Server 이야기 (45)
일상 (0)
Visitors up to today!
Today hit, Yesterday hit
daisy rss
tistory 티스토리 가입하기!
2009. 1. 5. 23:20

오랜만에 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에 있는 메모리 날리기.
*/
checkpoint 1

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가 보다 효과적으로 디스크로 플러쉬 되는 것을 확인 할 수 있습니다.


특정 테스트 환경이기에 위 테스트 결과를 절대적으로 신뢰할 수는 없습니다.

송 혁, SQL Server MVP
sqler.pe.kr // sqlleader.com
hyoksong.tistory.com