메모리 관리 아키텍처 – 버퍼 관리_페이지 읽기

메모리 관리 아키텍처 – 버퍼 관리_페이지 읽기

 

• Version : SQL Server 2005, 2008, 2008R2

 

SQL Server 데이터베이스 엔진 인스턴스의 I/O에는 논리적 읽기 수 및 물리적 읽기 수가 포함되어 있다.

 

 

논리적 읽기는 데이터베이스 엔진이 버퍼캐시에서 페이지를 요청할 때마다 발생 한다. 페이지가 현재 버퍼캐시에 없는 경우 물리적 읽기가 먼저 디스크의 페이지를 캐시로 복사한다.

 

 

데이터베이스 엔진의 인스턴스로 생성된 읽기 요청은 관계형 엔진에 의해 제어되고 저장소 엔진에 의해 최적화 된다. 관계형 엔진은 테이블 검색, 인덱스 검색, 키 사용 읽기와 같은 가장 효과적인 액세스 방법을 결정 한다. 저장소 엔진 및 버퍼 관리자 구성 요소는 수행할 일반 적인 읽기 패턴을 결정하고 액세스 방법을 구현하는데 필요한 읽기를 최적화 한다.

 

 

[미리 읽기(Read-Ahead)]

데이터베이스 엔진은 미리 읽기(Read-Ahead)라고 하는 성능 최적화 메커니즘을 지원한다. 미리 읽기는 쿼리 실행 계획을 수행하는 데 필요한 데이터 및 인덱스 페이지를 예상하고 페이지가 쿼리에서 실제로 사용되기 전에 해당 페이지를 버퍼 캐시로 가져온다. 이렇게 하면 계산과 I/O가 동시에 수행되어 CPU와 디스크를 모두 충분히 활용 할 수 있다.

 

미리 읽기 메커니즘을 통해 데이터베이스 엔진에서는 한 파일에서 연속하는 최대 64페이지 (512KB)를 읽을 수 있다. 읽기는 버퍼 캐시에서 연속되지 않은 적절한 수의 버퍼에 대해 단일 분산 수집 읽기(single scatter-gather read)로 수행 된다. 해당 범위의 페이지 중 버퍼 캐시에 이미 존재하는 페이지가 있을 경우 읽기가 완료 된 후 읽기에서 해당 페이지가 삭제 된다. 해당 페이지가 캐시에 이미 있을 경우 완료 후 페이지 범위가 잘릴 수도 있다.

 

 

[데이터 페이지 읽기]

데이터베이스 엔진에서는 데이터 페이지를 읽을 때 테이블 검색을 사용하면 매우 효율적이다. SQL Server 데이터베이스에 있는 IAM(Index Allocation Map)페이지에 테이블 또는 인덱스에서 사용하는 익스텐트가 나열 된다. 저장소 엔진은 IAM을 참조하여 읽어야 하는 정렬된 디스크 주소 목록을 작성할 수 있다. 이렇게 하면 저장소 엔진이 디스크에서의 I/O 위치를 기반으로 하여 순서대로 수행되는 대형 순차 읽기로 해당 I/O를 최적화 할 수 있다.

 

[인덱스 페이지 읽기]

저장소 엔진은 인덱스 페이지를 순서대로 읽는다. 저장소 엔진은 리프 수준 위의 중간 인덱스 페이지에 있는 정보를 사용하여 키를 포함하는 페이지의 직렬 미리 읽기를 예약한다.

 

아래 그림처럼 ABC에서 DEF까지의 모든 키에 대해 요청이 수행된 경우 저장소 엔진은 리프 페이지 위의 인덱스 페이지를 먼저 읽는다. 그러나 지정된 범위의 키를 포함하는 마지막 페이지까지 순서대로 읽지는 않는다. 대신 저장소 엔진은 중간 인덱스 페이지를 검색하고 읽어야 하는 리프 페이지 목록을 작성한 다음 키 순서대로 모든 읽기를 예약 한다.

 

또한 저장소 엔진은 504, 505, 527, 528 페이지가 인접해 있다고 인식하고 단일 분산 읽기(single scatter-gather read)를 수행 하여 단일 작업으로 인접한 페이지들을 검색 한다.

 

직렬 작업(serial operation)에서 검색할 페이지가 많으면 저장소 엔진은 한번에 읽을 블록을 예약한다. 이러한 읽기 하위 집합이 완료되면 저장소 엔진은 읽어야 할 모든 내용이 예약될 때까지 같은 수의 읽기를 예약한다.

 

저장소 엔진은 사전 인출(prefetch)을 사용하여 비클러스터형 인덱스의 기본 테이블 조회 속도를 향상 시킨다. 비클러스터형 인덱스의 리프 행은 각각의 특정 키 값을 포함하는 데이터 행에 대한 포인터를 포함 한다. 저장소 엔진이 비클러스터형 인덱스의 리프 페이지를 읽을 때 포인터가 이미 검색된 데이터 행에 대한 비동기 읽기도 예약하기 시작한다. 이를 통해 저장소 엔진은 비클러스터형 인덱스의 검색이 왼료 되기 전에 기본 테이블의 데이터 행을 검색할 수 있다.

 

