MS SQL Server-아키텍처

이해하기 쉽도록 SQL Server의 아키텍처를 다음과 같이 분류했습니다.

일반 아키텍처
메모리 아키텍처
데이터 파일 아키텍처
로그 파일 아키텍처

일반 아키텍처

Client − 요청이 시작된 곳.

Query − 고급 언어 인 SQL 쿼리.

Logical Units − 키워드, 표현 및 연산자 등

N/W Packets − 네트워크 관련 코드.

Protocols − SQL Server에는 4 개의 프로토콜이 있습니다.

공유 메모리 (로컬 연결 및 문제 해결 목적).
명명 된 파이프 (LAN 연결에있는 연결 용).
TCP / IP (WAN 연결에있는 연결 용).
VIA-Virtual Interface Adapter (공급 업체에서 설정하려면 특수 하드웨어가 필요하며 SQL 2012 버전에서 더 이상 사용되지 않음)

Server − SQL 서비스가 설치되고 데이터베이스가있는 위치.

Relational Engine− 여기에서 실제 실행이 이루어집니다. 여기에는 쿼리 파서, 쿼리 최적화 프로그램 및 쿼리 실행기가 포함됩니다.

Query Parser (Command Parser) and Compiler (Translator) − 이것은 질의의 구문을 확인하고 질의를 기계어로 변환합니다.

Query Optimizer − 쿼리, 통계, Algebrizer 트리를 입력으로 받아 실행 계획을 출력으로 준비합니다.

Execution Plan − 쿼리 실행의 일부로 수행 할 모든 단계의 순서를 포함하는 로드맵과 같습니다.

Query Executor − 여기에서 실행 계획의 도움으로 쿼리가 단계별로 실행되고 스토리지 엔진과 연결됩니다.

Storage Engine − 스토리지 시스템 (디스크, SAN 등)의 데이터 저장 및 검색, 데이터 조작, 트랜잭션 잠금 및 관리를 담당합니다.

SQL OS− 이것은 호스트 컴퓨터 (Windows OS)와 SQL Server 사이에 있습니다. 데이터베이스 엔진에서 수행되는 모든 작업은 SQL OS에서 처리됩니다. SQL OS는 블로킹 및 잠금 구조를 사용하여 버퍼 풀, 로그 버퍼 및 교착 상태 감지를 처리하는 메모리 관리 등 다양한 운영 체제 서비스를 제공합니다.

Checkpoint Process− 체크 포인트는 버퍼 캐시의 모든 더티 페이지 (수정 된 페이지)를 물리 디스크에 쓰는 내부 프로세스입니다. 이 외에도 로그 버퍼의 로그 레코드를 실제 파일에 기록합니다. 버퍼 캐시에서 데이터 파일로 더티 페이지를 쓰는 것을 더티 페이지 강화라고도합니다.

전용 프로세스이며 특정 간격으로 SQL Server에 의해 자동으로 실행됩니다. SQL Server는 각 데이터베이스에 대해 개별적으로 검사 점 프로세스를 실행합니다. 검사 점은 예기치 않은 종료 또는 시스템 충돌 \ 실패시 SQL Server의 복구 시간을 줄이는 데 도움이됩니다.

SQL Server의 검사 점

SQL Server 2012에는 네 가지 유형이 있습니다. checkpoints −

Automatic − 이것은 SQL Server 데이터베이스가 Recovery Interval − Server Configuration Option에 정의 된 시간 제한 내에 복구 될 수 있는지 확인하기 위해 백그라운드에서 프로세스로 실행되는 가장 일반적인 체크 포인트입니다.
Indirect− 이는 SQL Server 2012의 새로운 기능입니다.이 기능은 백그라운드에서도 실행되지만 옵션이 구성된 특정 데이터베이스에 대한 사용자 지정 대상 복구 시간을 충족하기 위해 실행됩니다. 주어진 데이터베이스에 대한 Target_Recovery_Time을 선택하면 서버에 대해 지정된 복구 간격을 무시하고 해당 DB에서 자동 체크 포인트를 방지합니다.
Manual− 이것은 다른 T-SQL 문과 마찬가지로 실행되며, 일단 체크 포인트 명령을 실행하면 완료 될 때까지 실행됩니다. 수동 체크 포인트는 현재 데이터베이스에 대해서만 실행됩니다. 선택 사항 인 Checkpoint_Duration을 지정할 수도 있습니다.이 기간은 체크 포인트를 완료 할 시간을 지정합니다.
Internal− 사용자는 내부 체크 포인트를 제어 할 수 없습니다. 다음과 같은 특정 작업에서 발행
- 종료는 종료가 깨끗하지 않은 경우 (nowait로 종료)를 제외하고 모든 데이터베이스에서 검사 점 작업을 시작합니다.
- 복구 모델이 Full \ Bulk-logged에서 Simple로 변경된 경우.
- 데이터베이스를 백업하는 동안.
- DB가 단순 복구 모델 인 경우 로그가 70 % 가득 차거나 서버 옵션 복구 간격에 따라 체크 포인트 프로세스가 자동으로 실행됩니다.
- 데이터 \ 로그 파일을 추가하거나 제거하는 데이터베이스 변경 명령도 검사 점을 시작합니다.
- 체크 포인트는 DB의 복구 모델이 대량 로그되고 최소 로그 작업이 수행 될 때도 발생합니다.
- DB 스냅 샷 생성.
Lazy Writer Process− Lazy writer는 버퍼 풀의 메모리를 확보해야하기 때문에 완전히 다른 이유로 더티 페이지를 디스크로 푸시합니다. 이것은 SQL 서버가 메모리 부족 상태에있을 때 발생합니다. 내가 아는 한, 이것은 내부 프로세스에 의해 제어되며 설정이 없습니다.

