다이어그램은 주로 SQL Server의 핵심 기능이 어떻게 유기적으로 결합하여 동작하는지에 대해 알고 싶을 때 보기 좋음
(처리 흐름을 제대로 반영하지 못해 오해의 소지가 다분함 -> 수정 필요)
2) Physical Architecture
Physical Architecture는 실제 SQL Server를 구성하고 있는 아키텍처
세부 기능들이 어떤 식으로 분류되어 있으며, 각각의 분리된 역할을 알고 싶을 때 보기 좋음
1. 프로토콜 계층(Protocol Layer)
TDS (Tabular Data Stream) 란?
클라이언트(응용 프로그램)와 SQL 서버 간에 통신할 때에는 'TDS(Tabular Data Stream)' 라는 프로토콜(메시지)을 사용함
TDS(Tabular Data Stream)는 클라이언트 애플리케이션 <-> Microsoft SQL Server 간에 쿼리 결과, 데이터 인증 정보 등을 주고 받기 위해 사용하는 핵심 통신 프로토콜(Application Layer Protocol)
Reqeust/Response 방식
응용 프로그램과 DB 서버 간의 상호작용(쿼리 요청(Bulk Insert 포함), 프로시저 동작, RPC(Remote Procedure Call), 데이터 반환, 트랜잭션 매니저 Request 등)을 용이하게 하고, 인증(Authentication) 및 채널 암호화 협상(Channel Encryption Negotiation) 기능을 제공
참고) TDS는 마이크로소프트의 독점적인 기술이기 때문에, 윈도우 외 환경에서는 TDS를 사용할 수 없음
TDS는 아래 3가지 유형의 통신 방식을 지원함
1-1. Shared Memory
SQL Server가 설치된 서버를 클라이언트로 사용하는 경우 기본적으로 공유 메모리 프로토콜을 사용하여 인스턴스에 연결을 시도
TCP/IP를 통한 연결의 경우 [서버 이름] 옵션은 "서버의 컴퓨터/인스턴스" 이어야 하며 SQL Server는 TCP/IP에서 포트 1433을 사용
1-3. Named Pipe
클라이언트와 SQL Server가 LAN을 통해 연결된 경우 Named Pipe 프로토콜을 통해 상호작용을 할 수 있음
이 옵션은 기본적으로 비활성화되어 있으며, SQL 구성 관리자에서 활성화
2. 관계형 엔진(Relational Engine)
스토리지 엔진에 데이터를 요청하고 반환된 결과를 처리하며 사용자 쿼리 실행을 담당
전반적인 프로세스를 쿼리 프로세서(Query Processor)가 수행
참고) T-SQL(Transact-SQL) : SQL Server에서 사용하는 강력한 프로그래밍 언어로 표준 SQL에 변수 선언, 흐름 제어, 데이터 처리, 함수 등의 기능을 확장한 것
세 가지 주요 구성요소로 구분함
2-1. CMD Parser
프로토콜 계층에서 수신된 사용자 쿼리를 수신
사용자 쿼리의 구문 및 의미 오류를 검증하고 쿼리 트리(Query Tree)를 생성
1. 구문(Syntactic) 검사 : 사용자(User) 입력이 MS SQL 구문 또는 문법 규칙을 따르지 않으면 오류를 반환하는 과정
ex) SELCET * FROM <Table Name>;
=> 'SELCET' 라는 키워드는 MS SQL에 존재하지 않는다. ('SELECT' 가 올바른 것.)
2. 의미(Semantic) 검사 : 노멀라이저(Normalizer)에 의해 수행되며, 조회 중인 Column명, Table명이 스키마에 존재하는지 확인하는 과정. 존재하는 경우 쿼리에 바인딩(Binding)한다고 한다.
ex) SELECT * FROM USER_ID;
=> 'USER_ID' 라는 테이블이 존재하지 않는다면, 노멀라이저가 찾지 못하여 오류 메시지를 표시한다.
3. Query Tree 생성 : 쿼리가 순서에 맞게 실행되게끔 하는 트리형 자료구조를 생성한다.
2-2. Optimizer
CMD Parser에게 받은 Query Tree를 기반으로 사용자 쿼리에 대한 가장 저렴하고 효율적인 실행 계획을 만듦 모든 쿼리가 최적화 되는 것은 아니며 SELECT, INSERT, DELETE 및 UPDATE와 같은 DML(Data Modification Language) 구문만 최적화가 수행
참고) CREATE 및 ALTER와 같은 DLL 명령은 최적회되지 않지만 내부 형식으로 컴파일 쿼리 비용은 CPU 사용량, 메모리 사용량 및 입/출력 요구 기반으로 계산
MS-SQL 옵티마이저는 내장된 exhaustive/heuristic 알고리즘에서 작동
참고) Plan Cache Lookup / Reuse 판단 옵타마이저가 실행 계획을 생성하기 전 단계로 "이 쿼리에 대해 재사용 가능한 계획이 있나?"를 확인 -> 버퍼/캐시 영역의 Plan Cache 확인 * 있으면 Soft Parsing(재사용 경로)없거나, * 있어도 재사용 불가면 Hard Parsing(최적화/컴파일 경로) 로 들어감 이 판단은 보통 쿼리 프로세서가 캐시 키/유효성(SET 옵션, 스키마 버전 등)까지 포함해 판단
Phase 1 : 트랜잭션 처리 탐색 Plan (Hard Parsing)
단순, 복합 Plan 검색이 포함
단순 Plan 검색 : 쿼리와 관련된 컬럼 및 인덱스의 과거 데이터를 통계 분석에 사용
복합 Plan 검색 : 다중 인덱스를 포함
Phase 2 : 병렬 처리 및 최적화
0단계, 1단계 중 어느것도 작동하지 않는 경우 실행
병렬 처리 가능성을 검색하는 단계이며, 기계의 처리능력과 구성에 따라 달라짐
이 단계도 가능하지 않은 경우, 최종 최적화 단계가 실행
참고) 최종 최적화 단계 알고리즘은 알려져 있지 않음(Microsoft의 기밀)
2-3. Query Executor
쿼리문 실행에 필요한 데이터를 불러오는 로직에 대한 실행 plan을 제공
Storage Engine의 Access Method를 호출
Storage Engine으로부터 데이터를 받으면, 프로토콜 계층으로 해당 데이터를 결과로서 반환
사용자 쿼리의 전체 처리 흐름 요약 (1) 연결/요청 수신 클라이언트가 프로토콜을 통해 요청을 보내면 Protocol Layer가 받아서 엔진으로 넘깁니다.
(2) 파싱 & 바인딩 SQL 문법 확인, 객체/스키마 확인, 권한 확인 등을 수행합니다.
(3) 최적화 & 실행 계획(Plan) 생성/재사용 기존에 같은/유사한 쿼리의 실행 계획 캐시가 있으면 재사용을 시도하고, 없으면 최적화 후 계획을 만듭니다. 실행은 크게 행 모드(Row mode) / 일괄 처리(Batch mode) 같은 모드 개념으로 설명됩니다.
(4) 실행(Execution) & 저장 엔진 호출 실행 계획의 오퍼레이터가 필요로 하는 데이터 페이지를 Storage Engine에 요청합니다.
(5) 결과 반환 필요한 연산(조인/집계/정렬 등)을 수행한 뒤 Protocol Layer를 통해 결과를 클라이언트로 돌려줍니다.
3. 스토리지 엔진(Storage Engine)
MS SQL에서 데이터 파일은 `Page`라는 단위로 저장되며, 이는 SQL Server의 기본적인 I/O 단위
Page 하나당 크기는 8 KB(다른 DBMS에서는 Block이라 하는 것)
이러한 Page들은 8개씩 논리적으로 묶여 Extent(익스텐트)를 형성
페이지에는 메타 데이터 정보를 전달하는 96byte 크기의 페이지 헤더라는 섹션이 있습니다. (페이지 유형, 페이지 번호, 사용된 공간 크기, 다음 페이지 및 이전 페이지에 대한 포인터 등)
<파일 유형> (1) 기본 파일 : *.mdf 모든 데이터베이스에는 하나의 기본 파일이 있으며
테이블, 뷰, 트리거 등과 관련된 모든 중요한 데이터를 저장 (2) 보조 파일 : *.ndf 보조파일은 선택사항
데이터베이스는 여러 개의 보조 파일을 포함할 수 있음 (3) 로그 파일 : *.ldf 트랜잭션 관리에 사용되는 파일로 원치 않는 인스턴스에서 복구하는데 사용 커밋되지 않은 트랜잭션으로 롤백하는 중요한 작업을 수행
3-1. Access Method
Query Executor <-> Buffer Manager, Transaction Logs 사이에서 인터페이스 역할
UPDATE/INSERT/DELETE로 페이지 내용이 바뀌면 그 페이지는 Dirty Page가 됩니다(메모리의 페이지가 디스크와 달라짐).
이때 디스크에 바로 쓰는 게 아니라(항상 즉시 X),
로그(트랜잭션 로그)는 먼저 기록되고(WAL 개념),
데이터 페이지는 나중에 적절한 시점에 디스크로 내려씁니다.
4) 페이지 퇴출(Eviction)과 메모리 압박 대응
Buffer Pool 공간이 부족하면, Buffer Manager는 “덜 중요한/덜 사용된” 페이지를 내보내고(또는 재사용) 새 페이지를 올립니다.
이때 더럽혀진(Dirty) 페이지는 그냥 버릴 수 없어서 먼저 디스크로 기록해야 합니다.
3-3. Transaction Manager
Transaction Manager는 Log Manager와 Lock Manager 로 이루어져 있다.
Log Manager
Transaction Log 를 통해 시스템에서 수행된 모든 업데이트 로그들을 기록
이때 로그에는 Trasaction ID가 포함되어 있는 Sequence Number와 데이터 수정 레코드(Data Modification Record)가 존재
이 트랜잭션 로그는 커밋된 트랜잭션과 트랜잭션 롤백에 대하여 기록하기 위함
Lock Manager
트랜잭션 수행 중에는 Data Storage와 관련된 데이터들은 Lock(잠금) 상태에 들어가는데, 이러한 프로세스가 Lock Manager에 의해 수행
일관성(Consistency) 및 독립성(Isolation) 보장
참고) - 일관성(Consistency) : 트랜잭션이 실행을 성공적으로 완료하면 언제나 일관성 있는 DB 상태로 유지하는 것을 의미 - 독립성(Isolation) : 트랜잭션을 수행 시 다른 트랜잭션의 연산 작업이 끼어들지 못하도록 보장하는 것을 의미
4. 버퍼/캐시(Buffer / Cache)
Plan Cache (실행 계획 캐시)
Plan Cache(실행 계획 캐시) 조회/재사용 여부 판단은 Relational Engine(쿼리 프로세서)-Optimizer 단계에서 일어남
담는 것: 쿼리의 컴파일 결과물인 실행 계획(Execution Plan), 일부 쿼리 메타데이터
목적: 같은/유사한 쿼리가 다시 들어왔을 때 재컴파일(최적화) 비용을 줄임
영향: Plan Cache가 잘 맞으면 CPU(컴파일/최적화) 부담이 줄고 응답이 빨라짐
반대로 계획이 부적절하게 재사용되면(파라미터 스니핑 등) 느려질 수도 있음
1) Plan Cache에서 기존 실행 계획을 찾으면? Soft parsing (계획 재사용 성공) 그대로 씀 효과: 컴파일/최적화 비용(CPU, 래치 등)이 거의 안 듦
2) Plan Cache에 기존 실행 계획이 없거나 있어도 재사용이 불가능하면? Hard parsing (새로 컴파일/최적화) SQL Server는 파싱 → 바인딩 → 최적화 → 컴파일을 해서 새 실행 계획을 만듦 효과: 컴파일 CPU 증가, 동시성 높으면 compile 관련 경합도 생길 수 있음
Data Cache (데이터 캐시 = Buffer Pool의 데이터 페이지)
담는 것: 데이터/인덱스의 8KB 페이지(테이블/인덱스 페이지)
목적: 디스크에서 읽지 않고 메모리에서 페이지를 읽게 해서 I/O를 줄임
영향: Data Cache가 잘 맞으면 Physical I/O가 줄고 조회/조인이 빨라짐
부족하면 디스크 읽기 증가 → 전반적인 지연 증가
1) Data Cache에 데이터가 존재한다면? Logical Read
Buffer Manager가 Data Cache 속 버퍼에 데이터가 존재하는지 확인
데이터가 존재한다면, 이 데이터가 Query Executor로 전달되어 사용
2) Data Cache에 데이터가 존재하지 않는다면? Physical Read
Buffer Manager가 Disk에 저장되어 있는 Data file에 접근
Dirty Page : Transacntion Manager의 로직 처리의 결과로 생긴다. 페이지가 메모리에 처음으로 읽혀 들어오면 이는 메모리에 있는 데이터 페이지와 Disk에 있는 데이터 페이지의 내용이 동일하기 때문에 Clean Page라고 부른다. 그러나, 페이지가 수정이 되면 메모리에 있는 데이터 페이지의 내용과 Disk에 있는 것과는 달라지기 때문에 이를 Dirty Page 라고 부른다. Buffer Manager가 작업 스레드의 읽기 요청을 받으면 Data Cache의 64 페이지 목록을 가져와 사용 가능한 Buffer 목록이 특정 임계 값 미만인지 여부를 확인하여, 임계값 미만일 경우에는 목록에 있는 Dirty page를 Disk에 기록하게 된다.
