다음을 통해 공유

  • 아티클

리소스 관리자 문제 해결

이 항목에서는 리소스 관리자를 사용할 때 발생할 수 있는 상황에 대한 문제 해결 지침을 제공합니다. 이 지침은 다음과 같은 범주에 따라 구성됩니다.

  • 오류

  • 예기치 않은 결과

  • 성능 관련 문제 및 오류

리소스 관리자 오류

리소스 관리자 오류 메시지는 리소스 관리자 구성 및 사용과 관련된 모든 동작을 포괄합니다.

다음 표에서는 리소스 관리자 오류 메시지의 예를 보여 주고 오류 메시지에 설명된 문제를 해결하는 방법에 대한 지침을 제공합니다.

오류 번호

오류 메시지

해결 방법

8645

리소스 풀 'myTestPool'(257)에서 메모리 리소스가 쿼리를 실행하기를 기다리는 동안 시간이 초과되었습니다. 쿼리를 다시 실행하십시오.

제한 시간 값을 더 높게 구성하거나 서버에 대한 쿼리 로드를 줄입니다.

8651

요청한 메모리 부여를 리소스 풀 'myTestPool'(257)에서 사용할 수 없어서 작업을 수행할 수 없습니다. 쿼리를 다시 실행하거나, 쿼리 부하를 줄이거나, 리소스 관리자 구성 설정을 확인하십시오.

쿼리를 나중에 다시 실행합니다. 서버의 쿼리 로드를 줄입니다. 관리자에게 리소스 관리자 구성 설정을 확인하도록 요청합니다.

8657

작업 그룹 'myTestGroup'(267) 및 리소스 풀 'myTestPool'(257)의 최대 구성 한계를 초과하므로 메모리를 1024KB까지 부여할 수 없습니다. 서버 관리자에게 문의하여 메모리 사용량 한계를 늘리십시오.

정렬 및 해시 조인과 같은 메모리 소비 작업을 줄이도록 쿼리를 다시 작성합니다. 시스템 관리자에게 메모리 사용 제한을 높이도록 요청합니다.

관리자는 다음과 같은 매개 변수 중 하나 또는 모두를 조정할 수 있습니다.

  • 리소스 풀의 max_memory_percent는 모든 쿼리에 대한 최대 물리적 메모리 부여 공간을 설정합니다.

  • 작업 그룹의 request_max_memory_grant_percent는 쿼리별 제한을 설정합니다.

관리자는 sys.dm_exec_query_resource_semaphores에서 max_target_memory_kb 열의 실제 물리적 제한을 가져올 수 있습니다.

쿼리별 제한은 max_target_memory_kb * request_max_memory_grant_percent로 계산할 수 있습니다.

참고참고
관리자는 오류 메시지에 언급된 필요 메모리가 위에서 계산된 쿼리별 제한보다 작은지 확인해야 합니다. 하지만 request_max_memory_grant_percent를 늘릴 경우 큰 쿼리의 동시성이 감소되는 부작용이 발생합니다. 예를 들어 사용자는 기본 25% 설정에서 3개의 큰 쿼리를 실행할 수 있지만 40% 설정에서는 두 개의 큰 쿼리만 실행할 수 있습니다.

10900

시작하는 동안 리소스 관리자를 구성하지 못했습니다. 특정 오류 메시지에 대한 SQL Server 오류 로그를 확인하거나 DBCC CHECKCATALOG('master')를 실행하여 master 데이터베이스의 일관성을 확인하십시오.

"DBCC CHECKCATALOG('master')"를 실행합니다.

10901

사용자에게 리소스 관리자 구성을 변경할 수 있는 권한이 없습니다.

리소스 관리자 구성의 수정을 허용하는 권한을 부여하고 다시 시도합니다.

10902

사용자 정의 함수 'dbo.rgclassifier_v1'이(가) master 데이터베이스에 없거나 사용자에게 데이터베이스에 액세스할 수 있는 권한이 없습니다.

master에 분류자 UDF(사용자 정의 함수)를 만들거나 기존 분류자 UDF에 필요한 권한을 부여합니다.

10903

분류자 사용자 정의 함수에 대해 지정된 스키마 이름 'dbo.rgclassifier_v1'이(가) 없거나 사용자에게 스키마를 사용할 수 있는 권한이 없습니다.

다른 스키마 이름을 사용하거나 이 스키마에 대한 올바른 권한을 얻습니다.

10904

