Computer >> 컴퓨터 >  >> 프로그램 작성 >> SQL Server

MS SQL Server의 아키텍처에 대해 알아보기

이전 기사에서 SQL Server, 컴퓨터에 SQL Server를 설치하는 방법에 대해 이미 간략하게 알고 있었습니다. 이 섹션에서는 SQL Server의 아키텍처에 대해 알아볼 것입니다.

이해하기 쉽도록 SQL Server 아키텍처를 다음 섹션으로 나눕니다.

  1. 일반 아키텍처 - 일반
  2. 메모리 아키텍처 - 메모리
  3. 데이터 파일 아키텍처 - 데이터 파일
  4. 로그 파일 아키텍처 - 로그 파일

이제 위에서 분류된 각 SQL Server 아키텍처 유형에 대해 자세히 살펴보겠습니다.

MS SQL Server의 아키텍처에 대해 알아보기

일반 아키텍처 - 일반

  1. 고객: 요청이 이루어진 곳입니다.
  2. 쿼리: SQL 쿼리는 고급 언어입니다.
  3. 논리 단위: 키워드, 표현식, 연산자, .
  4. N/W 패킷: 네트워크와 관련된 코드입니다.
  5. 프로토콜: SQL Server에는 4가지 프로토콜이 있습니다.
    1. 공유 메모리:로컬 연결 및 문제 해결용
    2. 명명된 파이프:LAN 연결용.
    3. TCP / IP:WAN 연결용
    4. VIA-Virtual Interface Adapter:특수 하드웨어 요구 사항은 공급업체에서 설정하며 SQL 2012 버전에서는 지원되지 않습니다.
  6. 서버: SQL Services가 설치되고 데이터베이스가 있는 위치입니다.
  7. 관계형 엔진: 여기에서 실제 실행이 수행됩니다. 여기에는 쿼리 분석기, 쿼리 최적화 프로그램 및 쿼리 런타임이 포함됩니다.
  8. 쿼리 파서(명령 파서) 및 컴파일러(번역기):다음 두 사람이 쿼리 구문을 확인하고 쿼리를 기계 언어로 변환하는 일을 담당합니다.
  9. 쿼리 최적화: 쿼리, 통계 및 Algebrizer 트리로 입력을 취하여 실행 계획으로 출력을 준비합니다.
  1. 실행 계획: 로드맵과 마찬가지로 쿼리 실행의 일부로 수행된 단계의 순서를 포함합니다.
  2. 쿼리 실행기: 여기에서 실행 계획의 도움을 받아 쿼리가 단계별로 실행되고 Storage Engine에 연결됩니다.
  3. 스토리지 엔진: 스토리지 시스템(드라이브, SAN, .)의 데이터 저장 및 검색, 데이터 조작, 트랜잭션 잠금 및 관리를 담당합니다.
  4. SQL OS: 호스트 시스템(Windows OS)과 SQL Server 사이에 위치합니다. 모든 작업은 SQL OS에서 "관리하는" 데이터베이스 엔진에서 수행됩니다. SQL OS는 버퍼 풀을 통한 메모리 관리, 로그 버퍼, 교착 상태 감지, 블록 및 잠금 구조를 사용한 메모리 관리 등 다양한 운영 체제 서비스를 제공합니다.
  5. 검문소: 체크포인트는 버퍼 캐시에서 물리적 드라이브로 수정된 모든 페이지(더티 페이지라고 함)를 쓰는 내부 프로세스입니다. 또한 Log Buffer에서 실제 파일로 로그를 기록합니다. 더티 페이지를 드라이브에 기록하는 것을 더티 페이지 강화라고도 합니다.
  6. 게으른 작가: Lazy Writer는 버퍼 풀의 메모리를 확보하기 위해 완전히 다른 이유로 Dirty Pages 및 하드 드라이브를 푸시합니다. 이것은 SQL Server의 메모리가 부족할 때 발생합니다. 이 프로세스는 내부 프로세스에 의해 제어되며 이에 대한 설정이 없습니다.

