SQL SERVER 가 수행하는 I/O의 특징적 동작

DB 버퍼 캐시는 데이터 파일로부터 읽어 들인 데이터 페이지 (또는 인덱스 페이지)를 담는 캐시 영역이다. 

일부 Direct Path Read 메커니즘이 작동하는 경우를 제외하면, 모든 페이지 읽기는 버퍼 캐시를 통해 이루어진다.
즉, 읽고자 하는 페이지를 먼저 버퍼 캐시에서 찾아보고 없을 때 디스크에서 읽는다.

디스크에서 읽을 때도 먼저 버퍼 캐시에 적재한 후에 읽는다. 
데이터 변경도 버퍼 캐시에 적재된 페이지를 통해 이루어지며, 

디스크 I/O는 물리적으로 액세스 암(Arm)이 움직이면서 헤드를 통해 이루어지는 반면, 
메모리 I/O는 전기적 신호에 불과하기 때문에 디스크 I/O에 비교할 수 없을 정도로 빠르다. 
디스크에서 읽은 데이터 블록을 메모리 상에 보관해 두는 기능이 모든 데이터베이스 시스템에 필수적인 이유다.

 

 

 

 

 

1. 미리 읽기 (Read-Ahead)

SQL 서버는 장래에 필요할 것으로 예측되는 데이터에 관해서 실제 읽기 요구가 발생하기 전에 
물리 디스크에 존재하는 데이터 파일에서 데이터를 미리 읽어 메모리 영역(버퍼 캐시)에 올려둘 수 있다.
이 동작을 미리 읽기 (Read-Ahead)라고 한다.
Standard 에디션의 경우 최대 128페이지, Enterprise 에디션은 1,024 페이지까지 읽어들일 수 있다.

 

 

2. 체크 포인트 (CheckPoint)

SQL 서버가 관리하는 데이터가 변경되면 우선 앞에서 말한 미리 쓰기 로그 조작이 실행된다. (트랜잭션 로그 파일에 기록)
이어서 버퍼 캐시 상의 데이터가 갱신된다. 이 시점의 갱신 조작은 논리 기록이라 부른다. (아직 물리 디스크에 갱신되지 않았음)
버퍼 캐시 상에 갱신된 데이터는 임의의 타이밍에 디스크에 기록되고 이 때의 갱신 조작을 물리 기록이라 한다.

 

버퍼 캐시 상에 갱신되었지만, 아직 물리 기록이 실행되지 않은 데이터를 더티 페이지(drity page)라고 한다.
그리고 더티 페이지에 대한 물리 기록 수단의 하나가 체크포인트이다.

SQL 서버 내에는 체크포인트 프로세스라 불리는 내부 컴포넌트가 있는데
이 컴포넌트가 버퍼 캐시에 읽어들인 데이터를 정기적으로 스캔하고 있다.

체크포인트 프로세스는 스캔을 통해 더티 페이지가 발견되면
각 더티 페이지에 대해 물리 기록을 실행한다.
체크 포인트 프로세스는 한 번에 16개 까지 더티 페이지의 물리 기록을 요구하며,
기본적으로 더티 페이지가 없어질 때까지 비동기로 물리 기록 요구를 반복한다.

 

 

 

3. 집중 기록 (Eager Write)

집중 기록 동작도 더티 페이지의 물리 기록 수단 중 하나이다.

일괄 조작
보통의 경우 미리 쓰기 동작에서 설명한 것처럼 데이터의 갱신에 대한 동작은 모두 로그 파일에 기록된다.
미리 쓰기는 데이터의 보전성과 트랜잭션의 일관성을 높이기 위해 매우 효과적인 동작이지만, 한편으로 퍼포먼스에 좋지 않는 영향을 미치는 경우도 있다.

가령, 다른 데이터 소스에서 텍스트 파일로 출력된 대량의 데이터를 SQL 서버 상의 데이터베이스에 입력하는 경우를 생각해보자.
입력해야 하는 모든 데이터에 대해 미리 쓰기 로그를 실행하면 대량의 트랜잭션 로그 기록이 발생한다.
그로 인해 처리율이 현저히 저하될 수 있다.

이러한 경우를 대비해서 SQL Server에는 일괄 조작이라는 선택지가 마련되어 있다.
대량 데이터의 입력을 일괄 조작으로 취급하면 개별 데이터의 갱신 로그는 기록되지 않고 최소한의 정보만 로그 파일에 기록된다.

 

- 모든 갱신 동작을 로그 출력하는 경우의 예 (1억 건의 불러오기)
대량의 트랜잭션 로그 기록이 발생  -> 대량 기록은 디스크에 큰 부하가 된다.

 

- 일괄 조작하는 경우의 예
일괄 조작 트랜잭션 로그만 기록된다.

 

일괄 조작을 실행하면 대량의 더티 페이지가 버퍼 캐시상에 발생한다.
집중 기록은 그 데이터를 물리 기록하기 위해 준비된 동작이다.

 

 

집중기록

대량의 데이터를 데이터 파일에 효율적으로 기록하기 위해서는 
새로운 데이터를 저장하는 데 필요한 페이지를 물리 디스크상의 데이터 파일에 작성하는 동작과
버퍼 캐시상의 더티 페이지에 따라서 물리 기록을 수행하는 동작을 병렬로 실행한다.

즉, 대량의 데이터를 물리 디스크에 쓰기 위해 페이지 생성과 페이지 기록을 병렬로 처리하는 것을 의미한다.

 

 

4. 지연 기록 (Lazy Write)

더티 페이지의 물리 기록을 수행하는 세 번째의 동작은 지연 기록이다.
지연 기록을 수행하기 위해 SQL Server에는 지연 기록기 (Lazy Writer)라고 하는 내부 컴포넌트가 준비되어 있다.

 

 

지연 기록기
지연 기록기의 가장 중요한 임무는 버퍼 캐시에 항상 일정량의 빈 페이지를 마련해두는 것이다.

디스크에서 읽어들인 데이터는 반드시 버퍼 캐시상에 저장된다.
데이터가 새로이 디스크에서 읽히면 저장하기 위한 빈 페이지가 버퍼 캐시상에 필요하다.
그러나 디스크를 읽어들일 때마다 빈 페이지를 찾아 매번 버퍼 캐시를 스캔하는 일은 비효율적이고 퍼포먼스에 악영향을 미친다.

그래서 미리 사용가능한 빈 페이지를 프리 리스트라 불리는 링크 리스트에 등록해둔다.
이러면 디스크에서 데이터를 읽어들인 후 버퍼 캐시를 스캔할 필요 없이 프리 리스트에 등록된 빈 페이지에 저장하기만 하면 된다.

 

미사용 페이지가 프리 리스트에 등록된다.
프리 리스트를 사용해서 미사용 영역을 관리함 -> 메모리를 효율적으로 사용

 

지연 기록
프리 리스트에 등록되어 있는 빈 페이지의 수가 임계값을 밑돌면 장래의 데이터 읽어들이기에 대비할 목적으로 
지연 기록기는 참조 빈도가 낮은 버퍼 캐시상의 페이지를 초기화해서 프리 리스트에 추가한다.
만약 이 페이지가 더티 페이지면 초기화 하기 전에 물리 디스크에 내용이 기록된다.

 

이러한 기록을 지연 기록이라고 한다.