SQL Server 메모리 부여로 인한 성능 저하 또는 메모리 부족 문제 해결
메모리 부여란?
QE(쿼리 실행) 예약, 쿼리 실행 메모리, 작업 영역 메모리 및 메모리 예약이라고도 하는 메모리 부여는 쿼리 실행 시 메모리 사용을 설명합니다. SQL Server 다음 목적 중 하나 이상에 대해 쿼리를 실행하는 동안 이 메모리를 할당합니다.
- 정렬 작업
- 해시 작업
- 대량 복사 작업(일반적인 문제가 아님)
- 런타임에 인덱스 빌드에 해시 사전/테이블이 사용되기 때문에 COLUMNSTORE 인덱스에 삽입하는 것을 포함하여 인덱스 만들기(일반적인 문제가 아님)
일부 컨텍스트를 제공하기 위해 쿼리는 수명 동안 수행해야 하는 사항에 따라 다른 메모리 할당자 또는 클럭의 메모리를 요청할 수 있습니다. 예를 들어 쿼리가 처음에 구문 분석되고 컴파일되면 컴파일 메모리가 소비됩니다. 쿼리가 컴파일되면 해당 메모리가 해제되고 결과 쿼리 계획이 계획 캐시 메모리에 저장됩니다. 계획이 캐시되면 쿼리를 실행할 준비가 된 것입니다. 쿼리가 정렬 작업, 해시 일치 작업(JOIN 또는 집계) 또는 COLUMNSTORE 인덱스에 삽입하는 경우 쿼리 실행 할당자의 메모리를 사용합니다. 처음에 쿼리는 해당 실행 메모리를 요청하고, 나중에 이 메모리가 부여되면 쿼리는 정렬 결과 또는 해시 버킷에 메모리의 전부 또는 일부를 사용합니다. 쿼리 실행 중에 할당된 이 메모리를 메모리 부여라고 합니다. 상상할 수 있듯이 쿼리 실행 작업이 완료되면 메모리 부여가 다른 작업에 사용할 SQL Server 다시 해제됩니다. 따라서 메모리 부여 할당은 본질적으로 일시적이지만 여전히 오래 지속될 수 있습니다. 예를 들어 쿼리 실행이 메모리의 매우 큰 행 집합에서 정렬 작업을 수행하는 경우 정렬에는 몇 초 또는 몇 분이 걸릴 수 있으며, 부여된 메모리는 쿼리의 수명 동안 사용됩니다.
메모리 부여가 있는 쿼리의 예
다음은 실행 메모리를 사용하는 쿼리의 예와 권한 부여를 보여 주는 쿼리 계획입니다.
SELECT *
FROM sys.messages
ORDER BY message_id
이 쿼리는 300,000개 이상의 행 집합을 선택하고 정렬합니다. 정렬 작업은 메모리 부여 요청을 유도합니다. SSMS에서 이 쿼리를 실행하는 경우 해당 쿼리 계획을 볼 수 있습니다. 쿼리 계획의 가장 왼쪽 SELECT 에 있는 연산자를 선택하면 쿼리에 대한 메모리 부여 정보를 볼 수 있습니다( F4 키를 눌러 속성을 표시).
또한 쿼리 계획의 공백을 마우스 오른쪽 단추로 클릭하면 실행 계획 XML 표시...를 선택하고 동일한 메모리 부여 정보를 표시하는 XML 요소를 찾을 수 있습니다.
<MemoryGrantInfo SerialRequiredMemory="512" SerialDesiredMemory="41232" RequiredMemory="5248" DesiredMemory="46016" RequestedMemory="46016" GrantWaitTime="0" GrantedMemory="46016" MaxUsedMemory="45816" MaxQueryMemory="277688" LastRequestedMemory="0" IsMemoryGrantFeedbackAdjusted="No: First Execution" />
몇 가지 용어는 여기에 설명이 필요합니다. 쿼리는 특정 양의 실행 메모리(DesiredMemory)를 원할 수 있으며 일반적으로 해당 양(RequestedMemory)을 요청합니다. 런타임에 SQL Server 가용성(GrantedMemory)에 따라 요청된 메모리의 전부 또는 일부를 부여합니다. 결국 쿼리는 처음 요청된 메모리(MaxUsedMemory)를 더 많거나 적게 사용할 수 있습니다. 쿼리 최적화 프로그램이 필요한 메모리 양을 과대 평가한 경우 요청된 크기보다 작게 사용됩니다. 그러나 그 메모리는 다른 요청에 의해 사용되었을 수 있으므로 낭비됩니다. 반면, 최적화 프로그램이 필요한 메모리 크기를 과소 평가한 경우 실행 시 작업을 완료하기 위해 초과 행이 디스크로 유출될 수 있습니다. SQL Server 처음 요청된 크기보다 더 많은 메모리를 할당하는 대신 디스크에 추가 행을 푸시하고 임시 작업 영역으로 사용합니다. 자세한 내용은 메모리 부여 고려 사항의 작업 파일 및 Worktables를 참조하세요.
용어
이 메모리 소비자와 관련하여 발생할 수 있는 다양한 용어를 검토해 보겠습니다. 다시 말하지만, 이러한 모든 항목은 동일한 메모리 할당과 관련된 개념을 설명합니다.
쿼리 실행 메모리(QE 메모리): 이 용어는 쿼리를 실행하는 동안 정렬 또는 해시 메모리가 사용된다는 사실을 강조 표시하는 데 사용됩니다. 일반적으로 QE 메모리는 쿼리 수명 동안 메모리의 가장 큰 소비자입니다.
QE(쿼리 실행) 예약 또는 메모리 예약: 쿼리에 정렬 또는 해시 작업에 대한 메모리가 필요한 경우 메모리에 대한 예약 요청을 수행합니다. 해당 예약 요청은 예상 카디널리티에 따라 컴파일 시간에 계산됩니다. 나중에 쿼리가 실행되면 SQL Server 메모리 가용성에 따라 부분적으로 또는 완전히 요청을 부여합니다. 결국 쿼리는 부여된 메모리의 백분율을 사용할 수 있습니다. 이러한 메모리 할당(DBCC MEMORYSTATUS또는 sys.dm_os_memory_clerks 검사)을 추적하는 'MEMORYCLERK_SQLQERESERVATIONS'이라는 메모리 클럭(메모리 회계사)이 있습니다.
메모리 부여: SQL Server 요청된 메모리를 실행 중인 쿼리에 부여하면 메모리 부여가 발생했다고 합니다. "grant"라는 용어를 사용하는 몇 가지 성능 카운터가 있습니다. 이러한 카운터
Memory Grants Outstanding및Memory Grants Pending는 충족되거나 대기 중인 메모리 부여 수를 표시합니다. 메모리 부여 크기를 고려하지 않습니다. 한 쿼리만으로는 정렬을 수행하기 위해 4GB의 메모리를 사용할 수 있지만 이러한 카운터 중 하나에는 반영되지 않습니다.작업 영역 메모리 는 동일한 메모리를 설명하는 또 다른 용어입니다. 일반적으로 이 용어는 현재 정렬, 해시, 대량 복사 및 인덱스 생성 작업에 사용되는 전체 메모리 양을 반영하는 Perfmon 카운터
Granted Workspace Memory (KB)에 표시될 수 있습니다. 또 다른 카운터는Maximum Workspace Memory (KB)해시, 정렬, 대량 복사 및 인덱스 만들기 작업을 수행해야 할 수 있는 모든 요청에 사용할 수 있는 최대 작업 영역 메모리 양을 차지합니다. 작업 영역 메모리라는 용어는 이러한 두 카운터 외부에서 자주 발생하지 않습니다.
대규모 QE 메모리 사용률의 성능 영향
대부분의 경우 스레드가 SQL Server 내의 메모리를 요청하여 작업을 수행하고 메모리를 사용할 수 없는 경우 메모리 부족 오류와 함께 요청이 실패합니다. 그러나 스레드가 실패하지는 않지만 메모리를 사용할 수 있을 때까지 대기하는 몇 가지 예외 시나리오가 있습니다. 이러한 시나리오 중 하나는 메모리 부여이고 다른 하나는 쿼리 컴파일 메모리입니다. SQL Server 세마포라는 스레드 동기화 개체를 사용하여 쿼리 실행에 부여된 메모리 양을 추적합니다. SQL Server 미리 정의된 QE 작업 영역이 부족하면 메모리 부족 오류로 쿼리에 실패하는 대신 쿼리가 대기합니다. 작업 영역 메모리가 전체 SQL Server 메모리의 상당 부분을 차지할 수 있다는 점을 감안할 때 이 공간에서 메모리를 기다리는 것은 심각한 성능 영향을 미칩니다. 많은 수의 동시 쿼리가 실행 메모리를 요청했으며, 함께 QE 메모리 풀을 모두 사용했거나 몇 가지 동시 쿼리가 각각 매우 큰 권한 부여를 요청했습니다. 어느 쪽이든 결과 성능 문제는 다음과 같은 증상이 있을 수 있습니다.
- 버퍼 캐시의 데이터 및 인덱스 페이지는 대용량 메모리 부여 요청을 위한 공간을 만들기 위해 플러시되었을 수 있습니다. 즉, 쿼리 요청에서 들어오는 페이지 읽기는 디스크에서 충족되어야 합니다(작업 속도가 상당히 느림).
- 리소스가 정렬, 해시 또는 인덱스 빌드 작업과 연결되어 있으므로 다른 메모리 할당에 대한 요청이 메모리 부족 오류로 인해 실패할 수 있습니다.
- 실행 메모리가 필요한 요청은 리소스를 사용할 수 있게 되기를 기다리고 있으며 완료하는 데 시간이 오래 걸립니다. 즉, 최종 사용자에게 이러한 쿼리는 느립니다.
따라서 Perfmon, DMV(동적 관리 뷰) 또는 DBCC MEMORYSTATUS의 쿼리 실행 메모리에 대한 대기가 관찰되는 경우 특히 문제가 자주 발생하는 경우 이 문제를 resolve 위해 조치를 취해야 합니다. 자세한 내용은 개발자가 정렬 및 해시 작업에 대해 수행할 수 있는 작업을 참조하세요.
쿼리 실행 메모리에 대한 대기를 식별하는 방법
QE 예약에 대한 대기를 확인하는 방법에는 여러 가지가 있습니다. 서버 수준에서 더 큰 그림을 보려면 가장 적합한 항목을 선택합니다. 이러한 도구 중 일부를 사용할 수 없을 수 있습니다(예: Azure SQL Database에서는 Perfmon을 사용할 수 없음). 문제를 식별한 후에는 개별 쿼리 수준에서 드릴다운하여 튜닝 또는 다시 작성해야 하는 쿼리를 확인해야 합니다.
서버 수준에서 다음 메서드를 사용합니다.
- 리소스 세마포 DMV sys.dm_exec_query_resource_semaphores 자세한 내용은 sys.dm_exec_query_resource_semaphores 참조하세요.
- 성능 모니터 카운터에 대한 자세한 내용은 SQL Server Memory Manager 개체를 참조하세요.
- DBCC MEMORYSTATUS 자세한 내용은 DBCC MEMORYSTATUS를 참조하세요.
- 메모리 클럭 DMV sys.dm_os_memory_clerks 자세한 내용은 sys.dm_os_memory_clerks 참조하세요.
- 확장 이벤트(XEvents)를 사용하여 메모리 부여 식별 자세한 내용은 확장 이벤트(XEvents)를 참조하세요.
개별 쿼리 수준에서 다음 메서드를 사용합니다.
- sys.dm_exec_query_memory_grants 특정 쿼리 식별: 현재 실행 중인 쿼리입니다. 자세한 내용은 sys.dm_exec_query_memory_grants 참조하세요.
- sys.dm_exec_requests 특정 쿼리 식별: 현재 실행 중인 쿼리입니다. 자세한 내용은 sys.dm_exec_requests 참조하세요.
- sys.dm_exec_query_stats 특정 쿼리 식별: 쿼리에 대한 기록 통계입니다. 자세한 내용은 sys.dm_exec_query_stats 참조하세요.
- sys.query_store_runtime_stats QDS(쿼리 저장소)를 사용하여 특정 쿼리 식별: QDS를 사용한 쿼리에 대한 기록 통계입니다. 자세한 내용은 sys.query_store_runtime_stats 참조하세요.
메모리 사용 통계 집계
리소스 세마포 DMV sys.dm_exec_query_resource_semaphores
이 DMV는 리소스 풀(내부, 기본값 및 사용자가 만든) 및 (일반 및 resource_semaphore 작은 쿼리 요청)을 기준으로 쿼리 예약 메모리를 세분화합니다. 유용한 쿼리는 다음과 같습니다.
SELECT
pool_id
,total_memory_kb
,available_memory_kb
,granted_memory_kb
,used_memory_kb
,grantee_count, waiter_count
,resource_semaphore_id
FROM sys.dm_exec_query_resource_semaphores rs
다음 샘플 출력은 22개의 요청에서 약 900MB의 쿼리 실행 메모리가 사용되고 3개 이상이 대기 중임을 보여 줍니다. 이 작업은 기본 풀(pool_id = 2) 및 일반 쿼리 세마포(resource_semaphore_id = 0)에서 발생합니다.
pool_id total_memory_kb available_memory_kb granted_memory_kb used_memory_kb grantee_count waiter_count resource_semaphore_id
------- --------------- ------------------- ----------------- -------------- ------------- ------------ ---------------------
1 30880 30880 0 0 0 0 0
1 5120 5120 0 0 0 0 1
2 907104 0 907104 898656 22 3 0
2 40960 40960 0 0 0 0 1
(4 rows affected)
성능 모니터 카운터
유사한 정보는 성능 모니터 카운터를 통해 사용할 수 있습니다. 여기서 현재 부여된 요청(Memory Grants Outstanding), 대기 권한 부여 요청() 및 메모리 부여에Memory Grants Pending 사용되는 메모리 양(Granted Workspace Memory (KB))을 관찰할 수 있습니다. 다음 그림에서 미결제 보조금은 18이고 보류 중인 권한은 2이며 부여된 작업 영역 메모리는 828,288KB입니다. Memory Grants Pending 0이 아닌 값이 있는 Perfmon 카운터는 메모리가 소진되었음을 나타냅니다.
자세한 내용은 SQL Server Memory Manager 개체를 참조하세요.
- SQLServer, Memory Manager: 최대 작업 영역 메모리(KB)
- SQLServer, 메모리 관리자: 메모리 부여 미해결
- SQLServer, 메모리 관리자: 메모리 부여 보류 중
- SQLServer, 메모리 관리자: 부여된 작업 영역 메모리(KB)
DBCC MEMORYSTATUS
쿼리 예약 메모리에 대한 세부 정보를 볼 수 있는 또 다른 위치는 (쿼리 메모리 개체 섹션)입니다 DBCC MEMORYSTATUS . 사용자 쿼리에 대한 출력을 Query Memory Objects (default) 볼 수 있습니다. 예를 들어 PoolAdmin이라는 리소스 풀을 사용하여 Resource Governor 사용하도록 설정한 경우 및 Query Memory Objects (PoolAdmin)를 모두 Query Memory Objects (default) 확인할 수 있습니다.
다음은 18개의 요청에 쿼리 실행 메모리가 부여되고 2개의 요청이 메모리를 대기하는 시스템의 샘플 출력입니다. 사용 가능한 카운터는 0으로, 사용 가능한 작업 영역 메모리가 더 이상 없음을 나타냅니다. 이 사실은 두 개의 대기 요청을 설명합니다. 는 Wait Time 요청이 대기 큐에 배치된 이후 경과된 시간을 밀리초 단위로 표시합니다. 이러한 카운터에 대한 자세한 내용은 메모리 개체 쿼리를 참조하세요.
Query Memory Objects (default) Value
------------------------------------------------------------------------ -----------
Grants 18
Waiting 2
Available 0
Current Max 103536
Future Max 97527
Physical Max 139137
Next Request 5752
Waiting For 8628
Cost 16
Timeout 401
Wait Time 2750
(11 rows affected)
Small Query Memory Objects (default) Value
------------------------------------------------------------------------ -----------
Grants 0
Waiting 0
Available 5133
Current Max 5133
Future Max 5133
DBCC MEMORYSTATUS 또한 쿼리 실행 메모리를 추적하는 메모리 클럭에 대한 정보도 표시합니다. 다음 출력은 QE(쿼리 실행) 예약에 할당된 페이지가 800MB를 초과했음을 보여 줍니다.
MEMORYCLERK_SQLQERESERVATIONS (node 0) KB
------------------------------------------------------------------------ -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 824640
메모리 클럭 DMV sys.dm_os_memory_clerks
섹션 기반 DBCC MEMORYSTATUS과 다른 테이블 형식 결과 집합이 더 필요한 경우 유사한 정보에 sys.dm_os_memory_clerks 사용할 수 있습니다. 메모리 클럭을 MEMORYCLERK_SQLQERESERVATIONS 찾습니다. 그러나 이 DMV에서는 쿼리 메모리 개체를 사용할 수 없습니다.
SELECT type, memory_node_id, pages_kb
FROM sys.dm_os_memory_clerks
WHERE type = 'MEMORYCLERK_SQLQERESERVATIONS'
샘플 출력은 다음과 같습니다.
type memory_node_id pages_kb
----------------------------------------------- -------------- --------------
MEMORYCLERK_SQLQERESERVATIONS 0 824640
MEMORYCLERK_SQLQERESERVATIONS 64 0
확장 이벤트(XEvents)를 사용하여 메모리 부여 식별
메모리 부여 정보를 제공하고 추적을 통해 이 정보를 캡처할 수 있는 여러 확장 이벤트가 있습니다.
- sqlserver.additional_memory_grant: 쿼리가 실행 중에 더 많은 메모리 부여를 얻으려고 할 때 발생합니다. 이 추가 메모리 부여를 얻지 못하면 쿼리 속도가 느려질 수 있습니다.
- sqlserver.query_memory_grant_blocking: 메모리 부여를 기다리는 동안 쿼리가 다른 쿼리를 차단할 때 발생합니다.
- sqlserver.query_memory_grant_info_sampling: 메모리 부여 정보를 제공하는 임의로 샘플링된 쿼리의 끝에서 발생합니다(예: 원격 분석에 사용할 수 있습니다).
- sqlserver.query_memory_grant_resource_semaphores: 각 리소스 관리자 리소스 풀에 대해 5분 간격으로 발생합니다.
- sqlserver.query_memory_grant_usage: 메모리 부여가 5MB 이상인 쿼리에 대한 쿼리 처리가 끝날 때 사용자에게 메모리 부여 부정확성에 대해 알릴 때 발생합니다.
- sqlserver.query_memory_grants: 메모리 부여를 사용하여 각 쿼리에 대해 5분 간격으로 발생합니다.
메모리 부여 피드백 확장 이벤트
메모리 부여 피드백 기능을 처리하는 쿼리에 대한 자세한 내용은 메모리 부여 피드백을 참조하세요.
- sqlserver.memory_grant_feedback_loop_disabled: 메모리 부여 피드백 루프를 사용하지 않도록 설정하면 발생합니다.
- sqlserver.memory_grant_updated_by_feedback: 피드백에 의해 메모리 부여가 업데이트되면 발생합니다.
메모리 부여와 관련된 쿼리 실행 경고
- sqlserver.execution_warning: T-SQL 문 또는 저장 프로시저가 메모리 부여를 위해 1초 이상 대기하거나 메모리를 가져오기 위한 초기 시도가 실패할 때 발생합니다. 대기를 식별하는 이벤트와 함께 이 이벤트를 사용하여 성능에 영향을 주는 경합 문제를 해결합니다.
- sqlserver.hash_spill_details: 해시 조인의 빌드 입력을 처리하기에 메모리가 부족한 경우 해시 처리가 끝날 때 발생합니다. 이 이벤트를 또는
query_post_execution_showplan이벤트와 함께query_pre_execution_showplan사용하여 생성된 계획에서 해시 유출을 일으키는 작업을 확인합니다. - sqlserver.hash_warning: 해시 조인의 빌드 입력을 처리하기에 메모리가 부족한 경우에 발생합니다. 이렇게 하면 빌드 입력이 분할될 때 해시 재귀가 발생하거나 빌드 입력 분할이 최대 재귀 수준을 초과할 때 해시 구제책이 발생합니다. 이 이벤트를 또는
query_post_execution_showplan이벤트와 함께query_pre_execution_showplan사용하여 생성된 계획에서 해시 경고를 발생시키는 작업을 확인합니다. - sqlserver.sort_warning: 실행 중인 쿼리의 정렬 작업이 메모리에 맞지 않을 때 발생합니다. 이 이벤트는 인덱스 생성으로 인한 정렬 작업에 대해 생성되지 않고 쿼리의 정렬 작업에 대해서만 생성됩니다. (예를 들어 문
Order By에 있는 입니다Select.) 이 이벤트를 사용하여 정렬 작업으로 인해 느리게 수행되는 쿼리를 식별합니다. 특히warning_type데이터를 여러 번의 전달이 정렬해야 했음을 나타내는 = 2입니다.
메모리 부여 정보가 포함된 이벤트 생성 계획
확장 이벤트를 생성하는 다음 쿼리 계획에는 기본적으로 granted_memory_kb 및 ideal_memory_kb 필드가 포함됩니다.
- sqlserver.query_plan_profile
- sqlserver.query_post_execution_plan_profile
- sqlserver.query_post_execution_showplan
- sqlserver.query_pre_execution_showplan
열 저장소 인덱스 빌드
XEvents를 통해 다루는 영역 중 하나는 열 저장소 빌드 중에 사용되는 실행 메모리입니다. 사용할 수 있는 이벤트 목록입니다.
- sqlserver.column_store_index_build_low_memory: 스토리지 엔진에서 메모리 부족 상태를 감지하고 행 그룹 크기를 줄였습니다. 여기에는 몇 가지 관심 있는 열이 있습니다.
- sqlserver.column_store_index_build_memory_trace: 인덱스 빌드 중에 메모리 사용량을 추적합니다.
- sqlserver.column_store_index_build_memory_usage_scale_down: 스토리지 엔진이 축소되었습니다.
- sqlserver.column_store_index_memory_estimation: COLUMNSTORE 행 그룹 빌드 중에 메모리 예측 결과를 표시합니다.
특정 쿼리 식별
개별 요청 수준을 볼 때 찾을 수 있는 두 가지 종류의 쿼리가 있습니다. 대량의 쿼리 실행 메모리를 사용하는 쿼리와 동일한 메모리를 기다리는 쿼리입니다. 후자 그룹은 메모리 부여에 대한 적당한 요구 사항이 있는 요청으로 구성되며, 이 경우 다른 곳에 집중할 수 있습니다. 그러나 그들은 또한 거 대 한 메모리 크기를 요청 하는 경우 범인이 될 수 있습니다. 당신이 그 경우를 발견하면 그들에 초점을 맞춥다. 하나의 특정 쿼리가 위반자이지만 많은 인스턴스가 생성된다는 것을 발견하는 것이 일반적일 수 있습니다. 메모리 부여를 가져오는 인스턴스는 동일한 쿼리의 다른 인스턴스가 부여를 기다리게 합니다. 특정 상황에 관계없이 궁극적으로 요청된 실행 메모리의 쿼리 및 크기를 식별해야 합니다.
sys.dm_exec_query_memory_grants 사용하여 특정 쿼리 식별
개별 요청 및 요청 및 부여된 메모리 크기를 보려면 동적 관리 뷰를 쿼리할 sys.dm_exec_query_memory_grants 수 있습니다. 이 DMV는 기록 정보가 아닌 현재 실행 중인 쿼리에 대한 정보를 보여 줍니다.
다음 문은 DMV에서 데이터를 가져오고 쿼리 텍스트와 쿼리 계획을 가져오기도 합니다.
SELECT
session_id
,requested_memory_kb
,granted_memory_kb
,used_memory_kb
,queue_id
,wait_order
,wait_time_ms
,is_next_candidate
,pool_id
,text
,query_plan
FROM sys.dm_exec_query_memory_grants
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
CROSS APPLY sys.dm_exec_query_plan(plan_handle)
다음은 활성 QE 메모리 사용 중에 쿼리의 약어 샘플 출력입니다. 대부분의 쿼리에는 NULL이 아닌 숫자 값에 used_memory_kb 표시된 granted_memory_kb 대로 메모리가 부여됩니다. 요청이 부여되지 않은 쿼리는 실행 메모리 및 granted_memory_kb = NULL를 기다리고 있습니다. 또한 = 6인 대기 큐에 queue_id 배치됩니다. 약 wait_time_ms 37초의 대기를 나타냅니다. 세션 72는 = 1로 표시된 wait_order 대로 보조금을 받기 위해 다음 줄에 있으며 세션 74는 = 2와 함께 wait_order 제공됩니다.
session_id requested_memory_kb granted_memory_kb used_memory_kb queue_id wait_order wait_time_ms is_next_candidate pool_id
---------- -------------------- -------------------- -------------------- -------- ----------- -------------------- ----------------- -------
80 41232 41232 40848 NULL NULL NULL NULL 2
83 41232 41232 40848 NULL NULL NULL NULL 2
84 41232 41232 40848 NULL NULL NULL NULL 2
74 41232 NULL NULL 6 2 37438 0 2
78 41232 41232 40848 NULL NULL NULL NULL 2
81 41232 41232 40848 NULL NULL NULL NULL 2
71 41232 41232 40848 NULL NULL NULL NULL 2
75 41232 NULL NULL 6 0 37438 1 2
82 41232 41232 40848 NULL NULL NULL NULL 2
76 41232 41232 40848 NULL NULL NULL NULL 2
79 41232 41232 40848 NULL NULL NULL NULL 2
85 41232 41232 40848 NULL NULL NULL NULL 2
70 41232 41232 40848 NULL NULL NULL NULL 2
55 41232 41232 40848 NULL NULL NULL NULL 2
59 41232 NULL NULL 6 3 37438 0 2
62 41232 41232 40848 NULL NULL NULL NULL 2
54 41232 41232 40848 NULL NULL NULL NULL 2
77 41232 41232 40848 NULL NULL NULL NULL 2
52 41232 41232 40848 NULL NULL NULL NULL 2
72 41232 NULL NULL 6 1 37438 0 2
69 41232 41232 40848 NULL NULL NULL NULL 2
73 41232 41232 40848 NULL NULL NULL NULL 2
66 41232 NULL NULL 6 4 37438 0 2
68 41232 41232 40848 NULL NULL NULL NULL 2
63 41232 41232 40848 NULL NULL NULL NULL 2
sys.dm_exec_requests 사용하여 특정 쿼리 식별
SQL Server 쿼리가 메모리 부여RESOURCE_SEMAPHORE를 기다리고 있음을 나타내는 대기 유형이 있습니다. 개별 요청에 대해 이 대기 유형을 sys.dm_exec_requests 관찰할 수 있습니다. 이 후자의 DMV는 부여 메모리 부족의 희생자인 쿼리를 식별하는 가장 좋은 시작점입니다. SQL Server 수준에서 집계된 데이터 포인트로 sys.dm_os_wait_stats 대기를 관찰 RESOURCE_SEMAPHORE 할 수도 있습니다. 이 대기 유형은 메모리를 사용한 다른 동시 쿼리로 인해 쿼리 메모리 요청을 부여할 수 없는 경우에 표시됩니다. 대기 요청 수가 많고 대기 시간이 길면 실행 메모리 또는 대용량 메모리 요청 크기를 사용하는 동시 쿼리의 수가 과도함을 나타냅니다.
참고
메모리 부여 대기 시간은 한정됩니다. 과도한 대기 후(예: 20분 이상) 쿼리를 SQL Server 시간 초과하고 오류 8645를 발생합니다. "메모리 리소스가 쿼리를 실행할 때까지 기다리는 동안 시간 초과가 발생했습니다. 쿼리를 다시 실행합니다." 에서 확인하여 서버 수준에서 timeout_sec 설정된 시간 제한 값을 볼 수 있습니다 sys.dm_exec_query_memory_grants. 시간 제한 값은 SQL Server 버전마다 약간 다를 수 있습니다.
를 사용하면 sys.dm_exec_requests메모리가 부여된 쿼리와 해당 권한 부여의 크기를 확인할 수 있습니다. 또한 대기 유형을 찾아 현재 메모리 부여를 기다리고 있는 쿼리를 식별할 RESOURCE_SEMAPHORE 수 있습니다. 부여된 요청과 대기 요청을 모두 보여 주는 쿼리는 다음과 같습니다.
SELECT session_id, wait_type, wait_time, granted_query_memory, text
FROM sys.dm_exec_requests
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
WHERE granted_query_memory > 0
OR wait_type = 'RESOURCE_SEMAPHORE'
샘플 출력은 두 개의 요청이 메모리를 부여했으며 다른 12개 요청은 보조금을 기다리고 있음을 보여 줍니다. 열은 granted_query_memory 8KB 페이지의 크기를 보고합니다. 예를 들어 값이 34,709이면 34,709 * 8KB = 277,672KB의 메모리가 부여됩니다.
session_id wait_type wait_time granted_query_memory text
---------- ----------------------------------- -------------------- -------------------------------------------------------------------
65 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
66 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
67 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
68 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
69 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
70 RESOURCE_SEMAPHORE 161435 0 select * from sys.messages order by message_id option (maxdop 1)
71 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
72 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
73 RESOURCE_SEMAPHORE 161435 0 select * from sys.messages order by message_id option (maxdop 1)
74 RESOURCE_SEMAPHORE 161435 0 select * from sys.messages order by message_id option (maxdop 1)
75 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
76 ASYNC_NETWORK_IO 11 34709 select * from sys.messages order by message_id option (maxdop 1)
77 RESOURCE_SEMAPHORE 161435 0 select * from sys.messages order by message_id option (maxdop 1)
78 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
79 RESOURCE_SEMAPHORE 161435 0 select * from sys.messages order by message_id option (maxdop 1)
80 RESOURCE_SEMAPHORE 161435 0 select * from sys.messages order by message_id option (maxdop 1)
81 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
82 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
83 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
84 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
85 ASYNC_NETWORK_IO 14 34709 select * from sys.messages order by message_id option (maxdop 1)
86 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
87 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
88 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
89 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
sys.dm_exec_query_stats 사용하여 특정 쿼리 식별
현재 메모리 부여 문제가 발생하지 않지만 잘못된 쿼리를 식별하려는 경우 를 통해 sys.dm_exec_query_stats기록 쿼리 데이터를 확인할 수 있습니다. 데이터의 수명은 각 쿼리의 쿼리 계획에 연결됩니다. 계획이 계획 캐시에서 제거되면 해당 행이 이 보기에서 제거됩니다. 즉, DMV는 SQL Server 다시 시작한 후 또는 메모리 압력으로 인해 계획 캐시 릴리스가 발생한 후 유지되지 않는 통계를 메모리에 유지합니다. 즉, 특히 집계 쿼리 통계의 경우 여기에서 유용한 정보를 찾을 수 있습니다. 누군가가 최근에 쿼리에서 큰 메모리 부여를 보고했을 수 있지만 서버 워크로드를 보면 문제가 사라진 것을 발견할 수 있습니다. 이 경우 는 sys.dm_exec_query_stats 다른 DVM에서 수행할 수 없는 인사이트를 제공할 수 있습니다. 다음은 가장 많은 양의 실행 메모리를 소비한 상위 20개 문을 찾는 데 도움이 되는 샘플 쿼리입니다. 이 출력은 쿼리 구조가 같더라도 개별 문을 표시합니다. 예를 들어 는 SELECT Name FROM t1 JOIN t2 ON t1.Id = t2.Id WHERE t1.Id = 5 와 별도의 행 SELECT Name FROM t1 JOIN t2 ON t1.Id = t2.Id WHERE t1.Id = 100 입니다(필터 조건자 값만 다름). 쿼리는 최대 허용 크기가 5MB보다 큰 상위 20개 문을 가져옵니다.
SELECT TOP 20
SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(ST.text)
ELSE QS.statement_end_offset END
- QS.statement_start_offset)/2) + 1) AS statement_text
,CONVERT(DECIMAL (10,2), max_grant_kb /1024.0) AS max_grant_mb
,CONVERT(DECIMAL (10,2), min_grant_kb /1024.0) AS min_grant_mb
,CONVERT(DECIMAL (10,2), (total_grant_kb / execution_count) /1024.0) AS avg_grant_mb
,CONVERT(DECIMAL (10,2), max_used_grant_kb /1024.0) AS max_grant_used_mb
,CONVERT(DECIMAL (10,2), min_used_grant_kb /1024.0) AS min_grant_used_mb
,CONVERT(DECIMAL (10,2), (total_used_grant_kb/ execution_count) /1024.0) AS avg_grant_used_mb
,CONVERT(DECIMAL (10,2), (total_ideal_grant_kb/ execution_count) /1024.0) AS avg_ideal_grant_mb
,CONVERT(DECIMAL (10,2), (total_ideal_grant_kb/ 1024.0)) AS total_grant_for_all_executions_mb
,execution_count
FROM sys.dm_exec_query_stats QS
CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST
WHERE max_grant_kb > 5120 -- greater than 5 MB
ORDER BY max_grant_kb DESC
로 집계 query_hash된 쿼리를 살펴보면 더욱 강력한 인사이트를 얻을 수 있습니다. 이 예제에서는 쿼리 계획이 처음 캐시된 이후 모든 인스턴스에서 쿼리 문에 대한 평균, 최대 및 최소 권한 부여 크기를 찾는 방법을 보여 줍니다.
SELECT TOP 20
MAX(SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(ST.text)
ELSE QS.statement_end_offset END
- QS.statement_start_offset)/2) + 1)) AS sample_statement_text
,CONVERT(DECIMAL (10,2), SUM(max_grant_kb) /1024.0) AS max_grant_mb
,CONVERT(DECIMAL (10,2), SUM(min_grant_kb) /1024.0) AS min_grant_mb
,CONVERT(DECIMAL (10,2), (SUM(total_grant_kb) / SUM(execution_count)) /1024.0) AS avg_grant_mb
,CONVERT(DECIMAL (10,2), SUM(max_used_grant_kb) /1024.0) AS max_grant_used_mb
,CONVERT(DECIMAL (10,2), SUM(min_used_grant_kb) /1024.0) AS min_grant_used_mb
,CONVERT(DECIMAL (10,2), (SUM(total_used_grant_kb)/ SUM(execution_count)) /1024.0) AS avg_grant_used_mb
,CONVERT(DECIMAL (10,2), (SUM(total_ideal_grant_kb)/ SUM(execution_count)) /1024.0) AS avg_ideal_grant_mb
,CONVERT(DECIMAL (10,2), SUM(total_grant_kb) /1024.0) AS total_grant_all_executions_mb
,SUM(execution_count) AS execution_count
,query_hash
FROM sys.dm_exec_query_stats QS
CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST
GROUP BY query_hash
HAVING SUM(max_grant_kb) > 5120 -- greater than 5 MB
ORDER BY SUM(max_grant_kb) DESC
OPTION (MAX_GRANT_PERCENT = 5)
열은 Sample_Statement_Text 쿼리 해시와 일치하는 쿼리 구조의 예를 보여 주지만 문의 특정 값과 관계없이 읽어야 합니다. 예를 들어 문에 가 포함된 WHERE Id = 5경우 보다 일반적인 형식 WHERE Id = @any_value인 로 읽을 수 있습니다.
다음은 선택한 열만 표시된 쿼리의 약어 샘플 출력입니다.
sample_statement_text max_grant_mb avg_grant_mb max_grant_used_mb avg_grant_used_mb avg_ideal_grant_mb total_grant_all_executions_mb execution_count
----------------------------------------- ------------ ------------- ----------------- ----------------- ------------------ ----------------------------- ----------------
select de.ObjectName,de.CounterName,d 282.45 282.45 6.50 6.50 282.45 282.45 1
SELECT SCHEMA_NAME(udf.schema_id) AS [Sch 33.86 8.55 7.80 1.97 8.55 42.74 5
insert into #tmpCounterDateTime (CounterD 32.45 32.45 3.11 3.11 32.45 32.45 1
select db_id() dbid, db_name() dbname, * 20.80 1.30 5.75 0.36 1.30 20.80 16
SELECT SCHEMA_NAME(obj.schema_id) AS [Sch 20.55 5.19 5.13 1.28 5.19 25.93 5
SELECT xmlplan FROM (SELECT ROW_NUMBER() 19.69 1.97 1.09 0.11 1.97 19.69 10
if ( select max(cast(countervalue as floa 16.39 8.20 0.77 0.38 8.20 16.39 2
SELECT udf.name AS [Name], udf.object_id 11.36 5.08 1.66 0.83 5.08 20.33 4
select --* Database_I 10.94 5.47 1.98 0.99 5.47 10.94 2
IF (select max(cast(dat.countervalue as f 8.00 1.00 0.00 0.00 0.53 8.00 8
insert into #tmpCounterDateTime (CounterD 5.72 2.86 1.98 0.99 2.86 5.72 2
INSERT INTO #tmp (CounterDateTime, Counte 5.39 1.08 1.64 0.33 1.08 6.47 6
sys.query_store_runtime_stats QDS(쿼리 저장소)를 사용하여 특정 쿼리 식별
쿼리 저장소 사용하도록 설정한 경우 지속된 기록 통계를 활용할 수 있습니다. 의 데이터sys.dm_exec_query_stats와 달리 이러한 통계는 데이터베이스에 저장되기 때문에 SQL Server 다시 시작 또는 메모리 압력에서 유지됩니다. QDS에는 크기 제한 및 보존 정책도 있습니다. 자세한 내용은 최적의 쿼리 저장소 캡처 모드 설정 및 쿼리 저장소관리 모범 사례의 쿼리 저장소 섹션에서 가장 관련성이 큰 데이터 유지를 참조하세요.
이 쿼리를 사용하여 데이터베이스에 쿼리 저장소 사용하도록 설정되어 있는지 확인합니다.
SELECT name, is_query_store_on FROM sys.databases WHERE is_query_store_on = 1조사하려는 특정 데이터베이스의 컨텍스트에서 다음 진단 쿼리를 실행합니다.
SELECT MAX(qtxt.query_sql_text) AS sample_sql_text ,CONVERT(DECIMAL(10,2), SUM(rts.avg_query_max_used_memory) / 128) AS avg_mem_grant_used_mb ,CONVERT(DECIMAL(10,2), SUM(rts.min_query_max_used_memory) / 128) AS min_mem_grant_used_mb ,CONVERT(DECIMAL(10,2), SUM(rts.max_query_max_used_memory) / 128) AS max_mem_grant_used_mb ,CONVERT(DECIMAL(10,2), SUM(rts.stdev_query_max_used_memory) / 128) AS stdev_mem_grant_used_mb ,CONVERT(DECIMAL(10,2), SUM(rts.last_query_max_used_memory) / 128) AS last_mem_grant_used_mb ,SUM(count_executions) AS count_query_executions FROM sys.query_store_runtime_stats rts JOIN sys.query_store_plan p ON p.plan_id = rts.plan_id JOIN sys.query_store_query q ON p.query_id = q.query_id LEFT OUTER JOIN sys.query_store_query_text qtxt ON q.query_text_id = qtxt.query_text_id GROUP BY q.query_hash HAVING SUM(rts.avg_query_max_used_memory) /128 > 5 -- greater than 5 MB ORDER BY SUM(avg_query_max_used_memory) DESC OPTION (MAX_GRANT_PERCENT = 5)여기서 원칙은 와 같습니다
sys.dm_exec_query_stats. 문에 대한 집계 통계가 표시됩니다. 그러나 한 가지 차이점은 QDS를 사용하면 전체 SQL Server 아니라 이 데이터베이스의 scope 쿼리만 보고 있다는 것입니다. 따라서 특정 메모리 부여 요청이 실행된 데이터베이스를 알아야 할 수 있습니다. 그렇지 않으면 상당한 메모리 부여를 찾을 때까지 여러 데이터베이스에서 이 진단 쿼리를 실행합니다.다음은 약식 샘플 출력입니다.
sample_sql_text avg_mem_grant_used_mb min_mem_grant_used_mb max_mem_grant_used_mb stdev_mem_grant_used_mb last_mem_grant_used_mb count_query_executions ----------------------------------------- ---------------------- ---------------------- ---------------------- ------------------------ ----------------------- ---------------------- SELECT qtxt.query_sql_text ,CONVERT(D 550.16 550.00 550.00 0.00 550.00 1 SELECT qtxt.query_sql_text ,rts.avg_q 61.00 36.00 65.00 10.87 51.00 14 SELECT qtxt.query_sql_text ,q.* ,rts 25.46 25.00 25.00 0.00 25.00 2 insert into #tmpStats select 5 'Database 13.69 13.00 13.00 0.03 13.00 16 SELECT q.* ,rts 11.93 11.00 12.00 0.23 12.00 2 SELECT * ,rts.avg_query_max_used_memory 9.70 9.00 9.00 0.00 9.00 1 SELECT qtxt.query_sql_text ,rts.avg_q 9.32 9.00 9.00 0.00 9.00 1 select db_id() dbid, db_name() dbname, * 7.33 7.00 7.00 0.00 7.00 9 SELECT q.* ,rts.avg_query_max_used_memo 6.65 6.00 6.00 0.00 6.00 1 (@_msparam_0 nvarchar(4000),@_msparam_1 5.17 4.00 5.00 0.68 4.00 2
사용자 지정 진단 쿼리
다음은 이전에 나열된 세 가지를 포함하여 여러 뷰의 데이터를 결합하는 쿼리입니다. 에서 제공하는 서버 수준 통계 외에도 및 을 통해 sys.dm_exec_requests 세션 및 해당 권한 부여에 대한 보다 철저한 보기를 제공합니다sys.dm_exec_query_resource_semaphores.sys.dm_exec_query_memory_grants
참고
이 쿼리는 (일반 리소스 세마포의 경우 한 행, 작은 쿼리 리소스 세마포의 경우 다른 행)을 사용하여 sys.dm_exec_query_resource_semaphores 세션당 두 개의 행을 반환합니다.
SELECT CONVERT (varchar(30), GETDATE(), 121) as runtime
, r.session_id
, r.wait_time
, r.wait_type
, mg.request_time
, mg.grant_time
, mg.requested_memory_kb
/ 1024 requested_memory_mb
, mg.granted_memory_kb
/ 1024 AS granted_memory_mb
, mg.required_memory_kb
/ 1024 AS required_memory_mb
, max_used_memory_kb
/ 1024 AS max_used_memory_mb
, rs.pool_id as resource_pool_id
, mg.query_cost
, mg.timeout_sec
, mg.resource_semaphore_id
, mg.wait_time_ms AS memory_grant_wait_time_ms
, CASE mg.is_next_candidate
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
ELSE 'Memory has been granted'
END AS 'Next Candidate for Memory Grant'
, r.command
, ltrim(rtrim(replace(replace (substring (q.text, 1, 1000), char(10), ' '), char(13), ' '))) [text]
, rs.target_memory_kb
/ 1024 AS server_target_grant_memory_mb
, rs.max_target_memory_kb
/ 1024 AS server_max_target_grant_memory_mb
, rs.total_memory_kb
/ 1024 AS server_total_resource_semaphore_memory_mb
, rs.available_memory_kb
/ 1024 AS server_available_memory_for_grants_mb
, rs.granted_memory_kb
/ 1024 AS server_total_granted_memory_mb
, rs.used_memory_kb
/ 1024 AS server_used_granted_memory_mb
, rs.grantee_count AS successful_grantee_count
, rs.waiter_count AS grant_waiters_count
, rs.timeout_error_count
, rs.forced_grant_count
, mg.dop
, r.blocking_session_id
, r.cpu_time
, r.total_elapsed_time
, r.reads
, r.writes
, r.logical_reads
, r.row_count
, s.login_time
, d.name
, s.login_name
, s.host_name
, s.nt_domain
, s.nt_user_name
, s.status
, c.client_net_address
, s.program_name
, s.client_interface_name
, s.last_request_start_time
, s.last_request_end_time
, c.connect_time
, c.last_read
, c.last_write
, qp.query_plan
FROM sys.dm_exec_requests r
INNER JOIN sys.dm_exec_connections c
ON r.connection_id = c.connection_id
INNER JOIN sys.dm_exec_sessions s
ON c.session_id = s.session_id
INNER JOIN sys.databases d
ON r.database_id = d.database_id
INNER JOIN sys.dm_exec_query_memory_grants mg
ON s.session_id = mg.session_id
INNER JOIN sys.dm_exec_query_resource_semaphores rs
ON mg.resource_semaphore_id = rs.resource_semaphore_id
CROSS APPLY sys.dm_exec_sql_text (r.sql_handle ) AS q
CROSS APPLY sys.dm_exec_query_plan(mg.plan_handle) qp
OPTION (MAXDOP 1, LOOP JOIN )
참고
힌트는 LOOP JOIN 쿼리 자체의 메모리 부여를 방지하기 위해 이 진단 쿼리에서 사용되며 절은 사용되지 않습니다 ORDER BY . 진단 쿼리가 권한 부여 자체를 기다리게 되면 메모리 부여를 진단하는 목적이 무효화됩니다. 힌트로 LOOP JOIN 인해 진단 쿼리가 느려질 수 있지만 이 경우 진단 결과를 얻는 것이 더 중요합니다.
다음은 선택한 열만 있는 이 진단 쿼리의 약어 샘플 출력입니다.
| session_id | wait_time | wait_type | requested_memory_mb | granted_memory_mb | required_memory_mb | max_used_memory_mb | resource_pool_id |
|---|---|---|---|---|---|---|---|
| 60 | 0 | NULL | 9 | 9 | 7 | 1 | 1 |
| 60 | 0 | NULL | 9 | 9 | 7 | 1 | 2 |
| 75 | 1310085 | RESOURCE_SEMAPHORE | 40 | NULL | 0 | NULL | 1 |
| 75 | 1310085 | RESOURCE_SEMAPHORE | 40 | NULL | 0 | NULL | 2 |
| 86 | 1310129 | RESOURCE_SEMAPHORE | 40 | NULL | 0 | NULL | 1 |
| 86 | 1310129 | RESOURCE_SEMAPHORE | 40 | NULL | 0 | NULL | 2 |
샘플 출력은 = 60으로 제출된 쿼리가 session_id 요청한 9MB 메모리 부여를 성공적으로 받았지만 쿼리 실행을 성공적으로 시작하려면 7MB만 필요한 방법을 명확하게 보여 줍니다. 결국 쿼리는 서버에서 받은 9MB 중 1MB만 사용했습니다. 또한 출력은 세션 75 및 86이 메모리 부여를 기다리고 있음을 보여 줍니다. 따라서 RESOURCE_SEMAPHOREwait_type입니다. 대기 시간은 1,300초(21분)를 초과했으며 granted_memory_mb 입니다 NULL.
이 진단 쿼리는 샘플이므로 필요에 맞는 방식으로 자유롭게 수정할 수 있습니다. 이 쿼리의 버전은 Microsoft SQL Server 지원에서 사용하는 진단 도구에도 사용됩니다.
진단 도구
Microsoft SQL Server 기술 지원에서 로그를 수집하고 문제를 보다 효율적으로 해결하는 데 사용하는 진단 도구가 있습니다. SQL LogScout 및 Pssdiag Configuration Manager(SQLDiag와 함께)은 메모리 부여 문제를 진단하는 데 도움이 될 수 있는 이전에 설명한 DMV 및 성능 모니터 카운터의 출력을 수집합니다.
LightPerf, GeneralPerf 또는 DetailedPerf 시나리오를 사용하여 SQL LogScout을 실행하는 경우 도구는 필요한 로그를 수집합니다. 그런 다음 YourServer_PerfStats.out을 수동으로 검사하고 및 -- dm_exec_query_memory_grants -- 출력을 찾을 -- dm_exec_query_resource_semaphores -- 수 있습니다. 또는 수동 검사 대신 SQL Nexus를 사용하여 SQL LogScout 또는 PSSDIAG에서 나오는 출력을 SQL Server 데이터베이스로 가져올 수 있습니다. SQL Nexus는 메모리 부여를 진단하는 데 필요한 정보를 포함하는 두 개의 테이블 tbl_dm_exec_query_resource_semaphores 과 tbl_dm_exec_query_memory_grants를 만듭니다. 또한 SQL LogScout 및 PSSDIAG는 Perfmon 로그를 형식으로 수집합니다. BLG 파일- 성능 모니터 카운터 섹션에 설명된 성능 카운터를 검토하는 데 사용할 수 있습니다.
개발자 또는 DBA에 메모리 부여가 중요한 이유
Microsoft 지원 환경에 따라 메모리 부여 문제는 가장 일반적인 메모리 관련 문제 중 일부인 경향이 있습니다. 애플리케이션은 종종 대규모 정렬 또는 해시 작업으로 인해 SQL Server 성능 문제를 일으킬 수 있는 겉보기에 간단한 쿼리를 실행합니다. 이러한 쿼리는 많은 SQL Server 메모리를 소비할 뿐만 아니라 다른 쿼리가 메모리가 사용 가능해질 때까지 기다리게 하므로 성능 병목 현상이 발생합니다.
여기에 설명된 도구(DMV, Perfmon 카운터 및 실제 쿼리 계획)를 사용하여 대규모 허용 소비자인 쿼리를 식별할 수 있습니다. 그런 다음 이러한 쿼리를 튜닝하거나 다시 작성하여 작업 영역 메모리 사용량을 resolve 줄일 수 있습니다.
개발자가 정렬 및 해시 작업에 대해 수행할 수 있는 작업
많은 양의 쿼리 예약 메모리를 사용하는 특정 쿼리를 식별한 후에는 이러한 쿼리를 다시 디자인하여 메모리 부여를 줄이는 단계를 수행할 수 있습니다.
쿼리에서 정렬 및 해시 작업의 원인
첫 번째 단계는 쿼리에서 메모리 부여로 이어질 수 있는 작업을 파악하는 것입니다.
쿼리에서 SORT 연산자를 사용하는 이유:
ORDER BY(T-SQL) 를 사용하면 최종 결과로 스트리밍되기 전에 행이 정렬됩니다.
그룹화된 열을 정렬하는 기본 인덱스가 없는 경우 그룹화하기 전에 T-SQL(GROUP BY)에서 쿼리 계획에 정렬 연산자를 도입할 수 있습니다.
DISTINCT(T-SQL) 는 와 유사하게
GROUP BY동작합니다. 고유 행을 식별하기 위해 중간 결과가 정렬된 다음 중복이 제거됩니다. 정렬된Sort인덱스 검색 또는 검색으로 인해 데이터가 아직 정렬되지 않은 경우 최적화 프로그램은 이 연산자 이전에 연산자를 사용합니다.병합 조인 연산자는 쿼리 최적화 프로그램에서 선택할 때 조인된 입력을 모두 정렬해야 합니다. SQL Server 테이블 중 하나의 조인 열에서 클러스터형 인덱스 를 사용할 수 없는 경우 정렬을 트리거할 수 있습니다.
쿼리에서 HASH 쿼리 계획 연산자를 사용하는 이유:
이 목록은 완전하지는 않지만 해시 작업에 가장 일반적으로 발생하는 이유가 포함되어 있습니다. 쿼리 계획을 분석 하여 해시 일치 작업을 식별합니다.
JOIN(T-SQL): 테이블을 조인할 때 SQL Server , 및
Hash Join의 세 가지 물리 연산자Nested LoopMerge Join중에서 선택할 수 있습니다. SQL Server 해시 조인을 선택하는 경우 중간 결과를 저장하고 처리하려면 QE 메모리가 필요합니다. 일반적으로 좋은 인덱스가 부족하면 리소스 비용이 가장 많이 드는 조인 연산자 로Hash Join이어질 수 있습니다. 쿼리 계획을 검사하여 를 식별Hash Match하려면 논리 및 물리 연산자 참조를 참조하세요.DISTINCT(T-SQL): 연산자를
Hash Aggregate사용하여 행 집합의 중복을 제거할 수 있습니다. 쿼리 계획에서 (Aggregate)를Hash Match찾으려면 논리 및 물리 연산자 참조를 참조하세요.UNION(T-SQL): 와 유사합니다
DISTINCT. 이Hash Aggregate연산자에 대한 중복을 제거하는 데 를 사용할 수 있습니다.SUM/AVG/MAX/MIN(T-SQL) : 모든 집계 작업은 잠재적으로 로
Hash Aggregate수행될 수 있습니다. 쿼리 계획에서 (Aggregate)를Hash Match찾으려면 논리 및 물리 연산자 참조를 참조하세요.
이러한 일반적인 이유를 알면 가능한 한 SQL Server 들어오는 대용량 메모리 부여 요청을 제거하는 데 도움이 될 수 있습니다.
정렬 및 해시 작업 또는 권한 부여 크기를 줄이는 방법
- 통계를 최신 상태로 유지합니다. 여러 수준의 쿼리 성능을 향상시키는 이 기본 단계는 쿼리 계획을 선택할 때 쿼리 최적화 프로그램이 가장 정확한 정보를 갖도록 합니다. SQL Server 통계에 따라 메모리 부여를 요청할 크기를 결정합니다. 오래된 통계는 보조금 요청의 과대 평가 또는 과소 평가로 인해 불필요하게 높은 권한 부여 요청으로 이어지거나 각각 디스크에 결과를 유출할 수 있습니다. 데이터베이스에서 자동 업데이트 통계가 사용하도록 설정되어 있는지 확인하거나 UPDATE STATISTICS 또는 sp_updatestats 사용하여 정적을 업데이트된 상태로 유지합니다.
- 테이블에서 들어오는 행 수를 줄입니다. 보다 제한적인 WHERE 필터 또는 JOIN을 사용하고 행 수를 줄이면 쿼리 계획의 후속 정렬이 더 작은 결과 집합의 순서를 지정하거나 집계합니다. 중간 결과 집합이 작을수록 작업 집합 메모리가 줄어듭니다. 이는 개발자가 작업 집합 메모리 저장뿐만 아니라 CPU 및 I/O를 줄이기 위해 따를 수 있는 일반적인 규칙입니다(이 단계가 항상 가능한 것은 아님). 잘 작성된 리소스 효율적인 쿼리가 이미 있는 경우 이 지침이 충족되었습니다.
- 조인 열에 인덱스를 만들어 병합 조인을 지원합니다. 쿼리 계획의 중간 작업은 기본 테이블의 인덱스의 영향을 받습니다. 예를 들어 테이블에 조인 열에 인덱스가 없고 병합 조인이 가장 비용 효율적인 조인 연산자인 경우 조인이 수행되기 전에 해당 테이블의 모든 행을 정렬해야 합니다. 대신 열에 인덱스가 있으면 정렬 작업을 제거할 수 있습니다.
- 해시 작업을 방지하는 데 도움이 되는 인덱스를 만듭니다. 일반적으로 기본 쿼리 튜닝은 쿼리에 읽기를 줄이고 가능한 경우 큰 정렬 또는 해시 작업을 최소화하거나 제거하는 데 도움이 되는 적절한 인덱스가 있는지 확인하는 것으로 시작됩니다. 해시 조인은 대개 큰 입력, 정렬되지 않은 입력 및 인덱싱되지 않은 입력을 처리하기 위해 선택됩니다. 인덱스를 만들면 이 최적화 프로그램 전략이 변경되고 데이터 검색 속도가 빨라질 수 있습니다. 인덱스 만들기에 대한 지원은 누락된 인덱스 제안으로 비클러스터형 인덱스 데이터베이스 엔진 튜닝 관리자 및 튜닝을 참조하세요.
- 를 사용하는 집계 쿼리에 적합한 경우 COLUMNSTORE 인덱스를 사용합니다
GROUP BY. 매우 큰 행 집합을 처리하고 일반적으로 "그룹화 기준" 집계를 수행하는 분석 쿼리는 작업을 완료하려면 큰 메모리 청크가 필요할 수 있습니다. 정렬된 결과를 제공하는 인덱스 를 사용할 수 없는 경우 쿼리 계획에 정렬이 자동으로 도입됩니다. 매우 큰 결과의 일종은 비용이 많이 드는 메모리 부여로 이어질 수 있습니다. ORDER BY필요하지 않은 경우 을 제거합니다. 결과를 고유한 방식으로 정렬하거나 사용자가 본 데이터의 순서를 수정할 수 있는 애플리케이션으로 결과가 스트리밍되는 경우 SQL Server 쪽에서 정렬을 수행할 필요가 없습니다. 서버에서 생성하는 순서대로 데이터를 애플리케이션으로 스트리밍하고 최종 사용자가 직접 정렬하도록 합니다. Power BI 또는 Reporting Services 같은 보고 애플리케이션은 최종 사용자가 데이터를 정렬할 수 있도록 하는 애플리케이션의 예입니다.- 신중하게는 아니지만 조인이 T-SQL 쿼리에 있을 때 LOOP JOIN 힌트를 사용하는 것이 좋습니다. 이 기술은 메모리 부여를 사용하는 해시 또는 병합 조인을 방지할 수 있습니다. 그러나 조인을 강제로 적용하면 쿼리가 상당히 느려질 수 있으므로 이 옵션은 최후의 수단으로만 제안됩니다. 워크로드를 스트레스 테스트하여 이것이 옵션인지 확인합니다. 경우에 따라 중첩된 루프 조인이 옵션이 아닐 수도 있습니다. 이 경우 "이 쿼리에 정의된 힌트 때문에 쿼리 프로세서에서 쿼리 계획을 생성할 수 없습니다." 오류 MSSQLSERVER_8622 SQL Server 실패할 수 있습니다.
메모리 부여 쿼리 힌트
SQL Server 2012 SP3 이후 쿼리당 메모리 부여 크기를 제어할 수 있는 쿼리 힌트가 존재했습니다. 다음은 이 힌트를 사용하는 방법의 예입니다.
SELECT Column1, Column2
FROM Table1
ORDER BY Column1
OPTION (MIN_GRANT_PERCENT = 3, MAX_GRANT_PERCENT = 5 )
특히 쿼리의 많은 인스턴스가 동시에 실행될 것으로 예상되는 경우 여기에 보수적 값을 사용하는 것이 좋습니다. 프로덕션 환경과 일치하도록 워크로드를 스트레스 테스트하고 사용할 값을 결정해야 합니다.
자세한 내용은 MAX_GRANT_PERCENT 및 MIN_GRANT_PERCENT 참조하세요.
Resource Governor
QE 메모리는 MIN_MEMORY_PERCENT 및 MAX_MEMORY_PERCENT 설정을 사용할 때 Resource Governor 실제로 제한하는 메모리입니다. 큰 메모리 부여를 유발하는 쿼리를 식별한 후에는 세션 또는 애플리케이션에서 사용하는 메모리를 제한할 수 있습니다. 워크로드 그룹을 사용하면 쿼리가 default SQL Server instance 부여할 수 있는 메모리의 최대 25%를 차지할 수 있습니다. 자세한 내용은 Resource Governor 리소스 풀 및 CREATE WORKLOAD GROUP을 참조하세요.
적응형 쿼리 처리 및 메모리 부여 피드백
2017년 SQL Server 메모리 부여 피드백 기능이 도입되었습니다. 이를 통해 쿼리 실행 엔진은 이전 기록에 따라 쿼리에 지정된 부여를 조정할 수 있습니다. 목표는 가능한 경우 권한 부여의 크기를 줄이거나 더 많은 메모리가 필요할 때 늘리는 것입니다. 이 기능은 세 가지 웨이브로 릴리스되었습니다.
- SQL Server 2017의 일괄 처리 모드 메모리 부여 피드백
- SQL Server 2019의 행 모드 메모리 부여 피드백
- 2022년 SQL Server 쿼리 저장소 및 백분위수 부여를 사용하여 디스크 내 지속성 메모리 부여 피드백
자세한 내용은 메모리 부여 피드백을 참조하세요. 메모리 부여 기능은 실행 시 쿼리에 대한 메모리 권한 부여 크기를 줄여 대규모 권한 부여 요청에서 발생하는 문제를 줄일 수 있습니다. 특히 행 모드 적응 처리를 사용할 수 있는 SQL Server 2019 이상 버전에서 이 기능을 사용하면 쿼리 실행에서 발생하는 메모리 문제를 알 수 없습니다. 그러나 기본적으로 이 기능이 있고 여전히 많은 QE 메모리 사용량이 표시되는 경우 이전에 설명한 단계를 적용하여 쿼리를 다시 작성합니다.
SQL Server 또는 OS 메모리 늘리기
쿼리에 대한 불필요한 메모리 부여를 줄이기 위한 단계를 수행한 후에도 메모리 부족 문제가 계속 발생하면 워크로드에 더 많은 메모리가 필요할 수 있습니다. 따라서 시스템에 실제 메모리가 충분한 경우 설정을 사용하여 max server memory SQL Server 메모리를 늘리는 것이 좋습니다. OS 및 기타 요구 사항에 대해 메모리의 약 25%를 남겨 두는 방법에 대한 권장 사항을 따릅니다. 자세한 내용은 서버 메모리 구성 옵션을 참조하세요. 시스템에서 사용 가능한 메모리가 충분하지 않은 경우 실제 RAM을 추가하거나 가상 머신인 경우 VM에 대한 전용 RAM을 늘리는 것이 좋습니다.
메모리 부여 내부
쿼리 실행 메모리에 대한 일부 내부 사항에 대한 자세한 내용은 SQL Server 메모리 부여 이해 블로그 게시물을 참조하세요.
메모리 부여 사용량이 많은 성능 시나리오를 만드는 방법
마지막으로, 다음 예제에서는 쿼리 실행 메모리의 대량 소비를 시뮬레이션하고 에서 RESOURCE_SEMAPHORE대기 중인 쿼리를 도입하는 방법을 보여 줍니다. 이 작업을 수행하여 이 문서에 설명된 진단 도구 및 기술을 사용하는 방법을 알아볼 수 있습니다.
경고
프로덕션 시스템에서는 사용하지 마세요. 이 시뮬레이션은 개념을 이해하고 더 잘 학습하는 데 도움이 되도록 제공됩니다.
테스트 서버에서 RML 유틸리티를 설치하고 SQL Server.
SQL Server Management Studio 같은 클라이언트 애플리케이션을 사용하여 SQL Server 최대 서버 메모리 설정을 1,500MB로 낮춥다.
EXEC sp_configure 'max server memory', 1500 RECONFIGURE명령 프롬프트를 열고 디렉터리를 RML 유틸리티 폴더로 변경합니다.
cd C:\Program Files\Microsoft Corporation\RMLUtilsostress.exe사용하여 테스트 SQL Server 대해 여러 동시 요청을 생성합니다. 이 예제에서는 30개의 동시 세션을 사용하지만 해당 값을 변경할 수 있습니다.
ostress.exe -E -S. -Q"select * from sys.messages order by message_id option (maxdop 1)" -n30앞에서 설명한 진단 도구를 사용하여 메모리 부여 문제를 식별합니다.
대용량 메모리 부여를 처리하는 방법 요약
- 쿼리를 다시 작성합니다.
- 통계를 업데이트하고 정기적으로 업데이트된 상태로 유지합니다.
- 식별된 쿼리 또는 쿼리에 적합한 인덱스를 만듭니다. 인덱스는 처리되는 행 수를 줄여 알고리즘을
JOIN변경하고 부여 크기를 줄이거나 완전히 제거할 수 있습니다. OPTION(min_grant_percent = XX, max_grant_percent = XX) 힌트를 사용합니다.- Resource Governor 사용합니다.
- SQL Server 2017 및 2019에서는 적응형 쿼리 처리를 사용하여 메모리 부여 피드백 메커니즘이 런타임에 동적으로 메모리 부여 크기를 조정할 수 있도록 합니다. 이 기능은 처음에 메모리 부여 문제를 방지할 수 있습니다.
- SQL Server 또는 OS 메모리를 늘입니다.
피드백
출시 예정: 2024년 내내 콘텐츠에 대한 피드백 메커니즘으로 GitHub 문제를 단계적으로 폐지하고 이를 새로운 피드백 시스템으로 바꿀 예정입니다. 자세한 내용은 다음을 참조하세요. https://aka.ms/ContentUserFeedback
다음에 대한 사용자 의견 제출 및 보기