Azure SQL Database를 사용하여 메모리 부족 오류 문제 해결

적용 대상:Azure SQL Database

SQL 데이터베이스 엔진이 쿼리를 실행할 충분한 메모리를 할당하지 못하면 오류 메시지가 표시될 수 있습니다. 이 문제는 선택한 서비스 목표 제한, 집계 워크로드 메모리 요구, 쿼리의 메모리 요구 등 다양한 이유로 인해 발생할 수 있습니다. Azure SQL Database의 메모리 리소스 제한에 관한 자세한 내용은 Azure SQL Database의 리소스 관리를 참조하세요.

참고 항목

이 문서에서는 Azure SQL Database를 중점적으로 다룹니다. SQL Server의 메모리 부족 문제 해결에 관한 자세한 내용은 MSSQLSERVER_701을 참조하세요.

다음에 대한 대응으로 아래 조사 방법을 시도합니다.

• 오류 코드 701 - 오류 메시지 “리소스 풀 ‘%ls’에 시스템 메모리가 부족하여 이 쿼리를 실행할 수 없습니다.”
• 오류 코드 802 - 오류 메시지 “버퍼 풀에 사용할 수 있는 메모리가 부족합니다.”

메모리 부족 이벤트 보기

메모리 부족 오류가 발생하면 sys.dm_os_out_of_memory_events를 검토합니다. 이 보기에는 경험적 접근 알고리즘에 의해 결정되고 유한한 신뢰도로 제공되는 예측된 메모리 부족 원인 관련 정보가 포함되어 있습니다.

SELECT * FROM sys.dm_os_out_of_memory_events ORDER BY event_time DESC;  

메모리 할당 조사

Azure SQL Database에서 메모리 부족 오류가 지속되면 Azure Portal에서 데이터베이스의 서비스 수준 목표를 적어도 일시적으로 늘리는 것이 좋습니다. 메모리 부족 오류가 지속되면 다음 쿼리를 사용하여 메모리 부족 상태를 초래할 수 있는 비정상적으로 높은 쿼리 메모리 부여를 검색합니다. 오류가 발생한 데이터베이스에서(Azure SQL 논리 서버의 master 데이터베이스에서 발생하지 않음) 다음 예제 쿼리를 실행합니다.

DMV를 사용하여 메모리 부족 이벤트 보기

sys.dm_os_out_of_memory_events에서는 이벤트 및 Azure SQL Database에서 OOM(메모리 부족) 이벤트의 원인을 볼 수 있습니다. summarized_oom_snapshot 확장 이벤트는 감지를 간소화하기 위한 기존 system_health 이벤트 세션의 일부입니다. 자세한 내용은 sys.dm_os_out_of_memory_events 및 블로그: 데이터베이스 엔진의 메모리 부족 오류를 해결하는 새로운 방법을 참조하세요.

DMV를 사용하여 메모리 클럭 보기

메모리 클럭에 대한 메모리 할당을 확인하여 메모리 부족 오류가 최근에 발생한 경우 광범위한 조사로 시작합니다. 메모리 클럭은 이 Azure SQL Database의 데이터베이스 엔진에 대해 내부적입니다. 할당된 페이지 측면에서 최고 메모리 클럭은 메모리를 가장 많이 사용하는 SQL Server 쿼리 또는 기능의 유형에 대한 정보를 제공합니다.

SELECT [type], [name], pages_kb, virtual_memory_committed_kb
FROM sys.dm_os_memory_clerks
WHERE memory_node_id <> 64 -- ignore Dedicated Admin Connection (DAC) node
ORDER BY pages_kb DESC;
GO
SELECT [type], [name], pages_kb, virtual_memory_committed_kb
FROM sys.dm_os_memory_clerks
WHERE memory_node_id <> 64 -- ignore Dedicated Admin Connection (DAC) node
ORDER BY virtual_memory_committed_kb DESC;

