MS SQL Server 아키텍처 완전 정복: 일반·메모리·데이터 파일·로그 파일 구조 이해하기

앞선 글에서는 SQL Server의 개요와 컴퓨터에 SQL Server를 설치하는 방법을 살펴보았습니다. 이번 글에서는 SQL Server의 아키텍처(Architecture), 즉 내부 구조를 본격적으로 알아보겠습니다.

SQL Server의 아키텍처는 크게 다음 네 가지 영역으로 나누어 이해하면 훨씬 쉽습니다.

일반 아키텍처 (General Architecture)
메모리 아키텍처 (Memory Architecture)
데이터 파일 아키텍처 (Data File Architecture)
로그 파일 아키텍처 (Log File Architecture)

지금부터 각각의 아키텍처를 하나씩 자세히 살펴보겠습니다.

1. 일반 아키텍처 (General Architecture)

주요 구성 요소

클라이언트(Client): 요청이 발생하는 지점입니다.
쿼리(Query): 고급 언어로 작성된 SQL 질의문입니다.
논리 단위(Logical Units): 키워드, 표현식, 연산자 등으로 구성됩니다.
네트워크 패킷(N/W Packets): 네트워크와 관련된 코드입니다.
프로토콜(Protocols): SQL Server에는 다음과 같은 4가지 프로토콜이 있습니다.
- 공유 메모리(Shared Memory): 로컬 연결 및 문제 해결(troubleshooting) 용도로 사용됩니다.
- 명명된 파이프(Named Pipes): LAN 환경에서의 연결에 사용됩니다.
- TCP/IP: WAN 환경에서의 연결에 사용됩니다.
- VIA(Virtual Interface Adapter): 벤더가 지정한 특수 하드웨어가 필요하며, SQL Server 2012 버전부터는 지원되지 않습니다.
서버(Server): SQL 서비스가 설치되고 데이터베이스가 존재하는 곳입니다.
관계형 엔진(Relational Engine): 실제 쿼리 실행이 수행되는 영역으로, 쿼리 분석기(Query Analyzer), 쿼리 옵티마이저(Query Optimizer), 쿼리 런타임(Query Runtime)을 포함합니다.
쿼리 파서(Query Parser, Command Parser)와 컴파일러(Compiler): 쿼리의 문법을 검사하고, 쿼리를 기계어로 변환하는 역할을 담당합니다.
쿼리 옵티마이저(Query Optimizer): 쿼리, 통계 정보, Algebrizer 트리를 입력받아 실행 계획(Execution Plan)이라는 출력물을 생성합니다.

실행 단계와 핵심 프로세스

실행 계획(Execution Plan): 로드맵과 같은 것으로, 쿼리를 실행할 때 거치는 단계들의 순서를 담고 있습니다.
쿼리 실행기(Query Executor): 실행 계획을 바탕으로 쿼리를 단계별로 실행하며, 스토리지 엔진(Storage Engine)과 통신하는 지점이기도 합니다.
스토리지 엔진(Storage Engine): 저장 장치(디스크, SAN 등)에서 데이터를 저장하고 조회하며, 데이터 조작, 잠금(Locking), 트랜잭션 관리를 담당합니다.
SQL OS: 호스트 운영체제(Windows OS)와 SQL Server 사이에 위치합니다. 데이터베이스 엔진에서 수행되는 모든 동작은 SQL OS가 관리하며, 버퍼 풀(Buffer Pool)을 활용한 메모리 관리, 로그 버퍼(Log Buffer), 교착 상태(Deadlock) 감지, 블록·락 구조 사용 등 다양한 운영체제 서비스를 제공합니다.
체크포인트(Checkpoint): 체크포인트는 내부 프로세스로, 버퍼 캐시(Buffer Cache)에서 수정된 모든 페이지(더티 페이지(Dirty Page))를 물리적 드라이브에 기록합니다. 또한 로그 버퍼의 로그 내용도 물리적 파일에 기록합니다. 더티 페이지를 디스크에 기록하는 과정은 '더티 페이지 하드닝(Hardening)'이라고도 부릅니다.
레이지 라이터(Lazy Writer): Lazy Writer는 체크포인트와 전혀 다른 목적으로 더티 페이지를 디스크로 밀어냅니다. 그 목적은 바로 버퍼 풀의 여유 메모리 확보입니다. SQL Server의 메모리가 부족해질 때 발생하며, 내부 프로세스가 제어하므로 별도의 설정은 없습니다.

