그림으로 이해하는
SQL서버의 구조

SQL 개발과 운용 업무의 효율화, 트러블슈팅에 도움!

스테디셀러 『그림으로 이해하는 SQL 서버의 내부 구조』(2009년 3월 2일 출간)를 오랜만에 전면 개정한 책이다. 전작을 토대로 현재의 SQL 서버 아키텍처를 따라 배우고자 설명과 그림 전체를 전면 수정했을 뿐 아니라 칼럼스토어/인메모리형 오브젝트, Paas/Iaas 각종 클라우드 데이터 플랫폼의 해설 등을 추가하여 물리적 구조와 내부 동작으로 이해함으로써 SQL서버의 기술적 특성을 충분히 활용할 수 있게 되어 현장에서의 데이터베이스 개발 및 운용업무의 효율화나 트러블슈팅에 도움이 되도록 했다. 저자의 SQL서버 지원 업무 경험도 그대로 살려 집필되어 SQL서버의 실천적 팁이나 노하우집으로도 활용할 수 있다.

본문은 크게 5가지 테마, 즉 ‘외부 컴포넌트의 이해’, ‘다양한 데이터의 구조’, ‘내부 컴포넌트의 이해’, ‘운용의 안정화’, ‘향후의 전망’으로 구분되어 있다. 책에서는 저자가 서문에서 밝힌 대로 SQL 서버의 편리한 사용법(가령 바로 사용할 수 있는 쿼리 샘플 등)은 소개하지 않았다. 대신 SQL 서버의 기본 아키텍처를 체계적으로 소개하고 각종 내부 기능의 특성과 동작을 하나하나 알기 쉽게 소개하는 데 더 집중했다. 트러블의 본질을 이해할 수 있으면 눈에 보이는 증상에 휘둘리지 않고 정확한 대처를 강구할 수 있다는 저자의 의도를 잘 읽을 수 있다.

들어가며
이 책에 대해

【제1장】 CPU 리소스의 최적화

1.1 멀티 스레드 프로그래밍
1.1.1 문맥 교환

1.2 윈도우 스케줄러와 SQL 서버
1.2.1 윈도 스케줄러가 인식할 수 없는 대기 상태

1.3 SQL 서버 내의 스케줄러
1.3.1 SQLOS 스케줄러란?
1.3.2 SQLOS 스케줄러를 구성하는 컴포넌트
- 스케줄러
- 작업자
- 작업자 스레드 풀
- 러너블 큐(Runnable Queue)
- 워크 리퀘스트 큐(Work Request Queue)
- I/O 리퀘스트 리스트(I/O Request List)
- 웨이터 리스트(Waiter List)
1.3.3 스케줄러의 동작
1.3.4 SQLOS 스케줄러가 사용하는 기술

1.4 SQLOS 스케줄러를 모니터링한다
1.4.1 동적 관리 뷰의 참조
- runnable_tasks_count
- current_workers_count
- work_queue_count

1.5 제1장 정리

【제2장】디스크 I/O 조작의 이해

2.1 SQL 서버가 관리하는 데이터베이스의 실체
2.1.1 데이터 파일(확장자 .mdf, .ndf)
2.1.2 트랜잭션 로그 파일(확장자 .ldf)

2.2 데이터베이스 파일 액세스 패턴
2.2.1 데이터 파일
- 온라인 트랜잭션 처리(OLTP) 시스템의 경우
- 데이터웨어하우스(DWH)의 경우
2.2.2 트랜잭션 로그 파일

2.3 SQL 서버가 수행하는 I/O의 특징적 동작
2.3.1 미리 읽기(Read-Ahead)
2.3.2 체크포인트(checkpoint)
2.3.3 집중 기록(Eager Write)
2.3.4 지연 기록(Lazy Write)

2.4 SQL 서버가 사용하는 I/O용 API
2.4.1 데이터 파일 및 트랜잭션 로그 파일의 오픈
- CreaterFile 함수

2.4.2 데이터 파일 및 트랜잭션 로그 파일에서 읽어들이기
- ReadFileScatter 함수
- ReadFile 함수
2.4.3 데이터 파일 및 트랜잭션 로그 파일에 기록
- WriteFileGather 함수
- WriteFile 함수

