[스크랩] [SQLNULL] Study - SQL Server Architecture

http://ddoung2.tistory.com/338

3 ~ 4개월전 발표를 하고 오래간만에 발표를 한것 같네요. 좋은 주말에 멀리까지 오셔서 같이 이야기 나누어 주신 모든 분들에게 감사드리며 발표 자료를 설명과 함께 올립니다.
본 발표 자료는 Professional SQL Server 2008 Internals and Troubleshooting 의 내용입니다. 

트랜잭션의 이해와 트랜잭션의 속성인 ACID에 대해서 살펴보고, SELECT 요청과 UPDATE 요청이 있을때 SQL 아키텍쳐 구성요서들이 어떻게 사용되는지와 이때 생성 또는 발생되는 Dirty Page와 CheckPoint와 LazyWriter와 마지막으로 트랜잭션 로그와 데이터베이스 복구간의 밀접한 관계를 보자.

트랜잭션은 한 단위로 처리되는 일련의 데이터베이스 처리 작업을 의미하며 이 작업은 무조건 모두 성공하거나 모두 실패를 해야 한다. 트랜잭션은 크게 4가지 속성을 가지고 있는데 아래와 같다.

 - 원자성(Atomicity) : 트랜잭션은 분리할 수 없는 하나의 단위이며 모두 수행되거나 모두 수행되지 않아야 한다. (All or Nothing)
 - 일관성(Consistency) : 트랜잭션에서 사용되는 모든 데이터는 일관되어야 한다.
 - 격리성(Isolation) : 트랜잭션은 다른 트랜잭션에 의해 영향을 받지도 주지도 않아야 한다.
 - 지속성(Durability) : 트랜잭션이 정상적으로 완료가 되었다면 시스템의 오류가 발생을 하였어도 완전히 원상 복구 될 수 있음을 의미한다.

트랜잭션의 종류는 아래와 같다.

 - 자동 트랜잭션(Automatic Transaction)
SQL Server에서 자동으로 시작되고 Commit되는 트랜잭션을 말한다.

 - 암시적 트랜잭션(Implicit Transaction)
암시적 트랜잭션은 SET IMPLICIT_TRANSACTION 명령으로 활성화를 시키며 활성화가 되었있다면 DDL구문이나 DML구문이 실행될 때 자동으로 시작되지만 사용자가 명시적으로 Commit 또는 Rollback 할 때까지 트랜잭션이 지속이 된다.

 - 명시적 트랜잭션(Explicit Transaction)
명시적 트랜잭션은 사용자에 의해 명시적으로 BEGIN TRAN으로 트랜잭션을 열고 Commit 또는 Rollback으로 완료되는 트랜잭션을 말한다.

 - 분산 트랜잭션(Distributed Transaction)
물리적으로 서로 다른 데이터베이스에 걸쳐 있는 트랜잭션을 분산 트랜잭션이라고 부른다. 이는 DTC를 활성화를 해야 하며 BEGIN DISTRIBUTED TRANSACTION 이라고 명시적으로 활성화를 시켜야 한다. 

SQL Server 내부 구성요서 들이며 SQL Server Engine은 크게 두개의 Engine으로 구분이 된다.

 - Relational Engine
관계형 엔진의 주요 기능은 쿼리를 실행하기 위해서 쿼리 옴티마이져와 쿼리 실행기가 포함되어 있기 때문에 포괄적으로 쿼리 프로세스라고도 불리우며 이는 쿼리 구문(Query syntax)을 체크하고 쿼리 트리(Query Trees)을 만들고 데이터베이스 꽃이라고 불리우는 옵티마이져(Optimizer)가 퀴리를 실행할 수 있게 플랜을 만들고 쿼리 실행기(Query Executor)가 그 쿼리를 실행을 한다.

 - Storage Engine
스토리지 엔진은 데이터에 대한 모든 I/O를 관리하는 액세드 메소드(Access Methods)와 SQL Server에서 사용하는 영역중 가장 많은 영역을 차지하고 있는 Buffer Pool을 관리하는 Buffer Manager와 트랜잭션 격리 수준을 유지하기 위하여 데이터 잠금을 처리하고 로그를 관리하는 Transaction Manager가 포함되어 있다.

 - SQL Server Network Interface
