메모리 관리 아키텍처 가이드

적용 대상:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics AnalyticsPlatform System(PDW)

Windows Virtual Memory Manager

주소 공간의 커밋된 영역은 Windows 가상 메모리 관리자(VMM)에 의해 사용할 수 있는 실제 메모리에 매핑됩니다.

다른 운영 체제에서 지원하는 실제 메모리 양에 대한 자세한 내용은 Windows 릴리스의 메모리 제한에 대한 Windows 설명서를 참조하세요.

가상 메모리 시스템은 실제 메모리의 과다 약정을 허용하므로 가상-실제 메모리의 비율이 1:1을 초과할 수 있습니다. 따라서 다양한 실제 메모리 구성이 있는 컴퓨터에서 더 큰 프로그램을 실행할 수 있습니다. 그러나 모든 프로세스의 결합된 평균 작업 집합보다 훨씬 더 많은 가상 메모리를 사용하면 성능이 저하될 수 있습니다.

SQL Server 메모리 아키텍처

SQL Server는 필요에 따라 동적으로 메모리를 획득하고 해제합니다. 일반적으로 관리자는 SQL Server에 할당해야 하는 메모리의 양을 지정할 필요가 없습니다. 옵션이 여전히 존재하며 일부 환경에서는 필수입니다.

모든 데이터베이스 소프트웨어의 주요 디자인 목표 중 하나는 디스크 읽기 및 쓰기가 리소스를 많이 사용하는 작업 중 하나이기 때문에 디스크 I/O를 최소화하는 것입니다. SQL Server는 데이터베이스에서 읽은 페이지를 보관하도록 메모리에 버퍼 풀을 만듭니다. SQL Server 코드 중 상당수가 디스크와 버퍼 풀 간의 물리적 읽기 횟수와 쓰기 횟수를 최소화하는 데 사용됩니다. SQL Server는 다음 두 목표 간의 균형을 맞추려고 합니다.

  • 전체 시스템의 메모리가 부족해지지 않도록 적정한 수준의 버퍼 풀 크기 유지
  • 버퍼 풀의 크기를 최대화하여 데이터베이스 파일에 대한 실제 입출력 최소화

많이 로드된 시스템에서는 많은 양의 메모리를 실행해야 하는 일부 대규모 쿼리는 요청된 메모리의 최소 양을 가져올 수 없으며 메모리 리소스를 기다리는 동안 시간 제한 오류를 받을 수 없습니다. 이 문제를 해결하려면 쿼리 대기 옵션을 늘입니다. 병렬 쿼리의 경우 최대 병렬 처리 수준 옵션을 줄이는 것이 좋습니다.

메모리가 많이 로드된 시스템에서 쿼리 계획에서 병합 조인, 정렬 및 비트맵을 사용하는 쿼리는 쿼리가 비트맵에 필요한 최소 메모리를 얻지 못하면 비트맵을 삭제할 수 있습니다. 이는 쿼리 성능에 영향을 줄 수 있으며 정렬 프로세스가 메모리에 맞지 않으면 데이터베이스 tempdb 에서 작업 가능 tempdb 개체의 사용량이 증가하여 증가할 수 있습니다. 이 문제를 해결하려면 실제 메모리를 추가하거나 쿼리를 조정하여 다른 빠른 쿼리 계획을 사용합니다.

기존(가상) 메모리

모든 SQL Server 버전은 64비트 플랫폼에서 기존 메모리를 지원합니다. SQL Server 프로세스는 x64 아키텍처에서 최대 운영 체제까지 가상 주소 공간에 액세스할 수 있습니다(SQL Server Standard Edition은 최대 128GB 지원). IA64 아키텍처의 경우 제한은 7TB(SQL Server 2012(11.x)에서 지원되지 않는 IA64)입니다. 자세한 내용은 Windows용 메모리 제한을 참조하세요.

AWE(Windows 확장) 메모리 주소 지정

AWE(주소 창 확장) 및 AWE에 필요한 LPIM(메모리의 잠금 페이지) 권한을 사용하여 대부분의 SQL Server 프로세스 메모리를 낮은 가상 메모리 조건에서 실제 RAM에 잠글 수 있습니다. 이는 32비트 및 64비트 AWE 할당 모두에서 발생합니다. 메모리 잠금은 AWE 메모리가 메모리 페이징을 제어하는 Windows의 Virtual Memory Manager를 통과하지 않기 때문에 발생합니다. AWE 메모리 할당 API에는 메모리잠금 페이지(SeLockMemoryPrivilege) 권한이 필요합니다. AllocateUserPhysicalPages 참고를 참조하세요. 따라서 AWE API를 사용하는 주요 이점은 시스템에 메모리 압력이 있는 경우 대부분의 메모리를 RAM에 유지하는 것입니다. SQL Server에서 AWE를 사용하도록 허용하는 방법에 대한 자세한 내용은 메모리에서 페이지 잠금 사용 옵션을 참조하세요.

LPIM이 부여된 경우 기본값인 2,147,483,647MB를 그대로 두지 않고 최대 서버 메모리(MB)를 특정 값으로 설정하는 것이 좋습니다. 자세한 내용은 서버 메모리 서버 구성: 수동으로 옵션 설정 및 LPIM(메모리의 페이지 잠금)을 참조하세요.

LPIM을 사용하지 않는 경우 SQL Server는 기존 메모리 사용으로 전환되며 OS 메모리 소모의 경우 오류 로그에 오류 17890 이 보고될 수 있습니다. 이 오류는 다음 예제와 유사합니다.

A significant part of SQL Server process memory has been paged out. This may result in a performance degradation. Duration: #### seconds. Working set (KB): ####, committed (KB): ####, memory utilization: ##%.

SQL Server 2012(11.x)부터 메모리 관리 변경