2.5 디스크 구성
2.5.1 RAID 0
2.5.2 RAID 1
2.5.3 RAID 5
2.5.4 RAID 10

2.6 모니터링
2.6.1 퍼포먼스 모니터
- Avg.Disk sec/Read 카운터
- Avg.Disk sec/Write 카운터
2.6.2 동적 관리 뷰의 참조

2.7 제2장 정리

【제3장】메모리 관리

3.1 SQL 서버와 가상 어드레스 공간

3.2 SQL 서버와 가상 어드레스 공간의 관리
3.2.1 가상 어드레스와 공간의 관리
- Committed
- Reserved
- Free
3.2.2 VirtualAlloc 함수
3.2.3 페이징

3.3 물리 메모리 사이즈와 SQL 서버의 메모리 사용량

3.4 NUMA
3.4.1 SMP 아키텍처의 메모리와 버스
- 레이지 라이터 스레드
- I/O 완료 포트 스레드
3.4.2 소프트 NUMA

3.5 SQL 서버 프로세스 내부의 메모리 관리 방법
3.5.1 메모리 매니저
3.5.2 워크스페이스의 내부 구조

3.6 메모리의 용도
3.6.1 버퍼 풀(버퍼 캐시)
3.6.2 플랜 캐시
3.6.3 쿼리 워크스페이스
3.6.4 최적화
3.6.5 글로벌
3.6.6 레이지 라이터 스레드
3.6.7 체크포인트 프로세스

3.7 모니터링
3.7.1 DBCC 커맨드
- Memory Manager 섹션
- Memory node ld=X 섹션
- ME MORYCLERK_XXXX, CACHESTORE_XXX, USERSTORE_XXX,
- OBJECTSTORE_XXX 섹션
- Procedure Cache 섹션
3.7.2 퍼포먼스 모니터
- 시스템 전체의 메모리 상황 확인
- SQL 서버 내부의 메모리 사용 상황 확인
3.7.3 동적 관리 뷰

3.8 제3장 정리

【제4장】데이터베이스 구조의 원리

4.1 데이터 파일
4.1.1 페이지와 익스텐트
4.1.2 단일 익스텐트와 혼합 익스텐트
4.1.3 페이지의 종류
4.1.4 페이지의 배치
- GAM(Global Allocation Map)
- SGAM(Shared Global Allocation Map)
- PFS(Page Free Space)
- IAM(Index Allocation Map)

4.2 트랜잭션 로그 파일
4.2.1 가상 로그 파일

4.3 데이터베이스 파일 내의 액세스 수법

4.4 데이터베이스 파일의 관리
4.4.1 원인 불명의 데이터베이스 확장 실패
4.4.2 SQL 서버 2005 이후 개선된 사항
- 제로잉의 회피
- 디폴트 설정의 재검토
- 이상적인 설정

4.5 데이터의 효율적인 저장 방법
4.5.1 NTFS 파일 압축의 사용
4.5.2 vardecimal형
4.5.3 데이터 압축
4.5.4 백업 압축

4.6 제4장 정리

【제5장】로우스토어(Rowstore)형 테이블

5.1 테이블과 오브젝트 ID

5.2 페이지의 분류

5.3 인덱스 페이지
5.3.1 클러스터화 인덱스와 비클러스터화 인덱스의 차이
5.3.2 인덱스 페이지의 내부 구조
- 헤더 정보
- 인덱스 키 행
- 행 오프셋 배열

5.4 데이터 페이지
5.4.1 데이터 페이지의 내부 구조
- 헤더 정보
- 데이터 행
- 행 오프셋 배열

5.5 DBCC PAGE에 의한 페이지 상세 정보의 확인
5.5.1 준비
5.5.2 테이블에 사용되는 페이지의 확인
5.5.3 페이지의 확인
a. 출력 옵션 : ‘-1’ 또는 ‘0’
b. 출력 옵션 : ‘1’
c. 출력 옵션 : ‘2’
d. 출력 옵션 : ‘3’

5.6 제5장 정리

【제6장】칼럼스토어(Columnstore)형 오브젝트(칼럼스터어 인덱스)