SQL Server는 메모리 사용량을 지속적으로 모니터링하여 리소스의 가용성과 경합 상태를 평가하고, 항상 일정량의 여유 공간을 확보하려 합니다. 리소스 충돌이 감지되면 Lazy Writer를 트리거하여 일부 더티 페이지를 디스크로 밀어내고 메모리를 확보합니다. 이때 어떤 페이지를 디스크로 보낼지 결정하는 데는 LRU(Least Recently Used) 알고리즘이 사용됩니다. 만약 Lazy Writer가 항상 활성화된 상태라면 메모리 병목 현상이 발생할 수 있으므로 주의해야 합니다.

2. 메모리 아키텍처 (Memory Architecture)

메모리 아키텍처의 핵심 특징은 다음과 같습니다.

모든 데이터베이스 소프트웨어의 기본 설계 목표 중 하나는 디스크 I/O 최소화입니다. 디스크 읽기·쓰기 작업은 가장 많은 시스템 자원을 소모하는 동작이기 때문입니다.
Windows의 내부 메모리는 VAS(Virtual Address Space, 가상 주소 공간)라고 불리며, 커널 모드(OS 모드)와 사용자 모드(SQL Server 같은 애플리케이션)가 함께 공유합니다.
SQL Server의 사용자 주소 공간은 MemToLeave(MTL)와 Buffer Pool(BPool) 두 부분으로 나뉩니다.
MTL과 Buffer Pool의 크기는 SQL Server가 시작(부팅) 과정에서 결정합니다.
높은 I/O 성능을 달성하려면 버퍼 관리(Buffer Management)가 매우 중요한 구성 요소입니다. 여기에는 데이터베이스를 읽고 갱신하는 버퍼 매니저(Buffer Manager)와, 데이터베이스 파일에 대한 I/O를 줄여주는 버퍼 풀 페이지라는 두 가지 메커니즘이 포함됩니다.
Buffer Pool은 여러 영역으로 나뉘며, 그중 가장 중요한 것이 Buffer Cache와 Procedure Cache입니다. Buffer Cache는 데이터 페이지를 메모리에 유지하여 자주 접근하는 데이터를 캐시에서 즉시 꺼낼 수 있게 합니다. 캐시에 데이터가 없으면 디스크에서 데이터 페이지를 읽어옵니다. 캐시에서 데이터를 읽으면 디스크 I/O 횟수가 줄어들어 성능이 최적화됩니다. 메모리 접근 속도가 디스크보다 훨씬 빠르기 때문입니다.
Procedure Cache는 저장 프로시저(Stored Procedure)와 실행 계획(Execution Plan)을 보관하여 실행 계획이 반복 생성되는 횟수를 줄여줍니다. Procedure Cache의 용량과 동작 정보는 DBCC PROCCACHE 명령으로 확인할 수 있습니다.
Buffer Pool의 그 외 영역은 다음과 같습니다.
- 시스템 수준 데이터 구조(System-level Data Structures): 데이터베이스와 잠금에 관한 인스턴스 수준의 데이터를 담습니다.
- 로그 캐시(Log Cache): 트랜잭션 페이지의 읽기·쓰기를 전담합니다.
- 연결 컨텍스트(Connection Context): 인스턴스에 대한 각 연결마다 현재 연결 상태를 기록하는 작은 메모리 영역이 할당됩니다. 여기에는 저장 프로시저 및 사용자 정의 함수의 매개변수, 커서 위치 등이 포함됩니다.
- 스택 공간(Stack Space): Windows는 SQL Server가 시작할 때 생성하는 각 스레드에 스택 공간을 할당합니다.

3. 데이터 파일 아키텍처 (Data File Architecture)

파일 그룹(File Group)

데이터베이스 파일은 배포 및 관리를 위해 파일 그룹으로 묶을 수 있습니다. 하나의 파일은 오직 하나의 파일 그룹에만 속할 수 있습니다. 로그 파일은 로그 크기가 데이터 용량과 별개로 관리되기 때문에 파일 그룹에 포함되지 않습니다.