SQL Server는 메모리 사용량을 지속적으로 모니터링하여 리소스 가용성과 경쟁을 평가하여 일정량의 여유 공간을 항상 사용할 수 있도록 합니다. 리소스 충돌을 감지하면 Lazy Writer가 일부 Dirty Page를 드라이브로 이동하고 메모리를 확보하도록 트리거합니다. LRU(최근에 사용된 것) 알고리즘을 사용하여 하드 드라이브에 푸시할 페이지를 결정합니다. Lazy Writer가 항상 활성화되어 있으면 메모리에 병목 현상이 발생할 수 있습니다.

메모리 아키텍처 - 메모리

다음은 메모리 아키텍처의 두드러진 특징입니다.

  1. 모든 데이터베이스 소프트웨어의 기본 설계 목표 중 하나는 디스크 읽기 및 쓰기 프로세스가 가장 리소스 집약적인 작업 중 하나이기 때문에 디스크 I/O를 최소화하는 것입니다.
  2. Windows 내부 메모리는 커널 모드(OS 모드)와 사용자(SQL Server와 같은 응용 프로그램)가 공유하는 가상 주소 공간으로 호출할 수 있습니다.
  3. SQL Server의 사용자 주소 공간은 MemToLeave와 Buffer Pool의 두 부분으로 나뉩니다.
  4. MemToLeave(MTL) 및 버퍼 풀(BPool)의 크기는 부팅 과정에서 SQL Server에 의해 결정됩니다.
  5. 버퍼 관리는 높은 I/O 성능을 달성하려는 경우 중요한 구성 요소입니다. 여기에는 데이터베이스에 액세스하고 업데이트하는 버퍼 관리자와 데이터베이스로 I/O 파일을 잘라내는 버퍼 풀 페이지의 두 가지 메커니즘이 포함됩니다.
  6. 버퍼 풀은 여러 부분으로 나뉩니다. 가장 중요한 것은 버퍼 캐시와 프로시저 캐시입니다. 버퍼 캐시는 자주 액세스하는 데이터를 캐시에서 추출할 수 있도록 데이터 페이지를 메모리에 유지합니다. 교체 프로세스는 드라이브에서 데이터 페이지를 읽습니다. 캐시에서 데이터를 읽으면 메모리에서 데이터에 액세스하는 것보다 느린 I/O 작업 수를 최소화하여 성능을 최적화합니다.
  7. 프로시저 캐시는 저장 프로시저와 실행 계획을 보유하여 실행 계획이 생성되는 횟수를 최적화합니다. DBCC PROCCACHE 명령을 사용하여 프로시저 캐시에서 용량 및 작업에 대한 정보를 찾을 수 있습니다.
  8. 버퍼 풀의 다른 부분은 다음과 같습니다.
    1. 시스템 수준 데이터 구조:데이터베이스 및 잠금에 대한 인스턴스 수준 데이터를 포함합니다.
    2. 로그 캐시:트랜잭션 페이지 읽기 및 쓰기 전용
    3. 연결 컨텍스트:Instance와의 각 연결에는 연결의 현재 상태를 기록하기 위한 작은 메모리 영역이 있습니다. 이 정보에는 저장 프로시저 및 사용자 정의 함수 매개변수, 마우스 커서 위치 등이 포함됩니다.
    4. 스택 공간:Windows는 SQL Server로 시작하는 각 스레드에 대해 스택 공간을 할당합니다.

데이터 파일 아키텍처 - 데이터 파일

이 아키텍처에는 다음 구성요소가 있습니다.

파일 그룹:

데이터베이스 파일은 배포 및 관리 목적으로 파일 그룹으로 함께 그룹화할 수 있습니다. 파일은 파일 그룹의 구성원만 될 수 있습니다. 로그 파일의 크기는 데이터 볼륨과 별도로 관리하기 때문에 로그 파일을 파일 그룹으로 그룹화할 수 없습니다.