이전 버전의 SQL Server에서는 다음과 같은 5가지 메커니즘을 사용하여 메모리 할당을 수행했습니다.

  • SQL Server 프로세스에서 8KB보다 작거나 같은 메모리 할당만 포함하는 SPA(단일 페이지 할당자)입니다. 최대 서버 메모리(MB)최소 서버 메모리(MB) 구성 옵션은 SPA에서 사용한 실제 메모리의 제한을 결정했습니다. 버퍼 풀은 SPA를 위한 메커니즘이자 단일 페이지 할당의 가장 큰 소비자였습니다.
  • 다중 페이지 할당자(MPA): 8KB 이상을 요청하는 메모리 할당입니다.
  • CLR 할당자: SQL CLR 힙 및 CLR 초기화 중에 생성되는 전역 할당을 포함합니다.
  • SQL Server 프로세스의 스레드 스택에 대한 메모리 할당입니다.
  • Windows에 직접 수행한 메모리 할당 요청에 대한 DWA(직접 Windows 할당)입니다. 여기에는 SQL Server 프로세스에 로드되는 모듈에서 만든 Windows 힙 사용 및 직접 가상 할당이 포함됩니다. 이러한 메모리 할당 요청의 예로는 확장 저장 프로시저 DLL의 할당, 자동화 프로시저(sp_OA 호출)를 사용하여 만든 개체 및 연결된 서버 공급자의 할당이 있습니다.

SQL Server 2012(11.x)부터 단일 페이지 할당, 다중 페이지 할당 및 CLR 할당은 모두 "모든 크기" 페이지 할당자로 통합되며 최대 서버 메모리(MB) 및 최소 서버 메모리(MB) 구성 옵션으로 제어되는 메모리 제한에 포함됩니다. 이 변경은 SQL Server 메모리 관리자를 통과하는 모든 메모리 요구 사항에 대해 보다 정확한 크기 조정 기능을 제공했습니다.

Important

SQL Server 2012(11.x) 이상으로 업그레이드한 후 현재 최대 서버 메모리(MB) 및 최소 서버 메모리(MB) 구성을 신중하게 검토합니다. SQL Server 2012(11.x)부터 이러한 구성은 이제 이전 버전에 비해 더 많은 메모리 할당을 포함하고 고려하기 때문입니다. 이러한 변경 내용은 32비트 및 64비트 버전의 SQL Server 2012(11.x) 및 SQL Server 2014(12.x) 및 64비트 버전의 SQL Server 2016(13.x) 이상에 모두 적용됩니다.

다음 표에서는 특정 유형의 메모리 할당이 최대 서버 메모리(MB)최소 서버 메모리(MB) 구성 옵션에 의해 제어되는지 여부를 나타냅니다.

메모리 할당 유형 SQL Server 2005(9.x), SQL Server 2008(10.0.x) 및 SQL Server 2008 R2(10.50.x) SQL Server 2012(11.x)부터
단일 페이지 할당 예, "임의 크기" 페이지 할당에 통합됨
다중 페이지 할당 없음 예, "임의 크기" 페이지 할당에 통합됨
CLR 할당 없음
스레드 스택 메모리 없음 없음
Windows에서 직접 할당 없음 없음

SQL Server 2012(11.x)부터 SQL Server는 최대 서버 메모리(MB) 설정에 지정된 값보다 많은 메모리를 할당할 수 있습니다. 이 동작은 최대 서버 메모리(MB)에 지정된 대로 총 서버 메모리(KB) 값이 이미 KB(대상 서버 메모리) 설정에 도달했을 때 발생할 수 있습니다. 메모리 조각화로 인해 다중 페이지 메모리 요청(8KB 이상)의 요구를 충족하기 위해 연속된 사용 가능한 메모리가 부족한 경우 SQL Server는 메모리 요청을 거부하는 대신 초과 약정을 수행할 수 있습니다.

이 할당이 수행되는 즉시 리소스 모니터 백그라운드 작업은 모든 메모리 소비자에게 할당된 메모리를 해제하라는 신호를 표시하기 시작하고, 총 서버 메모리(KB) 값을 대상 서버 메모리(KB) 사양 아래로 가져오려고 시도합니다. 따라서 SQL Server 메모리 사용량이 최대 서버 메모리(MB) 설정을 잠시 초과할 수 있습니다. 이 경우 총 서버 메모리(KB) 성능 카운터 판독값이 최대 서버 메모리(MB)KB(대상 서버 메모리) 설정을 초과합니다.

이 동작은 일반적으로 다음 작업 중에 관찰됩니다.

  • 큰 columnstore 인덱스 쿼리
  • rowstore 쿼리의 대규모 일괄 처리 모드
  • 많은 양의 메모리를 사용하여 해시 및 정렬 작업을 수행하는 Columnstore 인덱스(re)빌드
  • 큰 메모리 버퍼가 필요한 백업 작업
  • 큰 입력 매개 변수를 저장해야 하는 추적 작업

SQL Server 2012(11.x)부터 memory_to_reserve 변경

이전 버전의 SQL Server에서 SQL Server 메모리 관리자는 MPA(다중 페이지 할당자), CLR 할당자, SQL Server 프로세스의 스레드 스택대한 메모리 할당 및 DWA(Direct Windows 할당)에서 사용할 프로세스 VAS(가상 주소 공간)의 일부를 따로 둡니다. 가상 주소 공간의 이 부분을 "Mem-To-Leave" 또는 "비 버퍼 풀" 지역이라고도 합니다.

이러한 할당을 위해 예약된 가상 주소 공간은 memory_to_reserve 구성 옵션에 의해 결정됩니다. SQL Server에서 사용하는 기본값은 256MB입니다.

"모든 크기" 페이지 할당자는 8KB 보다 큰 할당도 처리하므로 memory_to_reserve 값에는 다중 페이지 할당이 포함되지 않습니다. 이 변경 사항을 제외하고 나머지는 이 구성 옵션과 함께 동일하게 유지됩니다.

다음 표에서는 특정 유형의 메모리 할당이 SQL Server 프로세스에 대한 가상 주소 공간의 memory_to_reserve 영역에 속하는지 여부를 나타냅니다.