SQL Server의 파일 그룹에는 Primary와 사용자 정의(User-defined) 두 종류가 있습니다. Primary에는 기본 데이터 파일과 특정 파일 그룹에 할당되지 않은 모든 파일이 포함되며, 시스템 테이블의 모든 페이지가 Primary에 할당됩니다. 사용자 정의 파일 그룹은 데이터베이스를 생성하거나 수정하는 명령에서 FILEGROUP 키워드를 사용해 지정할 수 있습니다.

각 데이터베이스에는 하나의 파일 그룹이 기본 파일 그룹(Default File Group) 역할을 합니다. SQL Server가 테이블이나 인덱스에 페이지를 할당할 때(생성 당시 특정 파일 그룹에 지정하지 않은 경우), 해당 페이지는 기본 파일 그룹에 배치됩니다. 기본 파일 그룹을 다른 파일 그룹으로 변경하려면 db_owner 고정 데이터베이스 역할이 필요합니다.

Primary가 기본 파일 그룹입니다. 파일 및 개별 파일 그룹을 백업하려면 사용자에게 db_owner 고정 데이터베이스 역할이 있어야 합니다.

파일(File)

데이터베이스에는 세 가지 유형의 파일이 있습니다. Primary(기본 데이터 파일), Secondary(보조 데이터 파일), Log(로그 파일)입니다. Primary는 데이터베이스의 시작점이며, 데이터베이스 내 다른 파일들을 가리킵니다.

각 데이터베이스에는 반드시 하나의 Primary 파일이 있으며, 확장자는 임의로 설정할 수 있지만 일반적으로 .mdf를 사용할 것이 권장됩니다. Secondary 파일은 기본 데이터 파일 외의 데이터 파일로, 하나의 데이터베이스에 여러 개를 둘 수도 있고 하나도 두지 않을 수 있습니다. 보조 데이터 파일의 확장자는 .ndf로 설정하는 것이 좋습니다.

로그 파일은 데이터베이스 복구에 필요한 모든 정보를 보관합니다. 데이터베이스에는 최소 한 개의 로그 파일이 반드시 필요하며, 여러 개를 둘 수도 있습니다. 로그 파일의 확장자는 .ldf로 설정합니다.

데이터베이스 내 모든 파일의 위치는 master 데이터베이스와 해당 데이터베이스의 Primary 파일 양쪽에 기록됩니다. 대부분의 경우 데이터베이스 도구는 master 데이터베이스에 기록된 파일 위치를 참조합니다.

각 파일에는 논리 이름(Logical Name)과 물리 이름(Physical Name)이라는 두 가지 이름이 있습니다. 논리 이름은 모든 T-SQL 명령에서 파일을 참조할 때 사용되며, 물리 이름(OS_file_name)은 운영체제 규칙을 따라야 합니다. 데이터 파일과 로그 파일은 FAT 또는 NTFS 파일 시스템에 배치할 수 있지만, 압축된(compressed) 파일 시스템에는 배치할 수 없습니다. 하나의 데이터베이스에는 최대 32,767개의 파일을 둘 수 있습니다.

익스텐트(Extent)

Extent는 테이블과 인덱스에 공간을 할당하는 기본 단위입니다. 각 Extent는 인접한 8개의 페이지, 즉 64KB입니다. SQL Server에는 Uniform과 Mixed 두 종류의 Extent가 있습니다. Uniform은 단일 객체만으로 구성되고, Mixed는 최대 8개의 객체로 구성됩니다.

페이지(Page)

Page는 SQL Server 데이터 저장의 기본 단위이며, 크기는 8KB입니다. 각 페이지의 시작 부분에는 96바이트의 헤더(페이지 제목)가 있어 페이지 유형, 페이지의 남은 여유 공간 크기, 해당 페이지를 소유한 객체의 ID 같은 시스템 정보를 저장합니다.

SQL Server에는 9가지 유형의 데이터 페이지가 있습니다.

Data: text, ntext, image를 포함한 모든 데이터가 담긴 데이터 행
Index: 인덱스 항목
Text/Image: text, ntext, image 데이터
GAM(Global Allocation Map): 할당된 익스텐트에 대한 정보
SGAM(Shared Global Allocation Map): 시스템 수준에서 할당된 익스텐트에 대한 정보
PFS(Page Free Space): 페이지의 여유 공간에 대한 정보
IAM(Index Allocation Map): 테이블이나 인덱스가 사용하는 익스텐트에 대한 정보
BCM(Bulk Changed Map): 마지막 백업 이후 대량(bulk) 작업으로 변경된 익스텐트 정보
DCM(Differential Changed Map): 마지막 데이터베이스 백업 이후 변경된 익스텐트 정보

