SQL Server 및 Azure SQL Managed Instance에서 쿼리 가능한 유형의 쿼리 성능 병목 현상

아티클
03/21/2024

적용 대상: SQL Server Azure SQL Managed Instance

성능 병목 상태를 해결하려는 경우, 우선 쿼리가 실행 중 상태 또는 대기 상태일 때 병목 현상이 발생하는지 확인합니다. 확인 내용에 따라 어떤 해결 방법이 적용할지가 달라집니다. 문제 및 각 문제 형식에 관련된 해결 방법은 이 문서에 설명되어 있습니다.

SQL Server DMV를 사용하여 이러한 유형의 성능 병목 현상을 검색할 수 있습니다.

실행 관련 문제: 실행 관련 문제는 일반적으로 컴파일 문제와 관련이 있으며, 만족스럽지 못한 쿼리 계획 또는 부족하거나 남용된 리소스와 관련된 실행 문제를 일으킵니다. 대기 관련 문제: 대기 관련 문제는 일반적으로 다음과 관련이 있습니다.

잠금 (차단)
I/O
tempdb 사용량과 관련된 경합
메모리 부여 대기

이 문서에서는 SQL Server 및 Azure SQL Managed Instance에 대해 설명하며 Azure SQL Database에서 검색 가능한 유형의 쿼리 성능 병목 현상도 참조하세요.

만족스럽지 못한 쿼리 계획을 발생시키는 컴파일 문제

SQL 쿼리 최적화 프로그램에서 생성한 차선 플랜은 쿼리 성능을 저하할 수도 있습니다. SQL 쿼리 최적화 프로그램은 누락된 인덱스, 부실 통계, 처리할 행 수에 대한 잘못된 예상치 또는 필요한 메모리에 대한 부정확한 예상치로 인해 만족스럽지 못한 계획을 생성할 수 있습니다. 이전 인스턴스 또는 다른 인스턴스에서 쿼리가 더 빠르게 실행된 경우, 실제 실행 계획을 비교하여 차이가 있는지 확인합니다.

DMV 및 쿼리 실행 계획에서 누락된 인덱스를 사용하여 누락된 인덱스를 식별합니다. 이 문서에서는 누락된 인덱스 요청을 사용하여 비클러스터형 인덱스를 검색하고 조정하는 방법을 보여 줍니다.
더 나은 계획을 가져오려면 통계를 업데이트하거나 인덱스를 다시 빌드해 보세요. 이런 문제를 자동으로 완화할 수 있도록 데이터베이스에서 자동 계획 수정 기능을 사용하세요.
고급 문제 해결 단계로 쿼리 저장소 힌트를 사용하여 코드를 변경하지 않고 쿼리 저장소를 사용하여 쿼리 힌트를 적용합니다.
데이터베이스 호환성 수준을 변경하고 지능형 쿼리 처리를 구현해보세요. SQL 쿼리 최적화 프로그램은 데이터베이스의 호환성 수준에 따라 다른 쿼리 계획을 만들 수 있습니다. 호환성 수준이 높을수록 더 뛰어난 지능형 쿼리 처리 기능을 제공합니다.

성능 개선을 위해 애플리케이션 및 데이터베이스 튜닝 문서에 나오는 쿼리 튜닝 및 힌트 예제에서는 매개 변수가 있는 쿼리로 초래된 만족스럽지 못한 쿼리 계획의 영향, 해당 조건을 검색하는 방법, 쿼리 힌트를 사용하여 해결하는 방법을 보여줍니다.

쿼리 처리에 대한 자세한 내용은 쿼리 처리 아키텍처 가이드를 참조하세요.
데이터베이스 호환성 수준을 변경하고 호환성 수준 간 차이점에 대해 자세히 알아보려면 ALTER DATABASE를 참조하세요.
카디널리티 예측에 대한 자세한 내용은 카디널리티 예측을 참조하세요.

최적이 아닌 쿼리 실행 계획으로 쿼리 해결

다음 섹션에는 차선 쿼리 실행 계획을 사용한 쿼리 해결 방법이 나와 있습니다.

PSP(매개 변수에 민감한 계획) 문제가 있는 쿼리

쿼리 최적화 도구가 특정 매개 변수 값(또는 값 집합)에만 최적의 쿼리 실행 계획을 생성하고 캐시된 계획이 연속 실행에 사용되는 매개 변수 값에 최적이 아닌 경우 PSP(매개 변수 민감성 계획) 문제가 발생합니다. 최적이 아닌 계획은 쿼리 성능 문제를 일으키고 전체 워크로드 처리량을 줄일 수 있습니다.

