[SQL Server / MS-SQL] 특정 테이블의 페이지 확인하기 (DBCC IND, DBCC PAGE)

 

 

 

 

---

 

 

 

DBCC PAGE([DB명],[FileID],[PageID],조회옵션)

 

DBCC PAGE 를 통해 특정 테이블의 페이지가 어떻게 되어있는지 확인해보자.

DBCC PAGE 를 실행하기 위해서는 먼저 인덱스의 ID 를 알아야한다.

 

 

1. 인덱스ID 확인

 

인덱스ID 는 아래 쿼리를 통해 확인이 가능하다.

SELECT name, index_id, type_desc
FROM sys.indexes
WHERE object_id = OBJECT_ID('테이블명')

 

 

위 쿼리를 조회하면 인덱스명, 인덱스ID, 인덱스종류를 확인할 수 있다.

인덱스에는 클러스터드 인덱스가 없는 경우와 있는 경우가 있다.

 

각각의 경우를 조회해보자.

 

테이블에 클러스터드 인덱스가 없는 경우

type_desc 가 HEAP 으로 조회되며, index_id 는 0 으로 조회된다.

 

 

 

테이블에 클러스터드 인덱스가 있는 경우

type_desc 가 CLUSTERED 로 조회되며, index_id 는 1 로 조회된다.

 

 

위 결과를 통해 원하는 인덱스를 조회해보자.

DBCC IND(DB명,테이블명,인덱스ID)

DBCC IND(TESTDB,NonClusteredTable,0)

 

나는 TESTDB 라는 데이터베이스에 있는 NonClusteredTable 이라는 테이블의 인덱스ID 0 (HEAP) 을 조회하였다.

 

 

 

결과를 보자.

 

첫번째 페이지(IndexID:0, PageType:10) 은 힙 구조의 IAM 페이지를 의미하는 것이다.

두번째 페이지(IndexID:0, PageType:1, IndexLevel:0) 은 힙 구조의 데이터 페이지를 의미하며,

PrevPagePID(이전 페이지) 가 0 이면서 IndexLevel(리프페이지)가 0인 PagePID 가 2398592 이 가장 첫번째 페이지임을 확인 할 수 있다. 

 

IAM 페이지는 기본적으로 테이블에 인덱스/힙 당 기본적으로 한개씩 만들어진다.

(만약, 해당 인덱스/힙이 4G 이상으로 커지면 그 이상으로 만들어지게 된다.)

고로, 힙에 IAM 하나 생성, 클러스터드 인덱스에 IAM 하나 생성, 넌클러스터드 인덱스에 IAM 하나 생성과 같이 각각의 인덱스(또는 힙) 에 최소 하나씩 생성이 된다.

 

자세한 부분은 아래 옵션에 맞게 조회해보면서 파악해볼 수 있다.

IndexID	설명	Page Type	설명	Index Level	설명
-2	IAM 페이지	1	데이터 페이지	NULL	인덱스 구조와 관련 없음
-1	테이블의 모든 페이지	2	인덱스 페이지	0	리프 레벨 페이지
0	데이터 페이지(HEAP)	3	텍스트/이미지 페이지	1	리프 다음 레벨 페이지
1	클러스터드 인덱스 페이지	10	IAM 페이지	2	루트 레벨 페이지
2~251	넌클러스터드 인덱스 페이지 (해당 인덱스ID)
255	text, ntext, image 형 데이터 페이지

 

 

 

 

*SQL Server 2012 부터 DBCC IND 를 대체하여 아래 DMV 를 통해 인덱스 정보 확인이 가능하다.

SELECT * FROM sys.dm_db_database_page_allocations(database_id, object_id, file_id, page_id, 'detailed')

 

 

 

 

2. 인덱스 페이지 정보 확인

 

DBCC PAGE([DB명],[FileID],[PageID],조회옵션)

 

 

조회옵션	설명
0	페이지 헤더만 출력
1	페이지 헤더, 실제 행 데이터 출력
2	페이지 헤더, 행 데이터, 페이지 개별 행 정보 출력
3	페이지 헤더, 행 데이터, 페이지 슬롯 어레이 출력

 

 

DBCC PAGE 를 실행하기 위해서는 데이터베이스명, 파일ID, 페이지ID 를 알아야하는데,

페이지ID 는 아까 위에서 찾았고, 

파일ID 는 아래 쿼리를 통해 확인할 수 있다.

SELECT fileid, name FROM sys.sysfiles;

 

위 쿼리에서 해당 데이터베이스의 fileid 를 보면 된다.

 

 

자. 그럼 위에서 조회하려는 페이지ID 2398592 페이지를 조회해보자.

나의 DB명은 TESTDB 이고, FileID 는 1, PageID 는 2398592, 조회옵션은 3을 선택해주었다.

