데이터베이스 검사점(SQL Server)

2015-10-13

이 항목에서는 SQL Server 데이터베이스 검사점의 개요를 제공합니다. 검사점은 SQL Server 데이터베이스 엔진이 예기치 않은 종료 또는 충돌 후 복구 중에 로그에 포함된 변경 내용을 적용하기 시작할 수 있는 알려진 좋은 지점을 만듭니다.

검사점 개요

성능상의 이유로 데이터베이스 엔진은 메모리 내 버퍼 캐시의 데이터베이스 페이지를 수정하고 변경 후 디스크에 이러한 페이지를 작성하지 않습니다. 대신 데이터베이스 엔진은 주기적으로 각 데이터베이스에 검사점을 발급합니다. 검사점은 메모리 내 수정된 현재 페이지(더티 페이지라고 함) 및 트랜잭션 로그 정보를 메모리에서 디스크로 쓰고 트랜잭션 로그에 대한 정보도 기록합니다.

데이터베이스 엔진은 자동, 간접, 수동 및 내부와 같은 여러 유형의 검사점을 지원합니다. 다음 표에는 검사점 유형이 요약됩니다.

이름	Transact-SQL 인터페이스	설명
자동	EXEC sp_configure *'`recovery interval`','`seconds`'*	서버 구성 옵션 `recovery interval`에서 제안하는 시간 상한을 충족하기 위해 백그라운드에서 자동으로 발급됩니다. 자동 검사점이 완료될 때까지 실행됩니다. 자동 검사점은 미해결 쓰기 수 및 데이터베이스 엔진이 20밀리초 이상의 쓰기 대기 시간 증가를 감지하는지 여부에 따라 제한됩니다. 자세한 내용은 복구 간격 서버 구성 옵션 구성을 참조하세요.
간접적인	ALTER DATABASE ... SET TARGET_RECOVERY_TIME =target_recovery_time { 초 \| 분 }	지정된 데이터베이스에 대해 사용자가 지정한 대상 복구 시간을 충족하기 위해 백그라운드에서 발급됩니다. 기본 대상 복구 시간은 0이며, 이로 인해 데이터베이스에서 자동 검사점 추론이 사용됩니다. ALTER DATABASE를 사용하여 TARGET_RECOVERY_TIME 0으로 >설정한 경우 이 값은 서버 인스턴스에 지정된 복구 간격이 아니라 사용됩니다. 자세한 내용은 데이터베이스의 대상 복구 시간 변경(SQL Server)을 참조하세요.
설명서	체크포인트 [ checkpoint_duration ]	Transact-SQL CHECKPOINT 명령을 실행할 때 실행됩니다. 수동 검사점은 연결을 위한 현재 데이터베이스에서 발생합니다. 기본적으로 수동 검사점은 완료될 때 실행됩니다. 제한은 자동 검사점과 동일한 방식으로 작동합니다. 필요에 따라 checkpoint_duration 매개 변수는 검사점이 완료되는 데 필요한 시간(초)을 지정합니다. 자세한 내용은 CHECKPOINT(Transact-SQL)를 참조하세요.
내부	없음.	디스크 이미지가 로그의 현재 상태와 일치하도록 백업 및 데이터베이스 스냅샷 만들기와 같은 다양한 서버 작업에서 발급됩니다.

비고

-kSQL Server 고급 설정 옵션을 사용하면 데이터베이스 관리자가 일부 유형의 검사점에서 I/O 하위 시스템의 처리량에 따라 검사점 I/O 동작을 제한할 수 있습니다. -k 설정 옵션은 자동 검사점 및 달리 할당되지 않은 수동 및 내부 검사점에 적용됩니다.

자동, 수동 및 내부 검사점의 경우 데이터베이스 복구 중에 최신 검사점 이후에 수행된 수정 사항만 롤 포워드해야 합니다. 이렇게 하면 데이터베이스를 복구하는 데 필요한 시간이 줄어듭니다.

중요합니다

커밋되지 않은 트랜잭션을 장기 실행하면 모든 유형의 검사점에서 복구 시간이 늘어나게 됩니다.

TARGET_RECOVERY_TIME 및 '복구 간격' 옵션의 상호 작용

다음 표는 서버 전체 설정인 sp_configurerecovery interval'와 데이터베이스별 ALTER DATABASE ... TARGET_RECOVERY_TIME 설정 간의 상호 작용을 요약합니다.

