실행 계획 및 DMV를 사용하여 쿼리 성능 평가

  • 10분

쿼리가 예상보다 느리게 실행되는 경우 첫 번째 단계는 데이터베이스 엔진이 쿼리를 실행하는 방법을 이해하는 것입니다. 실행 계획은 최적화 프로그램이 쿼리에 대해 선택한 정확한 연산자, 데이터 액세스 방법 및 리소스 비용을 보여 줍니다. DMV(동적 관리 뷰)는 데이터베이스의 모든 쿼리에 런타임 성능 데이터를 노출하여 단일 계획으로 전환하기 전에 가장 비싼 쿼리를 찾을 수 있도록 보완합니다.

실행 계획 읽기

실행 계획은 쿼리 최적화 프로그램에서 데이터를 검색하고 처리하기 위해 생성하는 명령 집합입니다. 먼저 액세스할 테이블, 인덱스 또는 검사 테이블을 사용할지 여부 및 결과를 조인, 필터링, 정렬 및 집계하는 방법을 정의합니다. 최적화 프로그램은 여러 후보 계획을 평가하고 예상 비용이 가장 낮은 계획을 선택합니다.

각 단계의 연산자, 데이터 흐름을 나타내는 화살표 및 비용 비율을 보여 주는 SQL Server Management Studio의 그래픽 실제 실행 계획의 스크린샷

실행 계획에는 두 가지 유형이 있습니다.

  • 예상 실행 계획: 쿼리를 실행하지 않고 생성됩니다. 통계를 기반으로 계획된 연산자 및 예상 행 수를 보여 줍니다. 데이터베이스에 영향을 주지 않고 빠른 분석을 위해 예상 계획을 사용합니다.
  • 실제 실행 계획: 쿼리 실행 중에 캡처됩니다. 여기에는 예상 계획과 실제 행 수, 실제 실행 시간, 메모리 부여 및 경고가 포함됩니다. 실제 계획은 최적화 프로그램이 예상한 것과 실제로 발생한 작업 간의 불일치를 보여줍니다.

예상 계획을 표시하려면 쿼리 전에 실행 SET SHOWPLAN_XML ON 하거나 SSMS(SQL Server Management Studio)에서 예상 실행 계획 표시 를 선택합니다. 실제 계획을 캡처하려면 쿼리를 실행하기 전에 SSMS에서 SET STATISTICS XML ON을 실행 하거나 선택합니다.

예상 계획과 실제 계획은 비슷하지만 실제 계획의 런타임 메트릭은 성능 문제를 진단하는 데 매우 중요합니다. 예를 들어 테이블 검색의 예상 행 수가 100이지만 실제 행 수가 10,000인 경우 잘못된 계획 선택으로 이어지는 오래된 통계를 나타낼 수 있습니다. 최적화 프로그램은 쿼리를 처음 접할 때 통계를 기반으로 계획을 컴파일합니다. 이러한 통계가 현재 데이터 분포를 반영하지 않으면 계획이 제대로 수행되지 않을 수 있습니다.

실행 계획의 일반적인 문제 식별

실행 계획은 왼쪽에서 오른쪽으로, 위에서 아래로 읽습니다. 첫 번째 연산자는 기본 테이블에 액세스하고 최종 연산자는 쿼리 결과를 생성합니다. 다음과 같은 일반적인 문제를 찾습니다.

연산자 유형 은 엔진이 데이터에 액세스하는 방법을 알려줍니다. 여러 연산자 형식이 있으며 각각 데이터를 검색하거나 처리하는 다른 방법을 나타냅니다. 예를 들어 Index Seek 연산자는 인덱 스 키를 사용하여 특정 행을 대상으로 하는 매우 효율적인 메서드를 나타냅니다. 반면 Table Scan 또는 Index Scan 연산자는 모든 행을 읽는 효율이 낮은 방법을 나타냅니다. 대용량 테이블에서 스캔이 일어난다면 인덱스 생성을 고려해야 합니다. 예를 들어, 전자 상거래 애플리케이션이 날짜별로 주문을 쿼리할 때, 계획에 Orders 테이블에 대한 클러스터형 인덱스 검색이 표시되는 경우, OrderDate 열에 비클러스터형 인덱스를 추가하면 해당 검색을 탐색으로 변경할 수 있습니다. 모든 검사가 나쁜 것은 아닙니다. 테이블이 작거나 검색 조건이 테이블에서 대부분의 행을 반환하는 경우 검색이 가장 효율적인 액세스 방법이 될 수 있습니다. 항상 쿼리의 컨텍스트와 데이터의 크기를 고려합니다. 데이터를 알고 실행 계획을 사용하여 액세스 방법이 적합한지 확인합니다.