리소스 관리자 구성에 실패했습니다. 삭제되거나 다른 리소스 풀로 이동되는 작업 그룹에 활성 세션이 있습니다. 영향을 주는 작업 그룹의 모든 활성 세션의 연결을 끊고 다시 시도하십시오.

영향을 주는 그룹의 모든 활성 세션의 연결을 끊고 다시 시도하십시오.

참고참고
이 리소스 관리자 릴리스에서는 열린 세션이 있는 풀 간의 그룹 이동을 허용하지 않습니다.

10905

메모리가 부족하여 리소스 관리자 구성을 완료할 수 없습니다. 서버 부하를 줄이거나 전용 관리자 연결에서 작업을 시도하십시오.

서버에 대한 부하를 줄이거나 전용 관리자 연결에서 구성 작업을 시도합니다.

10906

개체 'dbo'.'rgclassifier_v1'이(가) 올바른 리소스 관리자 분류자 사용자 정의 함수가 아닙니다. 올바른 분류자 사용자 정의 함수는 스키마 바운드여야 하고, sysname을 반환하며, 매개 변수가 없어야 합니다.

올바른 분류자 UDF를 제공합니다. 올바른 분류자 UDF의 조건은 다음과 같습니다.

  • sysname을 반환합니다.

  • 매개 변수가 없습니다.

  • SCHEMABINDING 옵션을 사용하여 만들어집니다.

10907

값이 50인 특성 'MIN_CPU_PERCENT'이(가) 값이 40인 특성 'MAX_CPU_PERCENT'보다 큽니다.

최대값보다 작거나 같은 최소값을 제공합니다.

10908

값이 40인 특성 ''MAX_MEMORY_PERCENT'이(가) 값이 60인 특성 ''MIN_MEMORY_PERCENT'보다 작습니다.

특성 최소값보다 크거나 같은 최대값을 제공합니다.

10909

리소스 풀을 만들 수 없습니다. 최대 리소스 풀의 개수는 미리 정의된 리소스 풀을 포함하여 현재 한계 20을 초과할 수 없습니다.

필요 없는 리소스 풀을 삭제합니다.

10910

작업을 완료할 수 없습니다. 지정된 'MIN_CPU_PERCENT' 값, 25(으)로 인해 모든 리소스 풀의 최소 합계가 100퍼센트를 초과합니다. 합계가 100보다 작도록 값을 줄이거나 다른 리소스 풀을 수정하십시오.

MIN_CPU_PERCENT 값을 줄입니다.

10911

리소스 풀 'myTestPool2'이(가) 없어서 요청된 작업을 수행할 수 없습니다.

sys.resource_governor_resource_pools 카탈로그 뷰를 쿼리하여 현재 정의된 리소스 풀을 확인합니다. 기존 풀을 선택하거나 새 풀을 만듭니다.

10912

작업을 완료할 수 없습니다. 미리 정의된 작업 그룹을(를) 삭제할 수 없습니다.

삭제할 사용자 생성 작업 그룹을 선택합니다.

10913

사용자는 'internal' 리소스 풀의 작업 그룹 'internal'을(를) 삭제할 수 없습니다.

사용자가 만든 풀 또는 기본 풀에 작업 그룹을 만듭니다.

10914

작업 그룹 '#mygroup'의 이름은 ##의 #으로 시작할 수 없습니다.

그룹 또는 풀을 만들 때 # 또는 ##을 사용하지 않습니다.

10915

작업을 완료할 수 없습니다. 'internal' 작업 그룹을(를) 변경할 수 없습니다.

변경할 사용자 생성 풀 또는 그룹을 선택합니다.

참고   기본 그룹 또는 리소스 풀의 구성 변경이 허용됩니다.

10916

작업 그룹 'myTestGroup'이(가) 들어 있으므로 리소스 풀 'myTestPool'을(를) 삭제할 수 없습니다. 이 리소스 풀을 삭제하기 전에 리소스 풀을 사용하여 모든 작업 그룹을 삭제하거나 제거하십시오.

이 풀을 사용하는 모든 작업 그룹을 삭제하거나 이동한 다음 풀을 삭제합니다.

10917

ALTER WORKLOAD GROUP이 실패했습니다. 'WITH' 또는 'USING' 절을 지정해야 합니다.

ALTER WORKLOAD GROUP 문에 'WITH' 또는 'USING' 절을 사용합니다.

10918

리소스 풀 'myTestPool'이(가) 이미 있으므로 만들 수 없습니다.