사전 인출(prefetch)은 테이블에 클러스터형 인덱스 여부와 관계없이 사용 되며 SQL Server Enterprise에서는 다른 버전의 SQL Server보다 더 많은 사전 인출을 사용하므로 더 많은 페이지를 마리 읽을 수 있다.

 

[instruction prefetch] 파이프라이닝 기법의 간단한 형태, 명령의 해독 및 실행과 예비 추출은 독립적으로 실행된다. 보통 예비 추출되는 명령어는 현재 프로그램 카운터가 가리키고 있는 위치 바로 뒤의 몇 개의 명령어로 프로그램이 분기 없이 그대로 실행되면 이들은 미리 대기 행렬에 들어가 있기 때문에 추출을 위한 기억 장치 접근 시간을 절약할 수 있으며, 컴퓨터의 중앙 처리 장치(CPU)가 앞으로 실행될 명령어를 기억 장치에서 미리 불러내어 CPU 내부의 대기 행렬에 넣어둠으로써 실행 속도를 빠르게하는 기법. 그러나 분기 명령이 있으면 미리 접근하여 대기 행렬에 들어가 있는 명령어들은 불필요하게 되므로 새로 명령어 추출 과정을 거치게 된다   http://terms.naver.com/entry.nhn?cid=209&docId=826129&mobile&categoryId=209

 

 

[고급 검색(Advanced Scanning)]

SQL Server Enterprise의 고급 검색 기능을 사용하여 여러 태스크에서 전체 테이블 검색을 공유 할 수 있다. T-SQL 문의 실행 계획이 테이블의 데이터 페이지 검색을 요구하고 데이터베이스 엔진에서 테이블이 이미 다른 실행 계획에 대해 검색되고 있음을 감지하는 경우 데이터베이스 엔진은 두 번째 검색의 현재 위치에서 두 번째 검색을 첫 번째 실행 계획에 조인한다.

 

데이터베이스 엔진은 각 페이지를 한 번 읽고 각 페이지의 행을 두 실행 계획에 전달 한다. 이 작업은 테이블 끝에 도달 할 때까지 계속 된다.

 

테이블 끝에서 첫 번째 실행 계획에 완전한 검색 결과가 포함되지만 두 번째 실행 계획은 진행 중인 검색을 조인하기 전에 읽어오는 데이터 페이지를 계속 검색 해야 한다. 그런 다음 두 번째 실행 계획은 테이블의 첫 번째 데이터 페이지로 다시 넘어가고 첫 번째 검색을 조인한 지점까지 검색이 진행 된다. 이러한 방식으로 원하는 수만큼 검색 결과를 결합 할 수 있다.

 

데이터베이스 엔진은 모든 검색을 완료할 때까지 데이터 페이지를 계속 루핑한다. 이 메커니즘은 ORDER BY 절없이 SELECT 문에서 반환된 결과의 순서를 보장할 수 없기 때문에 회전 목마 검색(merry-go-round)검색이라고도 한다.

 

예를 들어 500,000개의 페이지를 포함하는 테이블이 있다고 할 때

1. UserA는 테이블의 검색을 요청하는 T-SQL문을 실행한다.
2. 해당 검색이 100,000개의 페이지를 처리하는 동안 UserB가 동일한 테이블을 검색하는 T-SQL문을 실행한다.
3. 데이터베이스 엔진은 100,001 페이지 이후의 페이지에 대해 하나의 읽기 요청 집합을 예약하고 각 페이지의 행동을 두 검색에 다시 전달한다.
4. 200,000번째 페이지를 검색할 때 UserC가 동일한 테이블을 검색하는 T-SQL 문을 실행한다.
5. 200,001페이지부터 데이터베이스 엔진은 읽고 있는 각 페이지의 행을 세 가지 검색에 모두 다시 전달 한다.
6. 500,000번째 행을 읽고 나면 UserA는 검색이 완료 되고 UserB 및 UserC에 대한 검색이 1페이지부터 읽기 시작한다.
7. UserB가 100,000 페이지에 도달하면 UserB는 검색이 완료된다.
8. UserC는 200,000페이지를 읽을 때 까지 검색이 계속된다.

 

 

 

고급 검색을 사용하지 않는 경우 각 사용자는 버퍼 공간을 최대한 사용하려고 하여 이로 인해 디스크 충돌 경합이 발생한다. 그러면 같은 페이지를 여러 사용자가 한 번에 읽고 공유하는 대신 각 사용자가 한번씩 읽게 되므로 성능이 저하되고 리소스 처리 시간이 소모된다.

 

 

[참고자료]

페이지 읽기 : http://msdn.microsoft.com/ko-kr/library/ms191475(v=sql.105).aspx

SQL Query Optimization FAQ Part 1 (With video explanation) :

http://www.codeproject.com/Articles/55505/SQL-Query-Optimization-FAQ-Part-1-With-video-expla

SQL Server I/O Basics : http://technet.microsoft.com/library/Cc966500

How It Works: Bob Dorr's SQL Server I/O Presentation :

http://blogs.msdn.com/b/psssql/archive/2010/03/24/how-it-works-bob-dorr-s-sql-server-i-o-presentation.aspx

 

 

 

 

2013-06-03 / 강성욱 / http://sqlmvp.kr