SQL Server Architecture

DBMS 아키텍처

- DBMS는 적어도 하나 이상의 인스턴스와 데이터베이스를 포함

- 데이터베이스는 유저(응용)데이터와 시스템 데이터를 저장하는 물리 파일들로 구성

- 인스턴스는 DBMS 사용자가 데이터에 접근할 수 있는 시스템 리소스(memory, process)로 구성

** process: 응용 프로그램의 실행 단위, process 내에 여러 작업을 동시에 수행할 때 작업의 단위 또는 개체 thread

---

오라클 아키텍처

오라클 서버 = Instance(메모리 영역 + background 프로세스 영역) + Database(저장 파일 영역)

클라이언트 프로세스 -> 서버 프로세스: client가 요청한 SQL 쿼리를 server process가 실행 결과 전달, application을 통해 요청된 SQL을 parse-execute-fetch를 통해 실행

pmon - process 레벨의 모니터링과 resource cleanup 수행

smon - system 레벨의 모니터링과 cleanup 수행, instance 시작 시 recovery 수행

---

오라클 메모리 아키텍처

 

SGA

- 오라클 서버의 메모리 영역

- Instance의 데이터 및 제어정보를 포함하는 공유 메모리 그룹

- 모든 서버 및 Background 프로세스에 의해 공유

- 관련 parameter: sga_max_size, sga_target

Shared Pool

- DB에서 발생하는 대부분의 연산에 관계된 프로그램 데이터를 저장하는 영역

- Shared Pool = Library Cache + Data Dictionary Cache + 기타
- Library Cache: 싫행된 SQL, PLSQL문(SQL문의 확장된 개념)의 구문분석된 코드와 실행에 필요한 실행계획을 저장

- Data Dictionary Cache: 최근에 사용된 메타정보를 row 단위로 저장

- 관련 parameter: shared_pool_size

 

Database Buffer Cache

- Datafile에서 읽은 Data Block의 복사본을 저장하는 영역

- Database Buffer Cache = Default Buffer Pool + nk Buffer Pool + 기타(Keep, Recycle)

- Buffer state: Unused/Free, Clean, Pinned, Dirty buffer

- LRU List와 접근횟수 조합의 복합알고리즘으로 운영

- 관련 parameter: db_chche_size, db_nk_cache_size

 

Redo Log Buffer

- Database에 대한 모든 변경사항을 기록하는 순환 메모리 영역

- DML 또는 DDL이 수행한 변경사항을 리두(재생성)하는 데 필요한 항목을 기록

- Database의 Recovery를 위한 영역

- LGWR 프로세스에 의해 Online Redo Log File에 저장

- 관련 parameter: log_buffer

 

Large Pool

- 백업 및 복구, 병렬쿼리 작업에 대한 대용량 메모리 할당 시 사용

- 선택적 영역으로 최소로 할당

- 관련 parameter: large_pool_size

 

Java Pool/Streams Pool

- Java Pool: Oracle JVM의 모든 세션별 Java 코드 및 데이터를 저장

- Streams Pool: Oracle Stream 프로세스용 메모리 영역

- 관련 parameter: java_pool_size, streams_pool_size

 

PGA

- 서버 프로세스에 대한 데이터 및 제어정보를 포함하는 private 메모리 영역

- PGA = SQL Work Area + Private SQL Area + Session Area

- SQL Work Area: 데이터 정렬, 해시 조인, 비트맵 인덱스 생성 영역이 포함

- Private SQL Area: 실행되는 SQL 및 PLSQL의 상태, bind 변수, 커서의 runtime 정보 등을 포함

- Session Area: 유저 세션에 대한 제어 정보를 포함

- 관련 parameter: pga_aggregate_target

---

오라클 Process Architecture

 

- User Process : 응용프로그램 또는 오라클 도구를 실행하는 프로세스

$ 사용자가 오라클 application을 실행했을 때 OS가 생성하는 client process (SQL*PLUS, PRO*C, Toad 등)

$ Client Application은 오라클 라이브러리나 API 등을 통해 DB와 통신

$ 사용자가 실행시킨 SQL을 서버 프로세스에 전달하고, 그 결과를 서버 프로세스로 부터 받는 역할 수행

- Server Process : 유저 프로세스의 요청을 처리하는 데이터베이스 프로세스

§ Instance에 접속한 User Process의 요청을 처리

§Application을 통해 요청된 SQL을 Parse – Execute – Fetch 를 통해 실행시키는 역할을 수행

  - Parse : SQL문 문법, 사용자 권한, 객체의 사용여부 검사, 실행계획 생성

  - Execute : Parse 단계의 실행계획에 따라 데이터 block을 read하고 변경

  - Fetch : 결과 데이터를 User Process에 전달

- Background Process : Multi 유저에 대한 성능을 최대화 하기 위한 데이터베이스 프로세스

DBWn (Database Writer Process)

§Database buffer cache의 변경된 block을 Datafile에 기록

