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 Pooltbl1 테이블 데이터 페이지의 수는 몇개?

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

신고
Posted by 송혁 - HyokSong

CHECKPOINT Buffer Pool 커밋되지 않은 페이지를 디스크로 플러시를 하는 프로세스 입니다.

 

CHECKPOINT DB단위로 발생하며, 발생 주기는 recovery interval 옵션의 값을 기반으로 움직이게 됩니다.

CHECKPOINT 발생시 커밋되지 않은 페이지를 디스크로 페이지를 플러시 하며, CHECKPOINT 발생한 시점에 트랜잭션 로그파일도 마킹을 하여

비정상적인 종료시 마지막 CHECKPOINT 이후의 트랜잭션 로그를 바탕으로 데이터를 롤백 또는 롤포워드 하게됩니다.

 

CHECKPOINT 발생시 IO 병목이 발생 있으며 이러한 문제의 해결에 도움을 있는 몇가지 기능이 SQL Server 2005 추가되었습니다.

1.  CHECKPOINT Duration

è  수동 CHECKPOINT발생시 Duration 시간을 지정 있습니다.

BP 있는 내용을 최대한 빨리 내릴경우에는 Duration 1 지정 있으며

자동 Checkpoint 성능적 문제가 발생한 경우 수동 Checkpoint Duration값을 적절히 설정하면

천천히 페이지를 플러시 하여 IO 급격한 요청을 피해 전반적인 성능 향상을 꾀할 있습니다.

 

2.     -k 옵션 (http://support.microsoft.com/default.aspx/kb/929240)

è  SP2 누적 픽스 까지 설치하면 –k 시작 옵션을 사용할 있습니다.

해당 옵션은 초당 얼마의 IO작업을 할것인지 지정 있으며 만약 –k100으로 설정 하였다면

초당 100MB의 IO작업이 발생하게 됩니다.

Duration처럼 CHEKCPOINT 병목에 대해서 해결시 사용할 있습니다.

보다 자세한 내용은 KB문서를 참고 하세요.

 

3.  CHECKPOINT IO응답시간 20ms 이내, 서버 종료시는 100ms

è  2000 경우에는 Checkpoint 작업시 DISK병목 현상이 발생될 있었지만

2005 경우에는 Write 응답시간을 20ms 또는 100ms으로 지정하여 기존 버전보다 IO병목 현상에 대해 향상 되었습니다.

 

4.  연속된 32개의 페이지를 하나의 IO 처리(기존 SQL Server 2000에는 16페이지)

è  보통 DISK 랜덤IO 작업에 성능이 급격히 떨어지게 됩니다.

여러 개의 페이지를 하나의 IO작업으로 처리하면 보다 빨리 CHECKPOINT작업이 처리 있습니다.

2000 경우에는 16개의 페이지 였지만 2005 경우에는 32개의 페이지를 하나의 IO 처리 있습니다.

 

CHECKPOINT IO성능에 가장 이슈가 되는 프로세스이며, 조금 살펴보면 재미 있는 이슈가 많습니다.

조만간 아래 주제에 대한 글을 포스팅 하도록 하겠습니다.

 

1.  연속적, 랜덤한 데이터 변경에 대한 CHECKPOINT 성능 비교

2.  CHECKPOINT 발생시키는 이벤트

3.  IO Subsystem 성능에 따른 CHECKPOINT

4.  매뉴얼 CHECKPOINT,-k 시작 옵션, Duration 언제 사용할까?

5.  DBCC DROPCLEANBUFFERS 수행하면 모든 페이지가 정말 Buffer Pool에서 사라질까?

 

, SQL Server MVP

sqler.pe.kr

sqlleader.com

hyoksong.tistory.com

nexondbteam.tistory.com

신고
Posted by 송혁 - HyokSong
TAG Checkpoint, IO, SQL


티스토리 툴바