SQL 서버 Archi강의 (설명)

구성/설치 데스크의 참고 사항:

• 명명된 파이프를 통한 연결의 경우. 이 옵션은 기본적으로 비활성화되어 있으며 SQL 구성 관리자를 통해 활성화해야 합니다.

TDS 란 무엇입니까?

이제 우리는 세 가지 유형의 클라이언트-서버가 있다는 것을 알았습니다. Archi강의를 통해 TDS를 잠깐 살펴보겠습니다.

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

관계형 엔진

관계형 엔진은 쿼리 프로세서라고도 합니다. 그것은 SQL 서버 쿼리가 정확히 수행해야 하는 작업과 쿼리를 가장 잘 수행할 수 있는 방법을 결정하는 구성 요소입니다. 스토리지 엔진에서 데이터를 요청하고 반환된 결과를 처리하여 사용자 쿼리 실행을 담당합니다.

에 묘사 된대로 Archi구조 다이어그램이 있습니다 3가지 주요 구성요소 관계형 엔진의 구성 요소를 자세히 살펴보겠습니다.

CMD 파서

프로토콜 계층에서 수신된 데이터는 관계형 엔진으로 전달됩니다. "CMD 파서" 쿼리 데이터를 수신하는 관계형 엔진의 첫 번째 구성 요소입니다. CMD Parser의 주요 작업은 쿼리를 확인하는 것입니다. 구문 및 의미 오류. 마지막으로, 그것은 쿼리 트리 생성. 자세히 논의해보자.

구문 확인:

• 다른 모든 프로그래밍 언어와 마찬가지로 MS SQL에도 사전 정의된 키워드 세트가 있습니다. 또한 SQL Server에는 SQL Server가 이해하는 자체 문법이 있습니다.
• SELECT, INSERT, UPDATE 및 기타 여러 가지가 MS SQL 사전 정의 키워드 목록에 속합니다.
• CMD Parser는 구문 검사를 수행합니다. 사용자의 입력이 이러한 언어 구문이나 문법 규칙을 따르지 않는 경우 오류를 반환합니다.

예: 러시아인이 일본 식당에 갔다고 가정 해 보겠습니다. 그는 러시아어로 패스트푸드를 주문합니다. 불행히도 웨이터는 일본어만 이해합니다. 가장 확실한 결과는 무엇입니까?

대답은 – 웨이터가 주문을 더 이상 처리할 수 없다는 것입니다.

SQL Server가 허용하는 문법이나 언어에 차이가 있어서는 안 됩니다. 있는 경우 SQL Server는 이를 처리할 수 없으므로 오류 메시지를 반환합니다.

우리는 다가오는 튜토리얼에서 MS SQL 쿼리에 대해 더 많이 배울 것입니다. 그러나 아래에서 가장 기본적인 쿼리 구문을 고려하십시오.

SELECT * from <TABLE_NAME>;

이제 구문이 수행하는 작업에 대한 인식을 얻으려면 사용자가 아래와 같이 기본 쿼리를 실행한다고 가정해 보겠습니다.

SELECR * from <TABLE_NAME>

사용자는 'SELECT' 대신 'SELECR'을 입력했습니다.

결과 : CMD 구문 분석기는 이 명령문을 구문 분석하고 오류 메시지를 표시합니다. “SELECR”은 미리 정의된 키워드 이름과 문법을 따르지 않습니다. 여기서 CMD Parser는 "SELECT"를 예상했습니다.

의미 확인:

• 이는 다음에 의해 수행됩니다. 노멀 라이저.
• 가장 간단한 형태로 쿼리하는 Column명, Table명이 Schema에 존재하는지 확인한다. 그리고 존재하는 경우 쿼리에 바인딩합니다. 이는 다음으로도 알려져 있습니다. 구속력이있는.
• 사용자 쿼리에 VIEW가 포함되어 있으면 복잡성이 증가합니다. Normalizer는 내부적으로 저장된 뷰 정의와 훨씬 더 많은 것을 사용하여 대체를 수행합니다.

아래 예를 통해 이를 이해해 보겠습니다.

SELECT * from USER_ID

결과 : CMD 구문 분석기는 의미 검사를 위해 이 명령문을 구문 분석합니다. 요청된 테이블(USER_ID)이 존재하지 않기 때문에 노멀라이저가 해당 테이블을 찾을 수 없으므로 파서는 오류 메시지를 표시합니다.

쿼리 트리 만들기:

