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

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

SQL Server 테이블 및 인덱스 구조 아키텍처(1/4)

– 테이블 및 인덱스 구성

 

  • Version : SQL Server 2005, 2008, 2008R2, 2012

 

SQL Server 데이터베이스의 개체는 8KB 페이지의 컬렉션으로 저장된다. 테이블 미 및 인덱스 페이지가 구성되고 저장, 액세스 되는 방법에 대해서 4장에 걸쳐 다루어 본다.

 

[테이블 구성]

테이블은 하나 이상의 파티션에 포함되어 있으며 각 파티션에는 데이터 행이 힙 또는 클러스터형 인덱스 구조로 포함되어 있다. 힙 또는 클러스터형 인덱스 페이지는 데이터 행의 열 유형에 따라 하나 이상의 할당 단위로 관리 된다.

 

페이지 할당 관리 관련 : http://sqlmvp.kr/140186842239

 

아래 그림은 테이블의 구성을 나타낸다.

 

 

[파티션]

테이블 및 인덱스 페이지는 하나 이상의 파티션에 포함되어 있다. 파티션은 사용자 정의된 데이터 구성 단위이다. 기본적으로 테이블이나 인덱스는 하나의 파티션을 갖는다. 파티션은 단일 파일 그룹에 포함되어 있다.

 

테이블 또는 인덱스에 파티션이 여러 개 사용되면 지정된 열을 기준으로 행 그룹이 개별 파티션에 매핑 되도록 데이터가 행 분할 된다. 이러한 파티션은 데이터베이스에 있는 하나 이상의 파일 그룹에 포함 될 수 있다. 데이터에서 쿼리나 업데이트가 수행되면 테이블이나 인덱스는 단일 논리적 엔터티로 처리 된다.

 

[클러스터형 테이블, 힙 테이블, 인덱스]

SQL Server 테이블은 다음 두 방법 중 하나를 사용하여 파티션 내에 있는 데이트 페이지를 구성 한다.

  • 클러스터형 테이블 : 클러스터형 인덱스를 갖고 있는 테이블. 데이터 행은 클러스터형 인덱스 키에 기반한 순서대로 저장. B-tree 구조. 리프 수준의 데이터 페이지를 포함하여 각 인덱스 수준에 있는 페이지는 이중 연결 리스트로 연결. 한 수준에서 다른 수준으로 이동하는 경우 키 값을 사용

 

  • 힙 테이블 : 클러스터형 인덱스가 없는 테이블. 데이터 행은 특정한 순서로 저장되지 않음. 페이지 시퀀스에 대한 특별한 순서 없음. 데이터 페이지는 연결 리스트로 연결되지 않음.

 

힙 또는 클러스터형 테이블의 파티션이 여러 개인 경우 각 파티션에는 해당 파티션의 행 그룹이 포함된 힙 또는 B-tree구조가 있다. (파티션이 4개인 경우 4개의 B-tree가 있다.)

 

인덱싱된 뷰는 클러스터형 테이블과 동일한 저장소 구조를 갖는다.

 

[비클러스터형 인덱스]

비클러스터형 인덱스는 클러스터형 인덱스와 유사한 B-tree 구조를 가지고 있다. 클러스터형 인덱스와 차이점은 비클러스터형 인덱스는 행의 순서에 영향을 주지 않는다. 리프 수준에는 인덱스 행이 포함되어 있다. 각 인덱스 행에는 비클러스터형 키 값, 행 포인터 및 포괄 열이나 키가 아닌 열이 포함 된다. 포인터 값은 키 값이 있는 데이터 행을 가리킨다.

 

[XML 인덱스]

테이블의 각 XML열에는 기본 XML 인덱스 하나와 보조 XML 인덱스 여러 개를 만들 수 있다. XML 인덱스는 XML 데이터 형식 열의 XML BLO(Binary Large Object)를 영구적인 단편 형태로 표현한 것이다. XML 인덱스는 내부 테이블로 저장 된다.

 

[할당 단위]

할당 단위는 힙 또는 B-tree 내에서 해당 페이지 유형에 따라 데이터를 관리하는데 사용되는 페이지 컬렉션이다. 아래 표는 테이블 및 인덱스 데이터를 관리하는데 사용되는 할당 단위 유형이다. 힙 또는 B-tree는 특정 파티션에서 각 유형별로 하나의 할당 단위만 가질 수 있다.

할당 단위 유형

설명

IN_ROW_DATA

LB(Large Object)데이터를 제외한 모든 데이터가 포함되어 있는 데이터 또는 인덱스 행. 페이지 또는 인덱스 유형

LOB_DATA

Text, ntext, image,xml, varchar(max),nvarchar(max), varbinary(max) 또는 CLR UDT 형식의 큰 개체. 페이지는 텍스트 또는 이미지 유형

ROW_OVERFLOW_DATA

