sys.dm_exec_query_memory_grants(Transact-SQL)

  • 아티클

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

메모리 부여를 요청하고 대기 중이거나 메모리 부여를 받은 모든 쿼리에 대한 정보를 반환합니다. 메모리 부여가 필요하지 않은 쿼리는 이 보기에 표시되지 않습니다. 예를 들어 정렬 및 해시 조인 작업에는 쿼리 실행에 대한 메모리 부여가 있지만 절이 없는 ORDER BY 쿼리에는 메모리 부여가 없습니다.

Azure SQL Database에서 동적 관리 뷰는 데이터베이스 포함에 영향을 주거나 사용자가 액세스할 수 있는 다른 데이터베이스에 대한 정보를 노출하는 정보를 노출할 수 없습니다. 이 정보를 노출하지 않으려면 연결된 테넌트에 속하지 않는 데이터가 포함된 모든 행이 필터링됩니다. 또한 열 scheduler_idwait_order, , pool_idgroup_id 의 값이 필터링되고 열 값이 NULL로 설정됩니다.

참고 항목

Azure Synapse Analytics 또는 PDW(Analytics Platform System)에서 이를 호출하려면 이름을 sys.dm_pdw_nodes_exec_query_memory_grants사용합니다. 이 구문은 Azure Synapse Analytics의 서버리스 SQL 풀에서 지원되지 않습니다.

열 이름 데이터 형식 Description
session_id smallint 이 쿼리가 실행되고 있는 세션의 ID(SPID)입니다.
request_id int 요청의 ID입니다. 세션의 컨텍스트에서 고유합니다.
scheduler_id int 이 쿼리를 예약하는 스케줄러의 ID입니다.
Dop smallint 이 쿼리의 병렬 처리 수준입니다.
request_time datetime 이 쿼리가 메모리 부여를 요청한 날짜 및 시간입니다.
grant_time datetime 이 쿼리에 대한 메모리가 부여된 날짜 및 시간입니다. 메모리가 아직 부여되지 않은 경우 NULL입니다.
requested_memory_kb bigint 요청된 총 메모리 양(킬로바이트)입니다.
granted_memory_kb bigint 실제로 부여된 총 메모리 양(킬로바이트)입니다. 메모리가 아직 부여되지 않은 경우 NULL일 수 있습니다. 일반적인 경우 이 값은 .와 같 requested_memory_kb아야 합니다. 인덱스 생성 시에는 서버가 처음 부여된 메모리 외에 요청 시 메모리를 추가로 허용할 수 있습니다.
required_memory_kb bigint 이 쿼리를 실행하는 데 필요한 최소 메모리(KB)입니다. requested_memory_kb 은 이 양보다 같거나 큽다.
used_memory_kb bigint 현재 사용된 실제 메모리(KB)입니다.
max_used_memory_kb bigint 이 순간까지 사용된 최대 물리적 메모리(킬로바이트)입니다.
query_cost float 예상 쿼리 비용입니다.
timeout_sec int 이 쿼리가 메모리 부여 요청을 포기하기 전에 시간 제한(초)입니다.
resource_semaphore_id smallint 이 쿼리가 대기 중인 리소스 세마포의 고유하지 않은 ID입니다.

참고: 이 ID는 SQL Server 2008(10.0.x)보다 이전 버전의 SQL Server에서 고유합니다. 이 변경 내용은 쿼리 실행 문제 해결에 영향을 줄 수 있습니다. 자세한 내용은 이 문서의 뒷부분에 있는 "주의" 섹션을 참조하세요.
queue_id smallint 이 쿼리가 메모리 부여를 기다리는 대기 큐의 ID입니다. 메모리가 이미 부여된 경우 NULL입니다.
wait_order int 지정된 queue_id쿼리 내에서 대기 중인 쿼리의 순차적 순서입니다. 다른 쿼리에서 메모리 부여를 받거나 시간이 초과되는 경우 지정된 쿼리에 대해 이 값이 변경됩니다. 메모리가 이미 부여된 경우 NULL입니다.
is_next_candidate bit 다음 메모리 부여 후보입니다.

1 = 예

0 = 아니요

NULL = 메모리가 이미 부여되었습니다.
wait_time_ms bigint 대기 시간(밀리초)입니다. 메모리가 이미 부여된 경우 NULL입니다.
plan_handle varbinary(64) 이 쿼리 계획의 식별자입니다. 실제 XML 계획을 추출하는 데 사용합니다 sys.dm_exec_query_plan .
sql_handle varbinary(64) 이 쿼리에 대한 Transact-SQL 텍스트의 식별자입니다. 실제 Transact-SQL 텍스트를 가져오는 데 사용합니다 sys.dm_exec_sql_text .
group_id int 이 쿼리가 실행 중인 워크로드 그룹의 ID입니다.
pool_id int 이 워크로드 그룹이 속한 리소스 풀의 ID입니다.
is_small tinyint 1로 설정하면 이 권한 부여가 작은 리소스 세마포를 사용한다는 것을 나타냅니다. 0으로 설정되면 일반 세마포가 사용됩니다.
ideal_memory_kb bigint 실제 메모리에 적합하도록 부여된 메모리 크기(KB)입니다. 이는 카디널리티 예상치를 기반으로 합니다.
pdw_node_id int 이 배포가 있는 노드의 식별자입니다.