-- DBCC PAGE 명령 결과를 클라이언트 창에 출력하기 위함
-- (그렇지 않으면 SQL Server 로그에만 기록되고, 사용자 인터페이스에는 표시되지 않음)
DBCC TRACEON(3604);

-- 페이지ID 2398592 페이지 정보 조회
DBCC PAGE(TESTDB,1,2398592,3) WITH TABLERESULTS; -- 해당 옵션은 테이블 형식으로 보여줌

 

 

조회 시 크게 BUFFER, PAGE HEADER, Slot Offest (데이터) 가 조회되는 것을 확인 할 수 있다.

 

BUFFER

해당 페이지가 메모리에 로드될 때 사용되는 버퍼 캐시에 대한 정보를 포함한다.

페이지ID, 페이지 타입, 메모리 주소 등의 메타데이터가 포함된다.

 

 

 

 

PAGE HEADER

페이지ID, 이전페이지ID, 다음페이지ID, 객체ID, Slot 수, GAM/SGAM/PFS/DIFF/ML 할당 여부, Slot 정보가 포함된다.

 

 

여기에서 41번째 행에 PFS 를 보면 해당 페이지가 얼마나 차있는지를 확인할 수 있다.

80_PCT_FULL 은 약 80% 가량 차있다는 것을 의미한다.

 

또, 44번째 행을 보면 해당 Record Type 을 확인할 수 있다. 해당 레코드 정보는 아래와 같다.

 1) PRIMARY_RECORD: 해당 레코드가 데이터 레코드

 2) INDEX_RECORD: 해당 레코드 인덱스 레코드

 3) BLOB_FRAGMENT: Large Object(varchar(max), varbinary(max) 등) 를 저장하는 레코드

 4) GHOST_RECORD: 삭제되었으나 아직 공간 회수가 이루어지지 않은 레코드

 

*PFS/GAM/SGAM/IAM 내용 참고

https://ryean.tistory.com/79

[SQL Server / MS-SQL] 페이지 및 익스텐트 아키텍처 (FPS, GAM, SGAM, IAM, BCM, DCM)

 

 

 

Slot Offset

Slot 에서는 실제 데이터 값, 길이, 레코드의 시작점으로부터의 (물리적) 거리를 확인할 수 있다.

 

이때, 힙/클러스터드 인덱스와 넌클러스터드 인덱스가 다르게 표기된다. 

 

힙/클러스터드 인덱스의 경우에는 실제 저장된 데이터 레코드를 확인 할 수 있다.

 

처음 결과가 해당 테이블 조회 결과이고, 두번째 결과가 DBCC PAGE 결과이다.

DBCC PAGE 를 보면 슬롯에 컬럼별로 저장이 됨을 확인할 수 있으며, 각 컬럼별 데이터도 확인할 수 있다.

 

넌클러스터드 인덱스의 경우에는 실제 데이터 값은 확인할 수 없고, 인덱스 키 값 또는 RID 를 확인할 수 있다.

이 때, 클러스터드 인덱스가 있는 경우와 없는 경우의 확인 방식이 조금 다르다.

 

클러스터드 인덱스가 있는 테이블

클러스터드 인덱스가 있을 경우, 클러스터드 키 값 자체가 행을 식별하게 된다.

 

Name 컬럼은 Clustered Index 이고, No 컬럼은 Non-Clustered Index 로 해당 인덱스를 조회하였다.

Name(key), No(key) 값을 표기하여 클러스터드 인덱스 키 값을 확인 할 수 있다.

 

 

클러스터드 인덱스가 없는 테이블

클러스터드 인덱스가 없는 힙(Heap) 구조의 테이블에서도 넌클러스터드 인덱스를 조회해보자.

 

클러스터드 인덱스가 있는 구조와 다르게 HEAP RID 라는 별도의 RID 가 생겼다.

HEAP RID 는 해당 레코드의 위치를 나타내며, 이는 파일ID, 페이지ID, 슬롯번호를 포함한 8바이트 바이너리 값으로 표기된다.

이 RID 값을 통해 실제 데이터를 불러온다.

 

 

 

 

*Azure MI, Azure SQL Database, SQL Server 2019 이상부터 DBCC PAGE 를 대체하여 아래 DMV 를 통해 페이지 정보 확인이 가능하다.

SELECT * FROM sys.dm_db_page_info ( DatabaseId , FileId , PageId , Mode )

 

 

자세한 내용은 아래 Docs 참고

https://learn.microsoft.com/ko-kr/sql/relational-databases/system-dynamic-management-views/sys-dm-db-page-info-transact-sql?view=sql-server-ver16

sys.dm_db_page_info(Transact-SQL) - SQL Server