데이터베이스 개발자 Tip & 강좌

SQLER의 개발자들이 만들어가는 데이터베이스 사용자 Tip & 강좌 게시판입니다. SQL서버, Oracle, MySQL 등 여러 클라우드/오픈소스 기반 데이터베이스 개발 및 운영 관련 팁과 쿼리 노하우를 이곳에서 가장 먼저 접하실 수 있습니다. 많은 도움 되시길 바랍니다.

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

 

  • 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) 인덱스는 잠재적으로 기본 테이블에 액세스할 필요없이 데이터를 매우 빠른 검색을 허용한다.

 

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

 

 

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

--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

 

 

강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp

No. Subject Author Date Views
1870 백업 미디어 세트에 압축 백업 추가하기 jevida(강성욱) 2016.10.08 1432
1869 Collation에 따른 실행계획 변경과 성능 문제 jevida(강성욱) 2016.10.08 1666
1868 SQL Connection Timeout 디버깅 with BizTalk Server jevida(강성욱) 2016.10.08 2220
1867 인스턴스 파일 초기화 활성 jevida(강성욱) 2016.10.08 1681
1866 누락된 인덱스 확인하기 jevida(강성욱) 2016.10.08 2786
» 비클러스터 인덱스 페이지 내용 jevida(강성욱) 2016.10.08 2009
1864 ATTACH DATABASE 오류 1314 jevida(강성욱) 2016.10.08 1160
1863 SQL Server 커넥션 풀링 jevida(강성욱) 2016.10.08 3905
1862 가상 SQL Server에 Hot Add vCPU 사용하기 jevida(강성욱) 2016.10.08 1112
1861 DDL 트리거를 활용한 ERRORLOG에 XEVENT 상태 기록하기 jevida(강성욱) 2016.10.08 1344
1860 쉐어포인트의 SQL Server 접속 문제 jevida(강성욱) 2016.10.08 1445
1859 Lazy Log Truncation jevida(강성욱) 2016.10.08 1268
1858 인덱스 구성과 상황에 따른 인덱스 성능 jevida(강성욱) 2016.10.08 1456
1857 Max worker thread 초과 이슈 jevida(강성욱) 2016.10.08 2565
1856 SQL Server Failover 클러스터 설치 트러블슈팅 jevida(강성욱) 2016.10.08 2333
1855 MAXDOP 극대화 하기 jevida(강성욱) 2016.10.08 1963
1854 SQL Server 가상화 팁 jevida(강성욱) 2016.10.08 2015
1853 Net Framework 4.0과 SQL Server 2008 설치 오류 jevida(강성욱) 2016.10.08 1834
1852 SQL Server 인덱스 튜닝 접근 jevida(강성욱) 2016.10.07 3362
1851 Sys.dm_os_performance_counter 해석하기 jevida(강성욱) 2016.10.07 2232





XE Login