데이터와 인덱스 아키텍쳐

1. 시스템 구조

SQL 서버의 시스템 아키텍쳐

1. 논리적 데이터베이스

데이터베이스의 테이블, 뷰, 저장 프로시저 등

2. 물리적인 데이터베이스

데이터베이스의 파일

1. 주 데이터 파일 (Primary Data Files, MDF)

기본적인 데이터 구조 및 데이터 들이 저장

2. 보조 데이터 파일 (Secondary Data Files, NDF)

여러개의 디바이스 파일을 운영할 때 보조 데이터 파일

목적 : 데이터 I/O를 병렬적으로 수행하여 I/O 병목현상 줄이고, 수행 속도 증가

3. 로그 파일 (Log Files, LDF)

데이터베이스를 복구하는데 필요한 정보를 저장

각데이터베이스마다 반드시 있어야 한다.

 

TIP : 주 데이터 파일의 경로 알아내기

(Master 데이터베이스의 Sysdatabases 검색)

Select * from SYS.DATABASES WHERE name='데이터베이스이름'

 

파일을 백업 복사하여 다시 첨부 하는 것만으로 그 당시까지의 데이터와 로그를 다시 불러올 수 있다.

이러한 작업에 사용되는 프로시저 = sp_attach_db , sp_attach_single_file_db

 

2. 데이터 파일 구조

1. 페이지(Page)

데이터를 저장하고 처리하는데 사용되는 가장 기본적인 입출력 단위

페이지 헤더(96바이트)(페이지의 종류, 페이지의 여유공간정보, 페이지가 저장하는 데이터의 오브젝트ID) + 8096바이트 로 구성

- 데이터 파일 앞부분의 특수 페이지들

파일헤더 | PFS | GAM | SGAM | BCM | DCM | ...

* PFS (Page Free Space) - http://blog.naver.com/jumdol625/110169523098

- 페이지의 할당 여부 및 각 페이지에 있는 빈 공간의 크기를 기록하는 페이지

- 각 페이지에 1바이트를 사용하여 페이지의 할당 여부와, 페이지의 남은 공간을 기록

- 힙, 텍스트, 이미지 페이지에 대해서만 페이지 내의 빈 공간의 양이 관리 

- 새로 삽입된 행을 보관할 수 있는 빈 공간이 있는 페이지를 찾아야 할때 사용 

- GAM, SGAM, PFS를 이용해서 사용할 페이지 검색 

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

- 8088개의 페이지 관리, 8088개가 넘어가면 PFS페이지를 추가

* GAM (전역 할당 맵) : 어떤 익스텐트가 할당 되었는가

* SGAM (공유 전역 할당 맵) : 어떤 익스텐트가 혼합익스텐트인지, 빈페이지가 있는지, 4GB 관리.

http://blog.naver.com/jumdol625/110168581449 - GAM, SGAM 자세히 나와있는 곳

* BCM (Bulked Change Map)(대량 복사 변경 맵) : Backup Log 문 이후 대량 기록작업에 의해 수정된 익스텐트를 관리

* DCM (Differential Change Map)(차등 변경 맵) : 마지막 BACKUP DATABASE 문(FULL BACKUP) 이후에 변경된 익스텐트를 관리하는 페이지, 차등 백업의 대상 익스텐트를 찾기 위해 사용. 4GB 관리.

 

2. 익스텐트(Extent) : 8개의 연속된 페이지가 모여 이룬 단위

1. 혼합 익스텐트

8개의 페이지에 각각 다른 오브젝트의 데이터를 저장할 수 있다.

2. 단일 익스텐트

8개의 페이지에 모두 같은 오브젝트를 저장할 수 있다.

 

테이블이나 인덱스가

8 Page보다 커지기 전까지는 Mixed로 저장된다.(다른 개체에서도 추가되는 작업이 있다면 위의 그럼처럼 떨어져서....)

8 Page보다 커지는 순간

9번째 Page가 저장될때는 Uniform으로 8 Page의 공간이 할당되고 그 Extent에는 해당 객체만 들어갈수 있다!

 

처음부터 Uniform으로 8 KB씩 할당하지 않는 이유 : 데이터베이스가 많은 작은 테이블을 보유하고 있을때 

공간들이 낭비되지 않게하기 위해서이다. (공간을 효율적으로 사용!)

 

3. 로그 파일 구조

- 데이터 파일처럼 익스텐트로 구성 X, 가상 로그 파일(Virtual Log File, VLF)이라는 저장 단위로 구성.

- VLF는 트랜잭션 로그의 잘라내기 단위가 된다.

- 로그 파일은 Circular queue 방식으로 순차적으로 저장이 되기 때문에 여러개의 파일로 분할하여도 성능향상은 기대할 수 없다. Circular queue 방식의 경우 데이터가 최근 저장된 순서로 다시 인출될 수 있으므로 트랜잭션의 개념에 가장 잘 부합된다.

- Transaction Log : UPDATE/DELETE/INSERT 등과 같은 작업 시에 기록되는 로그, 임의의 시점에 Checkpoint가 발생하면 Physical database에 기록

- 가상 로그 파일의 성능 : 파일이 작고 많을 경우 성능을 저하시킨다.

- 가상 로그 파일 변경 방법