다른 리소스 풀 이름을 선택합니다.

10919

master 데이터베이스에서 리소스 관리자 구성을 읽는 동안 오류가 발생했습니다. master 데이터베이스의 무결성을 확인하거나 시스템 관리자에게 문의하십시오.

"DBCC CHECKCATALOG('master')"를 실행합니다.

10920

사용자 정의 함수 'dbo.myclassifer'을(를) 삭제할 수 없습니다. 이 사용자 정의 함수는 리소스 관리자 분류자로 사용됩니다.

없음

10921

'기본' 작업 그룹을(를) '기본' 리소스 풀에서 이동할 수 없습니다.

해당 사항 없음

10981

리소스 관리자가 다시 구성되었습니다.

이 메시지는 SQL Server 이벤트 로그에 기록됩니다.

10982

리소스 관리자 분류자 사용자 정의 함수를 실행하지 못했습니다. 자세한 내용은 세션 ID 58의 SQL Server 오류 로그에서 이전 오류를 확인하십시오. 분류자 경과 시간: 800밀리초.

이 메시지는 SQL Server 오류 로그에 기록됩니다.

참고   SQL Server 오류 로그에서 같은 SPID(서버 프로세스 식별자)의 이전 메시지를 통해 특정 실패 원인을 파악할 수도 있습니다. 장기 실행 분류자 때문에 사용자 로그인 제한 시간 초과가 발생할 수 있습니다. 분류자 경과 시간이 클라이언트 로그인 제한 시간을 초과하는지 확인합니다.

10983

관리자가 리소스 관리자 재구성을 취소했습니다.

해당 사항 없음

10984

리소스 관리자를 재구성하지 못했습니다.

해당 사항 없음

예기치 않은 결과

예기치 않은 결과란 리소스 관리자의 여러 요소가 작동하지만 결과가 예상과 다른 경우를 의미합니다. 예를 들어 세션 분류가 올바르게 작동하지 않는 것처럼 보이는 경우, 또는 작업 그룹을 삭제하거나 생성할 때 문제가 발생하는 경우가 있습니다.

세션 분류

다음 조건에 해당하는 경우 세션은 기본 작업 그룹으로 이동합니다.

  • 분류자 UDF가 존재하지 않거나 사용하지 않도록 설정되어 있습니다.

  • 분류자 UDF가 세션을 기본 작업 그룹에 넣습니다. 이는 함수 논리의 결함을 나타냅니다.

기본적인 문제 해결

분류에 사용할 분류자 UDF가 없는 경우 모든 세션은 자동으로 기본 작업 그룹으로 이동합니다. 분류자 UDF를 만든 후에는 이 분류자 UDF가 리소스 관리자에 등록되고 메모리 내 구성이 업데이트되었는지 확인해야 합니다.

분류자 UDF 작성, 등록 및 설정은 다음과 같은 3단계 프로세스로 진행됩니다.

  • 첫째, 함수를 만들어야 합니다.

    CREATE FUNCTION function_name() RETURNS <something> 
WITH SCHEMABINDING

  • 둘째, 리소스 관리자에 함수를 등록해야 합니다.

    ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION=schema_name.function_name)

  • 셋째, 리소스 관리자 메모리 내 구성을 업데이트해야 합니다.

    ALTER RESOURCE GOVERNOR RECONFIGURE

분류 문제를 해결할 때 가장 먼저 해야 할 일은 만든 함수가 리소스 관리자에 등록되어 있고 구성이 업데이트되었는지 확인하는 것입니다. 다음 쿼리를 사용하여 리소스 관리자가 현재 사용 중인 분류자 UDF에 대한 스키마 이름(schema_name)과 분류자 함수 이름(function_name)을 가져옵니다.

USE master
SELECT 
      object_schema_name(classifier_function_id) AS [schema_name],
      object_name(classifier_function_id) AS [function_name]
FROM sys.dm_resource_governor_configuration

위의 방식을 사용하면 분류자 UDF를 변경했지만 리소스 관리자가 여전히 이전 함수 논리를 사용하여 세션을 분류하는 문제를 해결할 수 있습니다. 이러한 동작은 변경한 사항이 메모리 내 구성에 적용되지 않았음을 나타냅니다.

고급 문제 해결

