SQL 서버 Archi강의 (설명)

MS SQL Server는 클라이언트-서버 아키텍처를 사용합니다. MS SQL Server 프로세스는 클라이언트 애플리케이션이 요청을 보내면서 시작됩니다. SQL Server는 요청을 수락하고 처리한 후 처리된 데이터를 응답으로 보냅니다. 아래 그림에 나타난 전체 아키텍처를 자세히 살펴보겠습니다.

아래 그림에서 볼 수 있듯이 SQL Server에는 세 가지 주요 구성 요소가 있습니다. Archi강의:

1. 프로토콜 계층
2. 관계형 엔진
3. 스토리지 엔진

프로토콜 계층 – SNI

SQL Server 프로토콜 계층(SNI(Server Network Interface)라고도 함)은 세 가지 유형의 클라이언트-서버 아키텍처를 지원합니다. 각 프로토콜은 서로 다른 네트워크 시나리오에 사용됩니다. 이러한 프로토콜을 이해하는 것은 쿼리가 내부적으로 어떻게 처리되는지 살펴보기 전에 필수적입니다.

공유 메모리

이른 아침 대화 상황을 생각해 보세요. 톰과 그의 어머니는 같은 장소, 즉 집에 있습니다. 톰이 커피를 달라고 하면 어머니는 바로 커피를 내어줍니다. 마찬가지로 SQL Server는 클라이언트와 서버가 같은 컴퓨터에서 실행될 때 공유 메모리 프로토콜을 제공합니다. 둘 다 네트워크 오버헤드 없이 공유 메모리를 통해 통신합니다.

유추: Tom은 클라이언트에, Mom은 SQL 서버에, Home은 컴퓨터에, 그리고 언어적 의사소통은 공유 메모리 프로토콜에 해당합니다.

설정 참고 사항: In SQL Management Studio로컬 연결의 경우 "서버 이름" 옵션은 ".", "localhost", "127.0.0.1" 또는 "머신\인스턴스"일 수 있습니다.

TCP / IP

이제 톰이 10km 떨어진 커피숍에서 커피를 사고 싶어한다고 가정해 봅시다. 톰은 집에 있고 커피숍은 번화한 시장에 있습니다. 둘은 휴대전화 네트워크를 통해 통신합니다. 마찬가지로 SQL Server는 다음과 같은 기능을 제공합니다. TCP / IP 프로토콜 클라이언트와 SQL 서버가 네트워크로 연결된 서로 다른 컴퓨터에 있는 경우.

유추: Tom은 클라이언트에, 커피숍은 SQL 서버에, 집과 시장은 원격 위치에, 그리고 이동통신망은 TCP/IP 프로토콜에 해당합니다.

설정 참고 사항: SQL Management Studio에서 TCP/IP 연결의 "서버 이름" 옵션은 "서버의 컴퓨터\인스턴스"여야 합니다. SQL Server는 기본적으로 TCP/IP 연결에 1433 포트를 사용합니다.

명명된 파이프

마지막으로, 톰은 이웃인 시에라에게서 녹차를 사고 싶어합니다. 두 사람은 같은 물리적 위치에 있는 이웃이며, 내부 네트워크를 통해 소통합니다. 이와 유사하게, SQL Server는 클라이언트와 서버가 근거리 통신망(LAN)으로 연결될 때 명명된 파이프 프로토콜을 제공합니다.

유추: Tom은 클라이언트에, Sierra는 SQL Server에, 이웃 관계는 LAN에, 그리고 내부 네트워크는 명명된 파이프 프로토콜에 해당합니다.

설정 참고 사항: 명명된 파이프는 기본적으로 비활성화되어 있으며 SQL 구성 관리자를 통해 활성화해야 합니다.

TDS 란 무엇입니까?

이제 세 가지 유형의 클라이언트-서버 아키텍처가 명확해졌으니, TDS에 대해 살펴보겠습니다.

• TDS는 테이블 형식 데이터 스트림을 나타냅니다.
• 세 가지 프로토콜 모두 TDS 패킷을 사용합니다.
• TDS는 네트워크 패킷으로 캡슐화되어 클라이언트 컴퓨터에서 서버 컴퓨터로 데이터를 전송할 수 있습니다.
• TDS는 Sybase에서 처음 개발했으며 현재는 다른 회사가 소유하고 있습니다. Microsoft.