예상 행 수와 실제 행 개수는 최적화 프로그램의 가정이 현실과 일치하는지 여부를 나타냅니다. 최적화 프로그램은 테이블의 데이터 분포 및 밀도를 설명하는 통계, 메타데이터에 대한 계획을 기반으로 합니다. 이러한 통계가 오래되면 예상 행 수와 실제 행 수가 달라집니다. 최적화 프로그램이 행 수를 과소 평가하면 메모리의 해시 테이블을 빠른 조회를 위해 구축하는 해시 조인 이 더 빠를 수 있고, 정렬 작업에 할당된 메모리가 부족하면 조인의 내부 테이블을 한 번에 한 행씩 처리하는 중첩된 루프 조인 을 선택할 수 있습니다. 중요한 데이터 변경 후 통계가 오래될 수 있으므로, UPDATE STATISTICS을 사용하여 통계를 업데이트하거나 자동 통계 업데이트를 사용 설정하면 옵티마이저가 더 나은 결정을 내리는 데 도움이 될 수 있습니다.

엔진이 비클러스터형 인덱스를 통해 행을 찾지만 클러스터형 인덱스에서 추가 열이 필요한 경우 키 조회 연산자가 나타납니다. 일치하는 모든 행에 대해 엔진은 클러스터형 인덱스에 대한 추가 왕복을 수행하여 해당 열을 검색합니다. 필터가 많은 행을 반환하는 경우 이러한 추가 조회가 빠르게 누적됩니다. 가령 전자상거래 애플리케이션이 CustomerID 기준으로 주문을 필터링한 뒤 OrderDate, TotalAmount, 및 ShippingAddress 항목을 조회하는데, 비클러스터형 인덱스 CustomerID 에 해당 컬럼들이 포함되어 있지 않다면, 쿼리 실행 계획상 매칭되는 주문마다 키 조회 작업이 발생하게 됩니다. 누락된 열을 인덱스에 포함된 열로 추가하여 키 조회를 제거할 수 있습니다. 포함된 열은 인덱스 크기를 증가하므로 쓰기 속도가 느려질 수 있으므로 쓰기 오버헤드에 대비하여 읽기 성능 이점을 평가합니다.

연산자 사이의 굵은 화살표는 연산자 간에 흐르는 행 수를 나타냅니다. 계획 초기 단계에서 굵은 화살표(왼쪽에서 오른쪽으로, 위에서 아래로 읽음)가 나타나는 것은 누락된 필터나 인덱스로 인해 과도한 행이 처리되고 있음을 의미하는 경우가 많습니다.

누락된 인덱스 제안은 SSMS의 그래픽 실행 계획 맨 위에 녹색으로 강조 표시된 텍스트로 표시됩니다. 최적화 프로그램에서 인덱스가 쿼리 비용을 크게 줄일 수 있음을 감지하면 계획에서 직접 권장 사항을 표시합니다. 제안을 마우스 오른쪽 단추로 클릭하고 누락된 인덱스 세부 정보를 선택하여 검토하고 실행할 수 있는 CREATE INDEX 문을 생성합니다. 이러한 제안은 실행 계획을 읽을 때 얻을 수 있는 가장 쉬운 승리 중 하나입니다.

경고는 영향을 받는 연산자에 느낌표(⚠)가 있는 노란색 삼각형으로 표시됩니다. 각 경고는 최적화 기회를 가리킵니다. 일반적인 경고는 다음과 같습니다.

  • 누락된 통계: 최적화 프로그램에서 열에 대한 통계를 찾을 수 없으므로 실제 데이터 분포를 사용하는 대신 행 수를 추측했습니다. 이 문제를 해결하려면 쿼리에 사용되는 열에 대한 통계를 만들거나 오래된 경우 기존 통계를 업데이트합니다.
  • 과도한 메모리 부여: 쿼리가 필요한 것보다 더 많은 메모리를 요청하여 다른 쿼리에서 사용할 수 있는 리소스를 낭비했습니다. 이 문제는 최적화 프로그램에서 행 수를 과대 과대 분석할 때 종종 발생합니다. 통계를 업데이트하거나 이전에 행을 필터링하도록 쿼리를 다시 작성하면 메모리 부여를 줄일 수 있습니다.
  • 조인 조건자 없음: 두 테이블이 적절한 조건 없이 조인되어 가능한 모든 행 조합을 반환하는 카티전 제품을 생성합니다. 쿼리에서 누락되거나 잘못된 ON 절이 있는지 확인합니다.
  • 암시적 변환: 데이터 형식이 일치하지 않으면 엔진이 런타임에 값을 변환하여 인덱스 검색을 검색으로 전환할 수 있습니다. 예를 들어 매개 변수 nvarchar를 열 varchar과 비교하는 절이 WHERE인 경우, 엔진은 비교하기 전에 열의 모든 행을 nvarchar로 변환합니다. 암시적 변환을 해결하려면 쿼리 매개 변수의 데이터 형식을 열 정의와 일치합니다.
  • 정렬 또는 해시 유출: 정렬 또는 해시 작업이 부여된 메모리가 부족하여 중간 결과를 tempdb로 유출했습니다. 이러한 작업은 검사 후 높은 CPU 사용률의 두 번째로 일반적인 요인입니다. 유출 경고가 표시되면 최적화 프로그램에서 행 수를 과소 평가하고 메모리를 너무 적게 요청했을 수 있습니다. UPDATE STATISTICS 테이블의 통계를 새로 고치거나 쿼리를 다시 작성하여 정렬 전에 행 수를 줄이면 분산이 제거되는 경우가 많습니다.