예상한 결과가 발생하지 않거나 리소스 사용량이 많은 매우 복잡한 분류자 함수를 만드는 경우가 있습니다. 기본적인 문제 해결을 수행한 다음에는 함수 논리가 올바른지 확인해야 합니다. 최악의 시나리오는 코딩 오류로 인해 무한 루프 또는 런어웨이 쿼리가 발생하는 경우입니다.

DAC(관리자 전용 연결)는 분류의 영향을 받지 않으며 리소스 관리자가 실행되면서 들어오는 세션을 분류 중일 때 사용할 수 있으므로 DAC를 사용하면 잘못 작성된 분류자 함수의 문제를 해결할 수 있습니다. 자세한 내용은 전용 관리자 연결 사용을 참조하십시오.

[!참고]

문제 해결을 위해 DAC를 사용할 수 없는 경우 단일 사용자 모드로 시스템을 다시 시작할 수 있습니다. 단일 사용자 모드는 분류의 영향을 받지 않지만 리소스 관리자가 실행 중일 때 이를 진단할 수 없습니다.

다음을 쿼리하여 분류자 함수에 대한 정보를 가져올 수 있습니다.

  • sys.dm_exec_query_stats(문 정보를 포함하지만 실제 함수는 포함하지 않음)

  • sys.dm_exec_sql_text(sys.dm_exec_query_stats에서 가져온 sql_handle과 함께 사용)

  • PreConnect:Starting Event Class(분류자 함수의 ID 및 이름 제공)

다시 구성 실패

리소스 관리자는 ALTER RESOURCE GOVERNOR RECONFIGURE 문이 완료될 때까지 메타데이터 변경 사항을 실행 중인 세션에서 분리한 상태로 둡니다. 활성 세션 또는 열린 세션이 포함된 그룹을 삭제하려고 시도하거나 작업 그룹이 포함된 리소스 풀을 삭제하려고 시도하면 ALTER RESOURCE GOVERNOR RECONFIGURE가 실패합니다.

메모리 내 구성 및 저장된 구성을 가져오려면 각각 sys.dm_resource_governor_configuration 및 sys.resource_governor_configuration을 쿼리합니다. is_reconfiguration_pending(sys.dm_resource_governor_configuration) 값이 1이면 세션 구성이 업데이트되지 않은 것입니다. 이 경우에는 선택할 수 있는 방법은 다음과 같습니다.

  • 세션이 완료되거나 연결을 삭제할 때까지 기다립니다.

  • 명시적으로 활성 세션을 중지하거나 세션 연결을 삭제합니다.

  • 삭제한 그룹 또는 풀을 다시 만들고 설정을 조정한 후 ALTER RESOURCE GOVERNOR RECONFIGURE를 다시 실행합니다.

성능 관련 문제 및 오류

리소스 관리자를 사용할 때 성능 문제가 있다고 보이면 그 문제가 리소스 관리자 구성 때문에 발생하는 것인지를 확인해야 합니다. 이 섹션에서 제공하는 문제 해결 지침은 다음 두 가지 범주로 나뉩니다.

  • 세션 분류

  • 쿼리 실행

세션 분류

장기 실행 로그온 트리거 또는 분류자 UDF(사용자 정의 함수)는 서버 성능에 영향을 줄 수 있습니다. 로그온 트리거나 분류자 UDF가 완료되는 데 많은 시간이 걸릴 경우 연결 제한 시간이 초과됩니다. 그러나 트리거나 함수는 계속해서 실행되어 서버 리소스를 사용합니다.

이전 연결 상태에서 실행 중인 세션이 있는 것으로 의심될 경우 전용 관리자 연결로 로그온하여 시작된 후 아직 완료되지 않은 여러 요청 또는 세션이 있는지 PreConnect:Starting 이벤트 클래스를 확인합니다.

이 문제를 해결하고 다시 발생하지 않도록 방지하려면

  • 세션을 중지합니다.

  • 장기 실행 함수 또는 로그온 트리거의 가능한 원인을 확인합니다.

  • 문제를 발생시키는 트리거 또는 함수를 제거하고 교체합니다.

쿼리 실행

쿼리가 분류되어 실행된 후에 응답을 중지하거나 실패한 것처럼 보일 수 있습니다. 현재 리소스 관리자 설정이 원인일 수 있습니다. 리소스 관리자 구성의 다음과 같은 부분을 조사해야 합니다.

  • 요청 수 스로틀

  • 최대 CPU 제한

  • CPU 대역폭 스로틀

  • 메모리 부여 크기

  • 메모리 부여 제한 시간 초과 오류

  • 메모리 부족 오류

  • 만족스럽지 못한 쿼리 계획