다음 표는 세 ​​가지 SQL Server 연결 프로토콜을 비교합니다.

제품 특장점	공유 메모리	TCP / IP	명명된 파이프
네트워크 범위	같은 기계	원격(WAN/인터넷)	LAN 전용
기본 포트	N/A	1433	445
성능	가장 빠름 (네트워크 오버헤드 없음)	우수함 (WAN에 최적화됨)	좋음 (LAN에 최적화됨)
기본적으로 활성화됨	가능	가능	아니
최상의 사용 사례	로컬 개발 및 테스트	프로덕션 원격 액세스	신뢰할 수 있는 LAN 환경

프로토콜 계층이 네트워크 통신을 처리하고 나면, SQL Server 아키텍처의 다음 단계는 쿼리 자체를 처리하는 것입니다. 바로 이 부분에서 관계형 엔진이 역할을 맡게 됩니다.

관계형 엔진

관계형 엔진은 쿼리 프로세서라고도 합니다. 이 엔진에는 쿼리가 수행해야 하는 작업과 가장 효율적인 실행 방법을 결정하는 SQL Server 구성 요소가 포함되어 있습니다. 관계형 엔진은 스토리지 엔진에서 데이터를 요청하고 반환된 결과를 처리하여 사용자 쿼리를 실행하는 역할을 담당합니다.

아키텍처 다이어그램에 나타난 것처럼 관계형 엔진은 세 가지 주요 구성 요소로 이루어져 있습니다.

CMD 파서

프로토콜 계층에서 수신된 데이터는 관계형 엔진으로 전달됩니다. CMD 파서는 쿼리 데이터를 수신하는 첫 번째 구성 요소입니다. CMD 파서의 주요 역할은 쿼리의 구문 및 의미 오류를 검사하고 쿼리 트리를 생성하는 것입니다.

구문 확인: 다른 모든 프로그래밍 언어와 마찬가지로 SQL Server에도 미리 정의된 키워드 및 문법 규칙 집합이 있습니다. SELECT, INSERT, UPDATE 등을 비롯한 여러 키워드가 미리 정의된 키워드 목록에 포함됩니다. CMD 파서는 입력이 이러한 규칙을 따르는지 확인합니다. 사용자의 입력이 예상 구문에서 벗어나면 파서는 오류를 반환합니다.

예: 러시아인이 일본 식당에 들어가 러시아어로 주문하는 상황을 생각해 보세요. 웨이터는 일본어만 알아듣기 때문에 주문을 처리할 수 없습니다. 마찬가지로, 사용자가 "SELECT" 대신 "SELECR"을 입력하면 CMD 파서는 해당 키워드를 인식하지 못하기 때문에 오류를 반환합니다.

의미 확인: 이 작업은 정규화 도구(Normalizer)에서 수행됩니다. 정규화 도구는 쿼리되는 열 이름, 테이블 이름 및 기타 객체가 스키마에 실제로 존재하는지 확인합니다. 존재하면 해당 객체를 쿼리에 바인딩합니다. 이 과정을 바인딩이라고도 합니다. 사용자 쿼리에 뷰가 포함된 경우, 정규화 도구는 이를 내부적으로 저장된 뷰 정의로 대체합니다.

예: 달리는 SELECT * from USER_ID 만약 USER_ID 테이블이 데이터베이스에 존재하지 않으면, 파서는 의미론적 검사 중에 오류를 발생시킬 것입니다.

쿼리 트리 만들기: 이 단계에서는 쿼리를 실행할 수 있는 다양한 방법을 나타내는 여러 실행 트리가 생성됩니다. 모든 트리는 동일한 원하는 출력을 생성합니다.

최적화

옵티마이저는 사용자의 쿼리에 대한 실행 계획을 생성합니다. 이 계획은 쿼리가 어떻게 실행될지 결정합니다. 모든 쿼리가 최적화되는 것은 아닙니다. 최적화는 SELECT, INSERT, DELETE, UPDATE와 같은 DML(데이터 수정 언어) 명령에 적용됩니다. CREATE, ALTER와 같은 DDL 명령은 최적화되지 않지만 내부 형식으로 컴파일됩니다.

쿼리 비용은 CPU 사용량, 메모리 사용량, 입출력 요구량 등의 요소를 기반으로 계산됩니다. 옵티마이저의 역할은 절대적으로 최적의 실행 계획을 찾는 것이 아니라, 비용 효율적인 가장 저렴한 실행 계획을 찾는 것입니다.