• MEMORYCLERK_SQLQERESERVATIONS와 같은 일부 일반적인 메모리 클럭은 메모리 부여가 큰 쿼리를 식별하고 향상된 인덱싱과 인덱스 튜닝을 통해 성능을 개선하면 가장 잘 해결됩니다.
• OBJECTSTORE_LOCK_MANAGER는 메모리 부여와 관련이 없지만, 잠금 에스컬레이션 사용 안 함 또는 매우 큰 트랜잭션 등으로 인해 쿼리가 많은 잠금을 클레임할 때 높을 것으로 예상됩니다.
• 일부 클럭은 사용률이 가장 높을 것으로 예상됩니다. MEMORYCLERK_SQLBUFFERPOOL는 거의 항상 최고 클럭이지만, CACHESTORE_COLUMNSTOREOBJECTPOOL은 columnstore 인덱스가 사용될 때 높습니다. 이러한 클럭의 가장 높은 사용률이 예상됩니다.

메모리 클럭 유형에 관한 자세한 내용은 sys.dm_os_memory_clerks를 참조하세요.

DMV를 사용하여 활성 쿼리 조사

대부분의 경우 실패한 쿼리는 이 오류의 원인이 아닙니다.

Azure SQL Database에 대한 다음 샘플 쿼리는 현재 메모리 부여를 보류 중이거나 대기 중인 트랜잭션의 중요한 정보를 반환합니다. 검사 및 성능 튜닝을 위해 식별된 상위 쿼리를 대상으로 지정하고 의도된 대로 실행되는지 여부를 평가합니다. 메모리 집약적 보고 쿼리 또는 유지 관리 작업의 타이밍을 고려합니다.

--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;

KILL 문을 사용하여 큰 메모리 부여를 보류 중이거나 대기 중인 현재 실행되는 쿼리를 중지하도록 결정할 수 있습니다. 특히 중요 프로세스가 실행되는 동안에는 이 문을 신중하게 사용합니다. 자세한 내용은 KILL(Transact SQL)을 참조하세요.

쿼리 저장소를 사용하여 이전 쿼리 메모리 사용량 조사

이전 샘플 쿼리는 라이브 쿼리 결과만 보고하지만, 다음 쿼리는 쿼리 저장소를 사용하여 이전 쿼리 실행에 관한 정보를 반환합니다. 이는 과거에 발생한 메모리 부족 오류를 조사하는 데 유용할 수 있습니다.

Azure SQL Database에 대한 다음 샘플 쿼리는 쿼리 저장소에서 기록한 쿼리 실행에 관한 중요한 정보를 반환합니다. 검사 및 성능 튜닝을 위해 식별된 상위 쿼리를 대상으로 지정하고 의도된 대로 실행되는지 여부를 평가합니다. 결과를 최근 기록으로 제한하는 qsp.last_execution_time의 시간 필터를 확인합니다. 환경에 따라 더 많거나 더 적은 결과를 생성하도록 TOP 절을 조정할 수 있습니다.

SELECT TOP 10 PERCENT --limit results
  a.plan_id, query_id, plan_group_id, query_sql_text
, query_plan = TRY_CAST(query_plan as XML)
, avg_query_max_used_memory
, min_query_max_used_memory
, max_query_max_used_memory
, last_query_max_used_memory 
, last_execution_time  
, query_count_executions
    FROM (
    SELECT 
      qsp.plan_id, qsp.query_id, qsp.plan_group_id, qsp.query_plan, qsqt.query_sql_text
    , last_execution_time = MAX(qsp.last_execution_time)
    , query_count_executions = SUM(qsrs.count_executions) 
    , avg_query_max_used_memory = AVG(qsrs.avg_query_max_used_memory)
    , min_query_max_used_memory = MIN(qsrs.min_query_max_used_memory)
    , max_query_max_used_memory = MAX(qsrs.max_query_max_used_memory)
    , last_query_max_used_memory = MAX(qsrs_latest.last_query_max_used_memory) --only from latest result
    FROM sys.query_store_plan AS qsp 
    INNER JOIN sys.query_store_query AS qsq
        ON qsp.query_id = qsq.query_id
    INNER JOIN sys.query_store_query_text AS qsqt
        ON qsq.query_text_id = qsqt.query_text_id 
    INNER JOIN sys.query_store_runtime_stats AS qsrs
        ON qsp.plan_id = qsrs.plan_id 
    INNER JOIN (SELECT plan_id
            , last_query_max_used_memory 
            , rownum = ROW_NUMBER() OVER (PARTITION BY plan_id ORDER BY last_execution_time DESC)
            FROM sys.query_store_runtime_stats qsrs) AS qsrs_latest
        ON qsrs_latest.plan_id = qsp.plan_id
        AND qsrs_latest.rownum = 1 --use latest last_query_max_used_memory per plan_id
    WHERE DATEADD(hour, -24, sysdatetime()) < qsp.last_execution_time --past 24 hours only
    AND qsrs_latest.last_query_max_used_memory > 0
    GROUP BY qsp.plan_id, qsp.query_id, qsp.plan_group_id, qsp.query_plan, qsqt.query_sql_text
    ) AS a