메모리 할당 유형 SQL Server 2005(9.x), SQL Server 2008(10.0.x) 및 SQL Server 2008 R2(10.50.x) SQL Server 2012(11.x)부터
단일 페이지 할당 없음 아니요, "임의 크기" 페이지 할당에 통합됨
다중 페이지 할당 아니요, "임의 크기" 페이지 할당에 통합됨
CLR 할당
스레드 스택 메모리
Windows에서 직접 할당

동적 메모리 관리

SQL Server 데이터베이스 엔진의 기본 메모리 관리 동작은 시스템에서 메모리 부족을 만들지 않고 필요한 만큼의 메모리를 획득하는 것입니다. SQL Server 데이터베이스 엔진은 Microsoft Windows의 메모리 알림 API를 사용하여 이 작업을 수행합니다.

SQL Server에서 메모리를 동적으로 사용하는 경우 시스템을 주기적으로 쿼리하여 사용 가능한 메모리 양을 확인합니다. 이 사용 가능한 메모리를 유지 관리하면 운영 체제(OS)가 페이징되지 않습니다. 메모리가 적으면 SQL Server는 OS에 메모리를 해제합니다. 더 많은 메모리가 사용 가능한 경우 SQL Server는 더 많은 메모리를 할당할 수 있습니다. SQL Server는 워크로드에 더 많은 메모리가 필요한 경우에만 메모리를 추가합니다. 미사용 서버는 가상 주소 공간의 크기를 증가하지 않습니다. SQL Server에서 동적 메모리 관리를 사용할 때 작업 관리자 및 성능 모니터에서 사용 가능한 메모리가 꾸준히 감소하는 것을 알 수 있는 경우 이는 기본 동작이며 메모리 누수로 인식되어서는 안 됩니다.

최대 서버 메모리는 SQL Server 메모리 할당, 컴파일 메모리, 모든 캐시(버퍼 풀 포함), 쿼리 실행 메모리 부여, 잠금 관리자 메모리 및 CLR1 메모리(기본적으로 sys.dm_os_memory_clerks 있는 모든 메모리 클럭)를 제어합니다.

1 CLR 메모리는 SQL Server 2012(11.x)부터 max_server_memory 할당에서 관리됩니다.

다음 쿼리는 현재 할당된 메모리에 대한 정보를 반환합니다.

SELECT
  physical_memory_in_use_kb/1024 AS sql_physical_memory_in_use_MB,
    large_page_allocations_kb/1024 AS sql_large_page_allocations_MB,
    locked_page_allocations_kb/1024 AS sql_locked_page_allocations_MB,
    virtual_address_space_reserved_kb/1024 AS sql_VAS_reserved_MB,
    virtual_address_space_committed_kb/1024 AS sql_VAS_committed_MB,
    virtual_address_space_available_kb/1024 AS sql_VAS_available_MB,
    page_fault_count AS sql_page_fault_count,
    memory_utilization_percentage AS sql_memory_utilization_percentage,
    process_physical_memory_low AS sql_process_physical_memory_low,
    process_virtual_memory_low AS sql_process_virtual_memory_low
FROM sys.dm_os_process_memory;

스택 크기

스레드 스택 1, CLR 2, 확장 프로시저 .dll 파일의 메모리, 분산 쿼리에서 참조하는 OLE DB 공급자, Transact-SQL 문에서 참조된 자동화 개체 및 SQL Server DLL이 아닌 메모리에 의해 할당된 모든 메모리는 최대 서버 메모리(MB)로 제어되지 않습니다.

1 현재 호스트에서 지정된 수의 선호도가 지정된 CPU에 대한 계산된 기본 작업자 스레드에 대한 자세한 내용은 최대 작업자 스레드 서버 구성 옵션을 구성하는 방법에 대한 문서를 참조하세요. SQL Server 스택 크기는 다음과 같습니다.

SQL Server 아키텍처 OS 아키텍처 스택 크기
x86(32비트) x86(32비트) 512KB
x86(32비트) x64(64비트) 768KB
x64(64비트) x64(64비트) 2048KB
IA64(이타늄) IA64(이타늄) 4096KB

2 CLR 메모리는 SQL Server 2012(11.x)부터 max_server_memory 할당에서 관리됩니다.

SQL Server는 메모리 알림 API QueryMemoryResourceNotification 을 사용하여 SQL Server 메모리 관리자가 메모리 및 릴리스 메모리를 할당할 수 있는 시기를 결정합니다.

SQL Server가 시작되면 시스템의 실제 메모리 양, 서버 스레드 수 및 다양한 시작 매개 변수와 같은 여러 매개 변수를 기반으로 버퍼 풀의 가상 주소 공간 크기를 계산합니다. SQL Server는 버퍼 풀에 대한 프로세스 가상 주소 공간의 계산된 양을 예약하지만 현재 로드에 필요한 양의 실제 메모리만 획득(커밋)합니다.

그런 다음 인스턴스는 워크로드를 지원하기 위해 필요에 따라 메모리를 계속 획득합니다. 더 많은 사용자가 쿼리를 연결하고 실행함에 따라 SQL Server는 요청 시 더 많은 실제 메모리를 확보합니다. SQL Server 인스턴스는 최대 서버 메모리(MB) 할당 대상에 도달하거나 OS가 더 이상 사용 가능한 메모리가 초과되지 않음을 나타내고 최소 서버 메모리 설정보다 많은 메모리가 있으면 메모리를 해제하고 OS는 사용 가능한 메모리 부족이 있음을 나타내기 전까지 실제 메모리를 계속 획득합니다.

다른 애플리케이션은 SQL Server 인스턴스를 실행하는 컴퓨터에서 시작되므로 메모리를 사용하고 사용 가능한 실제 메모리 양은 SQL Server 대상 아래로 떨어집니다. SQL Server 인스턴스가 메모리 사용량을 조정합니다. 다른 애플리케이션이 중지되고 더 많은 메모리를 사용할 수 있게 되면 SQL Server 인스턴스의 메모리 할당 크기가 증가합니다. SQL Server는 초 단위로 몇 MB의 메모리를 해제하고 확보하기 때문에 메모리 할당량 변화에 따라 빠르게 조정됩니다.

최소 및 최대 서버 메모리의 효과