매개 변수 스니핑 및 쿼리 처리에 대한 자세한 내용은 쿼리 처리 아키텍처 가이드를 참조하세요.

PSP 문제를 완화할 몇 가지 해결 방법이 있습니다. 각 해결 방법에는 다음과 같은 장단점이 있습니다.

SQL Server 2022(16.x)에 도입된 새로운 기능은 매개 변수 중요도로 인해 발생하는 가장 최적화되지 않은 쿼리 계획을 완화하려는 매개 변수 중요한 계획 최적화입니다. 데이터베이스 호환성 수준 160에서 사용하도록 설정됩니다.
각 쿼리 실행에서 RECOMPILE 쿼리 힌트를 사용합니다. 이 해결 방법에서는 계획 품질을 높이는 대신, 컴파일 시간 및 CPU 사용량이 증가합니다. RECOMPILE 옵션은 종종 처리량이 높은 워크로드에서 사용할 수 없습니다.
실제 매개 변숫값을 대부분의 매개 변숫값에 충분히 적합한 계획을 생성하는 일반적인 매개 변숫값으로 재정의하려면 OPTION(OPTIMIZE FOR…) 쿼리 힌트를 사용합니다. 이 옵션을 사용하려면 최적의 매개 변수 값 및 관련 계획 특성을 잘 이해해야 합니다.
OPTION(OPTIMIZE FOR UNKNOWN) 쿼리 힌트를 사용하여 실제 매개 변수 값을 재정의하고 대신 밀도 벡터 평균을 사용합니다. 또한 수신 매개 변수 값을 지역 변수에서 캡처하고 매개 변수 자체를 사용하는 대신 조건자 내 지역 변수를 사용하여 이 작업을 수행할 수 있습니다. 이 수정 사항에서는 평균 밀도가 충분해야 합니다.
DISABLE_PARAMETER_SNIFFING 쿼리 힌트를 사용하여 매개 변수 스니핑을 완전히 사용하지 않도록 설정합니다.
KEEPFIXEDPLAN 쿼리 힌트를 사용하여 캐시에서 다시 컴파일이 발생하지 않도록 합니다. 이 해결 방법은 충분히 좋은 일반 계획이 캐시에 이미 있다고 가정합니다. 또한 자동 통계 업데이트를 사용하지 않도록 설정하여 좋은 계획이 제거되고 새로운 잘못된 계획이 컴파일될 가능성을 줄일 수 있습니다.
쿼리를 다시 작성하고 쿼리 텍스트에 힌트를 추가하여 사용 계획 쿼리 힌트를 명시적으로 사용함으로써 계획을 강제로 적용합니다. 또는 쿼리 저장소를 사용하거나 자동 튜닝을 사용하여 특정 계획을 설정합니다.
단일 프로시저를 조건부 논리 및 연결된 매개 변수 값에 따라 각각 사용할 수 있는 중첩된 프로시저 집합으로 바꿉니다.
정적 프로시저 정의에 대한 동적 문자열 실행 대안을 생성합니다.

쿼리 힌트를 적용하려면 쿼리를 수정하거나 쿼리 저장소 힌트를 사용하여 코드를 변경하지 않고 힌트를 적용합니다. SQL Server 2022 이전 버전에서는 계획 지침을 사용합니다.

PSP 문제 해결에 대한 자세한 내용은 다음 블로그 게시물을 참조하세요.

잘못된 매개 변수화로 인한 컴파일 작업

쿼리가 리터럴을 포함하는 경우 데이터베이스 엔진이 명령문을 자동으로 매개 변수화하거나 사용자가 명령문을 명시적으로 매개 변수화하여 컴파일 수를 줄입니다. 패턴이 같지만 다른 리터럴 값을 사용하는 쿼리를 여러 번 컴파일하면 CPU 사용량이 많아질 수 있습니다. 마찬가지로, 리터럴을 계속 포함하는 쿼리를 부분적으로만 매개 변수화해도 데이터베이스 엔진이 추가로 쿼리를 매개 변수화하지는 않습니다.

다음은 부분적으로 매개 변수가 있는 쿼리의 예제입니다.

