리소스 관리자 문제 해결

분류에 사용할 분류자 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를 사용하면 잘못 작성된 분류자 함수의 문제를 해결할 수 있습니다. 자세한 내용은 전용 관리자 연결 사용을 참조하십시오.

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

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

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

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

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

가장 먼저 해야 할 일은 사용자가 포함된 그룹에 대해 요청 수 스로틀이 명시적으로 구성되었는지 여부를 확인하는 것입니다. 이렇게 하려면 사용자의 그룹 멤버 자격에서 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 제한(REQUEST_MAX_CPU_TIME_SEC)이 너무 낮지 않은지 확인해야 합니다.

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

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

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

이 시나리오에서는 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를 쿼리하여 부여된 총 메모리와 대상을 확인합니다.

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

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

기본적인 문제 해결

고급 문제 해결

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

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

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

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

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