비클러스터 인덱스 페이지 내용

비클러스터 인덱스 페이지 내용

 

• Version : SQL Server 2008, 2008R2, 2012

 

SQL Server의 비클러스터 인덱스에 저장되어 있는 내용을 살펴 본다.

 

다음 스크립트는 사용하여 예제 데이터베이스 및 데이터 생성한다.

use master go   SET STATISTICS XML OFF SET STATISTICS IO OFF SET NOCOUNT ON go   IF DB_ID('HeapsDB') IS NOT NULL BEGIN ALTER DATABASE HeapsDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE DROP DATABASE HeapsDB END GO   CREATE DATABASE HeapsDB GO   ALTER DATABASE HeapsDB SET RECOVERY SIMPLE GO   USE HeapsDB GO   CREATE TABLE NumbersTable ( NumberValue BIGINT NOT NULL, BiggerNumber BIGINT NOT NULL, CharacterColumn CHAR(50) ) GO   INSERT INTO NumbersTable ( NumberValue, BiggerNumber, CharacterColumn ) SELECT NumberValue, NumberValue + 5000000, LEFT(REPLICATE((CAST(NumberValue as VARCHAR(50))),50),50) FROM ( SELECT NumberValue = row_number() over(order by newid() asc) FROM master..spt_values a CROSS APPLY master..spt_values b WHERE a.type = 'P' AND a.number <= 200 AND a.number > 0 AND b.type = 'P' AND b.number <= 200 AND b.number > 0 ) a

 

다음은 기본키 제약으로 클러스터 인덱스를 추가하고 비클러스터 인덱스도 생성하였다.

ALTER TABLE NumbersTable ADD CONSTRAINT PK_NumbersTable PRIMARY KEY CLUSTERED (NumberValue) GO   CREATE NONCLUSTERED INDEX idx_NumbersTable ON NumbersTable(BiggerNumber) GO

 

 

DMV sys.dm_db_index_physical_stats는 비클러스터 인덱스의 B-tree 깊이를 볼 수 있다. 비클러스터형 인덱스가 기본 테이블에 있는 열의 하위 집합만 포함하는 B-tree 구조로 구성되어 있다. 이러한 NC(NonClustered) 인덱스는 잠재적으로 기본 테이블에 액세스할 필요없이 데이터를 매우 빠른 검색을 허용한다.

 

비클러스터 인덱스에 대한 자세한 내용은 다음 포스트를 참고 한다.

• 비클러스터 인덱스 구조 : http://sqlmvp.kr/140187951208

 

 

다음 스크립트는 비클러스터 인덱스의 깊이를 확인할 수 있다.

--look at the depth of the trees on the NC index idx_NumbersTable select page_count, index_level, record_count, index_depth from sys.dm_db_index_physical_stats(db_id(),object_id('NumbersTable'),2,null,'DETAILED');

 

 

현재 비클러스터 인덱스는 2단계가 있는 것을 확인 할 수 있다. DMV Sys.dm_databases_page_allocations는 인덱스 루트 페이지에 대한 페이지 ID를 찾을 수 있다. (이 기능은 2012 이전 버전에서 DBCC IND 명령어를 실행한다.)

--look at the linkages for the NC index select allocated_page_page_id, next_page_page_id, previous_page_page_id, page_level from sys.dm_db_database_page_allocations(db_id(),object_id('NumbersTable'),2,null,'DETAILED') where page_type_desc is not null and page_type_desc = 'INDEX_PAGE' and page_level = 1

 

 

(지금의 경우 페이지 ID는 232이다. 사용자마다 다를 수 있다.) 페이지 ID를 확인 하였으면 DBCC PAGE를 사용하여 엔덱스 페이지의 내용을 볼 수 있다. (TABLERESULTS옵션을 함께 사용하기 때문에 추적 플래그 3604를 활성화 하지 않아도 된다.) 다음과 같이 2개의 결과 집합이 나타난다.

DBCC PAGE(HeapsDB, 1, 232, 3) WITH TABLERESULTS

 

 

두 번째 결과 집합에서 반환키가 인덱스 레코드만큼 저장된 것을 확인 할 수 있다. . NC인덱스가 고유로 정의되어 있지 않기 때문에 NumberValue컬럼의 고유 키 값을 루트페이지에서 클러스터 키로 저장한다.

 

UNIQUE 비클러스터는 어떻게 다른지 살펴보자. 다음 스크립트를 실행하면 UNIQUE 비클러스터를 생성하고 PAGE ID를 반환한다.

CREATE UNIQUE NONCLUSTERED INDEX idx_NumbersTable ON NumbersTable(BiggerNumber) WITH DROP_EXISTING   select allocated_page_page_id, next_page_page_id, previous_page_page_id, page_level from sys.dm_db_database_page_allocations(db_id(),object_id('NumbersTable'),2,null,'DETAILED') where page_type_desc is not null and page_type_desc = 'INDEX_PAGE' and page_level = 1

 

 

DBCC PAGE 명령어를 사용하여 페이지 내용을 살펴보자. BiggerNumber(key)값이 루트 페이지에 저장하는 고유 값이다. 또한 ROW Size값이 23바이트(NC 인덱스)에서 15바이트(UNC 인덱스)로 변경된 것을 확인 할 수 있다. 8바이트의 차이는 NumberValue 컬럼의 Bigint 값 8바이트의 값이다.

DBCC PAGE(HeapsDB, 1, 488, 3) WITH TABLERESULTS

 

 

BiggerNumber 열은 인덱스의 하위 수준에 있는 하위 페이지를 가리킨다. 이 방법을 사용하여 인덱스를 순회하는 루트페이지 값을 확인하고 다음 레벨의 페이지 액세스 여부를 결정한다. 이는 단순 읽기 뿐만 아니라 미리 읽기에 대해서도 적용된다.

 

BiggerNumber 컬럼이 NULL 값인 ChildPageID를 살펴보면 하위 페이지를 확인 할 수 있으며 키 값에서 50000001 ~ 5000368을 포함하고 있다. 루트 페이지에서는 다음 키 값인 500369의 키를 가리킨다.

DBCC PAGE(HeapsDB, 1, 456, 3) WITH TABLERESULTS

 

 

리프 수준의 페이지에서 500001의 키 값은 포함하지 않는다. 이는 리프 수준이기 때문에 또 다시 기본 테이블에 대한 포인터가 있어야 한다. NumberValue 열이 존재하는 이유가 이 때문이다.

 

 

[참고자료]

http://blogs.msdn.com/b/sql_pfe_blog/archive/2013/04/04/viewing-sql-server-non-clustered-index-page-contents.aspx

 

 

 

2013-10-18 / 강성욱 / http://sqlmvp.kr