예: 온라인 은행 계좌를 개설한다고 가정해 보세요. 어떤 은행은 최대 2일이 걸립니다. 다른 20개의 은행 목록도 있는데, 각 은행의 처리 시간이 더 빠를 수도 있고 아닐 수도 있습니다. 20개 은행을 모두 검색한다고 해서 더 빠른 옵션을 찾을 수 있는 것은 아니며, 검색 자체에도 시간이 소요됩니다. 처음 검색한 은행을 이용하는 것이 더 나을 수도 있습니다. 이와 마찬가지로 SQL 옵티마이저는 쿼리 실행 시간을 최소화하기 위해 포괄적인 알고리즘과 휴리스틱 알고리즘을 사용합니다.

최적화 프로그램은 세 단계에 걸쳐 검색을 수행합니다.

0단계: 사소한 계획 찾기

이는 최적화 전 단계입니다. 일부 쿼리의 경우, 실행 가능한 계획이 하나만 존재하며 이를 단순 계획(trivial plan)이라고 합니다. 추가 검색은 동일한 실행 계획을 찾는 데 추가 비용만 발생하므로 더 이상 검색할 필요가 없습니다.

1단계: 거래 처리 계획 검색

여기에는 단순 실행 계획과 복잡 실행 계획 모두에 대한 검색이 포함됩니다. 단순 실행 계획 검색은 일반적으로 테이블당 하나의 인덱스로 제한되는 열 및 인덱스 데이터에 대한 통계 분석을 사용합니다. 단순 실행 계획을 찾을 수 없는 경우 테이블당 여러 인덱스를 사용하는 보다 복잡한 검색이 수행됩니다.

2단계: 병렬 처리 및 최적화

이전 전략들이 적절한 계획을 도출하지 못하면, 최적화 프로그램은 시스템의 처리 능력에 기반하여 병렬 처리 가능성을 탐색합니다. 병렬 처리가 불가능한 경우, 최종 최적화 단계가 시작되어 남은 모든 옵션을 활용하여 최적의 실행 계획을 찾아냅니다.

쿼리 실행자

쿼리 실행기는 스토리지 엔진의 액세스 메서드를 호출합니다. 이 메서드는 실행에 필요한 데이터 가져오기 로직이 포함된 실행 계획을 제공합니다. 스토리지 엔진에서 데이터를 수신하면 결과가 프로토콜 계층에 게시되어 최종 사용자에게 전송됩니다.

관계형 엔진이 쿼리 실행 방법을 결정한 후, 스토리지 엔진이 물리적 데이터 작업을 처리합니다. 이 계층은 데이터가 디스크에 저장되고, 캐시되고, 검색되는 방식을 관리합니다.

스토리지 엔진

스토리지 엔진은 디스크나 SAN과 같은 스토리지 시스템에 데이터를 저장하고 필요할 때 검색하는 역할을 담당합니다. 스토리지 엔진 구성 요소를 살펴보기 전에 데이터가 물리적으로 어떻게 저장되는지 이해하는 것이 중요합니다.

데이터 파일 및 익스텐트

데이터 파일은 물리적으로 데이터를 데이터 페이지 형태로 저장하며, 각 페이지의 크기는 8KB입니다. 이는 가장 작은 저장 단위입니다. SQL 서버데이터 페이지는 논리적으로 익스텐트(extent) 단위로 그룹화됩니다. 객체에 개별 페이지가 직접 할당되는 것이 아니라, 익스텐트를 통해 관리됩니다. 각 페이지에는 페이지 유형, 페이지 번호, 사용 공간, 여유 공간, 이전 및 다음 페이지를 가리키는 포인터와 같은 메타데이터를 포함하는 페이지 헤더(96바이트)가 있습니다.

파일 형식

기본 파일: 모든 데이터베이스에는 기본 파일이 하나 있습니다. 이 파일에는 테이블, 뷰, 트리거 및 기타 객체와 관련된 모든 중요한 데이터가 저장됩니다. 파일 확장자는 일반적으로 .mdf이지만 다른 확장자일 수도 있습니다.

보조 파일: 데이터베이스에는 여러 개의 보조 파일이 포함될 수도 있고 포함되지 않을 수도 있습니다. 이러한 보조 파일은 선택 사항이며 사용자별 데이터를 포함합니다. 확장자는 일반적으로 .ndf이지만 어떤 확장자든 사용할 수 있습니다.