6.1 칼럼스토어 인덱스 도입 배경

6.2 아키텍처/구조
6.2.1 행 그룹
6.2.2 열 세그먼트

6.3 칼럼스토어 구조 데이터 압축의 이점
6.3.1 ① 데이터 분할
6.3.2 ② 인코드
- 딕셔너리 인코딩(Dictionary Encoding)
- 밸류 인코드(Value Encoding)
6.3.3 ③ 데이터 압축
- Bit Packing
- Run Length Encoding

6.4 칼럼스토어 인덱스의 종류

6.5 칼럼스토어 인덱스의 적용 부분
6.5.1 로우스토어 인덱스와 칼럼스토어 인덱스의 조합
① 클러스터화 칼럼스토어 인덱스+비클러스터화 인덱스
② 클러스터화 인덱스+비클러스터화 칼럼스토어 인덱스(+비클러스터화 인덱스)
6.5.2 어느 쪽 조합을 선택하면 좋을까
- 베이스가 되는 데이터 부분의 사이즈
- 칼럼스토어 인덱스의 메인타넌스
- 퍼포먼스

6.6 배치 모드

6.7 칼럼스토어 인덱스의 갱신
6.7.1 INSERT 동작
6.7.2 DELETE 동작
6.7.3 UPDATE 동작

6.8 칼럼스토어 인덱스의 메인터넌스
6.8.1 델타 스토어
6.8.2 삭제 비트맵

6.9 SQL 서버의 인덱스 디자인 모범 사례
6.9.1 정보 시스템의 기능
- 온라인 트랜잭션 처리(OLTP)
- 분석/리포팅 처리
6.9.2 HTAP(Hybrid Transaction/Analytical Processing)

6.10 제6장 정리

【제7장】메모리 최적화 오브젝트(인메모리 OLTP)

7.1 인메모리 OLTP의 개요
7.1.1 인메모리 OLTP의 특징
- 항상 모든 데이터는 메모리상에 존재한다
- 인덱스는 메모리상에만 존재한다
- 락/래치를 사용하지 않는다
- 컴파일된 모듈의 사용
-7.1.2 인메모리 OLTP의 용도
- IoT
- Temp Table
- ETL의 중간 테이블

7.2 인메모리 OLTP를 구성하는 컴포넌트
7.2.1 메모리 최적화 테이블
7.2.2 인덱스
- 해시 인덱스
- 인메모리 비클러스터화 인덱스
7.2.3 네이티브 컴파일 스토어드 프로시저

7.3 인메모리 OLTP의 데이터 관리
7.3.1 데이터의 지속성
- SCHEMA_AND_DATA
- SCHEMA_ONLY
7.3.2 메모리 최적화 테이블의 데이터 구조
7.3.3 데이터의 라이프사이클
- INSERT 동작
- SELECT 동작
- DELETE 동작
- UPDATE 동작
7.3.4 가비지 컬렉션
- 가비지 컬렉션 메인 스레드의 동작
- 메모리의 해제
7.3.5 디스크에 보존하기 위한 오브젝트
- 메모리 최적화 파일 그룹
- 데이터 파일
- 델타 파일
- 체크포인트 파일 페어
7.3.6 디스크 보존 시의 동작
- 메모리 최적화 테이블의 체크포인트
- 자동 체크포인트
- 수동 체크포인트
- 체크포인트 파일 페어의 병합(merge)
7.3.7 사이징
- 데이터 사이즈

7.4 인메모리 OLTP 사용 시의 유의사항
7.4.1 가장 먼저 채용해야 할 인덱스
7.4.2 메모리 최적화 테이블에서 지원되지 않는 기능
7.4.3 네이티브 컴파일 스토어드 프로시저 사용 시의 유의사항

7.5 제7장 정리

【제8장】릴레이셔널 엔진의 동작

8.1 릴레이셔널 엔진의 구성

8.2 쿼리의 라이프사이클
① 플랜 캐시의 확인
② 파싱(Parsing)
③ 쿼리의 파라미터화
④ 바인드
⑤ 최적화
⑥ 쿼리 실행 플랜 생성(또는 재이용)
⑦ 리소스 확보
⑧ 실행
⑨ 쿼리 실행 플랜의 등록