SNI(SQL Server Network Interface)는 클라이언트와 서버 간의 네트워크 연결을 설정하는 프로토콜 계층이다. SNI는 아래와 같은 프로토콜을 제공하고 있다.

 - Shared memory
SQL Server Engine와 동일한 컴퓨터에서 클라이언트로 연결할 때 사용되는 기본 프로토콜이다.

- TCP/IP
TCP/IP는 SQL Server에서 가장 일반적으로 사용되는 액세스 프로토콜이다. IP주소와 Port번호를 지정하여 SQL Server에 연결할 수 있으며, 기본 Instance에 기본 포트로 접속이 되며 기본 포트는 1433이며 인스턴스가 여러개가 설치되어 있다면 정확한 인스턴스 명을 기입해야 한다.

 - Named Pipes
고속 LAN(Local area Network)을 위해 개발이 되었으며 WAN(Wide area Network)와 같은 느린 네트워크에서는 엄청난 성능 저하의 원인이 될 수 있으며 명명된 파이프는 445 포트를 사용하기 때문에 사전에 방화벽에 해당 포트가 오픈되어 있는 확인 해야 한다.

 - VIA
가상 인터페이스 어댑터(Virtual Interface Adapter)는 두 시스템 사이에 고성능 통신을 가능하게 하는 프로토콜이며 별도의 하드웨어 장비가 필요로 하다.

 - TDS(Tabular Data Stream) Endpoints
Relational Engine이 SNI으로 응용 어플리케이션관 통신을 할때 TDS(Tabular Data Stream)이라는 통신 방식을 사용한다. 이 통신 방식은 원래 사이베이스에서 개발을 했지만 현재 MS 독적 프로토콜이며 TDS는 TCP/IP와 같은 프로토콜에 캡슐화되어 내장되는 방식이다.

 - Command Parser
Command Parser는 T-SQL 구문을 받아서 그 구문이 정상적인 구문인지 검사를 하고 오류가 있으면 syntax error를 클라이언트에 반환을 하며 그 구문이 정상적이면 Buffer Pool 영역의 Plan Cache를 검색하여 기존에 사용하고 있었던 Query Plan이 있다면 Query Executor로 없다면 Query Plan을 만들기 위해 쿼리문을 Query tree 로 쪼개어 Optimizer로 전달이 된다.

 - Optimizer
Optimizer는 SQL Server Team에서도 가장 아끼는 기능으로 가장 복잡하고 비밀스러운 기능 중에 하나다. SQL Server는 비용 기반 Optimizer로 가장 좋은 것을 찾는 것 보다는 가장 효율적인 것을 찾는데에 더 중점을 둔다. 왜냐하면 가장 좋은 실행 계획을 찾는 시간이 가장 느린 계획으로 실행하는 시간보다 더 오래 걸릴 수도 있기 때문이다.

 - Buffer Manager
버퍼 관리자란? SQL Server에서 사용하는 메모리를 관리하는 영역이다. 페이지에서 일부 행을 읽어야 할 때 버퍼 관리자는 메모리에 해당 페이지가 있는지 확인을 하고 있으면 그 페이지를 Access Methods로 전달을 하고 없으면 디스크에서 해당 페이지를 읽어와 메모리 Data Cache 영역에 그 페이지를 올린다. 여기에서 발생하는 대기중에 유심히 살펴봐야 할 대기 상태가 PAGEIOLATCH이다. 이는 디스크에서 메모리로 데이터 페이지를 이동시키는데 대기가 걸린 시간이며 이 대기 시간이 길다는 것은 디스크 병목현상이 있음을 암시하는 것이다.

 - Data Cache
데이터 캐시는 SQL Server내에서 메모리를 가장 많이 사용하는 영역이다. 데이터 캐시는 디스크에서 읽혀진 페이지가 저장되고 있는 영역을 말한다. 데이터 캐시는 sys.dm_os_buffer_descriptors라는 DMV로 확인 할 수 있다. 데이터 캐시에서 페이지가 유지되는 시간은 LRU 정책에 의해서 결정이 된다. LRU(Least recently used)정책은 운영체제의 페이지 교체 알고리즘 중 하나이며 메모리에서 페이징해야 할 페이지를 결정할 때 가장 사용이 적었던 페이지를 고르는 알고리즘이다.