SQL Server는 지속적으로 메모리 사용량을 모니터링하여 리소스 경합 (또는 가용성)을 평가합니다. 그것의 임무는 항상 사용 가능한 일정량의 여유 공간이 있는지 확인하는 것입니다. 이 프로세스의 일부로 이러한 리소스 경합을 발견하면 Lazy Writer가 더티 페이지를 디스크에 기록하여 메모리의 일부 페이지를 해제하도록 트리거합니다. LRU (Least Recent Used) 알고리즘을 사용하여 디스크로 플러시 할 페이지를 결정합니다.

Lazy Writer가 항상 활성 상태이면 메모리 병목 현상을 나타낼 수 있습니다.

메모리 아키텍처

다음은 메모리 아키텍처의 주요 특징 중 일부입니다.

모든 데이터베이스 소프트웨어의 기본 설계 목표 중 하나는 디스크 읽기 및 쓰기가 가장 리소스 집약적 인 작업이기 때문에 디스크 I / O를 최소화하는 것입니다.
Windows의 메모리는 커널 모드 (OS 모드) 및 사용자 모드 (SQL Server와 같은 응용 프로그램)에서 공유하는 가상 주소 공간으로 호출 할 수 있습니다.
SQL Server "사용자 주소 공간"은 MemToLeave 및 Buffer Pool의 두 영역으로 나뉩니다.
MTL (MemToLeave) 및 BPool (버퍼 풀)의 크기는 시작 중에 SQL Server에 의해 결정됩니다.
Buffer managementI / O 효율성을 높이기위한 핵심 구성 요소입니다. 버퍼 관리 구성 요소는 데이터베이스 페이지에 액세스하고 업데이트하는 버퍼 관리자와 데이터베이스 파일 I / O를 줄이기위한 버퍼 풀의 두 가지 메커니즘으로 구성됩니다.
버퍼 풀은 여러 섹션으로 더 나뉩니다. 가장 중요한 것은 버퍼 캐시 (데이터 캐시라고도 함)와 프로 시저 캐시입니다.Buffer cache자주 액세스하는 데이터를 캐시에서 검색 할 수 있도록 데이터 페이지를 메모리에 보관합니다. 대안은 디스크에서 데이터 페이지를 읽는 것입니다. 캐시에서 데이터 페이지를 읽으면 메모리에서 데이터를 검색하는 것보다 본질적으로 느린 필수 I / O 작업 수를 최소화하여 성능을 최적화합니다.
Procedure cache저장 프로 시저 및 쿼리 실행 계획을 유지하여 쿼리 계획을 생성해야하는 횟수를 최소화합니다. DBCC PROCCACHE 문을 사용하여 프로 시저 캐시 내의 크기 및 활동에 대한 정보를 찾을 수 있습니다.

버퍼 풀의 다른 부분은 다음과 같습니다.

System level data structures − 데이터베이스 및 잠금에 대한 SQL Server 인스턴스 수준 데이터를 보관합니다.
Log cache − 트랜잭션 로그 페이지를 읽고 쓰기 위해 예약되어 있습니다.
Connection context− 인스턴스에 대한 각 연결에는 현재 연결 상태를 기록하기위한 작은 메모리 영역이 있습니다. 이 정보에는 저장 프로 시저 및 사용자 정의 함수 매개 변수, 커서 위치 등이 포함됩니다.
Stack space − Windows는 SQL Server가 시작한 각 스레드에 대해 스택 공간을 할당합니다.

데이터 파일 아키텍처

데이터 파일 아키텍처에는 다음과 같은 구성 요소가 있습니다.