목표 복구 시간	'복구 간격'	사용된 검사점 유형
0	0	대상 복구 간격이 1분인 자동 검사점입니다.
0	>0	사용자 정의 설정에 의해 sp_configurerecovery 간격 옵션으로 지정된 복구 목표 간격의 자동 검사점입니다.
>0	적용할 수 없습니다.	간접 검사점의 대상 복구 시간은 TARGET_RECOVERY_TIME 설정에 따라 결정되며, 초 단위로 표현됩니다.

자동 검사점

자동 검사점은 로그 레코드 수가 데이터베이스 엔진이 서버 구성 옵션에 지정된 시간 동안 처리할 수 있다고 예상하는 수에 recovery interval 도달할 때마다 발생합니다. 사용자 정의 대상 복구 시간이 없는 모든 데이터베이스에서 데이터베이스 엔진은 자동 검사점을 생성합니다. 자동 검사점의 빈도는 지정된 서버 인스턴스가 시스템을 다시 시작하는 동안 데이터베이스를 복구하는 데 사용해야 하는 최대 시간을 지정하는 고급 서버 구성 옵션에 따라 달라집니다 recovery interval . 데이터베이스 엔진은 복구 간격 내에서 처리할 수 있는 최대 로그 레코드 수를 추정합니다. 자동 검사점을 사용하는 데이터베이스가 이 최대 로그 레코드 수에 도달하면 데이터베이스 엔진에서 데이터베이스의 검사점을 발급합니다. 자동 검사점 간의 시간 간격은 매우 가변적일 수 있습니다. 트랜잭션 워크로드가 많은 데이터베이스는 주로 읽기 전용 작업에 사용되는 데이터베이스보다 검사점이 더 자주 있습니다.

또한 단순 복구 모델에서는 로그가 70% 가득 차면 자동 검사점도 큐에 대기됩니다.

단순 복구 모델에서 일부 요소가 로그 잘림을 지연하지 않는 한 자동 검사점은 트랜잭션 로그의 사용되지 않는 섹션을 자른다. 반면, 전체 및 대량 로그 복구 모델에서 로그 백업 체인이 설정되면 자동 검사점이 로그 잘림을 일으키지 않습니다. 자세한 내용은 트랜잭션 로그(SQL Server)를 참조하세요.

시스템 크래시 후 지정된 데이터베이스를 복구하는 데 필요한 시간은 크래시 시 더러워진 페이지를 다시 실행하기 위해 필요한 임의 I/O의 양에 따라 크게 달라집니다. 즉, 설정이 신뢰할 수 없음을 recovery interval 의미합니다. 정확한 복구 기간을 확인할 수 없습니다. 또한 자동 검사점이 진행 중인 경우 데이터에 대한 일반 I/O 작업이 상당히 증가하며 예측 불가능합니다.

복구 간격이 복구 성능에 미치는 영향

짧은 트랜잭션을 사용하는 OLTP(온라인 트랜잭션 recovery interval 처리) 시스템의 경우 복구 시간을 결정하는 주요 요인입니다. 그러나 이 recovery interval 옵션은 장기 실행 트랜잭션을 실행 취소하는 데 필요한 시간에 영향을 주지 않습니다. 장기 실행 트랜잭션이 있는 데이터베이스 복구는 옵션에 recovery interval 지정된 것보다 훨씬 오래 걸릴 수 있습니다. 예를 들어 서버 인스턴스를 사용하지 않도록 설정하기 전에 장기 실행 트랜잭션이 업데이트를 수행하는 데 2시간이 걸린 경우 실제 복구는 긴 트랜잭션을 복구하는 데 값보다 recovery interval 훨씬 오래 걸립니다. 장기 실행 트랜잭션이 복구 시간에 미치는 영향에 대한 자세한 내용은 트랜잭션 로그(SQL Server)를 참조하세요.

일반적으로 기본값은 최적의 복구 성능을 제공합니다. 그러나 복구 간격을 변경하면 다음과 같은 상황에서 성능이 향상될 수 있습니다.

장기 실행 트랜잭션이 롤백되지 않는 경우 복구가 정기적으로 1분보다 훨씬 오래 걸리는 경우
검사점이 자주 발생하면 데이터베이스의 성능이 저하됩니다.

