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

아티클
09/25/2024

적용 대상: SQL Server Azure SQL 데이터베이스 Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System(PDW)

Windows 가상 메모리 관리자

주소 공간의 커밋된 영역은 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(Address Windowing Extensions) 및 AWE에 필요한 LPIM(메모리의 페이지 잠금) 권한을 사용하여 대부분의 SQL Server 프로세스 메모리를 낮은 가상 메모리 조건에서 실제 RAM에 잠글 수 있습니다. 이는 32비트 및 64비트 AWE 할당 모두에서 발생합니다. 메모리 잠금은 AWE 메모리가 메모리 페이징을 제어하는 Windows의 가상 메모리 관리자를 통과하지 않기 때문에 발생합니다. AWE 메모리 할당 API에는 메모리의 페이지 잠금(SeLockMemoryPrivilege) 권한이 필요합니다. AllocateUserPhysicalPages 참고를 참조하세요. 따라서 AWE API를 사용하는 데 따른 주요 이점은 시스템에 메모리 압력이 있는 경우 대부분의 메모리가 RAM에 유지된다는 것입니다. SQL Server에서 AWE를 사용하도록 허용하는 방법에 대한 자세한 내용은 메모리에 페이지 잠금 옵션 사용(Windows)을 참조하세요.

LPIM 권한이 부여된 경우(32비트 또는 64비트 시스템에서)에는 기본값인 2,147,483,647MB(메가바이트)를 그대로 사용하지 않고 max server memory (MB)를 특정 값으로 설정하는 것이 좋습니다. 자세한 내용은 서버 메모리 구성: 수동으로 옵션 설정 및 메모리의 페이지 잠금(LPIM)을 참조하세요.

LPIM을 사용하지 않는 경우, SQL Server는 기존 메모리 및 OS 메모리 소모 시에 사용되도록 전환되며, 오류 로그에 [MSSQLSERVER_17890] 오류(errors-events/mssqlserver-17890-database-engine-error.md)가 보고될 수 있습니다. 다음 예제에서는 이 오류를 보여 줍니다.

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부터 메모리 관리 변경

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

SPA(단일 페이지 할당자)는 SQL Server 프로세스에서 8KB 이하인 메모리 할당만 포함합니다. 최대 서버 메모리(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) 및 SQL Server 2016(13.x) 이상 버전의 64비트 버전 모두에 적용됩니다.

