SQL Server 물리적 행 함수(RID 조회)

SQL Server를 운용하다 보면 물리적으로 저장하는 위치를 파악해야 하는 이슈가 발생될 때가 있다.

특히 Deadlock 등의 정보를 조회하다 보면 waitresource="RID: 8:1:16576:0" 등의 RID 정보를 제공함으로써 물리적인 저장정보를 알려주기도 한다.

 

RID의 구조는 다음과 같다.

RID: Database ID:File ID:PAGE 번호: Slot 번호

1)      Database ID

(1)    Database 고유 ID

(2)    DMV sys.databases 로 조회 가능

SELECT name, database_id FROM sys.databases

(3)    1~4번은 system Database로 구성 된다.

2)      File ID

(1)    Database를 구성하는 file들의 고유 번호

(2)    DMV sys.database_files 로 조회 가능

SELECT FILE_ID,type_desc, name, physical_name,state_desc5 

FROM AdventureWorks2019.sys.database_files

(3)    Database 속성에서 생성한 Data파일과 Log파일 모두 확인할 수 있다.

3)      Page 번호

(1)    Page는 8kb로 이루어진 데이터가 저장되는 공간

(2)    Page 번호는 페이지가 늘어날 때 자동으로 증가하며 할당 된다.

4)      Slot 번호 : 해당 페이지에 저장된 데이터 행의 순서로 0번부터 시작

위의 RID 정보를 가지고 Page의 저장 정보를 다음과 같이 조회하여 물리적으로 저장된 page의 상태정보를 조회할 수 있다.

SELECT * FROM SYS.dm_db_page_info(10,1,1,NULL)

 

그렇다면 테이블을 조회 시 해당 행의 RID 정보를 어떻게 알 수 있을 것인가?

SQL Server의 각 행은 자신의 물리적인 저장위치를 바이너리 형태의 메타 데이터로 가지고 있다. 해당 메타 정보는 다음과 같이 조회가 가능하다.

SELECT %%physloc%% AS physloc, * FROM AdventureWorks2019.Person.Person

 

해당 바이너리 정보는 다음과 같은 함수를 통해 RID를 조회할 수 있다.

SELECT sys.fn_PhysLocFormatter(%%physloc%%) AS Physical_RID, * 

FROM AdventureWorks2019.Person.Person

Physical_RID의 값은 (File ID:PAGE 번호: Slot 번호) 로 조회가 가능하고 해당 정보를 통해 page의 정보를 보다 상세히 조회할 수 있다.

 

해당 정보를 Cross Apply를 이용하여 다음과 같이 조회도 가능하다

SELECT B.*,A.* 

FROM AdventureWorks2019.Person.Person AS A 

CROSS APPLY AdventureWorks2019.sys.fn_PhysLocCracker(%%physloc%%) AS B

 

출처

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

2)      https://docs.microsoft.com/ko-kr/sql/relational-databases/pages-and-extents-architecture-guide?view=sql-server-ver15

3)      https://nexondbteam.tistory.com/31