최소 서버 메모리 및 최대 서버 메모리 구성 옵션은 버퍼 풀 및 데이터베이스 엔진의 다른 캐시에서 사용하는 메모리 양에 대한 상한 및 하한을 설정합니다. 버퍼 풀은 최소 서버 메모리에 지정된 메모리 양을 즉시 획득하지 않습니다. 버퍼 풀은 초기화하는 데 필요한 메모리만으로 시작합니다. SQL Server 데이터베이스 엔진 워크로드가 증가함에 따라 워크로드를 지원하는 데 필요한 메모리를 계속 확보합니다. 버퍼 풀은 최소 서버 메모리에 지정된 양에 도달할 때까지 획득한 메모리를 해제하지 않습니다. min server memory에 도달하면 버퍼 풀은 표준 알고리즘을 사용하여 필요할 때 메모리를 확보하고 해제합니다. 유일한 차이점은 버퍼 풀이 최소 서버 메모리에 지정된 수준 아래로 메모리 할당을 삭제하지 않으며 최대 서버 메모리(MB)지정된 수준보다 더 많은 메모리를 획득하지 않는다는 것입니다.

참고 항목

프로세스로서의 SQL Server는 최대 서버 메모리(MB) 옵션에서 지정한 것보다 더 많은 메모리를 획득합니다. 내부 및 외부 구성 요소 모두 추가 메모리를 사용하는 버퍼 풀 외부의 메모리를 할당할 수 있지만 일반적으로 버퍼 풀에 할당된 메모리는 SQL Server에서 사용하는 메모리 중 가장 큰 부분을 나타냅니다.

SQL Server 데이터베이스 엔진에서 획득한 메모리 양은 전적으로 인스턴스에 배치된 워크로드에 따라 달라집니다. 많은 요청을 처리하지 않는 SQL Server 인스턴스는 최소 서버 메모리에 도달하지 못할 수 있습니다.

최소 서버 메모리와 최대 서버 메모리(MB) 모두에 대해 동일한 값을 지정한 경우 SQL Server 데이터베이스 엔진에 할당된 메모리가 해당 값에 도달하면 SQL Server 데이터베이스 엔진은 버퍼 풀에 대한 메모리를 동적으로 해제하고 획득하는 것을 중지합니다.

다른 애플리케이션이 자주 중지되거나 시작되는 컴퓨터에서 SQL Server 인스턴스가 실행되는 경우 SQL Server 인스턴스의 메모리 할당 및 할당 취소로 인해 다른 애플리케이션의 시작 시간이 느려질 수 있습니다. 또한 SQL Server가 단일 컴퓨터에서 실행되는 여러 서버 애플리케이션 중 하나인 경우 시스템 관리자는 SQL Server에 할당된 메모리 양을 제어해야 할 수 있습니다. 이러한 경우 최소 서버 메모리 및 최대 서버 메모리(MB) 옵션을 사용하여 SQL Server에서 사용할 수 있는 메모리 양을 제어할 수 있습니다. 최소 서버 메모리최대 서버 메모리 옵션은 메가바이트 단위로 지정됩니다. 이러한 메모리 구성을 설정하는 방법에 대한 권장 사항을 비롯한 자세한 내용은 서버 메모리 구성 옵션을 참조 하세요.

SQL Server 개체 사양에서 사용하는 메모리

다음 목록에서는 SQL Server의 여러 개체에서 사용되는 대략적인 메모리 양을 설명합니다. 제시된 크기는 추정값이기 때문에 사용 중인 환경과 개체 생성 방법에 따라 다를 수 있습니다.

  • 잠금(잠금 관리자에서 유지 관리): 소유자당 64바이트 + 32바이트
  • 사용자 연결: 약 (3 * network_packet_size + 94KB)

네트워크 패킷 크기는 애플리케이션과 데이터베이스 엔진 간에 통신하는 데 사용되는 TDS(테이블 형식 데이터 스트림) 패킷의 크기입니다. 기본 패킷 크기는 4KB이며 네트워크 패킷 크기 구성 옵션으로 제어됩니다.

MARS(여러 활성 결과 집합)를 사용하도록 설정하면 사용자 연결은 약 (3 + 3 * num_logical_connections) * network_packet_size + 94KB입니다.

min memory per query 효과

쿼리 구성당 최소 메모리 옵션은 쿼리 실행에 할당될 최소 메모리 양(킬로바이트)을 설정합니다. 최소 메모리 부여라고도 합니다. 모든 쿼리는 요청된 최소 메모리가 보호될 수 있을 때까지, 실행을 시작하기 전에 또는 쿼리 대기 서버 구성 옵션에서 지정된 값을 초과할 때까지 기다려야 합니다. 이 시나리오에서 누적된 대기 유형은 .입니다 RESOURCE_SEMAPHORE.

Important

특히 사용량이 많은 시스템에서 쿼리 서버 구성당 최소 메모리 옵션을 너무 높게 설정하지 마세요. 이렇게 하면 다음이 발생할 수 있기 때문입니다.

  • 메모리 리소스에 대한 경쟁 증가.
  • 런타임에 필요한 메모리가 이 구성보다 낮더라도 모든 단일 쿼리에 대한 메모리 양을 늘려 동시성을 줄입니다.

이 구성 사용에 대한 권장 사항은 쿼리 서버 구성 옵션당 최소 메모리 구성을 참조 하세요.

메모리 부여 고려 사항

행 모드 실행의 경우 어떤 조건에서도 초기 메모리 부여를 초과할 수 없습니다. 해시 또는 정렬 작업을 실행하는 데 초기 권한 부여보다 더 많은 메모리가 필요한 경우 디스크로 분산됩니다. 분산되는 해시 작업은 Workfile에서 지원되지만 분산되는 정렬 작업은 Worktable에서 tempdb지원됩니다.

정렬 작업 중에 발생하는 분산을 정렬 경고라고 합니다. 정렬 경고는 정렬 작업이 메모리에 맞지 않음을 나타냅니다. 여기에는 인덱스 생성과 관련된 정렬 작업이 포함되지 않으며 쿼리 내의 정렬 작업(예: ORDER BY 문에 SELECT 사용되는 절)만 포함됩니다.