• 이 단계에서는 쿼리를 실행할 수 있는 다양한 실행 트리를 생성합니다.
• 모든 다른 트리는 동일한 원하는 출력을 갖습니다.

최적화

최적화 프로그램의 작업은 사용자 쿼리에 대한 실행 계획을 만드는 것입니다. 이는 사용자 쿼리가 실행되는 방법을 결정하는 계획입니다.

모든 쿼리가 최적화되는 것은 아닙니다. SELECT, INSERT, DELETE 및 UPDATE와 같은 DML(데이터 수정 언어) 명령에 대한 최적화가 수행됩니다. 이러한 쿼리는 먼저 표시된 다음 최적화 프로그램으로 전송됩니다. CREATE 및 ALTER와 같은 DDL 명령은 최적화되지 않고 대신 내부 형식으로 컴파일됩니다. 쿼리 비용은 CPU 사용량, 메모리 사용량, 입력/출력 요구 사항 등의 요소를 기준으로 계산됩니다.

옵티마이저의 역할은 다음을 찾는 것입니다. 최고는 아니지만 가장 저렴하고 비용 효율적인 실행 계획입니다.

Optimizer의 기술적인 세부 사항을 살펴보기 전에 아래의 실제 예를 고려하십시오.

예:

온라인 은행 계좌를 개설하고 싶다고 가정해 보겠습니다. 계좌를 개설하는 데 최대 2일이 걸리는 은행에 대해 이미 알고 계십니다. 하지만 20개의 다른 은행 목록도 있는데, 이 목록은 2일 이내에 완료될 수도 있고 완료되지 않을 수도 있습니다. 이러한 은행과 협력을 시작하여 2일 이내에 소요되는 은행을 결정할 수 있습니다. 이제는 2일 이내에 은행을 찾을 수 없으며 검색 활동 자체로 인해 추가 시간 손실이 발생합니다. 첫 번째 은행에 계좌를 개설하는 것이 더 나았을 것입니다.

결론 : 현명하게 선택하는 것이 더 중요합니다. 정확히 말해서, 어떤 것을 선택해야 합니까? 옵션이 가장 저렴하지 않고 가장 좋습니다.

마찬가지로 MS도 SQL Optimizer는 내장된 포괄적/휴리스틱 알고리즘에서 작동합니다. 목표는 쿼리 실행 시간을 최소화하는 것입니다. 모든 최적화 알고리즘은 타당성 Microsoft 그리고 비밀. 이기는하지만, 다음은 MS SQL Optimizer가 수행하는 상위 수준 단계입니다. 최적화 검색은 아래 다이어그램에 표시된 대로 세 단계를 따릅니다.

0단계: 사소한 계획 검색:

• 이것은라고도합니다. 사전 최적화 단계.
• 어떤 경우에는 실용적이고 실행 가능한 계획이 하나뿐일 수 있는데, 이를 사소한 계획이라고 합니다. 최적화된 계획을 만들 필요가 없습니다. 그 이유는 더 많이 검색하면 동일한 런타임 실행 계획을 찾게 되기 때문입니다. 최적화된 계획을 검색하는 데 드는 추가 비용도 전혀 필요하지 않습니다.
• 사소한 계획이 발견되지 않으면 1^st 단계가 시작됩니다.

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

• 여기에는 다음 검색이 포함됩니다. 단순하고 복잡한 계획.
• 단순 계획 검색: Query에 포함된 컬럼 및 인덱스의 과거 데이터를 통계 분석에 사용합니다. 이는 일반적으로 테이블당 하나의 인덱스로 구성되지만 이에 국한되지는 않습니다.
• 그래도, 간단한 플랜이 발견되지 않으면, 더 복잡한 플랜이 검색됩니다. 여기에는 테이블당 여러 인덱스가 포함됩니다.

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

• 위의 전략 중 어느 것도 작동하지 않으면 Optimizer는 병렬 처리 가능성을 검색합니다. 이는 기계의 처리 기능 및 구성에 따라 다릅니다.
• 그래도 가능하지 않으면 최종 최적화 단계가 시작됩니다. 이제 최종 최적화 목표는 쿼리를 최선의 방법으로 실행하기 위한 다른 가능한 모든 옵션을 찾는 것입니다. 최종 최적화 단계 Algorithms are Microsoft 예의.

쿼리 실행자

쿼리 실행기 호출 액세스 방법. 실행에 필요한 데이터 가져오기 로직에 대한 실행 계획을 제공합니다. Storage Engine에서 데이터가 수신되면 결과가 프로토콜 계층에 게시됩니다. 마지막으로 데이터가 최종 사용자에게 전송됩니다.