4. 로그 파일 아키텍처 (Log File Architecture)

SQL Server의 트랜잭션 로그는 일련의 로그 레코드(log record)로 구성될 때 올바르게 동작합니다. 각 로그 레코드는 LSN(Log Sequence Number)으로 식별되며, 자신이 속한 트랜잭션의 ID를 포함합니다.

로그는 데이터의 수정 내역이나 수행된 작업을 기록하거나, 데이터 편집 전후의 이미지(before/after image)를 저장합니다. '이전 이미지(previous image)'는 작업 수행 전 데이터의 복사본이고, '이후 이미지(after image)'는 작업 수행 후 데이터의 복사본입니다.

작업을 복구하는 방법은 로그 유형에 따라 달라집니다.

논리 연산이 기록된 경우:
이전 논리 연산으로 되돌아가려면 해당 연산을 다시 수행(redo)합니다.
이후 논리 연산으로 돌아가려면 역방향 논리 연산을 수행(undo)합니다.
이전/이후 이미지가 기록된 경우:
이전 연산으로 되돌아가려면 이후 이미지(after image)를 적용합니다.
이후 연산으로 돌아가려면 이전 이미지(before image)를 적용합니다.

다음과 같은 다양한 작업들이 트랜잭션 로그에 기록됩니다.

각 트랜잭션의 시작과 종료
저장 프로시저나 DDL(데이터 정의어) 명령을 통한 시스템 테이블 변경을 포함한 모든 데이터 수정(insert, update, delete)
모든 익스텐트 및 페이지 할당과 할당 취소
테이블 및 인덱스의 생성 또는 삭제

롤백(Rollback) 작업 역시 로그에 기록됩니다. 각 트랜잭션은 롤백을 실행하거나 오류 메시지를 출력하는 데 필요한 로그 공간이 충분히 확보되도록 로그 내에 공간을 예약합니다. 이 공간은 트랜잭션이 완료되면 해제됩니다.

전체 데이터베이스를 성공적으로 복원하는 데 필요한 첫 번째 로그 레코드부터 마지막 로그 레코드까지의 구간을 활성 로그(active log) 또는 로그 활동 섹션이라고 합니다. 이는 데이터베이스 전체 복구에 필수적인 로그 구간이며, 활성 로그의 어느 부분도 잘려나갈 수 없습니다. 첫 번째 로그 레코드의 LSN을 Min LSN(최소 복구 LSN)이라고 합니다.

가상 로그 파일(Virtual Log File)

SQL Server 데이터베이스 엔진은 각 물리적 로그 파일을 여러 개의 가상 로그 파일(Virtual Log File)로 나누어 관리합니다. 가상 로그 파일은 크기가 고정되어 있지 않으며, 물리적 로그 파일당 가상 로그 파일의 개수도 정해져 있지 않습니다.

데이터베이스 엔진은 로그 파일을 생성하거나 확장할 때 가상 로그 파일의 크기를 자동으로 선택하며, 가능한 한 적은 수의 가상 파일을 유지하려고 합니다. 가상 로그 파일의 크기는 관리자가 설정하거나 구성할 수 없습니다. 물리적 로그 파일을 작은 크기(size) 값과 작은 증분(growth_increment) 값으로 정의했을 때만 가상 로그 파일이 시스템 성능에 영향을 미칩니다.

size 값은 로그 파일의 초기 크기이고, growth_increment는 파일에 새 공간이 필요할 때마다 추가되는 공간의 크기입니다. 로그 파일이 작은 증분 값으로 여러 번 확장되어 큰 크기가 되면 가상 로그 파일이 많아지게 되는데, 이는 데이터베이스 시작 시간과 로그 백업·복원 작업을 느리게 만들 수 있습니다.

따라서 로그 파일의 size 값은 최종적으로 필요한 크기에 가깝게, growth_increment 값은 비교적 크게 설정하는 것이 좋습니다. SQL Server는 WAL(Write-Ahead Logging) 방식을 사용하여, 관련 로그가 디스크에 기록되기 전에는 데이터 수정 내용이 디스크에 기록되지 않도록 보장합니다. 이를 통해 트랜잭션의 ACID 속성을 유지할 수 있습니다.

