SQL Server 페이지 및 익스텐트 아키텍처(2/4)

– 익스텐트 할당 및 빈공간 관리

 

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

 

 

앞에서 SQL Server의 기본 저장공간 단위는 페이지(8KB)이며 8개의 페이지가 모여 익스텐트로 불리며 관리 된다고 설명 하였다.

페이지 및 익스텐트 이해 : http://sqlmvp.kr/140186753568

 

이번 시간에는 익스텐트 할당 미 및 빈공간에 대해서 어떻게 관리하는지 알아 보자. 익스텐트 할당을 SQL Server의 구조는 비교적 간단하다. 이 데이터 구조를 사용하면 다음과 같은 이점이 있다.

  • 빈 공간 정보는 빽빽하게 묶여 있으므로 이 정보를 포함하는 페이지의 수는 비교적 적다. 따라서 할당 정보 검색에 필요한 디스크의 IO 비용이 낮아진다
  • 할당 페이지가 메모리에 남아 있을 확률이 높다.
  • 대부분의 할당 정보는 연결되어 있지 않음으로 유지 관리가 간편하다
  • 페이지 할당 또는 취소 작업시 동시 태스크간의 경합이 줄어 든다.

 

[익스텐트 관리]

  • 전역 할당 맵(GAM : Global Allocation Map)
    • GAM은 어떤 익스텐트가 할당되었는지 기록한다.
    • 각 GAM은 64,000개의 익스텐트 또는 4GB의 데이터를 처리 한다.
    • GAM은 처리 간격으로 각 익스텐트에 대한 비트를 갖고 있다.

 

  • 공유 전역 할당 맵(Shares Global Allocation Map)
    • 어떤 익스텐트가 현재 혼합 익스텐트로 사용되고 있는지 기록
    • 사용되지 않은 페이지를 가지는지 기록
    • 각 SGAM은 64,000개의 익스텐트 또는 4GB의 데이터를 처리
    • SGAM은 처리 간격으로 익스텐트에 대한 비트를 갖고 있다.

 

[GAM 및 SGAM에 설정된 비트 패턴]

현재 익스텐트의 사용

GAM 비트 설정

SGAM 비트 설정

비어 있음, 사용 중이지 않음

1

0

단일 익스텐트 또는 완전 혼합 익스 텐트

0

0

빈 페이지가 있는 혼합 익스텐트

0

1

 

  • 익스텐트 할당 : GAM 비트가 1인 것 검색 -> GAM 비트를 0으로 설정 -> SGAM 비트가 1인 것 검색 -> SGAM 비트를 1로 설정
  • 익스텐트 할당 취소 : GAM 비트 1로 설정 -> SGAM 비트를 0으로 설정

 

데이터베이스 엔진은 데이터베이스 내에서 균일하게 데이터를 분산 시키므로 실제로 데이터베이스 내부에서 사용하는 알고리즘은 훨씬 복잡하다.

 

 

[빈 공간 추적]

PFS(Page Free Space)페이지는 개별 페이지의 할당 여부 및 각 페이지에 있는 빈 공간의 양과 같은 페이지의 할당 상태를 기록.

PFS는 각 페이지에 1바이트를 사용하여 페이지의 할당 여부를 기록, 비어있음, 1~50% 채워짐, 51~80% 채워짐, 81~95% 채워짐, 96~100% 채워짐을 기록 한다.

개체에 익스텐트가 할당된 후에는 데이터베이스 엔진에서 PFS 페이지를 사용하여 익스텐트의 할당된 페이지 또는 빈 페이지를 기록한다. 이 정보는 데이터베이스 엔진에서 새 페이지를 할당 해야 할 때 사용 된다.

힙 및 텍스트/이미지 페이지에 대해서만 페이지 내의 빈 공간의 양이 관리 된다.

PFS 페이지는 데이터베이스 엔진에서 새로 삽입된 행을 보관할 수 있는 빈 공간이 있는 페이지를 찾을 때 사용한다.

 

인덱스의 경우 새 행을 삽입할 지점이 인덱스 키 값에 의해 설정되므로 페이지의 빈 공간을 추적할 필요가 없다

 

아래 그림은 데이터베이스 엔진에서 사용하는 페이지 순서이다.

PFS는 파일 헤더 다음에 위치하며 그 다음에 GAM와 SGAM이 위치한다. 처음 PFS페이지 다음에 각 8000페이지의 PFS가 있다. 2페이지 첫 번째 GAM 페이지 다음에 64,000의 익스텐트가 있는 GAM페이지가 있으며 3페이지의 첫 번째 SGAM페이지 다음에 64,000개의 익스텐트가 있는 다른 SGAM 페이지가 있다.

 

 