SQL Server에는 기본 및 사용자 정의의 두 가지 유형의 파일 그룹이 있습니다. 기본에는 기본 데이터 파일과 파일 그룹에 특별히 할당되지 않은 모든 파일이 포함됩니다. 시스템 테이블의 모든 페이지는 Primary에 할당됩니다. 사용자 정의는 file group을 사용하여 지정되는 사용자 정의 파일 그룹입니다. 데이터베이스를 생성하거나 데이터베이스를 삭제하려면 명령에 키워드를 사용하십시오.

각 데이터베이스의 파일 그룹은 기본 파일 그룹으로 작동합니다. SQL Server에서 페이지를 테이블이나 인덱스(생성 시 파일 그룹에 없음)에 할당하면 해당 페이지는 기본 파일 그룹에 포함됩니다. 기본 파일 그룹을 파일 그룹에서 다른 파일 그룹으로 변환하려면 db_owner 고정 데이터베이스 역할이 필요합니다.

기본은 기본 파일 그룹입니다. 사용자는 파일 및 개별 파일 그룹을 백업하기 위해 db_owner 고정 데이터베이스 역할이 있어야 합니다.

파일

데이터베이스에는 Primary 파일(메인 데이터 파일), Secondary(보조 데이터 파일) 및 Log(로그 파일)의 3가지 유형이 있습니다. Primary는 데이터베이스의 시작점이며 데이터베이스의 다른 파일을 가리킵니다.

각 데이터베이스에는 기본이 있습니다. 기본 데이터 파일의 확장자도 설정할 수 있지만 권장 사항은 .mdf입니다. 보조 데이터 파일은 주 데이터 파일이 아닌 파일입니다. 데이터베이스에는 추가 데이터 파일이 많거나 하나만 있을 수 있습니다. 추가 데이터 파일의 확장자는 .ndf로 설정해야 합니다.

로그 파일은 데이터베이스 복구에 사용되는 모든 정보를 보관합니다. 데이터베이스에는 최소한 하나의 로그 파일이 있어야 합니다. 데이터베이스에 대해 여러 개의 로그 파일을 가질 수 있습니다. 확장자는 .ldf로 설정해야 합니다.

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

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

범위

Extent는 테이블과 인덱스별로 공간을 할당하는 기본 단위이다. 각 Extent는 8개의 인접 페이지 또는 64KB입니다. SQL Server에는 Uniform 및 Mixed의 두 가지 유형의 Extent가 있습니다. Uniform은 최대 8개의 개체로 구성된 단일 혼합 개체로 구성됩니다.

페이지

페이지(페이지)는 SQL Server 데이터 저장소의 기본 단위입니다. 페이지 크기는 8KB입니다. 각 페이지의 시작은 96byte 제목으로 페이지 유형, 페이지의 여유 공간 및 페이지 소유자 ID와 같은 시스템 정보를 저장하는 데 사용됩니다. SQL Server에는 9가지 유형의 데이터 페이지가 있습니다.

  1. 데이터:text, ntext 및 image의 모든 데이터가 포함된 데이터 행
  2. 색인:항목을 색인화합니다.
  3. TexImage:text, ntext 및 image 데이터입니다.
  4. GAM:지정된 범위에 대한 정보입니다.
  5. SGAM:범위에 대한 정보는 시스템 수준에서 할당됩니다.
  6. 페이지 여유 공간(PFS):페이지에서 사용 가능한 여유 공간에 대한 정보입니다.
  7. Index Allocation Map(IAM):테이블 또는 인덱스가 사용하는 범위에 대한 정보입니다.
  8. Bulk Changed Map(BCM):익스텐트 정보는 마지막 백업 명령부터 대량 작업으로 변경됩니다.
  9. DCM(Differential Changed Map):마지막 데이터베이스 백업 명령 이후에 익스텐트에 대한 정보가 변경되었습니다.