파일 그룹

데이터베이스 파일은 할당 및 관리 목적으로 파일 그룹으로 함께 그룹화 할 수 있습니다. 파일은 둘 이상의 파일 그룹의 구성원이 될 수 없습니다. 로그 파일은 파일 그룹의 일부가 아닙니다. 로그 공간은 데이터 공간과 별도로 관리됩니다.

SQL Server에는 기본 및 사용자 정의의 두 가지 유형의 파일 그룹이 있습니다. 기본 파일 그룹에는 기본 데이터 파일과 다른 파일 그룹에 특별히 지정되지 않은 기타 파일이 포함됩니다. 시스템 테이블의 모든 페이지는 1 차 파일 그룹에 할당됩니다. 사용자 정의 파일 그룹은 create database 또는 alter database 문에서 file group 키워드를 사용하여 지정된 파일 그룹입니다.

각 데이터베이스에서 하나의 파일 그룹이 기본 파일 그룹으로 작동합니다. SQL Server가 파일 그룹이 생성 될 때 지정되지 않은 테이블이나 인덱스에 페이지를 할당하면 페이지는 기본 파일 그룹에서 할당됩니다. 기본 파일 그룹을 한 파일 그룹에서 다른 파일 그룹으로 전환하려면 db_owner 고정 db 역할이 있어야합니다.

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

파일

데이터베이스에는 기본 데이터 파일, 보조 데이터 파일 및 로그 파일의 세 가지 유형의 파일이 있습니다. 기본 데이터 파일은 데이터베이스의 시작점이며 데이터베이스의 다른 파일을 가리 킵니다.

모든 데이터베이스에는 하나의 기본 데이터 파일이 있습니다. 기본 데이터 파일의 확장자를 지정할 수 있지만 권장 확장자는 다음과 같습니다..mdf. 보조 데이터 파일은 해당 데이터베이스에있는 기본 데이터 파일이 아닌 파일입니다. 일부 데이터베이스에는 여러 보조 데이터 파일이있을 수 있습니다. 일부 데이터베이스에는 단일 보조 데이터 파일이 없을 수 있습니다. 보조 데이터 파일의 권장 확장자는 다음과 같습니다..ndf.

로그 파일에는 데이터베이스를 복구하는 데 사용되는 모든 로그 정보가 들어 있습니다. 데이터베이스에는 하나 이상의 로그 파일이 있어야합니다. 하나의 데이터베이스에 대해 여러 로그 파일을 가질 수 있습니다. 로그 파일의 권장 확장자는 다음과 같습니다..ldf.

데이터베이스에있는 모든 파일의 위치는 데이터베이스의 마스터 데이터베이스와 기본 파일에 모두 기록됩니다. 대부분의 경우 데이터베이스 엔진은 마스터 데이터베이스의 파일 위치를 사용합니다.

파일에는 논리적 및 물리적 두 가지 이름이 있습니다. 논리 이름은 모든 T-SQL 문에서 파일을 참조하는 데 사용됩니다. 물리적 이름은 OS_file_name이며 OS 규칙을 따라야합니다. 데이터 및 로그 파일은 FAT 또는 NTFS 파일 시스템에 배치 할 수 있지만 압축 파일 시스템에는 배치 할 수 없습니다. 하나의 데이터베이스에 최대 32,767 개의 파일이있을 수 있습니다.

범위

익스텐트는 테이블과 인덱스에 공간이 할당되는 기본 단위입니다. 익스텐트는 8 개의 연속 페이지 또는 64KB입니다. SQL Server에는 균일 및 혼합이라는 두 가지 유형의 범위가 있습니다. 균일 한 범위는 단일 객체로만 구성됩니다. 혼합 된 범위는 최대 8 개의 개체에서 공유됩니다.

페이지

MS SQL Server에서 데이터 저장의 기본 단위입니다. 페이지 크기는 8KB입니다. 각 페이지의 시작은 페이지 유형, 페이지의 여유 공간 및 페이지를 소유 한 오브젝트의 오브젝트 ID와 같은 시스템 정보를 저장하는 데 사용되는 96 바이트 헤더입니다. SQL Server에는 9 가지 유형의 데이터 페이지가 있습니다.

Data − text, ntext 및 image 데이터를 제외한 모든 데이터가있는 데이터 행.
Index − 색인 항목.
Tex\Image − 텍스트, 이미지 및 ntext 데이터.
GAM − 할당 된 익스텐트에 대한 정보.
SGAM − 시스템 레벨에서 할당 된 익스텐트에 대한 정보.
Page Free Space (PFS) − 페이지에서 사용 가능한 여유 공간에 대한 정보.
Index Allocation Map (IAM) − 테이블 또는 인덱스에서 사용하는 익스텐트에 대한 정보.
Bulk Changed Map (BCM) − 마지막 백업 로그 문 이후 대량 작업에 의해 수정 된 익스텐트에 대한 정보.
Differential Changed Map (DCM) − 마지막 백업 데이터베이스 명령문 이후 변경된 익스텐트에 대한 정보.