익스텐트 할당에 대해서 실습을 통해서 알아 보자.

 

다음과 같이 DBCC 명령을 통해서 GAM의 정보를 확인 할 수 있다.

익스텐트 페이지의 할당 번호는 0 ~ 25352이다. 익스텐트 페이지는 0 ~ 25359까지 할당 되었다. 두 번째 줄의 25360 ~ 25416 까지는 할당 되지 않은 정보 이다. 할당 되지 않은 익스텐트 페이지는 25360 ~ 25479 이다.

 

DBCC TRACEON(3604)

GO

DBCC page('adventureworks2008R2',1,2,3)

 

 

 

페이지의 헤더 정보 이후 GAM 페이지가 있다고 위에서 설명 하였다. 할당 정보에 대한 페이지 정보를 보면 할당 정보에 속해 있다. 페이지 25424 경우는 같은 GAM 페이지에 있지만 상태가 할당 되어 있지 않은 것으로 나타난다.

DBCC TRACEON(3604)

GO

 

DBCC page('adventureworks2008R2',1,25352,1)

 

 

 

SGAM 정보를 확인해 보면 시작 번호는 0 ~ 11752이며 할당 되어 있지 않다. 할당 되어 있지 않은 익스텐트는 단일 익스텐트 또는 혼합 익스텐트의 프리 페이지를 가지고 있다.

두 번째 줄의 시작번호 11760은 적어도 하나의 혼합 익스텐트 프리 페이지를 가지고 있다.

DBCC TRACEON(3604)

GO

 

DBCC page('adventureworks2008R2',1,3,3)

 

 

 

[참고 자료]

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

http://www.sqlservercentral.com/blogs/practicalsqldba/2012/09/12/sql-server-understanding-gam-and-sgam-pages/




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

No. Subject Author Date Views
Notice SQL강좌: 챗GPT와 함께 배우는 SQL Server 무료 강좌 목차와 소개 (2023년 9월 업데이트) 코난(김대우) 2023.08.18 28296
Notice Python 무료 강좌 - 기초, 중급, 머신러닝(2023년 6월 업데이트) 코난(김대우) 2021.01.01 15463
» SQL Server 페이지 및 익스텐트 아키텍처(2/4) – 익스텐트 할당 및 빈공간 관리 jevida(강성욱) 2016.09.27 1626
1753 SQL Server 페이지 및 익스텐트 아키텍처(1/4) – 페이지 및 익스텐트 이해 jevida(강성욱) 2016.09.27 4012
1752 SQL Server Error Log 보관 주기 설정 jevida(강성욱) 2016.09.15 2310
1751 SQL Server 네트워크 백업 트러블슈팅(UNC 설정) jevida(강성욱) 2016.09.15 5443
1750 SQL Server 인증 실패시 반환되는 클라이언트 메시지 정보 jevida(강성욱) 2016.09.15 4255
1749 SQL Server에 할당된 메모리 개체 확인 jevida(강성욱) 2016.09.15 1632
1748 SQL Server 비동기 업데이트 활성 / 비활성에 따른 특성 jevida(강성욱) 2016.09.15 2019
1747 DBCC CHECKDB와 Compute Column 인덱스의 성능 관계 jevida(강성욱) 2016.09.15 1514
1746 Collation에 따른 DMV 실행 오류 jevida(강성욱) 2016.09.15 1354
1745 참조 개체 확인 (sys.sql_expression_dependencies) jevida(강성욱) 2016.09.15 1694
1744 특정 테이블의 마지막 접근 시간 알아보기 jevida(강성욱) 2016.09.15 1284
1743 SQL Server Fill Factor (채우기 비율)에 관한 오해와 진실 jevida(강성욱) 2016.09.15 4865
1742 LOB 데이터와 Shrink 작업 jevida(강성욱) 2016.09.15 1288
1741 데이터베이스 함수 검색 하기 jevida(강성욱) 2016.09.15 1108
1740 필터 통계 사용과 파리미터 사용 jevida(강성욱) 2016.09.15 1234
1739 유지관리 계획과 병렬처리 – Index Rebuild jevida(강성욱) 2016.09.15 1119
1738 유지관리 계획과 병렬 처리 – CHECKDB jevida(강성욱) 2016.09.15 1195
1737 쿼리 사이즈(길이) 에 따른 CPU 사용량 증가 jevida(강성욱) 2016.09.14 1351
1736 Ad-hoc 쿼리를 매개변수화 하여 성능 높이기 jevida(강성욱) 2016.09.14 1736
1735 통계 업데이트 옵션(ROWCOUNT and PAGECOUNT) jevida(강성욱) 2016.09.14 1291





XE Login