5. SQL의 Algebrizer

Algebrizer에 대해 조금 더 설명하겠습니다. Algebrizer는 쿼리 실행 과정 중 하나의 단계로, 파서(Parser) 다음에 동작을 시작합니다. Query Parser가 문법적으로 올바른 쿼리임을 확인하면 쿼리는 Algebrizer로 전달되고, 이때부터 Algebrizer의 작업이 시작됩니다.

Algebrizer는 쿼리에서 제공되거나 참조된 객체와 열(column) 이름의 유효성을 검증하는 역할을 담당합니다. 예를 들어 쿼리에 열 이름을 잘못 입력했다면, Algebrizer가 이를 확인하고 오류를 발생시킵니다. 또한 Algebrizer는 해당 쿼리에서 처리되는 모든 데이터 유형을 식별하며, GROUP BY 절과 집계된 열들이 올바른 위치에 놓였는지도 검증합니다.

예를 들어 아래 쿼리를 Ctrl+F5(구문 분석만)로 실행하면 오류가 발생하지 않습니다. 하지만 F5(실제 실행)로 실행하면 Algebrizer가 동작하여 오류를 반환합니다.

USE AdventureWorks
GO
SELECT MakeFlag, SUM(ListPrice)
FROM Production.Product
GROUP BY ProductNumber

MakeFlag 열이 GROUP BY 절에 포함되지 않았기 때문에 실제 실행 시점에 오류가 발생하는 것입니다.

6. SQL Server의 체크포인트(Checkpoint)

SQL Server 2012에는 4가지 유형의 체크포인트가 있습니다.

자동 체크포인트(Automatic): 가장 흔하게 발생하는 체크포인트입니다. 백그라운드 프로세스 형태로 실행되며, 서버 구성 옵션의 Recovery Interval에서 지정한 시간 안에 SQL Server 데이터베이스를 복구할 수 있도록 보장합니다.
간접 체크포인트(Indirect): SQL Server 2012에서 새로 도입된 체크포인트입니다. 역시 백그라운드로 실행되지만, 특정 데이터베이스에 대해 구성 옵션에서 복구 시간을 지정한 사용자에게만 적용됩니다. 특정 데이터베이스에 Target_Recovery_Time이 지정되면 서버에 할당된 Recovery Interval을 덮어쓰므로, 해당 데이터베이스에서는 자동 체크포인트가 무시됩니다.
수동 체크포인트(Manual): 다른 SQL 명령처럼 실행되는 체크포인트입니다. CHECKPOINT 명령을 실행하면 완료될 때까지 진행되며, 현재 데이터베이스에서만 동작합니다. CHECKPOINT_DURATION 옵션을 지정하여 체크포인트가 완료되기를 원하는 시간을 정할 수 있습니다.
내부 체크포인트(Internal): 사용자가 직접 제어할 수 없는 체크포인트로, 다음과 같은 상황에서 발생합니다.
- SHUTDOWN WITH NOWAIT 명령 등으로 정상 종료되지 않는 경우를 제외하고, 모든 데이터베이스를 종료(shutdown)할 때
- 복구 모델이 Full 또는 Bulk-logged에서 Simple로 변경될 때
- 데이터베이스 백업 중일 때
- 데이터베이스가 Simple 복구 모드이고, 로그가 70% 찼거나 서버의 Recovery Interval 옵션 기준에 도달했을 때 자동으로 수행될 때
- ALTER DATABASE 명령으로 로그/데이터 파일을 추가하거나 삭제할 때
- 데이터베이스 복구 모델이 Bulk-logged이고 최소 로깅(minimal logging) 작업이 수행될 때

마무리하며

SQL Server 아키텍처는 다소 생소하고 어렵게 느껴질 수 있는 주제이지만, 내부 구조를 이해하면 시스템이 어떻게 동작하는지, 오류가 발생했을 때 원인이 어디에 있는지 파악하는 데 큰 도움이 됩니다. 결과적으로 데이터베이스를 다루는 작업이 한층 수월해집니다.

다음 글에서는 Management Studio(SSMS)에 대해 알아보고, SQL Server의 기본 명령들을 차근차근 살펴보겠습니다.