로그 파일: 쓰기 선행 로그(Write-Ahead Logs)라고도 하며, 확장자는 .ldf입니다. 로그 파일은 트랜잭션 관리, 원치 않는 인스턴스 복구, 커밋되지 않은 트랜잭션의 롤백에 사용됩니다.

스토리지 엔진은 세 가지 주요 구성 요소로 이루어져 있으며, 각 구성 요소는 데이터 액세스 및 무결성 관리에 있어 특정 역할을 수행합니다.

접근 방법

액세스 메서드는 쿼리 실행기와 인터페이스 역할을 합니다. Buffer 관리자 또는 트랜잭션 로그. 자체적으로 실행을 수행하지는 않지만 쿼리 유형을 결정합니다.

• 쿼리가 다음과 같으면 SELECT 문(DML)그것은 전달됩니다 Buffer 추가 처리를 위한 관리자입니다.
• 쿼리가 다음과 같으면 SELECT 문 이외의 문(DDL 및 DML)그러면 해당 요청은 트랜잭션 관리자로 전달됩니다. 여기에는 주로 UPDATE, INSERT 및 DELETE 문이 포함됩니다.

Buffer 매니저

The Buffer 관리자는 플랜 캐시, 데이터 구문 분석 및 변경된 페이지 처리와 관련된 핵심 기능을 관리합니다.

계획 캐시

기존 쿼리 계획: The Buffer 관리자는 저장된 플랜 캐시에 실행 계획이 있는지 확인합니다. 실행 계획이 있는 경우 캐시된 쿼리 계획과 관련 데이터 캐시가 직접 사용됩니다.

첫 번째 캐시 계획: 최초 쿼리 실행 계획이 복잡한 경우 계획 캐시에 저장됩니다. 이렇게 하면 SQL Server가 다음에 동일한 쿼리를 수신할 때 더 빠른 가용성을 확보할 수 있습니다.

데이터 파싱: Buffer 캐시 및 데이터 저장소

The Buffer 관리자는 필요한 데이터에 대한 접근 권한을 제공합니다. 데이터가 캐시에 존재하는지 여부에 따라 두 가지 접근 방식이 가능합니다.

Buffer 캐시 – 소프트 파싱

The Buffer 관리자는 다음에서 데이터를 찾습니다. Buffer 캐시. 데이터가 캐시에 있으면 쿼리 실행기는 해당 데이터를 직접 사용합니다. 이렇게 하면 디스크 저장소에서 데이터를 가져오는 것보다 캐시에서 데이터를 가져오는 데 필요한 I/O 작업 수가 적어 성능이 향상됩니다.

데이터 저장 - 하드 파싱

데이터가 없는 경우 Buffer 캐시에서는 필요한 데이터를 디스크의 데이터 저장소에서 검색합니다. 검색된 데이터는 향후 사용을 위해 데이터 캐시에도 저장됩니다.

거래 관리자

트랜잭션 관리자는 액세스 방식이 쿼리가 SELECT 문이 아닌 것으로 판단할 때 호출됩니다. 트랜잭션 관리자는 여러 하위 구성 요소를 통해 데이터의 일관성과 내구성을 보장합니다.

로그 관리자

로그 관리자는 로그를 유지합니다. trac시스템에서 수행된 모든 업데이트는 트랜잭션 로그에 저장된 로그를 통해 기록됩니다. 각 로그 항목에는 트랜잭션 ID 및 데이터 수정 기록과 함께 로그 시퀀스 번호가 포함됩니다. 이 메커니즘은 tracks 커밋 및 롤백된 트랜잭션.

잠금 관리자

거래가 진행되는 동안 저장소에 저장된 관련 데이터는 잠금 상태가 됩니다. 잠금 관리자는 이 프로세스를 처리하여 데이터의 일관성과 격리성을 보장합니다. 이러한 속성은 ACID라고도 합니다.Atom정확성, 일관성, 격리성, 내구성).

실행과정

실행 과정은 다음 단계를 따릅니다.

1. 로그 관리자가 로깅을 시작하면 잠금 관리자가 관련 데이터를 잠급니다.
2. 데이터 사본은 다음 위치에 보관됩니다. Buffer 캐시.
3. 업데이트될 데이터의 사본은 로그에 보관됩니다. Buffer모든 이벤트는 데이터에 데이터를 업데이트합니다. Buffer.
4. 수정된 데이터를 저장하는 페이지를 페이지라고 합니다. 더티 페이지.