SELECT *
FROM t1 JOIN t2 ON t1.c1 = t2.c1
WHERE t1.c1 = @p1 AND t2.c2 = '961C3970-0E54-4E8E-82B6-5545BE897F8F';

이 예제에서 t1.c1은 @p1을 사용하지만 t2.c2는 GUID를 리터럴로 계속 사용합니다. 이 경우 c2에 대한 값을 변경하면 쿼리가 다른 쿼리로 처리되고 새 컴파일이 수행됩니다. 이 예제에서 컴파일을 줄이려면 GUID도 매개 변수화합니다.

다음 쿼리는 쿼리가 적절히 매개 변수화되었는지를 확인하기 위한 쿼리 해시별 쿼리 수를 보여 줍니다.

SELECT TOP 10
  q.query_hash
  , count (distinct p.query_id ) AS number_of_distinct_query_ids
  , min(qt.query_sql_text) AS sampled_query_text
FROM sys.query_store_query_text AS qt
  JOIN sys.query_store_query AS q
     ON qt.query_text_id = q.query_text_id
  JOIN sys.query_store_plan AS p
     ON q.query_id = p.query_id
  JOIN sys.query_store_runtime_stats AS rs
     ON rs.plan_id = p.plan_id
  JOIN sys.query_store_runtime_stats_interval AS rsi
     ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id
WHERE
  rsi.start_time >= DATEADD(hour, -2, GETUTCDATE())
  AND query_parameterization_type_desc IN ('User', 'None')
GROUP BY q.query_hash
ORDER BY count (distinct p.query_id) DESC;

쿼리 계획 변경에 영향을 주는 요소

쿼리 실행 계획을 다시 컴파일하면 원래 캐시된 계획과 다른 생성된 쿼리 계획이 생성될 수 있습니다. 다음과 같은 다양한 이유로 원래 계획이 자동으로 다시 컴파일될 수 있습니다.

스키마의 변경 내용이 쿼리에서 참조됨
테이블에 대한 데이터 변경 내용이 쿼리에서 참조됨
쿼리 컨텍스트 옵션이 변경됨

컴파일된 계획은 다음과 같은 다양한 이유로 캐시에서 제거될 수 있습니다.

인스턴스 다시 시작
데이터베이스 범위 구성 변경
메모리 압력
캐시를 지우는 명시적 요청

RECOMPILE 힌트를 사용하는 경우 계획이 캐시되지 않습니다.

다시 컴파일하면(또는 캐시 제거 후 새로 컴파일하면) 원래 계획과 동일한 쿼리 실행 계획이 여전히 생성될 수 있습니다. 계획이 이전 또는 원래 계획에서 변경되면 다음과 같은 설명이 적용될 수 있습니다.

변경된 실제 디자인: 예를 들어 새로 만든 인덱스는 쿼리 요구 사항을 보다 효과적으로 다룹니다. 새 인덱스는 쿼리 최적화 프로그램에서 쿼리 실행의 첫 번째 버전을 위해 원래 선택한 데이터 구조를 사용하는 것보다 새 인덱스를 사용하는 것이 더 적합하다고 판단하는 경우 새 컴파일에서 사용될 수 있습니다. 참조된 개체에 대한 물리적 변경으로 인해 컴파일 시간에 새 계획이 선택될 수 있습니다.
서버 리소스 차이: 한 시스템의 계획이 다른 시스템의 계획과 다른 경우 사용 가능한 프로세서 수와 같은 리소스 가용성이 생성되는 계획에 영향을 줄 수 있습니다. 예를 들어 한 시스템에 더 많은 프로세서가 있는 경우 병렬 계획을 선택할 수 있습니다. 병렬 처리에 대한 자세한 내용은 최대 병렬 처리 수준 서버 구성 옵션 구성을 참조하세요.
다른 통계: 참조된 개체와 관련된 통계가 변경되었거나 원래 시스템의 통계와 실질적으로 다를 수 있습니다. 통계가 변경되고 다시 컴파일되는 경우 쿼리 최적화 프로그램은 변경된 시점부터 통계를 사용합니다. 수정된 통계의 데이터 분포 및 빈도는 원래 컴파일과 다를 수 있습니다. 이러한 변경 내용은 카디널리티 예상치를 생성하는 데 사용됩니다. (카디널리티 예상치는 논리 쿼리 트리를 통과해야 하는 행의 수입니다.) 카디널리티 예상치를 변경하면 다른 물리 연산자 및 관련 작업 순서를 선택할 수 있습니다. 통계를 약간만 변경해도 쿼리 실행 계획이 변경될 수 있습니다.
변경된 데이터베이스 호환성 수준 또는 카디널리티 예상 도구 버전: 데이터베이스 호환성 수준을 변경하면 다른 쿼리 실행 계획에서 나타날 수 있는 새로운 전략과 기능을 사용할 수 있습니다. 데이터베이스 호환성 수준 외에도 사용 또는 사용하지 않도록 설정된 추적 플래그 4199 또는 데이터베이스 범위 구성 QUERY_OPTIMIZER_HOTFIXES의 변경된 상태가 컴파일 시간에 쿼리 실행 계획 선택에 영향을 줄 수도 있습니다. 추적 플래그 9481(강제 레거시 CE) 및 2312(강제 기본 CE)도 계획에 영향을 줍니다.

