버퍼 관리
SQL Server 데이터베이스의 주 목적은 데이터 저장과 데이터 검색이므로 데이터베이스 엔진의 핵심 특성은 집중형 디스크 I/O입니다. 디스크 I/O 작업은 많은 리소스를 소비하며 완료하는 데 비교적 오랜 시간이 걸리므로 SQL Server에서는 I/O의 효율성을 높이는 데 주안점을 둡니다. 버퍼 관리는 이러한 효율성을 얻기 위한 핵심 구성 요소입니다. 버퍼 관리 구성 요소는 데이터베이스 페이지를 액세스하고 업데이트하기 위한 버퍼 관리자와 데이터베이스 파일 I/O를 줄이기 위한 버퍼 캐시(버퍼 풀이라고도 함)의 두 가지 메커니즘으로 구성되어 있습니다.
버퍼 관리 작업 방법
버퍼는 데이터 또는 인덱스 페이지와 같은 크기인 8KB 메모리 페이지입니다. 따라서 버퍼 캐시는 8KB 페이지로 나누어집니다. 버퍼 관리자는 데이터 또는 인덱스 페이지를 데이터베이스 디스크 파일에서 버퍼 캐시로 읽어 오고 수정한 페이지를 디스크에 다시 쓰는 기능을 관리합니다. 페이지는 버퍼 관리자에 더 많은 데이터를 읽어 올 버퍼 영역이 필요할 때까지 버퍼 캐시에 남아 있습니다. 데이터는 수정되는 경우에만 다시 디스크에 쓰여집니다. 버퍼 캐시의 데이터는 여러 번 수정한 후 디스크에 다시 쓸 수 있습니다. 자세한 내용은 페이지 읽기 및 페이지 쓰기를 참조하십시오.
시작 시 SQL Server에서는 시스템의 실제 메모리 양, 구성된 최대 서버 스레드 수, 다양한 시작 매개 변수 등을 비롯한 여러 매개 변수를 기반으로 버퍼 캐시의 가상 주소 공간 크기를 계산합니다. SQL Server에서는 계산된 이러한 프로세스 가상 주소 공간(메모리 대상)을 버퍼 캐시용으로 예약하지만 현재 로드에 필요한 실제 메모리만 확보(커밋)합니다. sys.dm_os_sys_info 카탈로그 뷰에서 bpool_commit_target 및 bpool_committed 열을 쿼리하여 메모리 대상으로 예약된 페이지 수와 버퍼 캐시에 현재 커밋된 페이지 수를 각각 반환할 수 있습니다.
SQL Server 시작 시간과 버퍼 캐시가 해당 메모리 대상을 확보하는 시간 사이의 간격을 램프 업(ramp-up)이라고 합니다. 이 시간 동안 필요에 따라 읽기 요청이 버퍼를 채웁니다. 예를 들어 단일 페이지 읽기 요청은 단일 버퍼 페이지를 채웁니다. 즉, 램프 업은 클라이언트 요청의 개수 및 유형에 따라 달라집니다. 단일 페이지 읽기 요청을 정렬된 8페이지 요청으로 변환하여 램프 업을 신속히 처리합니다. 이를 통해 메모리가 많은 컴퓨터에서 특히 램프 업이 더 빠르게 완료될 수 있습니다.
버퍼 관리자는 SQL Server 프로세스에서 대부분의 메모리를 사용하므로 메모리 관리자와 협력하여 다른 구성 요소가 해당 버퍼를 사용할 수 있도록 합니다. 버퍼 관리자는 다음 구성 요소와 주로 상호 작용합니다.
전체 메모리 사용량을 제어하며 32비트 플랫폼에서 주소 공간 사용량을 제어하는 리소스 관리자
하위 수준 파일 I/O 작업을 위한 SQLOS(SQL Server 운영 체제) 및 데이터베이스 관리자
미리 쓰기 로깅을 위한 로그 관리자
지원되는 기능
버퍼 관리자는 다음과 같은 기능을 지원합니다.
버퍼 관리자는 NUMA(Non-Uniform Memory Access)를 인식합니다. 버퍼 캐시 페이지는 하드웨어 NUMA 노드에 분산되므로 스레드가 외부 메모리 대신 로컬 NUMA 노드에 할당된 버퍼 페이지에 액세스할 수 있습니다. 자세한 내용은 SQL Server의 NUMA 지원 방법을 참조하십시오. NUMA를 사용할 경우 버퍼 캐시의 메모리 페이지가 할당되는 방법을 이해하려면 NUMA에서 버퍼 풀 증가 및 축소를 참조하십시오.
버퍼 관리자는 Hot Add 메모리를 지원하므로 사용자가 서버를 다시 시작하지 않고도 실제 메모리를 추가할 수 있습니다. 자세한 내용은 Hot Add 메모리를 참조하십시오.
버퍼 관리자는 AWE가 설정된 경우 Microsoft Windows XP 32비트 및 Windows 2003 32비트 플랫폼에서 동적 메모리 할당을 지원합니다. 동적 메모리 할당을 통해 데이터베이스 엔진은 버퍼 캐시에서 메모리를 효율적으로 확보 및 해제하여 현재 작업을 지원할 수 있습니다. 자세한 내용은 동적 메모리 관리를 참조하십시오.
버퍼 관리자는 64비트 플랫폼에서 큰 페이지를 지원합니다. 페이지 크기는 Windows 버전에 따라 달라집니다. 자세한 내용은 Windows 설명서를 참조하십시오.
버퍼 관리자는 동적 관리 뷰를 통해 표시되는 추가 진단 기능을 제공합니다. 이러한 뷰를 사용하여 다양한 SQL Server 관련 운영 체제 리소스를 모니터링할 수 있습니다. 예를 들어 sys.dm_os_buffer_descriptors 뷰를 사용하여 버퍼 캐시의 페이지를 모니터링할 수 있습니다. 자세한 내용은 SQL Server 운영 체제 관련 동적 관리 뷰 및 함수(Transact-SQL)를 참조하십시오.
디스크 I/O
버퍼 관리자는 데이터베이스에 대해 읽기 및 쓰기만 수행합니다. 열기, 닫기, 확장, 축소와 같은 기타 파일 및 데이터베이스 작업은 데이터베이스 관리자 및 파일 관리자 구성 요소에 의해 수행됩니다.
버퍼 관리자가 수행하는 디스크 I/O 작업의 특성은 다음과 같습니다.
모든 I/O가 비동기적으로 수행되므로 I/O 작업이 백그라운드에서 수행되는 동안 호출한 스레드는 처리를 계속할 수 있습니다.
affinity I/O 옵션을 사용하지 않는 한 모든 I/O는 호출한 스레드에서 실행됩니다. affinity I/O mask 옵션은 SQL Server 디스크 I/O를 지정된 CPU 하위 집합에 바인딩합니다. 고성능 SQL Server OLTP(온라인 트랜잭션 처리) 환경에서 이러한 확장을 통해 I/O를 실행하는 SQL Server 스레드의 성능을 향상시킬 수 있습니다.
다중 페이지 I/O는 분산 수집 I/O로 수행되므로 데이터를 인접하지 않은 메모리 영역으로 또는 이러한 영역 밖으로 전송할 수 있습니다. 이를 통해 여러 실제 I/O 요청을 피하면서 SQL Server에서 버퍼 캐시를 신속하게 채우거나 플러시할 수 있습니다.
긴 I/O 요청
버퍼 관리자는 15초 이상 보류 상태에 있는 모든 I/O 요청을 보고합니다. 이를 통해 시스템 관리자는 SQL Server 문제와 I/O 하위 시스템 문제를 구분할 수 있습니다. 다음과 같이 오류 메시지 833이 보고되어 SQL Server 오류 로그에 나타납니다.
SQL Server에서 데이터베이스 [%ls](%d)의 파일 [%ls]을(를) 완료하는 데 %d초보다 더 오래 걸린 I/O 요청이 %d개 발견되었습니다. OS 파일 핸들은 0x%p입니다. 가장 최근의 시간 초과 I/O의 오프셋은 %#016I64x입니다.
긴 I/O는 읽기 또는 쓰기일 수 있으나 현재 이러한 정보는 메시지에 나타나 있지 않습니다. 긴 I/O 메시지는 오류가 아니라 경고입니다. 또한 SQL Server 관련 문제를 나타내지 않습니다. 시스템 관리자는 보고되는 메시지를 통해 느린 SQL Server 응답 시간의 원인을 보다 빨리 찾고 SQL Server 제어를 벗어난 문제를 구분할 수 있습니다. 해당 메시지를 받는다고 해서 특별한 동작을 수행해야 하는 것은 아니지만 시스템 관리자는 I/O 요청이 오래 걸리는 이유와 그러한 지연에 적합한 이유가 있는지 여부를 조사해야 합니다.
긴 I/O 요청의 원인
긴 I/O 메시지는 I/O가 영구적으로 차단되어 완료될 수 없거나(손실된 I/O) 단순히 아직 완료되지 않았음을 나타냅니다. 손실된 I/O로 인해 래치 제한 시간이 초과되는 경우가 많기는 하지만 메시지를 통해 어떤 시나리오가 이 경우에 해당하는지 알 수는 없습니다.
긴 I/O는 디스크 하위 시스템에 SQL Server 작업이 너무 많이 집중되어 있음을 나타냅니다. 다음과 같은 경우 디스크 하위 시스템이 부적절한 상태에 있다는 메시지가 나타날 수 있습니다.
많은 SQL Server 작업을 수행하는 동안 여러 개의 긴 I/O 메시지가 오류 로그에 나타나는 경우
Perfmon 카운터가 긴 디스크 지연 시간, 긴 디스크 큐 또는 디스크 유휴 시간 없음을 표시하는 경우
디스크 헤드의 현재 위치에 더 가까이 있는 새로운 요청을 처리하기 위해 이전 I/O 요청 처리를 계속 연기하는 I/O 경로의 구성 요소(예: 드라이버, 컨트롤러 또는 펌웨어)로 인해 긴 I/O가 발생할 수도 있습니다. 읽기/쓰기 헤드의 현재 위치에 가장 가까운 요청을 우선적으로 처리하는 방법을 "엘리베이터 검색"이라고 합니다. 대부분의 I/O가 즉시 처리되므로 Windows 시스템 모니터 도구(PERFMON.EXE)로 이를 확인하는 것은 어려울 수 있습니다. 긴 I/O 요청은 백업과 복원, 테이블 검색, 정렬, 인덱스 생성, 대량 로드, 파일 비우기와 같은 순차 I/O를 대량으로 수행하는 작업으로 인해 악화될 수 있습니다.
위에 나열된 어떤 조건과도 관련되지 않은 격리된 긴 I/O는 하드웨어 또는 드라이버 문제로 인해 발생할 수 있습니다. 시스템 이벤트 로그에 문제를 진단하는 데 도움이 되는 관련 이벤트가 포함되어 있을 수 있습니다.
오류 검색
데이터베이스 페이지는 페이지를 디스크에 쓸 때부터 다시 읽을 때까지의 페이지 무결성을 보장하는 두 가지 선택적 메커니즘인 조각난 페이지 보호와 체크섬 보호 중 하나를 사용할 수 있습니다. 이러한 메커니즘을 사용하면 데이터 저장소뿐만 아니라 컨트롤러, 드라이버, 케이블, 심지어는 운영 체제를 비롯한 하드웨어 구성 요소의 정확성을 확인하는 독자적인 방법을 활용할 수 있습니다. 이러한 보호는 페이지를 디스크에 쓰기 바로 전에 페이지에 추가되며 디스크에서 읽은 후 확인됩니다.
조각난 페이지 보호
SQL Server 2000에 도입된 조각난 페이지 보호는 주로 전원 오류로 인한 페이지 손상을 검색하는 방법입니다. 예를 들어 예기치 않은 전원 오류로 인해 페이지의 일부만 디스크에 쓰일 수 있습니다. 조각난 페이지 보호를 사용하면 원래의 2비트가 페이지 머리글로 복사된 후 페이지의 각 512바이트 구역 끝에 2비트 서명이 저장됩니다. 페이지를 쓸 때마다 서명은 이진수 01과 10이 번갈아 사용되므로 구역의 일부만 디스크에 쓰일 경우 이를 알 수 있습니다. 나중에 페이지를 읽을 때 비트가 잘못된 상태에 있으면 페이지가 잘못 쓰인 것이며 조각난 페이지가 검색됩니다. 조각난 페이지 검색은 최소한의 리소스를 사용하지만 디스크 하드웨어 오류로 인한 모든 오류를 검색하지는 않습니다.
체크섬 보호
SQL Server 2005에 도입된 체크섬 보호는 보다 강력한 데이터 무결성 검사 기능을 제공합니다. 체크섬은 쓰인 각 페이지의 데이터에 대해 계산되어 페이지 머리글에 저장됩니다. 저장된 체크섬이 있는 페이지를 디스크에서 읽을 때마다 데이터베이스 엔진은 페이지의 데이터에 대한 체크섬을 다시 계산하고 새 체크섬이 저장된 체크섬과 다를 경우 오류 824를 발생시킵니다. 체크섬 보호는 페이지의 모든 바이트에 의해 영향을 받으므로 조각난 페이지 보호보다 더 많은 오류를 catch할 수 있지만 많은 리소스를 소비합니다. 체크섬을 설정하면 버퍼 관리자가 디스크에서 페이지를 읽을 때마다 전원 오류 및 결함이 있는 하드웨어나 펌웨어로 인한 오류를 검색할 수 있습니다.
사용되는 페이지 보호 유형은 페이지를 포함하는 데이터베이스의 특성입니다. 체크섬 보호는 SQL Server 2005 이상 버전에서 생성된 데이터베이스의 기본 보호 메커니즘입니다. 페이지 보호 메커니즘은 데이터베이스 생성 시 지정하며 ALTER DATABASE를 사용하여 변경할 수 있습니다. sys.databases 카탈로그 뷰의 page_verify_option 열 또는 DATABASEPROPERTYEX 함수의 IsTornPageDetectionEnabled 속성을 쿼리하여 현재 페이지 보호 설정을 확인할 수 있습니다. 페이지 보호 설정을 변경해도 새 설정이 전체 데이터베이스에 즉시 영향을 주지는 않습니다. 대신 페이지는 다음에 쓰일 때부터 데이터베이스의 현재 보호 수준을 적용합니다. 이에 따라 데이터베이스가 보호 유형이 서로 다른 여러 페이지로 구성될 수 있습니다.