Professional SQL Server 2012 Internals and Troubleshooting 내용 정리
- Dirty Page: 페이지가 메모리에 처음 읽어들어오면 우리는 이것을 Clean Page라고 부른다. 그것은 메모리에 있는 데이터 페이지와 디스크에 있는 데이터 페이지의 내용이 동일하기 때문이다. 그러나 페이지가 수정이 되면 메모리의 내용과 디스크의 내용이 달라진다. 이것을 Dirty Page라고 부른다.
sys.dm_os_buffer_descriptiors를 통해 dirty page를 확인할 수 있다.
SELECT db_name(database_id) AS 'Database',count(page_id) AS 'Dirty Pages'
FROM sys.dm_os_buffer_descriptors
WHERE is_modified =1
GROUP BY db_name(database_id)
ORDER BY count(page_id) DESC
|
이러한 Dirty page가 디스크에 쓰이는 경우는 free buffer list가 낮거나 checkpoint가 발생했을 경우이다. SQL Server는 일정량의 free page를 유지하여 페이지 할당을 빠르게 하도록 설계 되어 있다. 그리고 이러한 free page에 관한 정보들은 free buffer list가 가지고 있다. 작업 스레드가 읽기 요청을 할 때마다 캐시의 64 페이지 목록을 가져와 사용 가능한 버퍼 목록이 특정 임계 값 미만인지 여부를 확인한다. 만약 임계값 미만일 경우 목록에 있는 Dirty page를 디스크에 기록한다. Lazy writer 또한 Free buffer list의 값이 낮을 때 발생한다.
- Lazy Writer: Lazy Writer는 Free buffer list를 주기적으로 체크하는 쓰레드이다. Free buffer list의 값이 낮을 때 데이터 캐시에서 Dirty page 들 중에서 오랜시간동안 사용하지 않아 시간제한이 지난 페이즈를 스캔한다. 만약 이러한 Dirty page가 있다면 해당 페이지를 디스크에 flush하고 Free memory를 확보한다. Lazy writer는 OS의 메모리가 부족할 경우 SQL의 메모리를 돌려줄 수 있도록 모니터링도 하고 있다. SQL Server가 사용 중일 때 이용 가능 한 물리적 메모리가 있고 설정한 max memory 임계 값에 도달하지 않은 경우 요구 (따라서 버퍼 풀)를 충족시키기 위해 사용 가능한 버퍼 목록의 크기가 커진다.
- Checkpoint Process: Commit된 트랜잭션들의 변화된 내용에 대해 모두 디스크에 기록했는지 체크하는 프로세스이다. 이 체크포인트는 SQL Server를 복구할 때, 기준점이 된다. Check point는 commit 된 트랜잭션과 연관된 Dirty page를 디스크에 쓰는 것을 보장한다. 또한 Commit되지 않은 Dirty page를 디스크에 flush하여 쓰기를 효율적으로 사용할 수 있다. 그러나 Lazy writer와 달리 Check point는 캐시에서 페이지를 제거하지 않는다. Dirty page가 디스크에 쓰여지면 해당 페이지 헤더에 Dirty page였다는 정보는 없어지고 다시 Clean Page가 된다.
기본적으로 사용량이 많은 서버에서 SQL Server는 트랜잭션 로그에 표시된 매 분마다 Check point를 발생시킨다. SQL Server 인스턴스나 데이터베이스가 다시 시작될 경우 로그를 읽는 복구 프로세스는 Check point 이전의 로그레코드는 신경쓰지 않아도 되는 기준이 되는 것이다.
따라서 체크 포인트 사이 시간은 마지막 체크 포인트 이후 발생한 commit 된 트랜잭션을 Roll Forward하고 commit되지 않은 트랜잭션을 Roll Back하기 위해 수행해야하는 작업의 양을 나타낸다. SQL Server는 매분 체크포인트를 지정하여 데이터베이스를 시작할 때 복구 시간을 1 분 미만으로 유지하려고 한다. 그러나 최소 10MB가 해당 시간 내에 로그에 기록되지 않으면 자동으로 체크 포인트를 수행하지 않는다.
체크포인트는 CHECKPOINT라는 명령어를 통해 수동으로 수행할 수 있다. 그리고 백업과 같은 작업이 이루어 질 때도 체크포인트가 가장 먼저 수행된다.
Trace flag 3502를 추가해 두면 체크포인트가 시작되고 중지되는 것을 에러로그에 기록한다.
Trace flag에 대한 설명은 다음 사이트에서 확인 할 수 있다.
참고: LSN(LOG Sequence Number): LSN은 트랜잭션 로그의 레코드를 식별하는 데 사용되며 SQL이 실행된 순서로 정렬 된다. SQL Server는 해당 SQL문이 실행된 순서를 알게 되는 것이다. Recovery가 Roll forward 또는 Roll back과 같은 작업을 수행하기 전에 최소 LSN을 계산한다. 이것은 체크 포인트 LSN뿐 아니라 다른 기준도 고려된다. 이것은 Dirty page가 디스크에 쓰이지 않은 경우 Recovery에 대해 Checkpoint 전에 페이지의 데이터에 대해서도 염려해야 하는다는 것을 의미한다. 많은 페이지를 가진 대형 시스템에서 이런 일이 발생할 수 있다.
Professional SQL Server 2012 Internals and Troubleshooting p.18-20
댓글 없음:
댓글 쓰기