요청 수 스로틀

이 시나리오에서는 사용자로부터 성능 저하 보고를 받고 요청 수가 스로틀되는 것으로 의심합니다.

가장 먼저 해야 할 일은 사용자가 포함된 그룹에 대해 요청 수 스로틀이 명시적으로 구성되었는지 여부를 확인하는 것입니다. 이렇게 하려면 사용자의 그룹 멤버 자격에서 GROUP_MAX_REQUESTS가 설정되어 있는지 확인합니다. GROUP_MAX_REQUESTS가 설정되어 있지 않으면 명시적 요청 수 스로틀이 없는 것이며 다음 단계에 따라 추가 조사를 수행해야 합니다.

  • sys.dm_os_waiting_tasks를 쿼리하여 RESMGR_THROTTLED 대기 유형으로 대기 중인 요청이 있는지 확인합니다. 이 대기 유형이 있다면 요청 수 스로틀이 있다는 의미입니다.

  • 성능 모니터를 시작하고 Queued requests 및 Active requests 카운터를 사용하여 데이터를 수집합니다. Queued request 수가 0이 아니면 요청 스로틀이 있다는 의미입니다.

  • Active requests 값이 GROUP_MAX_REQUESTS 설정과 일치하는지 확인합니다. Active requests 값이 GROUP_MAX_REQUESTS 설정보다 높으면 스로틀할 수 없는 요청이 그룹에 포함된 것일 수 있습니다(예: 트랜잭션 열기).

  • Queued requests가 0이면 너무 많은 요청으로 풀이 오버로드될 수 있으므로 동일한 리소스 풀을 공유하는 모든 작업 그룹에 대한 Active requests를 확인합니다.

최대 CPU 제한

리소스 관리자 이벤트 생성으로 구동되는 정책이 있는 경우 최대 CPU 제한에 도달할 때 생성되는 이벤트를 사용할 수 있습니다.

이 시나리오에서는 CPU를 너무 많이 사용하는 쿼리를 검색하기 위해 구성한 최대 CPU 제한(REQUEST_MAX_CPU_TIME_SEC)이 너무 낮지 않은지 확인해야 합니다.

다음 동작을 통해 CPU 제한 설정의 유효성을 확인할 수 있습니다.

  • SQL Trace 세션을 시작하고 CPU Threshold Exceeded 이벤트를 수집합니다. 사용자 요청이 최대 CPU 사용 제한에 도달하면 서버가 SQL Trace 이벤트를 자동으로 생성합니다. 설정이 너무 낮으면 이러한 이벤트가 많이 생성됩니다.

[!참고]

이 이벤트는 서버 이벤트 알림으로도 제공되므로 이벤트에 반응하는 스크립트를 작성할 수 있습니다.

  • 성능 모니터를 시작하고 Max request cpu time (ms) 카운터를 사용하여 데이터를 수집합니다. 이 카운터의 값을 척도로 사용하여 작업 그룹의 적절한 제한을 설정할 수 있습니다.

CPU 대역폭 스로틀

이 시나리오에서는 CPU usage % 성능 카운터가 리소스 관리자 MAX_CPU_PERCENT 설정에 도달했거나 이와 근접하기 때문에 CPU 대역폭이 스로틀되는 것으로 의심합니다. 다음 쿼리에서는 SQL Server 인스턴스의 모든 작업 그룹 및 리소스 풀에 대한 CPU 사용률을 반환합니다.

select * from sys.dm_os_performance_counters where counter_name = 'cpu usage %'

자세한 내용은 sys.dm_os_performance_counters(Transact-SQL)를 참조하십시오.