ORDER BY max_query_max_used_memory DESC, avg_query_max_used_memory DESC;

확장 이벤트

이전 정보 외에도 서버에서 활동 추적을 캡처하여 Azure SQL Database의 메모리 부족 문제를 철저히 조사하는 데 유용할 수 있습니다.

확장 이벤트(XEvents) 및 프로파일러 추적과 같이 SQL Server에서 추적을 캡처하는 두 가지 방법이 있습니다. 그러나 SQL Server Profiler는 Azure SQL Database에서 지원하지 않는 더 이상 사용되지 않는 추적 기술입니다. 확장 이벤트는 관찰된 시스템에 주는 영향을 적으면서 더 많은 다양성을 부여하는 최신 추적 기술이며, 해당 인터페이스는 SQL Server Management Studio(SSMS)에 통합됩니다. Azure SQL Database의 확장 이벤트 쿼리에 대한 자세한 내용은 Azure SQL Database의 확장 이벤트를 참조하세요.

SSMS의 확장 이벤트 새 세션 마법사를 사용하는 방법을 설명하는 문서를 참조하세요. 그러나 Azure SQL Database의 경우 SSMS는 개체 탐색기에서 각 데이터베이스 아래에 확장 이벤트 하위 폴더를 제공합니다. 확장 이벤트 세션을 사용하여 이러한 유용한 이벤트를 캡처하고 이벤트를 생성하는 쿼리를 식별합니다.

• 범주 오류:

  • error_reported
  • exchange_spill
  • hash_spill_details

• 범주 실행:

  • excessive_non_grant_memory_used

• 범주 메모리:

  • query_memory_grant_blocking
  • query_memory_grant_usage

• summarized_oom_snapshot

  메모리 부여 블록, 메모리 부여 유출 또는 과도한 메모리 부여의 캡처는 쿼리가 갑자기 과거보다 더 많은 메모리를 점유하는 경우의 잠재적 단서가 되고 기존 워크로드의 긴급한 메모리 부족 오류에 관한 잠재적 설명이 될 수 있습니다. summarized_oom_snapshot 확장 이벤트는 감지를 간소화하기 위한 기존 system_health 이벤트 세션의 일부입니다. 자세한 내용은 블로그: 데이터베이스 엔진의 메모리 부족 오류를 해결하는 새로운 방법을 참조하세요.

메모리 내 OLTP 메모리 부족

메모리 내 OLTP를 사용하는 경우 Error code 41805: There is insufficient memory in the resource pool '%ls' to run this operation 오류가 발생할 수 있습니다. 메모리 최적화 테이블 및 메모리 최적화 테이블 반환 매개 변수의 데이터 양을 줄이거나, 더 많은 메모리를 포함하도록 데이터베이스를 더 높은 서비스 목표로 스케일 업합니다. SQL Server 메모리 내 OLTP의 메모리 부족 문제에 관한 자세한 내용은 메모리 부족 문제 해결을 참조하세요.

Azure SQL Database 지원 받기

Azure SQL Database에서 메모리 부족 오류가 지속되면 Azure 지원 사이트에서 지원 받기를 선택하여 Azure 지원 요청을 제출합니다.

관련 콘텐츠

• SQL 데이터베이스의 인텔리전트 쿼리 처리
• 쿼리 처리 아키텍처 가이드
• SQL Server 데이터베이스 엔진 및 Azure SQL Database용 성능 센터
• Azure SQL Database 및 Azure SQL Managed Instance의 연결 문제 및 기타 오류 문제 해결
• SQL Database 및 SQL Managed Instance에서 일시적인 연결 오류 문제 해결
• 지능형 쿼리 처리 시연
• Azure SQL Database의 리소스 관리
• 블로그: 데이터베이스 엔진의 메모리 부족 오류를 해결하는 새로운 방법