실행 계획은 쿼리 성능을 이해하는 강력한 도구입니다. 엔진이 쿼리를 실행하는 방법과 병목 현상이 있는 위치를 정확하게 보여 줍니다. 실행 계획을 효과적으로 읽는 방법을 학습하면 데이터베이스 쿼리에서 성능 문제를 신속하게 식별하고 해결할 수 있습니다.

런타임 성능 데이터에 대한 DMV 쿼리

DMV는 데이터베이스 엔진에서 실시간 및 누적된 성능 데이터를 노출합니다. Azure SQL Database에서 VIEW DATABASE STATE을(를) 쿼리하려면 해당 권한이 필요합니다. 실행 계획은 단일 쿼리를 실행하는 방법을 보여 주지만 DMV는 모든 쿼리에서 발생하는 작업을 보여 주며, 이는 가장 비싼 쿼리를 먼저 찾는 데 도움이 됩니다.

가장 비싼 쿼리 찾기

CPU 시간, 논리적 읽기 및 실행 수는 비용이 많이 드는 쿼리를 식별하는 가장 일반적인 메트릭입니다. 높은 CPU 시간 또는 논리적 읽기는 쿼리가 리소스를 많이 사용함을 나타내고, 실행 횟수가 높으면 보통 비용이 많이 드는 쿼리도 전체 성능에 큰 영향을 줄 수 있습니다. 먼저 평균 CPU 시간 또는 논리적 읽기별로 상위 쿼리를 검토하여 최적화할 후보를 찾습니다.

sys.dm_exec_query_stats 는 캐시된 쿼리 계획에 대한 집계 성능 통계를 반환합니다. sys.dm_exec_sql_text과(와) 연결하여 쿼리 텍스트를 확인하고 sys.dm_exec_query_plan을(를) 사용하여 실행 계획을 검색합니다.

다음 쿼리는 평균 CPU 시간별로 상위 10개 쿼리를 찾습니다.

SELECT TOP 10
    qs.total_worker_time / qs.execution_count AS avg_cpu_time,
    qs.execution_count,
    qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
    SUBSTRING(st.text, (qs.statement_start_offset / 2) + 1,
        ((CASE qs.statement_end_offset
            WHEN -1 THEN DATALENGTH(st.text)
            ELSE qs.statement_end_offset
        END - qs.statement_start_offset) / 2) + 1) AS query_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY avg_cpu_time DESC;

이 스크립트는 주의할 만한 쿼리를 식별하는 데 도움이 됩니다. 결과 집합의 크기가 상대적으로 avg_logical_reads 크다는 것은 종종 누락된 인덱스나 비효율적인 실행 계획을 의미합니다. 그러나 이러한 결과를 해석할 때는 주의해야 합니다. 하루에 한 번만 실행되는 평균 CPU 시간이 높은 쿼리는 시간당 수천 번 실행되는 보통 쿼리보다 덜 중요할 수 있습니다. 우선 순위를 지정할 때는 항상 평균 비용과 실행 수를 모두 고려합니다. 쿼리가 I/O에 집중되어 있는 경우를 찾기 위해 avg_logical_reads 기준으로 정렬할 수도 있습니다. 이러한 쿼리는 종종 누락된 인덱스나 비효율적인 액세스 방법을 나타냅니다.

현재 실행 중인 쿼리 확인

이전 쿼리는 계획 캐시 sys.dm_exec_requests 에서 가장 비용이 많이 드는 기록 쿼리를 보여 주지만 현재 실행 중인 모든 요청의 스냅샷을 제공합니다. 여기에는 CPU 시간, 읽기, 쓰기, 대기 유형, 대기 시간 및 차단 세션 ID에 대한 열이 포함됩니다. 이 보기를 사용하여 너무 많은 리소스를 사용하거나 잠금을 기다리는 동안 중단된 활성 쿼리를 발견할 수 있습니다. 이 보기(DMV: 동적 관리 보기)는 실시간 성능 모니터링 및 문제 해결을 위한 가장 중요한 요소 중 하나입니다.