시스템에서 다음 검사를 수행하여 CPU 대역폭이 스로틀되는지 여부를 확인할 수 있습니다.

  • 전체 서버 CPU 사용률을 확인합니다. SQL Server 이외의 부하가 현재 활성 상태인 경우 문제 해결 중인 쿼리에 영향을 줄 수 있습니다.

  • 리소스 풀 간의 CPU 사용 분배를 확인합니다. 다른 풀에 CPU 사용에 대한 최소값이 높게 구성되어 있는 이유로 리소스 풀이 스로틀될 수 있습니다. 실제 CPU 사용률과 예상 CPU 사용률(계산된 CPU 사용률)에 대한 카운터를 비교합니다.

  • 문제의 리소스 풀에 할당된 작업 그룹을 확인합니다. 다른 작업 그룹의 부하가 동일한 풀을 공유하는 사용자에게 영향을 줄 수 있습니다.

  • 스케줄러 간의 CPU 사용 분배를 확인합니다. 조사 중인 쿼리가 장기 실행 쿼리를 포함하는 스케줄러에 배치되어 있을 수 있습니다. 이 경우 쿼리가 스로틀된 것으로 나타날 수 있지만 실제 문제는 스케줄러 간의 불균일한 부하 분산입니다.

  • 작업이 리소스 관리자 설정에 의해 스로틀되지 않고 다른 세션에 의해 차단될 수 있는 경우가 있는지 확인합니다.

  • 현재 시스템에서 쿼리를 실행 중인 세션의 수를 확인합니다. 동시 실행 요청 수가 늘어나면 SQL Server는 CPU가 고갈되지 않도록 하기 위해 모든 요청이 조금이라도 CPU 시간을 받도록 보장하려고 시도합니다.

메모리 부여 크기

이 시나리오에서는 쿼리가 느리게 실행되는 이유가 부여 메모리의 크기에 있다고 의심합니다.

리소스 관리자는 큰 쿼리가 제한 내에 포함되도록 부여 메모리를 줄여서 최대 쿼리 메모리 제한을 적용합니다. 쿼리에 부여되는 메모리가 100% 미만인 경우 쿼리는 임시 데이터를 디스크에 기록해야 하는 경우가 있는데, 이는 성능에 눈에 띄는 영향을 미칩니다.

적절한 최대 쿼리 크기 제한을 설정하려면 큰 쿼리의 비율을 확인해야 합니다. 다음 동작을 통해 최적의 설정을 확인할 수 있습니다.

  • sys.dm_exec_query_memory_grants를 쿼리하여 메모리 부여의 현재 상태를 확인합니다. ideal_memory_kb 열은 카디널리티 예측을 기반으로 이상적인 양을 보여 줍니다. requested_memory_kb 열은 최대 쿼리 제한에 도달한 후 줄어들었을 수 있는 요청 양을 보여 줍니다. requested_memory_kb가 ideal_memory_kb보다 큰 차이로 낮으면 쿼리의 메모리가 넘치는 경우가 잦아지게 됩니다(카디널리티 예측이 정확하다고 가정할 경우).

  • 성능 모니터를 시작하고 Reduced memory grants/sec 카운터를 사용하여 데이터를 수집합니다. 이 카운터의 값은 최대 요청 크기 제한에 도달한 후 이상적인 양보다 적은 양을 받은 메모리 부여 수의 속도를 나타냅니다. 큰 쿼리의 경우 메모리 제한 내로 유지하기 위해 넘치는 부분을 디스크에 기록해야 하기 때문에 이상적인 양을 받은 쿼리보다 훨씬 느리게 실행될 수 있습니다.

메모리 부여 문제를 완화하려면 경우에 따라 풀 크기 제한 및/또는 최대 메모리 크기 제한을 늘려야 합니다.

[!참고]

최대 메모리 크기만 늘리면 큰 쿼리의 동시성이 줄어들 수 있습니다.

메모리 부여 제한 시간 초과 오류

이 시나리오에서는 메모리 부여 제한 시간 초과 오류로 쿼리가 실패합니다.

작업 그룹과 리소스 풀 정의에 지정된 총 활성 메모리 부여 요청 수와 메모리 제한은 모두 메모리 부여 제한 시간 초과에 영향을 줄 수 있습니다. 단일 리소스 풀을 여러 리소스 그룹에서 공유할 경우 다른 그룹의 동시 쿼리 수도 메모리 부여 제한 시간 초과에 영향을 줄 수 있습니다.

다음 동작을 통해 최적의 리소스 풀 설정을 확인할 수 있습니다.

  • sys.dm_exec_query_memory_grants를 쿼리하여 이 그룹 및 풀에 대한 메모리 부여 및 대기 중인 쿼리의 수를 확인합니다.

  • sys.dm_exec_query_resource_semaphores를 쿼리하여 부여된 총 메모리와 대상을 확인합니다.

부여 메모리 사용량이 사용 가능한 메모리 공간보다 큰 경우 리소스 풀 크기 제한을 늘려야 할 수 있습니다.

메모리 부족 오류

메모리 부족 오류로 쿼리가 실패합니다.

기본적인 문제 해결