Azure SQL Managed Instance의 리소스 제한 문제

만족스럽지 못한 쿼리 계획 및 누락된 인덱스와 관련되지 않은 느린 쿼리 성능은 일반적으로 리소스 부족이나 과용과 관련이 있습니다. 쿼리 계획이 최적이면 쿼리(및 데이터베이스)가 관리형 인스턴스의 리소스 한도에 도달하는 것일 수 있습니다. 예를 들면 서비스 수준을 위한 과도한 로그 쓰기 처리량이 있을 수 있습니다.

sys.dm_db_resource_stats DMV는 데이터베이스에 대한 CPU, I/O 및 메모리 사용량을 반환합니다. 데이터베이스에서 활동이 없더라도 15초 간격으로 하나의 행이 존재합니다. 기록 데이터는 한 시간 동안 유지됩니다.
sys.server_resource_stats DMV에서는 Azure SQL Managed Instance에 대한 CPU 사용량 및 스토리지 데이터를 반환합니다. 데이터는 15초 간격으로 수집 및 집계됩니다.
점증적으로 CPU 사용량이 높은 여러 개별 쿼리

문제 원인이 리소스 부족으로 확인되면 리소스를 업그레이드하여 CPU 요구 사항을 충족하도록 데이터베이스의 용량을 늘릴 수 있습니다. 관리형 인스턴스 크기 조정에 대한 자세한 내용은 서비스 계층 리소스 한도를 참조하세요.

워크로드 볼륨이 증가하여 발생하는 성능 문제

애플리케이션 트래픽과 워크로드 볼륨이 증가하면 CPU 사용량이 증가할 수 있습니다. 그러나 이 문제를 제대로 진단하려면 신중해야 합니다. 높은 CPU 문제가 표시되면 다음 질문에 대답하여 워크로드 볼륨의 변경으로 증가했는지를 확인합니다.

애플리케이션의 쿼리가 높은 CPU 문제의 원인이 되나요?
식별할 수 있는 상위 CPU 사용 쿼리의 경우:
- 여러 실행 계획이 동일한 쿼리와 연결되었나요? 그렇다면 이유는 무엇인가요?
- 동일한 실행 계획을 사용하는 쿼리의 경우 실행 시간이 일관되나요? 실행 수가 증가했나요? 그렇다면 워크로드가 증가할 때 성능 문제가 발생할 수 있습니다.

즉, 쿼리 실행 계획은 다르게 실행되지만, CPU 사용량은 실행 수에 따라 증가할 경우 성능 문제는 워크로드 증가와 관련이 있을 수 있습니다.

CPU 문제를 유발하는 워크로드 볼륨 변경을 식별하기란 항상 쉽지많은 않습니다. 다음 항목을 고려합니다.

변경된 리소스 사용량: 예를 들어 CPU 사용량이 장기간 80%까지 증가한 시나리오를 고려합니다. CPU 사용량만으로 워크로드 볼륨이 변경되었다고 판단할 수 없습니다. 애플리케이션이 정확히 동일한 워크로드를 실행하더라도 쿼리 실행 계획의 회귀 및 데이터 분포의 변경이 리소스 사용량 증가에 영향을 줄 수 있습니다.
새 쿼리의 모양: 애플리케이션이 다른 시간에 새 쿼리 집합을 유도할 수 있습니다.
요청 수 증가 또는 감소: 이 시나리오는 워크로드를 가장 확실하게 측정할 수 있는 상황입니다. 쿼리 수가 항상 더 많은 리소스 사용률에 대응하는 것은 아닙니다. 그러나 다른 요인이 변경되지 않는다고 가정할 때 이 메트릭은 여전히 중요한 신호가 됩니다.
병렬 처리: 과도한 병렬 처리는 다른 CPU 및 작업자 스레드 리소스 쿼리의 리소스를 사용함으로써 다른 동시 워크로드 성능을 저해할 수 있습니다. 병렬 처리에 대한 자세한 내용은 최대 병렬 처리 수준 서버 구성 옵션 구성을 참조하세요.