해시 작업 중에 발생하는 유출을 해시 경고라고 합니다. 해시 작업 중에 해시 재귀 또는 해시 중단(해시 구제책)이 발생한 경우에 발생합니다.

  • 해시 재귀는 빌드 입력이 사용 가능한 메모리에 맞지 않을 때 발생하므로 입력이 별도로 처리되는 여러 파티션으로 분할됩니다. 이러한 파티션이 여전히 사용 가능한 메모리에 맞지 않는 경우 하위 파트로 분할되며 별도로 처리됩니다. 이 분할 프로세스는 각 파티션이 사용 가능한 메모리에 맞거나 최대 재귀 수준에 도달할 때까지 계속됩니다.
  • 해시 연산이 최대 재귀 수준에 도달하면 해시 재귀 한도 초과가 발생하며 대체 계획으로 변경하여 남은 파티션 데이터를 처리합니다. 이러한 이벤트로 인해 서버의 성능이 저하될 수 있습니다.

일괄 처리 모드 실행의 경우 초기 메모리 부여는 기본적으로 특정 내부 임계값까지 동적으로 증가할 수 있습니다. 이 동적 메모리 부여 메커니즘은 일괄 처리 모드에서 실행되는 해시 또는 정렬 작업의 메모리 상주 실행을 허용하도록 설계되었습니다. 이러한 작업이 여전히 메모리에 맞지 않으면 디스크로 분산됩니다.

실행 모드에 대한 자세한 내용은 쿼리 처리 아키텍처 가이드를 참조하세요.

버퍼 관리

SQL Server 데이터베이스의 주요 목적은 데이터를 저장하고 검색하는 것이므로 집약적인 디스크 I/O는 데이터베이스 엔진의 핵심 특성입니다. 디스크 I/O 작업은 많은 리소스를 사용하고 완료하는 데 비교적 오랜 시간이 걸릴 수 있으므로 SQL Server는 I/O를 매우 효율적으로 만드는 데 중점을 둡니다. 버퍼 관리는 이러한 효율성을 달성하는 핵심 구성 요소입니다. 버퍼 관리 구성 요소는 데이터베이스 페이지에 액세스 및 업데이트하는 버퍼 관리자데이터베이스 파일 I/O를 줄이기 위한 버퍼 캐시(버퍼 풀이라고도 함)의 두 가지 메커니즘으로 구성됩니다.

버퍼 관리 작동 방식

버퍼는 메모리의 8KB 페이지로, 데이터 또는 인덱스 페이지와 크기가 같습니다. 따라서 버퍼 캐시는 8KB 페이지로 나뉩니다. 버퍼 관리자는 데이터베이스 디스크 파일에서 버퍼 캐시로 데이터 또는 인덱스 페이지를 읽고 수정된 페이지를 디스크에 다시 쓰기 위한 함수를 관리합니다. 버퍼 관리자가 더 많은 데이터를 읽을 버퍼 영역이 필요할 때까지 페이지는 버퍼 캐시에 남아 있습니다. 데이터는 수정되는 경우에만 다시 디스크에 쓰여집니다. 버퍼 캐시의 데이터는 디스크에 다시 기록되기 전에 여러 번 수정할 수 있습니다. 자세한 내용은 페이지 읽기 및 페이지 쓰기를 참조하세요.

SQL Server가 시작되면 시스템의 실제 메모리 양, 구성된 최대 서버 스레드 수 및 다양한 시작 매개 변수와 같은 여러 매개 변수를 기반으로 버퍼 캐시의 가상 주소 공간 크기를 계산합니다. SQL Server는 버퍼 캐시에 대해 이 계산된 프로세스 가상 주소 공간(메모리 대상이라고 함)을 예약하지만 현재 로드에 필요한 양의 실제 메모리만 획득(커밋)합니다. sys.dm_os_sys_info 카탈로그 뷰에서 committed_target_kbcommitted_kb 열을 쿼리하여 메모리 대상으로 예약된 페이지 수와 버퍼 캐시에 현재 커밋된 페이지 수를 각각 반환할 수 있습니다.

SQL Server 시작 시간과 버퍼 캐시가 해당 메모리 대상을 확보하는 시간 사이의 간격을 램프 업(ramp-up)이라고 합니다. 이 시간 동안 읽기 요청은 필요에 따라 버퍼를 채웁니다. 예를 들어 단일 8KB 페이지 읽기 요청은 단일 버퍼 페이지를 채웁니다. 즉, 램프 업은 클라이언트 요청의 개수 및 유형에 따라 달라집니다. 단일 페이지 읽기 요청을 정렬된 8개 페이지 요청(1개 익스텐트 구성)으로 변환하여 진입을 신속하게 처리할 수 있습니다. 이를 통해 메모리가 많은 컴퓨터에서 특히 램프 업이 더 빠르게 완료될 수 있습니다. 페이지 및 익스텐트에 대한 자세한 내용은 페이지 및 익스텐트 아키텍처 가이드를 참조 하세요.

버퍼 관리자는 SQL Server 프로세스에서 대부분의 메모리를 사용하므로 메모리 관리자와 협력하여 다른 구성 요소가 해당 버퍼를 사용할 수 있도록 합니다. 버퍼 관리자는 주로 다음 구성 요소와 상호 작용합니다.

  • 리소스 관리자는 전체 메모리 사용량을 제어하고 32비트 플랫폼에서 주소 공간 사용량을 제어합니다.
  • 낮은 수준의 파일 I/O 작업을 위한 데이터베이스 관리자 및 SQLOS(SQL Server 운영 체제).
  • 미리 쓰기 로깅을 위한 로그 관리자

지원되는 기능