다음과 같은 방법으로 최적의 작업 그룹 설정을 확인할 수 있습니다.

  • sys.dm_os_memory_brokers를 쿼리하여 리소스 풀 내의 상대적인 메모리 분산과 추세를 확인합니다. 작은 메모리 공간에 요청이 너무 많으면 작업 그룹/리소스 풀이 오버로드되어 메모리 부족 오류가 발생할 수 있습니다.

  • 성능 모니터를 시작하고 메모리 관련 리소스 풀 카운터를 통해 데이터를 수집하여 메모리 부여, 캐시된 메모리 및 컴파일/최적화 프로그램 메모리를 위한 대상 및 현재 메모리 사용량을 확인합니다. 현재 값이 대상 값보다 높은 경우 리소스 풀이 오버로드되었음을 나타냅니다. 풀 메모리 제한을 변경해 보십시오.

  • 성능 모니터를 시작하고 request max memory grant 카운터를 사용하여 데이터를 수집합니다. 카운터 값이 작업 그룹의 REQUEST_MAX_MEMORY_GRANT_PERCENT 설정으로 지정된 값을 초과할 경우 쿼리가 실패할 가능성이 높습니다. 작업 그룹 제한을 변경해 보십시오.

고급 문제 해결

메모리 부족(701) 오류는 태스크가 메모리 관리자에서 메모리 블록을 할당하려고 시도했으나 이 시도가 실패할 경우 반환되는 일반 오류입니다. 자세한 내용은 MSSQLSERVER_701을 참조하십시오.

이 오류는 다음과 같은 경우에 발생할 수 있습니다.

  • 메모리 풀이 총 제한에 도달합니다.

[!참고]

리소스 관리자 외의 다른 원인도 있을 수 있습니다. 서버에서 실행 중인 다른 응용 프로그램의 메모리 요구가 원인의 일부분일 수도 있습니다.

  • 가상 주소 공간에 필요한 예약을 위해 충분할 만큼의 비어 있는 블록이 없어 다중 페이지 또는 가상 주소 공간 할당이 실패합니다. 이러한 상황은 32비트 아키텍처에서 발생할 가능성이 높으며 64비트 아키텍처에서는 잘 발생하지 않습니다.

  • 총 커밋이 커밋 제한에 도달하여 다중 페이지 또는 가상 주소 공간 할당이 실패합니다. 이러한 경우는 32비트 및 64비트 아키텍처에 모두 해당됩니다.

메모리 부족 오류가 발생하면 오류 원인을 확인하기 위해 가장 먼저 오류 로그를 조사하는 것이 좋습니다. 로그에는 다음 예와 비슷한 출력이 포함되어 있습니다.

2006-01-28 04:27:15.43 spid51 Failed allocate pages: FAIL_PAGE_ALLOCATION 1

오류 로그에 기록되는 가능한 실패는 다음과 같습니다.

  • FAIL_PAGE_ALLOCATION - 할당을 시도한 페이지 수

  • FAIL_VIRTUAL_RESERVE - 예약을 시도한 바이트 수

  • FAIL_VIRTUAL_COMMIT - 커밋을 시도한 바이트 수

메모리 부족 오류를 트리거한 태스크가 오류의 원인이 아닌 경우가 많다는 사실을 알아야 합니다. 런어웨이 태스크가 없다면 메모리 부족 오류가 발생하는 조건은 일반적으로 실행 중인 여러 태스크가 최고점에 이른 경우입니다. 따라서 일반적인 FAIL_PAGE_ALLOCATION 오류의 경우 보다 넓은 관점에서 시스템 작업을 조사해야 합니다.

