[MSSQL] CheckPoint (검사점) 주기 설정하기

CheckPoint 란 ( 데이터베이스 검사점 )

---

CheckPoint 란 SQL Server 데이터베이스 엔진이 예기치 않은 종료 또는 충돌 후 복구과정에서 로그에 포함된 변경 내용의 적용을 시작할 수 있는 알려진 올바른 지점을 만드는 것을 의미합니다.

성능상의 이유로 SQL Server 는 변경이 있을 때마다 메모리에서 페이지를 수정하며 이러한 페이지를 실시간으로 디스크에 기록하지 않습니다.

대신 SQL Server CheckPoint 는 메모리 상의 Dirty 된 페이지와 메모리의 트랜잭션 로그 정보들을 디스크로 동기화 하는 작업을 정기적으로 수행합니다.

이러한 작업은 위에서 말했던 것처럼 복구 시간을 단축하기 입니다.

CheckPoint 설정 종류

---

속성	구문	설명
자동	EXEC sp_configure 'recovery interval', 초 ;	최대 제한 시간에 맞게 백그라운드에서 자동으로 실행
간접	ALTER DATABASE database_name SET TARGET_RECOVERY_TIME = 숫자 SECONDS | MINUTES ;	지정된 데이터베이스의 사용자 지정 대상 복구 시간에 맞게 백그라운드에서 실행
수동	CHECKPOINT 초	검사점을 완료하기 위해 요청된 시간을 지정

CheckPoint 설정 방법

---

기본 복구 값은 0 이며, 이는 엔진에서 복구 간격을 자동으로 구성함을 의미합니다.

일반적으로는 CheckPoint(검사점) 실행 간격은 1분이고 복구 시간은 1분 미만입니다.

예를 들어 복구 간격을 3으로 설정하면 3분마다 검사점이 수행되며 복구 시간은 약 3분 입니다.

https://docs.microsoft.com/ko-kr/sql/database-engine/configure-windows/configure-the-recovery-interval-server-configuration-option?view=sql-server-2017

EXEC sp_configure 'show advanced options', 1;

GO

RECONFIGURE;

GO

EXEC sp_configure 'recovery interval', 3 ;  

GO  

RECONFIGURE;

GO

SQL Server 2016 버전부터 기본값은 1분입니다.

https://docs.microsoft.com/ko-kr/sql/relational-databases/logs/change-the-target-recovery-time-of-a-database-sql-server?view=sql-server-2017

ALTER DATABASE AdventureWorks2017 SET TARGET_RECOVERY_TIME = 3 MINUTES;

수동 검사점을 생성합니다.

https://docs.microsoft.com/ko-kr/sql/t-sql/language-elements/checkpoint-transact-sql?view=sql-server-2017

CHECKPOINT 3;

간접 검사점과 자동 검사점의 상호 작용

---

자동 검사점	간접 검사점	사용되는 검사점 유형
0	0	대상 복구 간격이 1분인 자동 검사점
!= 0	0	sp_configure 에 설정된 자동 검사점
0	!= 0	TARGET_RECOVERY_TIME 에 의해 설정된 간접 검사점

자동 검사점과 간접 검사점

---

자동 검사점은 로그 레코드 수가 데이터베이스 엔진이 복구 간격 서버 구성 옵션에 지정된 시간 동안 처리할 수 있을 것으로 예상하는 수에 도달할 때마다 발생합니다.

따라서 트랜잭션이 많은 서버와, 그렇지 않은 경우 같은 설정을 지정하더라도 많은 서버의 검사점이 훨씬 자주 일어나게 됩니다.

간접 검사점은 대상 복구 시간을 데이터베이스 구성옵션을 지정하여 구성할 수 있습니다.

데이터베이스 복구가 대상 복구 시간 내에 완료되도록 더티 페이지 수가 특정 임계값보다 적어야 합니다.

복구 간격 구성 옵션은 트랜잭션 수를 사용하여 복구 시간을 결정합니다.

많은 수의 DML 작업을 하는 데이터베이스에서 간접 검사점을 사용하는 경우 복구 시간 설정 내로 유지하기 위해 더티 버퍼를 디스크로 보다 빠른 주기로 플러시하게 됩니다.

내부 검사점

---

위에서는 작성하지 않은 검사점이 하나 더 있습니다.

아래와 같은 경우 위의 내용과는 관계없이 검사점이 수행되게 됩니다.

1. ALTER DATABASE 를 사용하여 데이터베이스 파일을 추가 혹은 제거한 경우

2. 데이터베이스르 백업한 경우

3. 명시적 혹은 내부적으로 데이터베이스 스냅숏이 생성된 경우

4. 데이터베이스를 종료해야 하는 작업을 수행한 경우

5. FCI ( 장애 조치 클러스터 인스턴스 ) 를 오프라인으로 전환한 경우