로그 파일 구조 - 로그 파일

SQL Server의 트랜잭션 로그는 일련의 로그 레코드일 때 제대로 작동합니다. 각 로그는 해당 로그가 속한 트랜잭션의 ID가 포함된 LSN(로그 시퀀스 번호)으로 식별됩니다.

로그는 데이터 수정 또는 수행된 활동을 기록하거나 데이터 편집 전후의 이미지를 검색합니다. 이전 이미지는 작업 수행 전 데이터의 복사본이고, 다음 이미지는 작업 수행 후 데이터 복사본입니다.

작업 복구 단계는 로그 유형에 따라 다릅니다.

  1. 논리적 작업이 기록됩니다.
  2. 이전 논리 연산으로 이동하려면 연산을 다시 수행합니다.
  3. 뒤의 논리연산으로 돌아가기 위해 역논리연산이 수행됩니다.
  4. 이전 및 다음 이미지가 기록됩니다.
  5. 이전 작업으로 이동하려면 다음 이미지가 적용됩니다.
  6. 다음 작업으로 돌아가려면 이전 이미지가 적용됩니다.

트랜잭션 로그에 다양한 작업이 기록되었습니다. 여기에서 다음 작업을 사용할 수 있습니다.

  1. 각 거래를 시작하고 종료합니다.
  2. 시스템 저장 절차 또는 시스템 테이블을 포함한 테이블에 대한 데이터 정의 언어 명령(DDL) 변경을 포함한 모든 데이터 수정(삽입, 업데이트, 삭제)
  3. 전체 범위 및 할당, 페이지 할당 취소.
  4. 테이블과 인덱스를 생성하거나 삭제합니다.

롤백 작업도 기록됩니다. 각 트랜잭션은 명령 또는 오류 메시지를 실행하기 위해 롤백에 필요한 충분한 로그 공간이 있는지 확인하기 위해 로그에 공간을 보유합니다. 이 공간은 거래가 완료되면 해제됩니다.

첫 번째 로그(전체 데이터베이스를 성공적으로 복원하는 데 필요)에서 마지막 로그까지의 로그 파일 부분을 로그 활동 섹션 또는 활성 로그라고 합니다. 전체 데이터베이스 복구에 필요한 로그 섹션입니다. 활성 로그의 어떤 부분도 왜곡되지 않습니다. 첫 번째 로그 기록의 LSN을 최소 복구 LSN(Min LSN)이라고 합니다.

SQL Server 데이터베이스 엔진은 각 실제 로그 파일을 여러 가상 로그 파일로 나눕니다. 가상 로그 파일은 크기가 고정되어 있지 않으며 물리적 로그 파일마다 가상 로그 파일 수가 고정되어 있지 않습니다.

데이터베이스 엔진은 로그 파일을 생성하거나 확장할 때 가상 로그 파일의 크기를 자동으로 선택합니다. 데이터베이스 엔진은 소수의 가상 파일을 유지 관리하려고 합니다. 가상 로그 파일의 크기는 관리자가 구성하거나 설정할 수 없습니다. 로그 Physical 파일이 작은 크기와 growth_increment 값으로 정의된 경우에만 가상 로그 파일이 시스템 성능에 영향을 미치게 됩니다.

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

필요한 최종 크기와 상대적으로 큰 growth_increment 값에 가까운 크기 값을 로그 파일에 할당해야 한다는 조언이 있습니다. SQL Server는 미리 쓰기 로그(WAL)를 사용하여 관련 로그가 드라이브에 기록되기 전에 드라이브에 기록된 데이터가 수정되지 않도록 합니다. 이는 트랜잭션 속성에 대한 ACID를 유지하는 데 도움이 됩니다.

SQL의 대수기