SELECT
    r.session_id,
    r.status,
    r.command,
    r.wait_type,
    r.wait_time,
    r.blocking_session_id,
    r.cpu_time,
    r.logical_reads,
    t.text AS query_text
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.session_id > 50
ORDER BY r.cpu_time DESC;

이 쿼리는 CPU 시간별로 시스템 세션(세션 ID 1-50) 및 주문을 필터링합니다. I/O가 많은 쿼리를 찾기 위해 logical_reads에 따라 정렬할 수도 있습니다. wait_type 열은 wait_time 쿼리가 잠금, I/O 또는 기타 리소스에서 대기 중인지 여부를 식별하는 데 도움이 됩니다.

누락된 인덱스 검색

앞에서는 실행 계획에서 단일 쿼리에 대한 누락된 인덱스 제안을 표시하는 방법을 알아보았습니다. 누락된 인덱스 DMV는 최적화 프로그램이 존재하는 경우 모든 쿼리에서 사용할 인덱스를 보다 광범위하게 볼 수 있습니다. 이러한 보기는 여러 쿼리에 영향을 주는 최적화 기회를 찾는 좋은 방법입니다. sys.dm_db_missing_index_details 는 테이블, 같음 및 같지 않음 열과 포함된 열을 보여 줍니다. sys.dm_db_missing_index_group_stats 는 비용 절감을 예측하는 개선 측정값을 제공합니다.

SELECT
    mid.statement AS table_name,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns,
    migs.avg_total_user_cost * migs.avg_user_impact *
        (migs.user_seeks + migs.user_scans) AS improvement_measure
FROM sys.dm_db_missing_index_groups AS mig
INNER JOIN sys.dm_db_missing_index_group_stats AS migs
    ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid
    ON mig.index_handle = mid.index_handle
ORDER BY improvement_measure DESC;

이 쿼리는 누락된 각 인덱스 improvement_measure 권장 사항을 계산합니다. 이는 인덱스, 평균 백분율 향상 및 해당 쿼리가 실행된 횟수의 이점을 얻을 수 있는 쿼리의 평균 비용의 산물입니다. 이 측정값을 기준으로 정렬하면 먼저 만들 누락된 인덱스의 우선 순위를 지정할 수 있습니다. 그러나 이러한 결과는 현재 계획 캐시에 있는 쿼리를 기반으로 하는 권장 사항일 뿐입니다. 항상 제안된 인덱스 열을 검토하고 쿼리 성능과 쓰기 오버헤드 모두에 미치는 영향을 테스트한 후 프로덕션에 추가합니다.

비고

누락된 인덱스 권장 사항은 지시문이 아닌 제안입니다. 항상 새 인덱스가 쿼리 성능과 쓰기 오버헤드에 미치는 영향을 테스트한 후 프로덕션 환경에 추가합니다.

활성 세션 및 대기 작업 모니터링

sys.dm_exec_sessions 에서는 로그인 시간, 호스트 이름, 프로그램 이름, 누적 CPU 및 읽기를 포함하여 인증된 모든 세션에 대한 정보를 제공합니다. sys.dm_os_waiting_tasks 이를 결합하여 대기 중인 작업과 대기 중인 리소스를 확인합니다. 이러한 보기는 이후 단원에서 차단 및 리소스 경합을 진단할 때 필수적입니다.

전부 합치세요

실행 계획 및 DMV는 쿼리 동작의 전체 그림을 제공합니다. DMV로 시작하여 가장 비싼 쿼리를 식별합니다. 그런 다음 실행 계획을 자세히 조사하여 비용이 많이 드는 이유를 파악합니다. 누락된 인덱스로 인해 검사가 발생합니까? 오래된 통계로 인해 행 추정 오류가 발생합니까? 제거할 수 있는 키 조회입니까? 시스템 전체 보기에서 개별 쿼리 분석에 이르는 이 체계적인 접근 방식은 성능 병목 상태를 찾아서 해결하는 가장 효율적인 방법입니다.

주요 내용

실행 계획은 쿼리에 대한 최적화 프로그램의 전략을 표시하며 실제 계획에는 예상 행 수와 실제 행 수 간의 불일치를 노출하는 런타임 메트릭이 포함됩니다. 계획을 읽을 때 연산자 형식(탐색(Seek) 및 스캔(Scan)), 행 수 예상, 경고 및 키 조회 연산자에 집중합니다. DMV는 시스템 전반의 성능 데이터를 제공합니다. 가장 비싼 쿼리를 찾기 위해 sys.dm_exec_query_stats를 사용하고, 현재 실행 중인 쿼리를 찾기 위해 sys.dm_exec_requests를 사용하며, 최적화 기회를 위해 누락된 인덱스 DMV를 사용하십시오. DMV로 광범위하게 시작하여 주요 문제가 있는 위치를 파악한 다음, 개별 실행 계획을 세부 분석하여 이유를 이해합니다.