적용 대상: Azure Synapse Analytics, Analytics Platform System(PDW)
reserved_worker_count bigint 예약된 작업자 스레드 수입니다.

적용 대상: SQL Server (SQL Server 2016(13.x)부터 시작) 및 Azure SQL Database
used_worker_count bigint 현재 사용되는 작업자 스레드 수입니다.

적용 대상: SQL Server (SQL Server 2016(13.x)부터 시작) 및 Azure SQL Database
max_used_worker_count bigint 이 순간까지 사용된 최대 작업자 스레드 수입니다.

적용 대상: SQL Server (SQL Server 2016(13.x)부터 시작) 및 Azure SQL Database
reserved_node_bitmap bigint 작업자 스레드가 예약된 NUMA 노드의 비트맵입니다.

적용 대상: SQL Server (SQL Server 2016(13.x)부터 시작) 및 Azure SQL Database

사용 권한

SQL Server에서 VIEW SERVER STATE 권한이 필요합니다.
Azure SQL Database에서 데이터베이스에 대한 VIEW DATABASE STATE 권한이 필요합니다.

SQL Server 2022 이상에 대한 권한

서버에 대한 VIEW SERVER PERFORMANCE STATE 권한이 필요합니다.

설명

포함 ORDER BY 하거나 집계하는 동적 관리 뷰를 사용하는 쿼리는 메모리 사용량을 증가시켜 문제를 해결할 수 있습니다.

데이터베이스 관리자는 리소스 관리자 기능을 사용하여 서버 리소스를 최대 20개의 리소스 풀에 배치할 수 있습니다. SQL Server 2008(10.0.x)부터 각 풀은 작은 독립 서버 인스턴스처럼 동작하며 두 개의 세마포가 필요합니다. 반환 sys.dm_exec_query_resource_semaphores 되는 행 수는 SQL Server 2005(9.x)에서 반환되는 행보다 최대 20배 더 많을 수 있습니다.

예

쿼리 제한 시간에 대한 일반적인 디버깅 시나리오는 다음을 조사할 수 있습니다.

  • sys.dm_os_memory_clerks, sys.dm_os_sys_info 및 다양한 성능 카운터를 사용하여 전체 시스템 메모리 상태를 확인합니다.

  • 에서 type = 'MEMORYCLERK_SQLQERESERVATIONS'쿼리 실행 메모리 예약을 sys.dm_os_memory_clerks 확인합니다.

  • 다음을 사용하여 sys.dm_exec_query_memory_grants1에서 부여를 기다리는쿼리를 확인합니다.

    --Find all queries waiting in the memory queue  
SELECT * FROM sys.dm_exec_query_memory_grants WHERE grant_time IS NULL;

    1 이 시나리오에서는 대기 유형이 일반적으로 RESOURCE_SEMAPHORE. 자세한 내용은 sys.dm_os_wait_stats(Transact-SQL)를 참조하세요.

  • sys.dm_exec_cached_plans(Transact-SQL) 및 sys.dm_exec_query_plan(Transact-SQL)를 사용하여 메모리 부여가 있는 쿼리에 대한 캐시 검색

    -- retrieve every query plan from the plan cache  
USE master;  
GO  
SELECT * FROM sys.dm_exec_cached_plans cp CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle);  
GO

  • 런어웨이 쿼리가 의심되는 경우 sys.dm_exec_query_plan 열의 query_plan Showplan을 검사하고 sys.dm_exec_sql_text 일괄 처리를 text쿼리합니다. sys.dm_exec_requests 사용하여 현재 실행 중인 메모리 집약적 쿼리를 추가로 검사합니다.

    --Active requests with memory grants
SELECT
--Session data 
  s.[session_id], s.open_transaction_count
--Memory usage
, r.granted_query_memory, mg.grant_time, mg.requested_memory_kb, mg.granted_memory_kb, mg.required_memory_kb, mg.used_memory_kb, mg.max_used_memory_kb     
--Query 
, query_text = t.text, input_buffer = ib.event_info, query_plan_xml = qp.query_plan, request_row_count = r.row_count, session_row_count = s.row_count
--Session history and status
, s.last_request_start_time, s.last_request_end_time, s.reads, s.writes, s.logical_reads, session_status = s.[status], request_status = r.status
--Session connection information
, s.host_name, s.program_name, s.login_name, s.client_interface_name, s.is_user_process
FROM sys.dm_exec_sessions s 
LEFT OUTER JOIN sys.dm_exec_requests AS r 
    ON r.[session_id] = s.[session_id]
LEFT OUTER JOIN sys.dm_exec_query_memory_grants AS mg 
    ON mg.[session_id] = s.[session_id]
OUTER APPLY sys.dm_exec_sql_text (r.[sql_handle]) AS t
OUTER APPLY sys.dm_exec_input_buffer(s.[session_id], NULL) AS ib 
OUTER APPLY sys.dm_exec_query_plan (r.[plan_handle]) AS qp 
WHERE mg.granted_memory_kb > 0
ORDER BY mg.granted_memory_kb desc, mg.requested_memory_kb desc;
GO

참고 항목