§DBWn 구동 시기

  - Checkpoint가 발생할때

  - Dirty buffer 가 임계치에 달하거나 Free buffer가 없을때

  - Tablespace offline 또는 Read Only로 변경시

  - Table Drop 또는 Truncate 시

  - Tablespace begin Backup 시

§관련 parameter : db_writer_proceses

 

LGWR (Log Writer Process)

§Redo Log buffer를 Online Redo Log file에 기록

§LGWR 구동 시기

  - Commit 수행시

  - Redo log buffer가 1/3이상 차거나 1MB 이상의 buffer가 포함될 때

  - Checkpoint에 의한 DBWn 프로세스의 작업이 요구될 때

  - 3초마다 또는 설정된 Timeout 시간에 의해

  - Log Switch 발생시

CKPT (Checkpoint Process)

§SCN(System Cange Number) 및 데이터베이스 상태를 Control file과 Data File Header 에 기록

§메모리 내의 데이터와 데이터 파일에 저장된 데이터를 일치시키는 일

§CKPT 구동 시기

  - 데이터베이스가 정상 종료될 때

  - Alter system checkpoint; 명령어로 강제 발생

  - Log Switch 발생시

  - Tablespace 변경 또는 Begin Backup시

§관련 parameter : fast_start_mttr_target

PMON (Process Monitor Process)

§Process-Level의 모니터링과 Cleanup 수행

  - Server Process의 비정상 종료시 Recovery를 수행(Transaction Rollback)

  - User Process가 사용했던 Database buffer cache 정리 및 Resource 해제

  - Instance와 Server Process의 정보를 Listener에 동적으로 등록

 

SMON (Sytem Monitor Process)

§Sytem-level의 모니터링과 Cleanup 수행

  - Instance 시작시 Recovery 수행

  - 사용하지 않는 Temporary Segment 정리

---

Storage Architecture

 

RDBMS는 Datafile과 같은 물리적 구조와 table, index와 같은 논리적 구조가 분리되어 있어 독립적으로 관리 가능함

 

Data Files

§데이터베이스의 User Data 및 Sytem Data를 저장하는 파일

§논리 구조인 Tablespace Data를 물리적으로 저장

  - Tablespace는 1개 이상의 Data File로 구성

 

Control Files

§데이터베이스의 현재 상태와 물리적 구성 정보를 저장하는 binary 파일

§데이터베이스 시작과 트랜잭션 종료시 모든 현재 상태정보를 저장하고 검증

§포함 정보

  - Database명과 ID, Database 생성 시간

  - Datafile, Online Redo log file, Archived Redo log file 정보

  - Tablespace 정보

  - Checkpoint 및 Backup 정보

§주요 파일로 데이터베이스 생성시 3개의 File로 미러링 구성

§관련 parameter : control_files

§정보조회 : v$controlfile_record_section

Online Redo Log Files

§데이터베이스의 모든 Transaction이 저장되는 파일

§데이터베이스 Recovery에 사용

§포함 정보

  - Commit되지 않은 Transaction정보, Undo 데이터

  - Schema와 Ojbect 관리 구문

§Online Redo Log Group / Member

  - 최소 2개 이상의 Group이 필요

  - 각 Group은 1개 이상의 Member로 구성

  - LGWR은 Group내 모든 Member에 동일한 정보 기록

  - 각 Member는 동일한 Log Sequence와 크기를 가짐

§Log Switch

  - Redo Log Group간의 이동

  - 현재 Log file이 full일때 발생

§정보조회 : v$log, v$logfile

 

Database의 모든 Data는 논리적 공간에 할당

논리적 단위: Data block, Extent, Segment, Tablespace

Data Block

§오라클의 가장 작은 논리적인 Database 할당 단위

§오라클의 최소 I/O 단위

§Data Block에 Data(row)가 저장

§표준 Data Block Size

  - DB 생성시 db_block_size 파라미터로 설정

  - 일반적으로 4KB 또는 8KB로 설정

§비표준 Data Block Size

  - Tablespace 생성 시 BLOCKSIZE 구문으로

    default block size외에 지정가능

  - 2K, 4K, 8K, 16K, 32K 단위로 생성

  - 해당 blocksize의 subcache 영역이

    (db_nk_cache_size) 설정되어야 함

Extents

§하나 이상의 연속된 Data Block으로 구성

§Segment에 저장 영역을 할당하는 단위

 

Segment

§하나 이상의 Extent로 구성

§하나의 Segment는 하나의 User Object 에만 할당

§Segment 종류

  - Table / Index / LOB Segment

  - Temporary Segments

  - Undo Segments

 

Tablespace

§Segment를 포함하는 논리적 저장 영역

§물리적 level의 하나 이상의 Data file 또는 Temp file로 구성

§Tablespace 종류

  - SYSTEM Tablespace : Data Dictionary 등 DB관리용 메타정보 object 저장

  - SYSAUX Tablespace : Enterprise Manager 등 추가적인 DB 구성시 사용

  - UNDO Tablespace : Data 변경시 commit전까지 변경전 data 저장관리

  - TEMP Tablespace : Session이 유지되는 동안 사용하는 임시 데이터 저장

  - USER Tablespace : 사용자 생성 object 저장