체크포인트 및 선행 로깅

체크포인트 프로세스는 약 1분마다 실행되며, 변경된 모든 페이지를 디스크에 기록하도록 표시합니다. 하지만 그 전에 페이지는 먼저 로그 파일의 데이터 페이지로 이동됩니다. Buffer 로그. 이 메커니즘은 쓰기 선행 로깅(Write-Ahead Logging)이라고 합니다. 변경된 페이지는 디스크에 기록된 후에도 캐시에 남아 있습니다.

게으른 Writer

SQL Server는 부하가 심해지고 새 트랜잭션을 위해 버퍼 메모리가 필요할 때 캐시에서 변경된 페이지를 해제합니다. 지연 로딩(Lazy Log)은 이러한 기능을 제공합니다. Writer LRU(최근 사용 빈도가 가장 낮은) 알고리즘을 사용하여 버퍼 풀에서 디스크로 페이지를 정리합니다.

SQL Server는 쿼리를 처음부터 끝까지 어떻게 처리하나요?

각 계층을 개별적으로 이해하는 것도 중요하지만, 계층들이 어떻게 함께 작동하는지 살펴보면 전체적인 그림을 명확히 파악할 수 있습니다. 클라이언트 애플리케이션이 SQL 쿼리를 전송하면 다음과 같은 순서로 진행됩니다.

The 프로토콜 계층 공유 메모리, TCP/IP 또는 명명된 파이프를 통해 요청을 수신하고 이를 TDS 패킷으로 래핑합니다. 관계형 엔진 그다음에는 CMD 파서가 구문과 의미를 검사하고, 최적화 프로그램이 가장 저렴한 실행 계획을 생성하며, 쿼리 실행기가 데이터 검색을 시작합니다.

쿼리 실행기는 다음을 호출합니다. 스토리지 엔진의 액세스 메서드는 SELECT 쿼리를 라우팅합니다. Buffer 트랜잭션 관리자에 대한 관리자 및 수정 쿼리입니다. Buffer 관리자가 플랜 캐시를 확인합니다. Buffer 캐시에 먼저 데이터를 읽어옵니다(소프트 파싱). 데이터가 캐시되어 있지 않으면 디스크에서 데이터를 읽어옵니다(하드 파싱). 쓰기 작업의 경우, 트랜잭션 관리자는 로그 관리자, 잠금 관리자 및 체크포인트 프로세스를 조정하여 ACID 규칙을 준수하도록 합니다.

스토리지 엔진이 요청된 데이터를 반환하면 관계형 엔진이 결과 집합의 형식을 지정하고 프로토콜 계층은 동일한 TDS 프로토콜을 통해 클라이언트 애플리케이션에 다시 전달합니다.

SQL Server 연결에 적합한 프로토콜을 선택하는 방법

올바른 프로토콜을 선택하는 것은 클라이언트와 서버 간의 물리적 관계뿐만 아니라 성능 요구 사항에 따라 달라집니다.

공유 메모리를 사용하세요 클라이언트 애플리케이션이 SQL Server와 동일한 머신에서 실행될 때 가장 빠른 옵션입니다. 네트워크 오버헤드가 전혀 발생하지 않기 때문입니다. 로컬 개발, 테스트 및 단일 머신 배포에 이상적입니다.

TCP/IP를 사용하세요 클라이언트와 서버가 WAN 또는 인터넷을 통해 연결된 서로 다른 컴퓨터에 있을 때 사용됩니다. 이는 프로덕션 환경에서 가장 일반적으로 사용되는 프로토콜입니다. SQL Server는 기본적으로 1433번 포트에서 수신 대기하며, 이 프로토콜은 TLS를 통한 암호화 연결을 지원합니다.

명명된 파이프를 사용하세요 클라이언트와 서버가 동일한 신뢰할 수 있는 LAN에 있고 내부 네트워크 성능이 우선시될 때 명명된 파이프를 사용할 수 있습니다. 명명된 파이프는 기본적으로 비활성화되어 있으며 SQL Server 구성 관리자를 통해 활성화해야 합니다. 최신 배포 환경에서는 사용 빈도가 낮지만 기존 인트라넷 애플리케이션에는 여전히 유용합니다.

자주 묻는 질문