8.3 쿼리 옵티마이저
① Simplication
② Auto-Create Stats
③ Derive Cardinality
④ Heuristic Join Reorder
⑤ Project Normalization
⑥ Trivial Plan
⑦ Exploration
⑧ 쿼리 트리 변환

8.4 쿼리 옵티마이저와 쿼리 실행 플랜

8.5 통계 정보
8.5.1 히스토그램(histogram)
8.5.2 밀도(density)
- 1/열이 보관하는 하나의 행 수
8.5.3 기타

8.6 통계 정보가 작성되는 계기
8.6.1 명시적으로 작성
8.6.2 자동 작성 프로퍼티 설정에 의한 작성
8.6.3 인덱스 작성 시에 작성

8.7 통계 정보의 확인
8.7.1 헤더 정보
8.7.2 밀도 정보
8.7.3 히스토그램

8.8 제8장 정리

【제9장】네트워크

9.1 클라이언트와 통신할 때 필요한 작업
9.1.1 오퍼레이팅 시스템과 네트워크
- SQL 서버 컴포넌트

9.2 SQL 서버와 클라이언트의 통신
9.2.1 TDS(표 형식 데이터 스트림)
9.2.2 클라이언트로부터 송신되는 메시지
- 로그인
- SQL 커맨드
- 바이너리 데이터를 포함한 SQL 커맨드
- 리모트 프로시저 콜
- 어텐션
- 트랜잭션 매니저 리퀘스트
9.2.3 SQL 서버에서 송신되는 메시지
- 로그인 리스폰스
- 행 데이터
- 반환값 스테이터스
- 리턴 파라미터
- 리퀘스트의 종료
- 에러 메시지와 정보 메시지
- 어텐션

9.3 SQL 서버와 클라이언트의 데이터 수수

9.4 토큰 없는 데이터 스트림과 토큰 있는 데이터 스트림
9.4.1 토큰 없는 데이터 스트림
9.4.2 토큰 있는 데이터 스트림

9.5 클라이언트가 접속 시에 사용하는 프로토콜의 관리

9.6 접속지 정보의 캐시
9.6.1 디폴트 인스턴스에 접속
9.6.2 네임드 인스턴스에 접속

9.7 제9장 정리

【제10장】데이터베이스 백업과 복원

10.1 백업 파일의 출력 형식

10.2 백업 파일의 내용
- 미디어 헤더
- 데이터세트 개시 블록
- SQL 서버 설정 정보
- SQL 서버 데이터 스트림
- SQL 서버 로그 스트림
- 데이터세트 종료 블록
- 미디어 종료

10.3 백업의 종류
10.3.1 완전 백업
10.3.2 차등 백업
10.3.3 파일 백업
10.3.4 파일 차등 백업
10.3.5 트랜잭션 로그 백업

10.4 백업 처리의 흐름

10.5 백업 미디어의 훼손
10.5.1 데이터베이스 자체가 훼손됐다
10.5.2 백업 파일 생성 시에 오류가 발생했다

10.6 백업 파일의 압축

10.7 BACKUP/RESTORE 이외의 백업
- 이점
- 주의사항

10.8 제10장 정리

【제11장】트러블슈팅

11.1 퍼포먼스 문제 해결의 어려움
① 정보 수집에 시간이 걸린다
② 퍼포먼스의 비교가 어렵다
③ 정보의 해석이 어렵다

11.2 쿼리 저장소
11.2.1 실현할 수 있는 것
- 정보 수집의 자동화
- 퍼포먼스의 비교
- 원활한 쿼리 해석과 문제점 발견
- 쿼리의 실행 플랜 강제
11.2.2 쿼리 저장소의 구조
- 쿼리 텍스트
- 쿼리 실행 플랜
- 쿼리 실행 통계
11.2.3 쿼리 저장소의 사용 예
- 시나리오 1 어느 날 갑자기 특정 쿼리의 퍼포먼스가 나빠졌다. 대책을 강
구하시오.
- 시나리오 2 서버의 CPU 부하가 높으므로 원인이 되는 쿼리를 발견하시오.
11.2.4 쿼리 저장소의 사용과 주의사항
- 쿼리 저장소의 사용
- 주의사항
- 강제된 계획