설정을 recovery interval 늘리기로 결정한 경우, 조금씩 점진적으로 증가시키고 각 증가가 복구 성능에 미치는 영향을 평가하는 것이 좋습니다. 이 접근 방식은 recovery interval 설정이 증가함에 따라 데이터베이스 복구 완료에 시간이 그만큼 더 오래 걸리기 때문에 중요합니다. 예를 들어 10을 변경하는 recovery interval 경우 복구를 완료하는 데 0으로 설정된 경우보다 recovery interval 약 10배 더 오래 걸립니다.

간접 검사점

SQL Server 2012의 새로운 간접 검사점은 자동 검사점 대신 구성 가능한 데이터베이스 수준 대안을 제공합니다. 시스템 충돌 시 간접 검사점은 자동 검사점보다 잠재적으로 더 빠르고 예측 가능한 복구 시간을 제공합니다. 간접 검사점은 다음과 같은 이점을 제공합니다.

간접 검사점이 구성된 데이터베이스의 온라인 트랜잭션 작업으로 인해 성능이 저하될 수 있습니다. 간접 검사점은 데이터베이스 복구가 대상 복구 시간 내에 완료되도록 더티 페이지 수가 특정 임계값보다 적은지 확인합니다. 복구 간격 구성 옵션은 더티 페이지 수를 사용하는 간접 검사점과 달리 트랜잭션 수를 사용하여 복구 시간을 결정합니다. 많은 수의 DML 작업을 수신하는 데이터베이스에서 간접 검사점을 사용하도록 설정하면 백그라운드 작성기는 복구 수행에 필요한 시간이 데이터베이스의 목표 복구 시간 집합 내에 있는지 확인하기 위해 더티 버퍼를 디스크로 적극적으로 플러시하기 시작할 수 있습니다. 이로 인해 특정 시스템에서 추가 I/O 작업이 발생할 수 있으므로 디스크 하위 시스템이 I/O 임계값 위에서 또는 근처에서 작동하는 경우 성능 병목 현상에 영향을 줄 수 있습니다.
간접 검사점을 사용하면 REDO 중에 임의 I/O 비용을 고려하여 데이터베이스 복구 시간을 안정적으로 제어할 수 있습니다. 이렇게 하면 서버 인스턴스가 지정된 데이터베이스의 복구 시간에 상한 내에 있을 수 있습니다(장기 실행 트랜잭션으로 인해 과도한 UNDO 시간이 발생하는 경우 제외).
간접 검사점은 백그라운드에서 디스크에 더티 페이지를 지속적으로 작성하여 검사점 관련 I/O 급증을 줄입니다.

그러나 간접 검사점용으로 구성된 데이터베이스의 온라인 트랜잭션 워크로드는 성능 저하를 경험할 수 있습니다. 간접 검사점에서 사용하는 백그라운드 기록기가 서버 인스턴스에 대한 총 쓰기 부하를 늘리는 경우가 있기 때문입니다.

내부 검사점

내부 검사점은 디스크 이미지가 로그의 현재 상태와 일치하도록 보장하기 위해 다양한 서버 구성 요소에 의해 생성됩니다. 내부 검사점은 다음 이벤트에 대한 응답으로 생성됩니다.

ALTER DATABASE를 사용하여 데이터베이스 파일이 추가되거나 제거되었습니다.
데이터베이스 백업이 수행됩니다.
DBCC CHECK에 대해 명시적으로 또는 내부적으로 데이터베이스 스냅샷이 만들어집니다.
데이터베이스를 종료해야 하는 작업이 수행됩니다. 예를 들어 AUTO_CLOSE ON이고 데이터베이스에 대한 마지막 사용자 연결이 닫혀 있거나 데이터베이스를 다시 시작해야 하는 데이터베이스 옵션 변경이 수행됩니다.
SQL Server 인스턴스는 SQL Server(MSSQLSERVER) 서비스를 중지하여 중지됩니다. 두 작업 중 하나를 수행하면 SQL Server 인스턴스의 각 데이터베이스에 검사점이 발생합니다.
SQL Server 장애 조치 클러스터 인스턴스를 오프라인으로 설정합니다.

또한 참조하십시오

트랜잭션 로그(SQL Server)

다음을 통해 공유