---

SQL Server 아키텍처

Instance(메모리 영역 + relational/storage engine + 프로세스 영역) + Database(파일 영역)

---

SQL Server Engine 구성요소

---

Instance와 Database

오라클 - 하나의 인스턴스가 하나의 데이터베이스를 접근

SQL server - 하나의 인스턴스에 최대 32,767개의 데이터베이스를 사용

---

Memory

SQL Server는 thread 기반으로 프로세서 전용 메모리 영역(PGA)가 없음

Oracle		SQL Server		설명
SGA	Shared pool	Memory Pool	Procedure/Plan Cache	•SQL, Procedure 코드와 실행계획을 저장
	Data Buffer cache		Data buffer cache	•Data file의 Data block/page를 저장하는 메모리 영역
	Redo Log buffer		Log cache	•Data 변경내역을 저장하는 메모리 영역
PGA			Connection Context	•사용자 세션정보, 커서의 위치정보,  SP의 매개변수를 저장 •PGA 내에 Sort 등을 수행하는 SQL 작업 영역은 SQL Server의 경우 Data buffer에서 수행

---

Process

SQL Server는 윈도우 기반으로 thread가 oracle의 process 역할을 수행

Oracle	SQL Server	설명
Server Process	Worker Thread	Client 요청을 실행하고 결과를 전달
Process Monitor(PMON)	Open Data Services(ODS)	프로세스 레벨의 모니터링 및 리소스 복구 수행
System Monitor(SMON)	Database Cleanup/Shrinking	시스템 레벨의 모니터링과 인스턴스 복구
Database Writers(DBWn)	Lazy Writer	버퍼 캐시의 Dirty 버퍼를 데이터 파일에 기록
Log Writer(LGWR)	Log Writer	로그 버퍼 엔트리를 로그 파일에 기록
Checkpoint Processes(CKPT)	Database Checkpoint	데이터베이스 변경사항을 컨트롤 파일과 데이터파일에 기록
Archive Processes(ARCn)	N/A	SQL Server는 Transaction Log파일에 계속해서 기록
Recoverer(RECO)	Distributed Transaction Coordinateor(DTC)	분산 트랜잭션 과정에 발생한 문제를 해결
Job Queue(CJQ0, Jnnn)	SQL Agent	스케줄링 작업을 수행

Oracle

- pmon: process-level의 모니터링과 resource cleanup 수행

- smon: system-level의 모니터링과 cleanup 수행, instance 시작 시 recovery 수행

SQL Server

- SQLOS(SQL server Operating Service): SQL 엔진에서 memory, IO, 스케줄링 관리 등 수행

---

Storage Architecture

Oracle과 SQL Server 모두 물리적으로는 파일에 데이터 저장, 논리적 구조에 약간의 차이 있음

Oracle - ASM을 통한 Raw device도 사용 (ASM: 자동 스토리지 관리)

SQL Server

- 보안 및 가용성 측면에서 NTFS 권장

- raw device limitation) 1개 파티션에 1개의 데이터파일만 생성가능 / 파일 copy, move, delete 불가 / Windows NT Backup utility 사용 불가 / Datafile 자동확장 불가 등등

---

Data blocks, extents, segments

구분	Oracle	SQL Server
최소 할당 단위	Block	Page
Block/Page size	2K, 4K, 8K, 16K, 32K, 64K	8K 고정
Storage 할당 단위	Extents (multiple block)	Extents (multiple page)
Extent size	Variable	64K 고정
Extent 사용	Uniform (단일 object가 사용)	Uniform, Mixed (혼합으로 사용하다 object 크기가 64K보다 커지면 단일 할당)
오브젝트 저장 단위	Segment	Heap/Index
Segment 종류	Table, Index, Lob, Temp, Undo	Table, Index

---

temp, undo, log 영역

구분	Oracle	SQL Server	비고
TEMP 영역	Temporary tablespace	Tempdb	•Oracle의 경우 여러 개의 Temporary tablespace 생성하여 사용자별로 지정 가능하나 •SQL Server는 Tempdb가 전역 리소스로 모든 사용자의 임시 데이터를 저장
UNDO 영역	Undo tablespace	Transaction log file	•Oracle의 경우 Undo와 Redo 정보를 별도의 파일에 기록 관리하나 •SQL Server는 DB별로 Transaction log file에 모두 기록하므로 그에 따른 운영관리가 중요
Log 영역	Redo log file
	Archive log file	N/A	•SQL Server는 별도의 Archive 파일이 없으며, 로그 백업 전까지 Transaction log file에 계속해서 기록 •Database 복구모드 설정과 백업 스케줄링이 중요

SQL Server는 transaction log file에 변경 정보 모두 기록하므로 로그 영역의 운영 관리가 중요

---