Algebrizer에 대해 조금 이야기하고 싶습니다. Algebrizer는 쿼리 실행 과정의 프로세스입니다. Parser 이후에 작동하기 시작했습니다. Query Parser가 올바른 쿼리를 찾으면 Algebrizer로 이동하고 Algebrizer의 작업이 시작됩니다. Algebrizer는 개체 및 열 이름(쿼리에서 제공했거나 쿼리에서 참조하는)을 확인하는 역할을 합니다. 예를 들어 쿼리에 컬럼 이름이 잘못 쓰여진 경우 Algebrizer가 이를 확인하고 오류를 생성하는 역할을 합니다. Algebrizer는 또한 주어진 쿼리에서 처리되는 모든 유형의 데이터를 식별합니다. Algebrizer는 GROUP BY와 결합된 열이 올바른 위치에 배치되었는지 여부를 확인합니다. 예를 들어 다음 쿼리를 작성하고 Ctrl + F5만 눌러 구문 분석하면 오류가 발생하지 않습니다. 그러나 F5 키를 눌러 쿼리를 실행하면 Algebrizer가 작동하고 오류를 반환합니다.

 USE AdventureWorks 
 GO 
 SELECT MakeFlag,SUM(ListPrice) 
 FROM Production.Product 
  GROUP BY ProductNumber

SQL Server의 체크포인트

SQL Server 2012에는 4가지 유형의 체크포인트가 있습니다.

  1. 자동: 이 유형은 서버 구성 옵션에서 복구 간격으로 지정된 시간 내에 SQL Server 데이터베이스를 복원할 수 있는지 확인하기 위해 백그라운드 프로세스의 형태로 실행되는 가장 일반적인 체크포인트입니다.
  2. 간접: 이 체크포인트는 SQL Server 2012에서만 사용할 수 있습니다. 또한 백그라운드 실행 프로세스이지만 구성 옵션에서 특정 데이터베이스의 복구 시간을 지정하는 특정 사용자에게만 해당됩니다. 특정 데이터베이스에 대한 Target_Recovery_Time을 선택하면 데이터베이스의 자동 체크포인트를 피하여 서버에 할당된 복구 간격을 덮어씁니다.
  3. 수동: 이 체크포인트는 다른 SQL 명령처럼 실행되며, 체크포인트 명령을 생성하면 완료될 때까지 실행됩니다. 이 체크포인트는 현재 데이터베이스에서만 실행됩니다. 옵션에서 Checkpoint_Duration을 지정하여 체크포인트를 완료할 시간을 지정할 수 있습니다.
  4. 내부: 사용자는 다음과 같은 특정 작업에서 이러한 유형의 체크포인트를 제어할 수 없습니다.
    1. 종료가 실패하지 않는 한 모든 데이터베이스의 체크포인트 작업을 종료합니다. 정상이 아닙니다(nowith 명령과 함께 Shutdown 사용).
    2. 복구 모델이 FullBulk-logged에서 Simple로 변경된 경우
    3. 데이터베이스 백업 중
    4. 데이터베이스가 단순 복구 모드인 경우 체크포인트 프로세스가 자동으로 수행되거나 로그가 70% 차면 서버의 복구 간격 옵션을 기반으로 합니다.
    5. 로그/데이터 파일을 추가하거나 삭제하는 ALTER DATABASE 명령도 체크포인트를 시작합니다.
    6. 데이터베이스 복구 모델이 대량 로그되고 최소 쓰기 작업이 수행된 경우에도 체크포인트가 발생합니다.

이것은 아마도 SQL Server의 "가장 좋은" 부분이지만 아키텍처를 이해하는 아이디어는 모든 것이 어떻게 작동하는지 이해하는 데 도움이 될 것입니다. 다소, . 이렇게 하면 데이터베이스 작업이 더 쉬워집니다.

다음 섹션에서는 Management Studio에 대해 배우고 점차 SQL Server의 기본 명령에 대해 알아보겠습니다.