스토리지 엔진

스토리지 엔진의 작업은 디스크나 SAN과 같은 스토리지 시스템에 데이터를 저장하고 필요할 때 데이터를 검색하는 것입니다. 스토리지 엔진에 대해 자세히 알아보기 전에 데이터가 스토리지 엔진에 어떻게 저장되는지 살펴보겠습니다. 데이터베이스 and 사용 가능한 파일 유형.

데이터 파일 및 범위:

데이터 파일은 물리적으로 데이터 페이지 형태로 데이터를 저장하며, 각 데이터 페이지의 크기는 8KB로 SQL Server에서 가장 작은 저장 단위를 구성합니다. 이러한 데이터 페이지는 논리적으로 그룹화되어 익스텐트를 형성합니다. SQL Server에서는 페이지에 개체가 할당되지 않습니다.

객체의 유지 관리는 익스텐트를 통해 수행됩니다. 페이지에는 페이지 유형, 페이지 번호, 사용된 공간 크기, 여유 공간 크기, 다음 페이지와 이전 페이지에 대한 포인터 등 페이지에 대한 메타데이터 정보를 전달하는 96바이트 크기의 페이지 헤더라는 섹션이 있습니다. , 등.

파일 형식

1. 기본 파일

• 모든 데이터베이스에는 하나의 기본 파일이 포함되어 있습니다.
• 여기에는 테이블, 뷰, 트리거 등과 관련된 모든 중요한 데이터가 저장됩니다.
• 확장자는 .MDF 일반적으로 확장이 가능합니다.

2. 보조 파일

• 데이터베이스에는 여러 개의 보조 파일이 포함될 수도 있고 포함되지 않을 수도 있습니다.
• 이는 선택 사항이며 사용자별 데이터를 포함합니다.
• 확장자는 .나프 일반적으로 확장이 가능합니다.

3. 로그 파일

• 미리 쓰기 로그라고도 합니다.
• 확장자는 .ldf
• 거래 관리에 사용됩니다.
• 이는 원치 않는 인스턴스를 복구하는 데 사용됩니다. 커밋되지 않은 트랜잭션에 대한 롤백의 중요한 작업을 수행합니다.

스토리지 엔진에는 3가지 구성요소가 있습니다. 자세히 살펴보겠습니다.

접근 방법

이는 쿼리 실행기와 Buffer 관리자/트랜잭션 로그.

접근 방법 자체는 어떠한 실행도 하지 않습니다.

첫 번째 작업은 쿼리가 다음과 같은지 확인하는 것입니다.

1. Select 문(DDL)
2. 비Select 문(DDL 및 DML)

결과에 따라 액세스 방법은 다음 단계를 수행합니다.

1. 쿼리가 다음과 같은 경우 DDL, SELECT 문을 실행하면 쿼리가 Buffer 매니저 추가 처리를 위해.
2. 그리고 쿼리하면 DDL, NON-SELECT 문, 쿼리는 트랜잭션 관리자로 전달됩니다. 여기에는 주로 UPDATE 문이 포함됩니다.

Buffer 매니저

Buffer 관리자는 아래 모듈의 핵심 기능을 관리합니다.

• 계획 캐시
• 데이터 파싱: Buffer 캐시 및 데이터 저장
• 더티 페이지

우리는 계획을 배울 것입니다, Buffer 이 섹션의 데이터 캐시. 거래 섹션에서 더티 페이지를 다룰 것입니다.

계획 캐시

• 기존 쿼리 계획: 버퍼 관리자는 실행 계획이 저장된 Plan Cache에 있는지 확인합니다. Yes인 경우 쿼리 계획 캐시와 연관된 데이터 캐시가 사용됩니다.
• 최초 캐시 계획: 기존 Plan 캐시는 어디에서 나오나요?첫 번째 쿼리 실행 계획이 실행 중이고 복잡하다면 Plane 캐시에 저장하는 것이 합리적입니다. 이렇게 하면 다음에 SQL 서버에서 동일한 쿼리를 받을 때 더 빠른 가용성이 보장됩니다. 따라서 처음으로 실행되는 경우 Plan 실행이 저장되는 것은 쿼리 자체에 불과합니다.

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

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

Buffer 캐시 – 소프트 구문 분석:

Buffer 관리자는 다음에서 데이터를 찾습니다. Buffer 데이터 캐시에 있습니다. 존재하는 경우 이 데이터는 쿼리 실행자에 의해 사용됩니다. 이렇게 하면 캐시에서 데이터를 페치할 때 데이터 저장소에서 데이터를 페치하는 것보다 I/O 작업 수가 줄어들어 성능이 향상됩니다.

데이터 저장 – 하드 구문 분석:

데이터가 존재하지 않는 경우 Buffer Manager는 Data Storage에서 필요한 데이터를 검색합니다. 또한 나중에 사용할 수 있도록 데이터 캐시에 데이터를 저장합니다.

더티 페이지

Transaction Manager의 처리 로직으로 저장됩니다. 자세한 내용은 Transaction Manager 섹션에서 알아보도록 하겠습니다.

거래 관리자

액세스 방법에서 Query가 Non-Select 문임을 확인할 때 트랜잭션 관리자가 호출됩니다.

로그 관리자

• 로그 관리자는 트랜잭션 로그의 로그를 통해 시스템에서 수행된 모든 업데이트를 추적합니다.
• 로그에는 거래 ID 및 데이터 수정 기록을 포함한 로그 시퀀스 번호.
• 이는 다음을 추적하는 데 사용됩니다. 트랜잭션 커밋 및 트랜잭션 롤백.

잠금 관리자

• Transaction 중에는 Data Storage의 관련 데이터가 Lock 상태입니다. 이 프로세스는 Lock Manager에 의해 처리됩니다.
• 이 프로세스는 다음을 보장합니다. 데이터 일관성 및 격리. ACID 속성이라고도 합니다.

실행과정

• Log Manager는 로깅을 시작하고 Lock Manager는 관련 데이터를 잠급니다.
• 데이터 사본은 Buffer 은닉처.
• 업데이트되어야 할 데이터의 사본은 로그 버퍼에 보관되고, 모든 이벤트 업데이트 데이터는 데이터 버퍼에 저장됩니다.
• 데이터를 저장하는 페이지라고도 합니다. 더티 페이지.
• 체크포인트 및 미리 쓰기 로깅: 이 프로세스가 실행되어 더티 페이지(Dirty Pages)에서 디스크까지 모든 페이지를 표시하지만 페이지는 캐시에 남아 있습니다. 빈도는 분당 약 1회 실행됩니다. 그러나 페이지는 먼저 로그 파일의 데이터 페이지로 푸시됩니다. Buffer 통나무. 이것은 다음과 같이 알려져 있습니다. 미리 쓰기 로깅.
• 게으른 작가: 더티 페이지는 메모리에 남아 있을 수 있습니다. SQL Server가 막대한 부하를 관찰하고 Buffer 새로운 트랜잭션에 메모리가 필요하면 캐시에서 더티 페이지를 해제합니다. LRU – 버퍼 풀에서 디스크로 페이지를 정리하기 위한 최근 사용 알고리즘입니다.

요약

• 세 가지 유형의 클라이언트 서버 Archi기술 존재: 1) 공유 메모리 2) TCP/IP 3) 명명된 파이프
• Sybase가 개발했으며 현재는 Sybase가 소유하고 있는 TDS Microsoft는 클라이언트 시스템에서 서버 시스템으로 데이터를 전송하기 위해 네트워크 패킷에 캡슐화된 패킷입니다.
• 관계형 엔진에는 세 가지 주요 구성 요소가 포함되어 있습니다.CMD 파서: 이는 구문 및 의미 오류를 담당하며 마지막으로 쿼리 트리를 생성합니다.최적화 : 옵티마이저의 역할은 최고가 아닌 가장 저렴하고 비용 효율적인 실행 계획을 찾는 것입니다.

  쿼리 실행자: 쿼리 실행기는 Access Method를 호출하고 실행에 필요한 데이터 가져오기 로직에 대한 실행 계획을 제공합니다.

• 파일에는 기본 파일, 보조 파일, 로그 파일의 세 가지 유형이 있습니다.
• 저장 엔진: 다음과 같은 중요한 구성 요소가 있습니다.액세스 방법: 이 구성 요소 쿼리가 Select 문인지 Non-Select 문인지 결정합니다. 호출 Buffer 이에 따라 전송 관리자.Buffer 매니저 : Buffer Manager는 Plan Cache, Data Parsing, Dirty Page의 핵심 기능을 관리합니다.

  거래 관리자: 로그 및 잠금 관리자의 도움으로 비선택 트랜잭션을 관리합니다. 또한 Write Ahead 로깅 및 Lazy 작성기의 중요한 구현을 용이하게 합니다.