버퍼 관리자는 다음과 같은 기능을 지원합니다.

  • 버퍼 관리자는 NUMA(비균일 메모리 액세스) 를 인식합니다. 버퍼 캐시 페이지는 하드웨어 NUMA 노드에 분산되므로 스레드가 외장 메모리가 아닌 로컬 NUMA 노드에 할당된 버퍼 페이지에 액세스할 수 있습니다.

  • 버퍼 관리자는 핫 추가 메모리지원하므로 사용자가 서버를 다시 시작하지 않고도 실제 메모리를 추가할 수 있습니다.

  • 버퍼 관리자는 64비트 플랫폼에서 큰 페이지를 지원합니다. 페이지 크기는 Windows 버전과 관련이 있습니다.

    참고 항목

    SQL Server 2012(11.x) 이전에는 SQL Server에서 큰 페이지를 사용하도록 설정하려면 추적 플래그 834필요합니다.

  • 버퍼 관리자는 동적 관리 뷰를 통해 노출되는 추가 진단을 제공합니다. 이러한 보기를 사용하여 SQL Server와 관련된 다양한 운영 체제 리소스를 모니터링할 수 있습니다. 예를 들어 sys.dm_os_buffer_descriptors 뷰를 사용하여 버퍼 캐시의 페이지를 모니터링할 수 있습니다.

디스크 I/O

버퍼 관리자는 데이터베이스에 대한 읽기 및 쓰기만 수행합니다. 열기, 닫기, 확장, 축소와 같은 기타 파일 및 데이터베이스 작업은 데이터베이스 관리자 및 파일 관리자 구성 요소에 의해 수행됩니다.

버퍼 관리자의 디스크 I/O 작업에는 다음과 같은 특징이 있습니다.

  • 모든 I/O는 비동기적으로 수행되므로 I/O 작업이 백그라운드에서 발생하는 동안 호출 스레드가 처리를 계속할 수 있습니다.
  • 선호도 I/O 옵션을 사용하지 않는 한 모든 I/O가 호출 스레드에서 발급됩니다. 선호도 I/O 마스크 옵션은 SQL Server 디스크 I/O를 지정된 CPU 하위 집합에 바인딩합니다. 고성능 SQL Server OLTP(온라인 트랜잭션 처리) 환경에서 이러한 확장을 통해 I/O를 실행하는 SQL Server 스레드의 성능을 향상시킬 수 있습니다.
  • 다중 페이지 I/O는 분산 수집 I/O로 수행되므로 데이터를 인접하지 않은 메모리 영역으로 또는 이러한 영역 밖으로 전송할 수 있습니다. 즉, SQL Server는 여러 물리적 I/O 요청을 피하면서 버퍼 캐시를 빠르게 채우거나 플러시할 수 있습니다.

긴 I/O 요청

버퍼 관리자는 15초 이상 미해결된 모든 I/O 요청에 대해 보고합니다. 이를 통해 시스템 관리자는 SQL Server 문제와 I/O 하위 시스템 문제를 구분할 수 있습니다. 오류 메시지 833이 보고되고 다음과 같이 SQL Server 오류 로그에 표시됩니다.

SQL Server has encountered ## occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [##] in database [##] (#). The OS file handle is 0x00000. The offset of the latest long I/O is: 0x00000.

긴 I/O는 읽기 또는 쓰기일 수 있습니다. 현재 메시지에 표시되지 않습니다. 긴 I/O 메시지는 오류가 아니라 경고입니다. SQL Server의 문제가 아니라 기본 I/O 시스템에 문제가 있음을 나타냅니다. 이 메시지는 시스템 관리자가 SQL Server 응답 시간 저하의 원인을 보다 신속하게 파악하고 SQL Server를 제어할 수 없는 문제를 구분하는 데 도움이 되는 것으로 보고됩니다. 따라서 아무 작업도 필요하지 않지만 시스템 관리자는 I/O 요청이 너무 오래 걸린 이유와 시간이 정당한지 여부를 조사해야 합니다.

긴 I/O 요청의 원인

긴 I/O 메시지는 I/O가 영구적으로 차단되고 완료되지 않음(손실된 I/O라고 함) 또는 아직 완료되지 않았음을 나타낼 수 있습니다. 손실된 I/O로 인해 래치 시간 제한이 발생하는 경우가 많지만 메시지에서 어떤 시나리오가 발생하는지 알 수 없습니다.

긴 I/O는 디스크 하위 시스템에 너무 많은 SQL Server 워크로드를 나타내는 경우가 많습니다. 다음과 같은 경우에 부적절한 디스크 하위 시스템이 표시될 수 있습니다.

  • SQL Server 워크로드가 많은 동안 오류 로그에 여러 긴 I/O 메시지가 표시됩니다.
  • 성능 모니터 카운터는 긴 디스크 대기 시간, 긴 디스크 큐 또는 디스크 유휴 시간을 표시합니다.

또한 I/O 경로의 구성 요소(예: 드라이버, 컨트롤러 또는 펌웨어)가 디스크 헤드의 현재 위치에 가까운 최신 요청을 서비스하기 위해 이전 I/O 요청의 서비스를 지속적으로 연기하여 I/O가 길어질 수도 있습니다. 읽기/쓰기 헤드의 현재 위치에 가장 가까운 요청을 우선 순위에 따라 처리하는 일반적인 기술을 "엘리베이터 검색"이라고 합니다. 대부분의 I/O가 즉시 서비스되기 때문에 성능 모니터 도구로 확증하기 어려울 수 있습니다. 긴 I/O 요청은 백업 및 복원, 테이블 검색, 정렬, 인덱스 만들기, 대량 로드 및 파일 제로와 같은 대량의 순차 I/O를 수행하는 워크로드에 의해 악화될 수 있습니다.

이전 조건과 관련이 없는 격리된 긴 I/O는 하드웨어 또는 드라이버 문제로 인해 발생할 수 있습니다. 시스템 이벤트 로그에는 문제를 진단하는 데 도움이 되는 관련 이벤트가 포함될 수 있습니다.

메모리 압력 감지

메모리 압력은 메모리 부족으로 인한 조건이며 다음과 같은 결과를 초래할 수 있습니다.

  • 추가 I/O(예: 매우 활성 지연 기록기 백그라운드 스레드)
  • 더 높은 재컴파일 비율
  • 더 오래 실행되는 쿼리(메모리 부여 대기가 있는 경우)
  • 추가 CPU 주기