로그 파일 아키텍처

SQL Server 트랜잭션 로그는 트랜잭션 로그가 로그 레코드 문자열 인 것처럼 논리적으로 작동합니다. 각 로그 레코드는 LSN (로그 시퀀스 번호)으로 식별됩니다. 각 로그 레코드에는 자신이 속한 트랜잭션의 ID가 포함됩니다.

데이터 수정에 대한 로그 레코드는 수행 된 논리 연산을 기록하거나 수정 된 데이터의 전후 이미지를 기록합니다. 이전 이미지는 작업이 수행되기 전 데이터의 복사본입니다. 이후 이미지는 작업이 수행 된 후 데이터의 복사본입니다.

작업을 복구하는 단계는 로그 레코드 유형에 따라 다릅니다.

논리적 작업이 기록되었습니다.
- 논리 연산을 롤 포워드하기 위해 연산이 다시 수행됩니다.
- 논리 연산을 롤백하기 위해 역 논리 연산이 수행됩니다.
이미지 기록 전후.
- 작업을 롤 포워드하기 위해 이후 이미지가 적용됩니다.
- 작업을 롤백하려면 이전 이미지가 적용됩니다.

다양한 유형의 작업이 트랜잭션 로그에 기록됩니다. 이러한 작업에는 다음이 포함됩니다.

각 거래의 시작과 끝.
모든 데이터 수정 (삽입, 업데이트 또는 삭제). 여기에는 시스템 테이블을 포함하여 모든 테이블에 대한 시스템 저장 프로 시저 또는 데이터 정의 언어 (DDL) 문에 의한 변경이 포함됩니다.
모든 범위 및 페이지 할당 또는 할당 해제.
테이블 또는 인덱스 생성 또는 삭제.

롤백 작업도 기록됩니다. 각 트랜잭션은 명시 적 롤백 문으로 인한 롤백을 지원하거나 오류가 발생한 경우 충분한 로그 공간이 있는지 확인하기 위해 트랜잭션 로그에 공간을 예약합니다. 이 예약 된 공간은 트랜잭션이 완료되면 해제됩니다.

마지막으로 기록 된 로그 레코드로의 성공적인 데이터베이스 전체 롤백을 위해 있어야하는 첫 번째 로그 레코드의 로그 파일 섹션을 로그의 활성 부분 또는 활성 로그라고합니다. 이것은 데이터베이스의 전체 복구에 필요한 로그 섹션입니다. 활성 로그의 어떤 부분도 잘릴 수 없습니다. 이 첫 번째 로그 레코드의 LSN을 최소 복구 LSN (Min LSN)이라고합니다.

SQL Server 데이터베이스 엔진은 각 실제 로그 파일을 내부적으로 여러 가상 로그 파일로 나눕니다. 가상 로그 파일에는 고정 된 크기가 없으며 실제 로그 파일에 대한 고정 된 수의 가상 로그 파일이 없습니다.

데이터베이스 엔진은 로그 파일을 만들거나 확장하는 동안 가상 로그 파일의 크기를 동적으로 선택합니다. 데이터베이스 엔진은 적은 수의 가상 파일을 유지하려고합니다. 가상 로그 파일의 크기 또는 수는 관리자가 구성하거나 설정할 수 없습니다. 가상 로그 파일이 시스템 성능에 영향을 미치는 유일한 경우는 물리적 로그 파일이 작은 크기 및 growth_increment 값으로 정의되는 경우입니다.

크기 값은 로그 파일의 초기 크기이고 growth_increment 값은 새 공간이 필요할 때마다 파일에 추가되는 공간의 양입니다. 많은 작은 증분으로 인해 로그 파일이 큰 크기로 커지면 많은 가상 로그 파일을 갖게됩니다. 이로 인해 데이터베이스 시작 속도가 느려지고 백업 및 복원 작업도 기록됩니다.

로그 파일에 필요한 최종 크기에 가까운 크기 값을 할당하고 growth_increment 값도 비교적 큰 것이 좋습니다. SQL Server는 관련 로그 레코드가 디스크에 기록되기 전에 데이터 수정 사항이 디스크에 기록되지 않도록하는 미리 쓰기 로그 (WAL)를 사용합니다. 이것은 트랜잭션에 대한 ACID 속성을 유지합니다.