실행 문제와 관련된 만족스럽지 못한 계획 및 대기 관련 문제를 제거한 후에 성능 문제는 일반적으로 쿼리가 일부 리소스를 기다리고 있기 때문에 발생할 수 있습니다. 대기 관련 문제의 원인은 다음과 같을 수 있습니다.

차단:

한 쿼리는 데이터베이스의 개체 잠금을 유지할 수 있지만, 다른 쿼리는 동일한 개체에 액세스하려고 합니다. DMV를 사용하여 차단 쿼리를 식별할 수 있습니다. 자세한 내용은 차단 문제 이해 및 해결을 참조하세요.
IO 문제

쿼리가 데이터 또는 로그 파일에 페이지가 기록될 때까지 대기하고 있을 수 있습니다. 이 경우 DMV에서 INSTANCE_LOG_RATE_GOVERNOR, WRITE_LOG 또는 PAGEIOLATCH_* 대기 통계를 확인합니다. DMV를 사용하여 IO 성능 문제 식별을 참조하세요.
TempDB 문제

워크로드가 임시 테이블을 사용하거나 계획에 tempdb 스필이 있는 경우 쿼리에 tempdb 처리량 문제가 있을 수 있습니다. 더 자세히 조사하려면 tempdb 문제 식별을 검토합니다.
메모리 관련 문제

워크로드에 충분한 메모리가 없는 경우 페이지 예상 수명이 줄어들거나 쿼리에 필요한 것보다 메모리가 작게 확보될 수 있습니다. 때에 따라 쿼리 최적화 프로그램에서 기본 제공되는 인텔리전스를 통해 메모리 관련 문제를 해결할 수 있습니다. DMV를 사용하여 메모리 부여 문제 파악을 참조하세요. 메모리 부족 오류가 발생하면 sys.dm_os_out_of_memory_events를 검토합니다. vCore에 대한 메모리 비율이 더 높은 Azure SQL Managed Instance 하드웨어의 메모리 최적화 프리미엄 시리즈 계층도 고려합니다.

상위 대기 범주를 표시하는 메서드

이러한 메서드는 일반적으로 대기 유형의 상위 범주를 표시하는 데 사용됩니다.

쿼리 저장소를 사용하여 시간에 따른 각 쿼리의 대기 통계를 찾을 수 있습니다. 쿼리 저장소에서 대기 유형은 대기 범주에 결합됩니다. 대기 유형에 대한 대기 범주의 매핑은 sys.query_store_wait_stats에서 찾을 수 있습니다.
sys.dm_os_wait_stats를 사용하여 쿼리 작업 중에 실행된 스레드로 인해 발생한 모든 대기 관련 정보를 반환합니다. 이 집계 보기를 사용하여 Azure SQL Managed Instance 또는 SQL Server 인스턴스의 성능 문제를 진단할 수 있습니다. 쿼리는 리소스, 큐 대기 또는 외부 대기를 기다릴 수 있습니다.
sys.dm_os_waiting_tasks를 사용하여 일부 리소스에서 대기 중인 작업의 큐 관련 정보를 반환합니다.

높은 CPU 시나리오에서 쿼리 저장소 및 대기 통계는 다음 경우에 CPU 사용량을 반영하지 않을 수 있습니다.

높은 CPU 소비 쿼리가 아직 실행되고 있습니다.
장애 조치(failover)가 발생했을 때 CPU 사용량이 많은 쿼리가 실행 중인 경우.

쿼리 저장소를 추적하는 DMV 및 대기 통계에서 성공적으로 완료된 쿼리 및 제한 시간을 초과한 쿼리에 대한 결과만 표시하는 경우. 문이 완료될 때까지 현재 실행 중인 문의 데이터를 표시하지 않습니다. 동적 관리 뷰 sys.dm_exec_requests를 사용하여 현재 실행 중인 쿼리와 관련된 작업자 시간을 추적합니다.

팁

추가 도구:

다음을 통해 공유