다음 표에서는 특정 유형의 메모리 할당이 최대 서버 메모리(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가 최대 서버 메모리 설정을 통해 메모리를 커밋할 수 있습니다.

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

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

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

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

이 동작을 자주 관찰하는 경우 리소스 모니터가 더 빨리 정리할 수 있도록 sSQL Server 2019 (15.x)에서 추적 플래그 8121을 사용하는 것이 좋습니다. SQL Server 2022(16.x)부터 이 기능은 기본적으로 사용하도록 설정되며 추적 플래그는 영향을 주지 않습니다.

SQL Server 2012부터 memory_to_reserve 변경

이전 버전의 SQL Server에서 SQL Server 메모리 관리자는 MPA(다중 페이지 할당자), CLR 할당자, SQL Server 프로세스의 스레드 스택에 대한 메모리 할당 및 DWA(직접 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 메모리 할당, 컴파일 메모리, 모든 캐시(버퍼 풀 포함), 쿼리 실행 메모리 부여, 잠금 관리자 메모리 및 CLR¹ 메모리(기본적으로 sys.dm_os_memory_clerks에 있는 모든 메모리 클럭)를 제어합니다.

¹ SQL Server 2012(11.x)에서 CLR 메모리는 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;

스택 크기

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

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

SQL Server 아키텍처	OS 아키텍처	스택 크기
x86(32비트)	x86(32비트)	512KB
x86(32비트)	x64(64비트)	768KB
x64(64비트)	x64(64비트)	2,048KB
IA64(Itanium)	IA64(Itanium)	4,096KB

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

SQL Server는 메모리 알림 API QueryMemoryResourceNotification을 사용하여 SQL Server 메모리 관리자가 메모리를 할당하고 해제하는 시기를 결정합니다.

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

그런 다음 인스턴스는 워크로드를 지원하는 데 필요한 메모리를 계속 획득합니다. 더 많은 사용자가 쿼리를 연결하고 실행함에 따라 SQL Server는 요청 시 더 많은 실제 메모리를 확보합니다. SQL Server 인스턴스는 최대 서버 메모리(MB) 할당 목표량에 도달하거나 Windows에서 더 이상 여유 메모리가 없다고 표시할 때까지 실제 메모리를 계속 확보합니다. 최소 서버 메모리 설정보다 메모리가 많거나 OS에서 사용 가능한 메모리가 부족하다고 표시하면 메모리를 해제합니다.

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

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

최소 서버 메모리 및 최대 서버 메모리 구성 옵션은 데이터베이스 엔진의 버퍼 풀 및 기타 캐시에서 사용하는 메모리 양의 상한 및 하한을 설정합니다. 버퍼 풀은 min server memory에 지정된 메모리의 양을 즉시 확보하지 않습니다. 버퍼 풀은 초기화하는 데 필요한 메모리만으로 시작합니다. 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 + 94 KB)

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

다중 활성 결과 집합(MARS)을 사용할 수 있으면 사용자 연결은 약 (3 + 3 num_logical_connections) * network_packet_size + 94KB입니다.

min memory per query 효과

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

Important

특히 사용량이 많은 시스템에서 쿼리당 최소 메모리 서버 구성 옵션을 너무 높게 설정하지 마세요. 그럴 ㄹ경우 다음 문제가 발생할 수 있습니다.

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

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

메모리 부여 고려 사항

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

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

해시 작업 중에 발생하는 분산은 해시 경고 이벤트 클래스라고 합니다. 이는 해시 작업 중에 해시 재귀 또는 해시 중단(해시 구제책)이 발생하는 경우에 발생합니다.

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

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

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

버퍼 관리

SQL Server 데이터베이스의 주 목적은 데이터 저장 및 검색이므로 데이터베이스 엔진의 핵심 특성은 집중형 디스크 I/O입니다. 또한 디스크 I/O 작업은 리소스를 많이 소모하고 완료하는 데 상대적으로 시간이 오래 걸릴 수 있으므로, SQL Server에서는 I/O의 효율성을 높이는 데 중점을 둡니다. 버퍼 관리는 이러한 효율성을 달성하는 데 있어 핵심적인 구성 요소입니다. 버퍼 관리 구성 요소는 데이터베이스 페이지에 액세스하고 업데이트하는 버퍼 관리자와 데이터베이스 파일 I/O를 줄이기 위한 버퍼 캐시(버퍼 풀이라고도 함)의 두 가지 메커니즘으로 구성되어 있습니다.

SQL Server의 디스크 I/O에 대한 자세한 설명은 SQL Server I/O 기본 사항을 참조하세요.

버퍼 관리 작업 방법

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

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

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

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

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

지원되는 기능

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

버퍼 관리자는 NUMA(Non-Uniform Memory Access)를 인식합니다. 버퍼 캐시 페이지는 하드웨어 NUMA 노드에 분산되므로, 스레드가 외장 메모리가 아닌 로컬 NUMA 노드에 할당된 버퍼 페이지에 액세스할 수 있습니다.
버퍼 관리자는 핫 추가 메모리를 지원하므로 사용자가 서버를 다시 시작하지 않고도 실제 메모리를 추가할 수 있습니다.
버퍼 관리자는 64비트 플랫폼에서 큰 페이지를 지원합니다. 페이지 크기는 Windows 버전에 따라 다릅니다.

참고 항목

SQL Server 2012(11.x) 이전에는 SQL Server에서 큰 페이지를 사용하도록 설정하려면 추적 플래그 834가 필요합니다.
버퍼 관리자는 동적 관리 뷰를 통해 표시되는 추가 진단 기능을 제공합니다. 이러한 보기를 사용하여 SQL Server와 관련된 다양한 운영 체제 리소스를 모니터링할 수 있습니다. 예를 들어 sys.dm_os_buffer_descriptors 뷰를 사용하여 버퍼 캐시의 페이지를 모니터링할 수 있습니다.

메모리 압력 감지

메모리 압력은 메모리 부족으로 인해 발생하는 상태이며, 다음과 같은 결과를 초래할 수 있습니다.

추가 I/O(예: 매우 활성화된 지연 Writer 백그라운드 스레드)
더 높은 재컴파일 비율
더 오래 실행되는 쿼리(메모리 부여 대기가 존재하는 경우)
추가 CPU 사이클

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

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

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

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

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

두 링 버퍼에 동적 메모리 관리와 관련된 정보가 저장됩니다.

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

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

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

오류 검색

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

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

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

참고 항목

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

조각난 페이지 보호

SQL Server 2000(8.x)에 도입된 조각난 페이지 보호는 주로 정전으로 인한 페이지 손상을 감지하는 기능입니다. 예를 들어 예기치 않은 정전이 발생하면 디스크에 기록된 페이지의 일부만 남을 수 있습니다. 조각난 페이지 보호를 사용하면 페이지를 디스크에 쓸 때 8KB 데이터베이스 페이지의 각 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 보호의 최소 하위 집합만 제공합니다.

NUMA(Non-Uniform Memory Access) 이해

SQL Server는 NUMA(Non-Uniform Memory Access)를 인식하며 특별한 구성 없이 NUMA 하드웨어에서 잘 작동합니다. 클록 속도와 프로세서 수가 증가함에 따라 이 추가 처리 능력을 사용하는 데 필요한 메모리 대기 시간을 줄이기가 갈수록 어려워집니다. 이러한 문제를 피하기 위해 하드웨어 공급업체는 대형 L3 캐시를 제공하지만, 이는 제한적인 솔루션일 뿐입니다. NUMA 아키텍처는 확장성 있는 솔루션으로 이 문제를 해결합니다.

SQL Server는 애플리케이션을 변경하지 않고도 NUMA 기반 컴퓨터를 활용할 수 있도록 설계되었습니다. 자세한 내용은 Soft-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별 구현으로 승격할 수 있습니다. PMO는 승격된 후 SQL Server 프로세스가 다시 시작될 때까지 승격된 상태로 유지됩니다. CMemThread 경합은 sys.dm_os_wait_stats DMV에서 높은 CMEMTHREAD 대기가 존재한다는 사실과 sys.dm_os_memory_objects DMV 열 contention_factor, partition_type, exclusive_allocations_count 및 waiting_tasks_count를 확인하여 감지할 수 있습니다.

다음을 통해 공유

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

Windows 가상 메모리 관리자

SQL Server 메모리 아키텍처

기존(가상) 메모리

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

SQL Server 2012부터 메모리 관리 변경

SQL Server가 최대 서버 메모리 설정을 통해 메모리를 커밋할 수 있습니다.

SQL Server 2012부터 memory_to_reserve 변경

동적 메모리 관리

스택 크기

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

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

min memory per query 효과

메모리 부여 고려 사항

버퍼 관리

버퍼 관리 작업 방법

지원되는 기능

메모리 압력 감지

오류 검색

조각난 페이지 보호

체크섬 보호

NUMA(Non-Uniform Memory Access) 이해

메모리 개체의 동적 파티션

피드백

추가 리소스

다음을 통해 공유

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

Windows 가상 메모리 관리자

SQL Server 메모리 아키텍처

기존(가상) 메모리

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

SQL Server 2012부터 메모리 관리 변경

SQL Server가 최대 서버 메모리 설정을 통해 메모리를 커밋할 수 있습니다.

SQL Server 2012부터 memory_to_reserve 변경

동적 메모리 관리

스택 크기

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

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

min memory per query 효과

메모리 부여 고려 사항

버퍼 관리

버퍼 관리 작업 방법

지원되는 기능

메모리 압력 감지

오류 검색

조각난 페이지 보호

체크섬 보호

NUMA(Non-Uniform Memory Access) 이해

메모리 개체의 동적 파티션

관련 콘텐츠

피드백

추가 리소스