8060바이트 행 크기 제한을 초과한 varchar, nvarchar, varbinary 또는 sql_variant 열에 저장된 가변 길이 데이터. 페이지는 텍스트 또는 이미지 유형.

 

 

[파티션 및 할당 단위 예제]

다음 스크립트를 실행 하면 DatabaseLog 테이블과 Currency 테이블의 파티션 할당 단위를 확인 할 수 있다.

USE AdventureWorks2008R2;

GO

SELECT o.name AS table_name,p.index_id, i.name AS index_name , au.type_desc AS allocation_type, au.data_pages, partition_number

FROM sys.allocation_units AS au

JOIN sys.partitions AS p ON au.container_id = p.partition_id

JOIN sys.objects AS o ON p.object_id = o.object_id

JOIN sys.indexes AS i ON p.index_id = i.index_id AND i.object_id = p.object_id

WHERE o.name = N'DatabaseLog' OR o.name = N'Currency'

ORDER BY o.name, p.index_id;

 

DatabaseLog 테이블에는 텍스트/이미지 페이지 유형이 포함되어있기 때문에 세가지 할당 유형이 모두 사용 된다. Currency 테이블에는 LOB 데이터가 없지만 데이터 페이지 관리에 필요한 할당 단위가 있다. Currency 테이블이 나중에 LOB 데이터 형식 열을 포함하도록 수정되면 해당 데이터를 관리 하기 위해 LOB_DATA 할당 단위가 생성 된다.

 

 

[참고자료]

http://msdn.microsoft.com/ko-kr/library/ms189051(v=sql.105).aspx

http://sqlmvp.kr/140186842239

 



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

No. Subject Author Date Views
1770 SQL Server 쿼리 처리 아키텍처 _ SQL 문 처리 (3/4) – 뷰(View)의 인덱스 확인 jevida(강성욱) 2016.09.27 973
1769 SQL Server 쿼리 처리 아키텍처 _ SQL 문 처리 (2/4) – 뷰(View) 확인 jevida(강성욱) 2016.09.27 1447
1768 SQL Server 쿼리 처리 아키텍처 _ SQL 문 처리 (1/4) – SQL 문 최적화 및 Worktables jevida(강성욱) 2016.09.27 933
1767 SQL Server DMV를 이용한 통계 정보 확인 jevida(강성욱) 2016.09.27 1471
1766 DMV를 이용한 플랜 캐시 사용 정보 확인 jevida(강성욱) 2016.09.27 1172
1765 SQL Server 테이블 및 인덱스 구조 아키텍처(4/4) – 비클러스터형 인덱스 구조 jevida(강성욱) 2016.09.27 1065
1764 SQL Server 테이블 및 인덱스 구조 아키텍처(3/4) – 클러스터형 인덱스 구조 jevida(강성욱) 2016.09.27 1367
1763 SQL Server 테이블 및 인덱스 구조 아키텍처(2/4) – 힙 구조 jevida(강성욱) 2016.09.27 1068
» SQL Server 테이블 및 인덱스 구조 아키텍처(1/4) – 테이블 및 인덱스 구성 jevida(강성욱) 2016.09.27 1125
1761 SQL Server 트랜잭션 로그 아키텍처(4/4) – 미리 쓰기 트랜잭션 로그 jevida(강성욱) 2016.09.27 1537
1760 SQL Server 트랜잭션 로그 아키텍처(3/4) – 검사점 및 로그의 활성 부분 jevida(강성욱) 2016.09.27 1048
1759 SQL Server 트랜잭션 로그 아키텍처(2/4) – 트랜잭션 로그 물리 아키텍처 jevida(강성욱) 2016.09.27 1094
1758 SQL Server 트랜잭션 로그 아키텍처(1/4) – 트랜잭션 로그 논리 아키텍처 jevida(강성욱) 2016.09.27 1249
1757 파일 및 파일 그룹 아키텍처 jevida(강성욱) 2016.09.27 798
1756 SQL Server 페이지 및 익스텐트 아키텍처(4/4) – 수정된 익스텐트 추적 jevida(강성욱) 2016.09.27 1128
1755 SQL Server 페이지 및 익스텐트 아키텍처(3/4) – 개체에서 사용하는 공간 관리 jevida(강성욱) 2016.09.27 972
1754 SQL Server 페이지 및 익스텐트 아키텍처(2/4) – 익스텐트 할당 및 빈공간 관리 jevida(강성욱) 2016.09.27 1365
1753 SQL Server 페이지 및 익스텐트 아키텍처(1/4) – 페이지 및 익스텐트 이해 jevida(강성욱) 2016.09.27 3172
1752 SQL Server Error Log 보관 주기 설정 jevida(강성욱) 2016.09.15 2177
1751 SQL Server 네트워크 백업 트러블슈팅(UNC 설정) jevida(강성욱) 2016.09.15 5124





XE Login