이 상황은 외부 또는 내부 원인에 의해 트리거될 수 있습니다. 외부 원인은 다음과 같습니다.

  • 사용 가능한 실제 메모리(RAM)가 낮습니다. 이로 인해 시스템이 현재 실행 중인 프로세스의 작업 집합을 트리밍하므로 전반적인 속도가 느려질 수 있습니다. SQL Server는 버퍼 풀의 커밋 대상을 줄이고 내부 캐시를 더 자주 트리밍하기 시작할 수 있습니다.
  • 전반적으로 사용 가능한 시스템 메모리(시스템 페이지 파일 포함)가 낮은 수준입니다. 이로 인해 현재 할당된 메모리를 페이징할 수 없으므로 시스템에서 메모리 할당에 실패할 수 있습니다.

내부 원인은 다음과 같습니다.

  • SQL Server 데이터베이스 엔진이 낮은 메모리 사용량 한도를 설정하는 경우 외부 메모리 압력에 응답합니다.
  • 최대 서버 메모리 구성을 줄여 메모리 설정을 수동으로 낮췄습니다.
  • 여러 캐시 간의 내부 구성 요소의 메모리 분포 변경

SQL Server 데이터베이스 엔진은 동적 메모리 관리의 일환으로 메모리 압력을 감지하고 처리하는 데 전념하는 프레임워크를 구현합니다. 이 프레임워크에는 리소스 모니터라는 배경 작업이 포함되어 있습니다. 리소스 모니터 태스크는 외부 및 내부 메모리 표시기의 상태를 모니터링합니다. 이러한 표시기 중 하나가 상태를 변경하면 해당 알림을 계산하고 브로드캐스트합니다. 이러한 알림은 각 엔진 구성 요소의 내부 메시지이며 링 버퍼에 저장됩니다.

두 링 버퍼는 동적 메모리 관리와 관련된 정보를 보유합니다.

  • 리소스 모니터 활동을 추적하는 리소스 모니터 링 버퍼는 메모리 가 중 신호 여부를 나타냅니다. 이 링 버퍼에는 현재 RESOURCE_MEMPHYSICAL_LOWRESOURCE_MEMPHYSICAL_STEADYRESOURCE_MEMVIRTUAL_LOW상태의 RESOURCE_MEMPHYSICAL_HIGH상태에 따라 상태 정보가 있습니다.
  • 각 Resource Governor 리소스 풀에 대한 메모리 알림 레코드를 포함하는 메모리 브로커 링 버퍼입니다. 내부 메모리 압력이 감지되면 메모리를 할당하는 구성 요소에 대해 메모리 부족 알림이 켜져 캐시 간의 메모리 균형을 조정하는 작업을 트리거합니다.

메모리 브로커는 각 구성 요소에 의한 메모리 수요 소비를 모니터링한 다음 수집된 정보에 따라 이러한 각 구성 요소에 대한 메모리의 최적 값을 계산하고 최적 값을 계산합니다. 각 Resource Governor 리소스 풀에 대한 broker 집합이 있습니다. 이 정보는 각 구성 요소에 브로드캐스트되어 필요에 따라 사용량을 확대하거나 축소합니다.

메모리 브로커에 대한 자세한 내용은 sys.dm_os_memory_brokers 참조하세요.

오류 검색

데이터베이스 페이지는 디스크에 기록된 시점부터 다시 읽을 때까지 페이지의 무결성을 보장하는 데 도움이 되는 두 가지 선택적 메커니즘 중 하나인 조각난 페이지 보호 및 체크섬 보호 중 하나를 사용할 수 있습니다. 이러한 메커니즘을 사용하면 데이터 스토리지뿐만 아니라 컨트롤러, 드라이버, 케이블, 심지어는 운영 체제를 비롯한 하드웨어 구성 요소의 정확성을 확인하는 독자적인 방법을 활용할 수 있습니다. 보호는 디스크에 쓰기 직전에 페이지에 추가되고 디스크에서 읽은 후 확인됩니다.

SQL Server는 체크섬, 조각난 페이지 또는 기타 I/O 오류로 읽기가 실패할 경우 4번 다시 시도합니다. 다시 시도 중 하나에서 읽기가 성공하면 메시지가 오류 로그에 기록되고 읽기를 트리거한 명령이 계속됩니다. 다시 시도에 실패하면 오류 메시지 824와 함께 명령이 실패합니다.

사용되는 페이지 보호의 종류는 페이지를 포함하는 데이터베이스의 특성입니다. 체크섬 보호는 SQL Server 2005(9.x) 이상에서 만든 데이터베이스에 대한 기본 보호입니다. 페이지 보호 메커니즘은 데이터베이스를 만들 때 지정되며 ALTER DATABASE SET. sys.databases 카탈로그 뷰의 열 또는 IsTornPageDetectionEnabled DATABASEPROPERTYEX 함수의 속성을 쿼리 page_verify_option 하여 현재 페이지 보호 설정을 확인할 수 있습니다.

참고 항목

페이지 보호 설정을 변경해도 새 설정이 전체 데이터베이스에 즉시 영향을 주지는 않습니다. 대신 페이지는 다음에 기록할 때마다 데이터베이스의 현재 보호 수준을 채택합니다. 즉, 데이터베이스는 다양한 종류의 보호가 있는 페이지로 구성될 수 있습니다.

조각난 페이지 보호

SQL Server 2000(8.x)에 도입된 조각난 페이지 보호는 주로 정전으로 인한 페이지 손상을 감지하는 방법입니다. 예를 들어 예기치 않은 정전으로 디스크에 기록된 페이지의 일부만 남을 수 있습니다. 조각난 페이지 보호를 사용하는 경우 8KB(KB) 데이터베이스 페이지의 각 512바이트 섹터에 대한 특정 2비트 서명 패턴이 디스크에 기록될 때 데이터베이스 페이지 헤더에 저장됩니다.