보통 성능카운터에서 MSSQL$<instance>:Buffer Manager\Page Life Expectancy 카운터를 보면 데이터 캐시에서 페이지가 얼마나 오래 동안 지속이 되고 있는지 볼 수 있다. MS에서는 해당 카운터가 300초 이상이 유지되어야 한다고 하고 그 이하로 떨어져서 지속이 되고 있다면 메모리가 압박이 있다고 말하고 있다.

 - Transaction Manager
변경된 데이터가 디스크에 기록되기까지 유지되어야 하며 그 역활을 담당하는 것은 Transaction Manager이다. Transaction Manager에는 크게 두가지 요소가 있는데 Lock Manager와 Log Manager이다. Lock Manager는 데이터 동시성 및 트랜잭션 격리성을 보장하기 위하여 격리 수준을 설정하며 Log Manager는 트랜잭션 원자성과 지속성을 보장하기 위하여 존재를 한다. Access Methods는 변경된 내용을 코드로 만들어서 Transaction Manager에 요청을 하고 Transaction Manager는 Log Manager를 통해 그 내용들을 Transaction Log에 쓰는 작업을 하는데 이 작업을 writer-Ahead Logging(로그 미리쓰기)라고 한다. 이는 트랜잭션 속성중 원자성과 지속성을 보장하기 위하여 일어나는 작업이며 SQL Server가 시스템 장애로 부터 데이터를 복구할 수 있는 유일한 수단이 Transaction Log이다. 

 - Buffer Manager
버퍼 관리자는 변경할 데이터가 데이터 캐시에 없다면 이전 SELECT 요청과 마찬가지로 디스크에서 페이지를 읽어와 데이터 캐시에 올리고 데이터 캐시에 존재하는 페이지를 변경을 한다. 이때 디스크에 존재하는 페이지와 Buffer Pool에 존재하는 페이지가 달라지게 되고 이를 Dirty Page라고 부른다. 

 - Dirty page
Dirty page는 데이터 캐시에 존재를 하게 되고 데이터 캐시는 하드웨어의 물리적인 RAM에 존재를 하게 된다. 우리가 알고 있는 RAM은 전원 공급이 차단되면 가지고 있던 모든 데이터를 버리게 되는데 이때 디스크에 아직 기록이 되지 않은 Dirty page도 같이 버려지게 된다. 이를 복구하기 위해서 Transaction Log가 존재를 하는 것이고 Writer-Ahead Logging작업을 한것이다.

 - CheckPoint
Dirty page가 Buffer Pool 에서 디스크로 쓰여질 때는 CheckPoint가 발생하거나 LazyWriter가 발생했을 때다. CheckPoint는 퍼지알고리즘으로 발동이 되며 트랜잭션이 완료가 된 Dirty page를 물리적 디스크에 쓰게 된다. Checkpoint를 주기적으로 발생시키고 싶다면 SQL Server 구성옵션에서 Recovery Interval 옵션을 이용하면 된다. Checkpoint가 발생되는 주기를 보고 싶으면 시작 플래그에 3502를 주면 에러로그에 Checkpoint가 발생 시점 끝나는 시점을 기록하게 된다.

 - LazyWriter
Lazywriter은 Buffer가 부족할 때 발생하는 Thread이며 LazyWriter은 Checkpoint는 다르게 데이터 캐시에 존재하고 있는 Dirty page을 디스크에 기록하면서 완전 삭제를 한다. Checkpoint는 디스크에 기록하면 페이지를 Dirty page에서 더 이상 Dirty page가 아니라고 변경만 한다.

 - Checkpoint와 복구 시점
Roll Backup(undo) - Checkpoint가 발생하기 전에 시작된 트랜잭션이 있으면 취소
Roll Forward(redo) - Checkpoint가 발생하기 전에 완료된 트랜잭션이 있으면 다시 실행

시스템 장애가 있은 후 TRAN1, TRAN3 은 SQL Server 서비스가 올라오면서 해당 내용이 디스크에 쓰여지게 된다. 하지만 TRAN4은 시스템 장애가 발생할 당시 트랜잭션이 완료가 되지 않은 상태이기 때문에 모두 취소가 된다.