11.3 트러블 1 SQL 서버에 접속하지 못한다
11.3.1 SQL 서버가 대기하고 있는 프로토콜의 확인
- SQL 서버 로그의 참조
11.3.2 클라이언트 컴퓨터의 프로토콜 설정 확인
11.3.3 SQL 서버 브라우저의 상태 확인
11.3.4 윈도우의 보안 설정

11.4 트러블 2 블로킹 문제
11.4.1 블로킹 상황의 해석
- 확장 이벤트에 의한 블로킹 해석
11.4.2 블로킹 경감 수단
- 대처 1 개개의 트랜잭션을 짧게 해서 락 시간을 단축한다
- 대처 2 효과적인 WHERE절의 지정 등으로 락 범위를 좁힌다
- 대처 3 트랜잭션 분리 레벨의 변경

11.5 트러블 3 데드락 문제
11.5.1 전형적인 데드락 유형
- 사이클 데드락
- 변환 데드
11.5.2 데드락 정보의 해석 방법
- 추적 플래그 1204 사용
- 추적 플래그 1204의 사용 방법
11.5.3 데드락 대처 방법
- 오브젝트 액세스 순서 고려
- 나쁜 예
- 좋은 예
- 트랜잭션 내의 사용자 입력 대기 회피

11.6 제11장 정리

【제12장】새로운 플랫폼으로의 전개

12.1 리눅스에 대응
12.1.1 Linux 버전 SQL 서버 탄생 경위
12.1.2 최적의 이식 방법
12.1.3 리눅스 버전 SQL 서버를 사용할 때 주의사항

12.2 클라우드로 배포
12.2.1 IaaS로서의 SQL 서버
12.2.2 PaaS로 제공되는 SQL 서버
- Azure SQL Database
- Azure SQL Database Managed Instance

12.3 제12장 정리

Appendix 부록
용어집

Index 찾아보기

Column
SQLOS 스케줄러는 비선점형(non pre-emptive)
SQLOS 스케줄러의 발전
최적의 쿼리 실행 플랜을 위해
하이브리드 버퍼 풀
부가 열 인덱스의 이점
해시 버킷
락/래치를 획득하지 않고 데이터 갱신을 실현하는 방법은?
메모리 최적화 테이블과 디스크 테이블의 액세스 방법 차이
TDS의 변천
버퍼 사이즈의 변경
파손된 백업 파일의 복원
최적의 쿼리 실행 플랜을 위해
트랜잭션 분리 레벨을 이해한다
다양한 상태의 데이터를 보호하는 보안 기능군

히라야마 오사무

● 내부 컴포넌트의 이해(1): 제1장부터 제3장까지는 SQL 서버가 처리를 실행하기 위해 어떻게 컴퓨터의 리소스(프로세서, 디스크 I/O 및 메모리)를 효율적으로 관리하는지를 설명한다.

● 다양한 데이터 구조: 제4장부터 제7장까지의 각 장에서는 SQL 서버가 관리하는 다양한 오브젝트(데이터베이스와 테이블, 인덱스 등)의 상세한 구조를 소개한다. 이들에 대한 이해는 적절한 데이터베이스와 오브젝트의 배치 같은 논리 설계와 물리 설계 시의 중요한 지침이 된다.

● 내부 컴포넌트의 이해(2): 제8장에서는 앞의 내용에서 이해한 SQL 서버의 동작과 오브젝트 구조를 토대로 SQL 서버가 어떻게 테마를 다루는지를 알아본다. 제9장에서는 클라이언트와의 커뮤니케이션에서 빼놓을 수 없는 네트워크 관련 특성을 소개한다.

● 운용의 안정화: 제10장에서는 비즈니스의 지속성에 필수적인 데이터베이스의 백업과 복원에 대해 살펴본다. 또 11장에서는 전형적인 트러블에 대한 대처 방법과 퍼포먼스 튜닝을 지원하는 툴을 소개한다.

● 향후 전망: 제12장에서는 향후 전망으로 멀티 플랫폼 전개와 클라우드 상의 데이터베이스를 소개한다.