오류 로그에서 확인해야 하는 또 다른 중요한 정보는 메모리 상태 출력입니다. 오류에 따라 단일 페이지, 다중 페이지, 개별 메모리 클럭에 대한 가상 예약 또는 커밋 수를 확인해야 합니다. 오류 조사에서 핵심적인 단계는 메모리를 가장 많이 소비하는 항목을 식별하는 것입니다. 일반적으로 가장 큰 메모리 소비자는 다음과 같은 유형입니다.

  • MEMORYCLERK_*은 서버 구성 또는 작업에 특정 메모리 할당이 필요하다는 것을 의미합니다. SQL Server 구성 요소에 해당하는 메모리 클럭이 있으며 개별 구성 요소가 여러 메모리 클럭을 포함할 수 있습니다. 자세한 내용은 sys.dm_os_memory_clerks(Transact-SQL)를 참조하십시오. 문제를 발생시키는 작업을 메모리 클럭에서 식별하는 경우도 있지만 대량 메모리 소비를 일으킨 원인을 확인하려면 클럭과 연결된 메모리 개체를 조사해야 하는 경우가 더 많습니다.

  • CACHESTORE_*, USERSTORE_*, OBJECTSTORE_*는 캐시 유형입니다. 캐시에 의한 메모리 소비가 크다면 다음을 의미할 수 있습니다.

    • 메모리가 캐시에서 할당되었지만 아직 제거될 수 있는 항목으로 삽입되지 않았습니다. 이것은 위의 MEMORYCLERK 경우와 매우 비슷합니다.

    • 모든 캐시 항목이 사용 중이므로 제거할 수 없습니다. 이러한 경우는 sys.dm_os_memory_cache_counters를 조사하여 entries_count column과 entries_in_use_count 열의 값을 비교하면 확인할 수 있습니다.

  • MEMORYCLERK_SQLQERESERVATIONS는 QE(쿼리 실행)에서 정렬/조인을 포함하는 쿼리를 실행하기 위해 예약한 메모리의 양을 나타냅니다. 예약 수준이 높을 때 발생하는 메모리 부족 오류는 일반적으로 서버의 버그를 나타냅니다.

오류 로그의 메모리 상태 출력에는 고갈된 메모리 풀도 표시됩니다. 각 풀에 대한 메모리 브로커는 빼앗긴(컴파일) 메모리, 캐시된 메모리 및 예약된(부여된) 메모리 간의 메모리 분산을 보여 줍니다. 세 브로커의 숫자는 메모리 클럭과 연결된 이전 메모리 개체에 해당합니다. 사용자 지정 스크립트 및 sys.dm_os_memory_cache_entries 동적 관리 뷰를 사용하여 전체 덤프에서 정보를 추출하면 지정된 클럭 또는 메모리 개체에서 풀에 할당된 메모리의 양을 확인할 수 있습니다. sys.dm_os_memory_cache_entries 동적 관리 뷰는 각 항목이 연결된 pool_id를 보여 줍니다.

고객 지원 서비스에 문의해야 하는 경우 다음 정보를 수집하여 지원 담당자에게 알려 주십시오.

  • 메모리 부족 오류와 오류 발생 시점의 메모리 상태 출력을 보여 주는 오류 로그

  • 다음 문을 이용한 출력

    dbcc memorystatus
dbcc sqlperf(spinlockstats)
select * from sys.dm_os_memory_clerks
select * from sys.dm_os_wait_stats order by wait_type
select * from sys.dm_os_waiting_tasks
select * from sys.dm_os_ring_buffers where ring_buffer_type='RING_BUFFER_OOM'
select * from sys.dm_os_ring_buffers where ring_buffer_type='RING_BUFFER_RESOURCE_MONITOR'
select * from sys.dm_os_ring_buffers where ring_buffer_type='RING_BUFFER_MEMORY_BROKER'
select * from sys.dm_os_memory_cache_clock_hands

  • T8004로 수집한 메모리 덤프 출력(필요한 경우). 이 미니덤프는 링 버퍼 및 spinlock/wait 상태와 같은 중요한 정보를 포함합니다. T8004에 대한 덤프 카운터는 서버를 다시 시작하지 않고도 T8004를 껐다 켜면 재설정할 수 있습니다.

만족스럽지 못한 쿼리 계획

이 시나리오에서는 만족스럽지 못한 쿼리 계획으로 인해 쿼리가 느리게 실행되는 것으로 의심합니다. 리소스 풀에 대한 낮은 메모리 제한 설정으로 인해 쿼리 최적화 프로그램이 충분한 메모리를 확보하지 못하는 경우 만족스럽지 못한 쿼리 계획이 생성될 수 있습니다.

다음 동작을 통해 최적의 리소스 풀 설정을 확인할 수 있습니다.

  • Query optimizations/sec 카운터의 데이터를 통해 작업 그룹의 쿼리 컴파일 수가 높은지 여부를 확인합니다.

  • Suboptimal plans/sec 카운터의 데이터를 통해 쿼리 최적화 프로그램이 만족스럽지 못한 계획을 생성하는 빈도가 높은지 확인합니다.

두 조건 중 하나에 해당할 경우 리소스 풀의 메모리 제한을 늘리는 것이 좋습니다.