디스크에서 페이지를 읽으면 페이지 헤더에 저장된 조각난 비트가 실제 페이지 섹터 정보와 비교됩니다. 서명 패턴은 이진 01 패턴과 10 모든 쓰기를 번갈아 가며, 따라서 섹터의 일부만 디스크에 적용한 시기를 항상 알 수 있습니다. 나중에 페이지를 읽을 때 비트가 잘못된 상태이면 페이지가 잘못 쓰여지고 조각난 페이지가 검색됩니다. 조각난 페이지 검색은 최소한의 리소스를 사용합니다. 그러나 디스크 하드웨어 오류로 인한 모든 오류를 검색하지는 않습니다. 조각난 페이지 검색 설정에 대한 자세한 내용은 ALTER DATABASE SET 옵션(Transact-SQL)을 참조하세요.

체크섬 보호

SQL Server 2005(9.x)에 도입된 체크섬 보호는 보다 강력한 데이터 무결성 검사를 제공합니다. 체크섬은 페이지 머리글에 기록되고 저장되는 각 페이지의 데이터에 대해 계산됩니다. 저장된 체크섬이 있는 페이지를 디스크에서 읽을 때마다 데이터베이스 엔진은 페이지의 데이터에 대한 체크섬을 다시 계산하고 새 체크섬이 저장된 체크섬과 다를 경우 오류 824를 발생시킵니다. 체크섬 보호는 페이지의 모든 바이트에 의해 영향을 받으므로 조각난 페이지 보호보다 더 많은 오류를 catch할 수 있지만 많은 리소스를 소비합니다.

체크섬을 설정하면 버퍼 관리자가 디스크에서 페이지를 읽을 때마다 전원 오류 및 결함이 있는 하드웨어나 펌웨어로 인한 오류를 검색할 수 있습니다. 체크섬 설정에 대한 자세한 내용은 ALTER DATABASE SET 옵션(Transact-SQL)을 참조하세요.

Important

사용자 또는 시스템 데이터베이스가 SQL Server 2005(9.x) 이상으로 업그레이드되면 PAGE_VERIFY 값(NONE 또는 TORN_PAGE_DETECTION)이 유지됩니다. 를 사용하는 CHECKSUM것이 좋습니다. TORN_PAGE_DETECTION 는 더 적은 리소스를 사용할 수 있지만 보호의 최소 하위 집합을 CHECKSUM 제공합니다.

균일하지 않은 메모리 액세스 이해

SQL Server는 NUMA(비균일 메모리 액세스)를 인식하며 특별한 구성 없이 NUMA 하드웨어에서 잘 수행됩니다. 클록 속도와 프로세서 수가 증가함에 따라 이 추가 처리 능력을 사용하는 데 필요한 메모리 대기 시간을 줄이기가 점점 더 어려워집니다. 이를 피하기 위해 하드웨어 공급업체는 대형 L3 캐시를 제공하지만 이는 제한된 솔루션일 뿐입니다. NUMA 아키텍처는 이 문제에 대한 확장 가능한 솔루션을 제공합니다.

SQL Server는 애플리케이션을 변경하지 않고도 NUMA 기반 컴퓨터를 활용하도록 설계되었습니다. 자세한 내용은 방법: 소프트 NUMA를 사용하도록 SQL Server 구성을 참조하세요.

메모리 개체의 동적 파티션

SQL Server의 메모리 개체라고 하는 힙 할당자를 사용하면 데이터베이스 엔진이 힙에서 메모리를 할당할 수 있습니다. sys.dm_os_memory_objects DMV를 사용하여 추적할 수 있습니다.

CMemThread는 여러 스레드에서 동시 메모리 할당을 허용하는 스레드로부터 안전한 메모리 개체 형식입니다. 올바른 추적을 위해 CMemThread 개체는 동기화 구문(뮤텍스)을 사용하여 단일 스레드만 한 번에 중요한 정보를 업데이트하도록 합니다.

참고 항목

CMemThread 개체 형식은 데이터베이스 엔진 코드 베이스 전체에서 다양한 할당에 사용되며 노드 또는 CPU별로 전역적으로 분할할 수 있습니다.

그러나 뮤텍스를 사용하면 여러 스레드가 동일한 메모리 개체에서 동시에 할당할 때 경합이 발생할 수 있습니다. 따라서 SQL Server에는 PMO(분할된 메모리 개체)의 개념이 있으며 각 파티션은 단일 CMemThread 개체로 표시됩니다. 메모리 개체의 분할은 정적으로 정의되며 만든 후에는 변경할 수 없습니다. 메모리 할당 패턴은 하드웨어 및 메모리 사용량과 같은 측면에 따라 크게 다르므로 완벽한 분할 패턴을 미리 작성할 수 없습니다.

대부분의 경우 단일 파티션을 사용하는 것으로 충분하지만 일부 시나리오에서는 경합이 발생할 수 있으며, 이는 매우 분할된 메모리 개체로만 방지할 수 있습니다. 더 많은 파티션이 다른 비효율성을 초래하고 메모리 조각화를 증가시킬 수 있으므로 각 메모리 개체를 분할하는 것은 바람직하지 않습니다.

참고 항목

SQL Server 2016(13.x) 이전에는 추적 플래그 8048을 사용하여 노드 기반 PMO가 CPU 기반 PMO가 되도록 할 수 있습니다. SQL Server 2014(12.x) SP2 및 SQL Server 2016(13.x)부터 이 동작은 동적이며 엔진에 의해 제어됩니다.

SQL Server 2014(12.x) SP2 및 SQL Server 2016(13.x)부터 데이터베이스 엔진은 특정 CMemThread 개체에 대한 경합을 동적으로 감지하고 개체를 노드당 또는 CPU별 구현으로 승격할 수 있습니다. 승격된 후에는 SQL Server 프로세스가 다시 시작될 때까지 PMO가 승격된 상태로 유지됩니다. CMemThread 경합은 sys.dm_os_wait_stats DMV에서 높은 CMEMTHREAD 대기가 존재하고 sys.dm_os_memory_objects DMV 열, exclusive_allocations_countpartition_typewaiting_tasks_count를 관찰하여 검색할 수 있습니다contention_factor.

다음 단계