1. BACKUP LOG 문을 사용하여 로그 백업을 수행.

2. CHECKPOINT 문을 사용하여 메모리와 디스크를 동기화.

  DBCC SHRINKFILE( logfilename, TRUNCATEONLY ) 를 사용하여 트랜잭션 로그 파일 축소.

  Sp_helpdb 데이터베이스명  으로 logfilename을 알 수 있다.

3. 데이터베이스 변경을 하여 로그 파일 크기를 수정(충분히 크게 늘려서 가상로그 파일의 크기가 크고, 개수가 적도록 한다. 증가값 역시 충분히 크게 잡는다.) 구문은 다음과 같다.

ALTER DATABASE dbname

 MODIFY FILE ( NAME = file_name, SIZE = new_size, FILEGROWTH = XXMB )

 

 

 

4. 데이터 페이지의 구조

text, ntext, image의 데이터 형태를 제외한 모든 데이터 로우들이 저장된다.

페이지 헤더에는 전페이지와 후페이지의 관계에 대한 포인터를 가지고 있다.

Offset은 각 Row가 시작되는 위치(시작되는 바이트가 페이지의 시작에서 얼마나 떨어져 있는지)를 알려준다.

 

5. 데이터 페이지의 동작 원리 

1. 클러스터드 인덱스 테이블(Clustered Index Table)

데이터 페이지들이 순서대로 정렬이 되어 저장되어 있는 상태. 따라서 각 테이블에 클러스터드 인덱스는 1개만 존재가능.

 

2. 넌 클러스터드 인덱스 테이블(Non-Clustered Index Table)

 

3. 데이터페이지의 관리

클러스터드나 넌클러스터드 인덱스 모두 B-Tree(Balanced Tree)구조를 가진다.

인덱스 된 뷰(Indexed View)는 클러스터드 테이블과 동일한 저장구조를 가진다.

 

* 모든 테이블, 인덱스, 인덱스 된 뷰 는 SYSINDEXES 테이블에 페이지 포인터(Page Pointer)를 가지게 된다. SYSINDEXES 테입블은 각 인덱스에 존재. SYSINDEXES 테이블에는 각 객체들에 대한 ID와 인덱스에 대한 ID인 INDID가 존재

이를 조회하는 쿼리

SELECT ID, INDID, FirstIAM FROM SYSINDEXES

이를 조회했을 때

1. INDID = 0  : 클러스터드 인덱스가 아닐 때. 순서 없이 저장된 테이블 = 이를 힙(Heap)이라고 한다. 이러한 힙 형태의 정보를 찾기 위해서는 SYSINDEXES.FirstIAM을 통해 데이터를 검색한다.

- 데이터 페이지 검색 방법 : SYSINDEXES의 FirstIAM 컬럼의 주소 값을 기준으로 첫 번째 IAM 페이지를 찾아간다. 이 IAM 페이지를 기준으로 익스텐트를 찾고 IAM 페이지에 있는 익스텐트 들을 모두 스캔했으면 다음 IAM페이지로 가서 또 스캔한다.

2. INDID = 1 : 클러스터드 인덱스. SYSINDEXES.Root 컬럼에는 클러스터드 인덱스의 루트 인덱스 위치 값이 존재

- 데이터 페이지 검색 방법 : 데이터가 정렬되어 있으므로 B-Tree를 검색하면 원하는 데이터 페이지 바로 검색 가능

3. INDID = 2~250 : 넌클러스터드 인덱스. SYSINDEXES.Root 컬럼에 넌클러스터드 인덱스의 루트 인덱스 위치 값이 존재

- 데이터 페이지 검색 방법 : SYSINDEXES의 개체 아이디가 일치하는 것을 검색한 후에 Root 컬럼 값을 찾아내면 해당 넌클러스터드 인덱스의 루트 노드를 가리키는 주소 값을 찾을 수 있다. 동일하게 B-Tree를 검색하면 원하는 데이터 페이지의 주소를 검색할 수 있다.

4. INDID = 255 : 텍스트나 이미지 형태. 각 테이블당 하나의 텍스트 및 이미지 컬럼을 가질 수 있다.

 

4. IAM (Index Allocation Map Page)

힙이나 인덱스 된 형태, 텍스트와 이미지 등 모든 개체의 익스텐트들을 관리

IAM 페이지에는 익스텐트의 시작 지점을 가리키는 헤더 정보를 갖고있다.

개체가 커져서 하나의 IAM으로 관리할 수 없을 떄는 연결 리스트 구조로 새로운 IAM이 추가된다.

IAM 때문에 한 데이터베이스에 파일이 여러개 일 때 DBCC SHOWCONTIG를 하면 약간의 왜곡이 생긴다.

 

* SQL 서버가 새로운 데이터를 데이터 페이지에 할당할 때

- 먼저 IAM페이지를 통해 개체에 할당된 익스텐트를 참조하여 익스텐트가 비어있지 않다면, PFS 페이지 정보를 검색하여 새로운 데이터를 저장. 이때 IAM이나 PFS 페이지 모두 메모리 버퍼 풀(Memory Buffer Pool)에 상주하여 매우 빠른 속도로 검색된다) 그리고 이러한 익스텐트에 대한 검색은 데이터를 삽입할 때 충분한 공간이 존재하지 않았을 때만 발생.