쿼리 저장소를 사용하여 성능 조정

적용 대상: SQL Server 2016(13.x) 이상 Azure SQL 데이터베이스 Azure SQL Managed Instance Azure Synapse Analytics

SQL Server 쿼리 저장소 기능은 SQL Server Management Studio 시각적 인터페이스와 Transact-SQL 쿼리를 통해 워크로드에서 쿼리를 검색하고 튜닝할 수 있는 기능을 제공합니다. 이 문서에서는 사용 통계 및 강제 계획을 기반으로 쿼리를 식별하는 방법을 비롯하여, 데이터베이스에서 쿼리 성능을 높이기 위해 유용한 정보를 얻는 방법을 자세히 설명합니다. 쿼리 저장소 힌트 기능을 사용하여 애플리케이션 코드를 변경하지 않고도 쿼리를 식별하고 쿼리 계획을 구체화할 수도 있습니다.

• 이 데이터를 수집하는 방법에 대한 자세한 내용은 쿼리 저장소 데이터 수집 방법을 참조하세요.
• 쿼리 저장소를 구성하고 관리하는 방법에 대한 자세한 내용은 쿼리 저장소를 사용하여 성능 모니터링을 참조하세요.
• Azure SQL Database에서 쿼리 저장소 운영하는 방법에 대한 자세한 내용은 Azure SQL 데이터베이스에서 쿼리 저장소 운영을 참조하세요.

성능 조정 샘플 쿼리

쿼리 저장소는 쿼리 실행 전체에서 컴파일 및 런타임 메트릭의 기록을 유지하므로, 사용자가 워크로드에 대한 정보를 조회할 수 있습니다.

다음 샘플 쿼리는 성능 기준 및 쿼리 성능 조사에 유용할 수 있습니다.

데이터베이스에서 실행된 마지막 쿼리

지난 1시간 내에 데이터베이스에서 실행된 마지막 n개 쿼리:

SELECT TOP 10 qt.query_sql_text,
    q.query_id,
    qt.query_text_id,
    p.plan_id,
    rs.last_execution_time
FROM sys.query_store_query_text AS qt
INNER JOIN sys.query_store_query AS q
    ON qt.query_text_id = q.query_text_id
INNER JOIN sys.query_store_plan AS p
    ON q.query_id = p.query_id
INNER JOIN sys.query_store_runtime_stats AS rs
    ON p.plan_id = rs.plan_id
WHERE rs.last_execution_time > DATEADD(HOUR, -1, GETUTCDATE())
ORDER BY rs.last_execution_time DESC;

실행 수

지난 1시간 이내의 각 쿼리의 실행 수:

SELECT q.query_id,
    qt.query_text_id,
    qt.query_sql_text,
    SUM(rs.count_executions) AS total_execution_count
FROM sys.query_store_query_text AS qt
INNER JOIN sys.query_store_query AS q
    ON qt.query_text_id = q.query_text_id
INNER JOIN sys.query_store_plan AS p
    ON q.query_id = p.query_id
INNER JOIN sys.query_store_runtime_stats AS rs
    ON p.plan_id = rs.plan_id
WHERE rs.last_execution_time > DATEADD(HOUR, -1, GETUTCDATE())
GROUP BY q.query_id,
    qt.query_text_id,
    qt.query_sql_text
ORDER BY total_execution_count DESC;

가장 긴 평균 실행 시간

지난 1시간 이내에 평균 기간이 가장 긴 쿼리 수:

SELECT TOP 10 ROUND(CONVERT(FLOAT, SUM(rs.avg_duration * rs.count_executions)) /
        NULLIF(SUM(rs.count_executions), 0), 2) avg_duration,
    SUM(rs.count_executions) AS total_execution_count,
    qt.query_sql_text,
    q.query_id,
    qt.query_text_id,
    p.plan_id,
    GETUTCDATE() AS CurrentUTCTime,
    MAX(rs.last_execution_time) AS last_execution_time
FROM sys.query_store_query_text AS qt
INNER JOIN sys.query_store_query AS q
    ON qt.query_text_id = q.query_text_id
INNER JOIN sys.query_store_plan AS p
    ON q.query_id = p.query_id
INNER JOIN sys.query_store_runtime_stats AS rs
    ON p.plan_id = rs.plan_id
WHERE rs.last_execution_time > DATEADD(HOUR, -1, GETUTCDATE())
GROUP BY qt.query_sql_text,
    q.query_id,
    qt.query_text_id,
    p.plan_id
ORDER BY avg_duration DESC;

가장 높은 평균 물리적 I/O 읽기

지난 24시간 동안 평균 물리적 I/O 읽기가 가장 큰 쿼리 수 및 해당하는 평균 행 개수와 실행 횟수:

SELECT TOP 10 rs.avg_physical_io_reads,
    qt.query_sql_text,
    q.query_id,
    qt.query_text_id,
    p.plan_id,
    rs.runtime_stats_id,
    rsi.start_time,
    rsi.end_time,
    rs.avg_rowcount,
    rs.count_executions
FROM sys.query_store_query_text AS qt
INNER JOIN sys.query_store_query AS q
    ON qt.query_text_id = q.query_text_id
INNER JOIN sys.query_store_plan AS p
    ON q.query_id = p.query_id
INNER JOIN sys.query_store_runtime_stats AS rs
    ON p.plan_id = rs.plan_id
INNER 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, -24, GETUTCDATE())
ORDER BY rs.avg_physical_io_reads DESC;

여러 계획을 사용하는 쿼리

둘 이상의 계획이 있는 쿼리는 계획 선택 변경으로 인해 성능이 저하될 수 있으므로, 특히 흥미롭습니다.

다음 쿼리는 지난 1시간 이내에 계획 수가 가장 많은 쿼리를 식별합니다.

SELECT q.query_id,
    object_name(object_id) AS ContainingObject,
    COUNT(*) AS QueryPlanCount,
    STRING_AGG(p.plan_id, ',') plan_ids,
    qt.query_sql_text
FROM sys.query_store_query_text AS qt
INNER JOIN sys.query_store_query AS q
    ON qt.query_text_id = q.query_text_id
INNER JOIN sys.query_store_plan AS p
    ON p.query_id = q.query_id
INNER JOIN sys.query_store_runtime_stats AS rs
    ON p.plan_id = rs.plan_id
WHERE rs.last_execution_time > DATEADD(HOUR, -1, GETUTCDATE())
GROUP BY OBJECT_NAME(object_id),
    q.query_id,
    qt.query_sql_text
HAVING COUNT(DISTINCT p.plan_id) > 1
ORDER BY QueryPlanCount DESC;

다음 쿼리는 지난 1시간 내의 모든 계획과 함께 이러한 쿼리를 식별합니다.

WITH Query_MultPlans
AS (
    SELECT COUNT(*) AS QueryPlanCount,
        q.query_id
    FROM sys.query_store_query_text AS qt
    INNER JOIN sys.query_store_query AS q
        ON qt.query_text_id = q.query_text_id
    INNER JOIN sys.query_store_plan AS p
        ON p.query_id = q.query_id
    GROUP BY q.query_id
    HAVING COUNT(DISTINCT plan_id) > 1
)
SELECT q.query_id,
    object_name(object_id) AS ContainingObject,
    query_sql_text,
    p.plan_id,
    p.query_plan AS plan_xml,
    p.last_compile_start_time,
    p.last_execution_time
FROM Query_MultPlans AS qm
INNER JOIN sys.query_store_query AS q
    ON qm.query_id = q.query_id
INNER JOIN sys.query_store_plan AS p
    ON q.query_id = p.query_id
INNER JOIN sys.query_store_query_text qt
    ON qt.query_text_id = q.query_text_id
INNER JOIN sys.query_store_runtime_stats AS rs
    ON p.plan_id = rs.plan_id
WHERE rs.last_execution_time > DATEADD(HOUR, -1, GETUTCDATE())
ORDER BY q.query_id,
    p.plan_id;

가장 긴 대기 기간

이 쿼리는 지난 1시간 동안 대기 기간이 가장 길었던 상위 10개 쿼리를 반환합니다.

SELECT TOP 10 qt.query_text_id,
    q.query_id,
    p.plan_id,
    sum(total_query_wait_time_ms) AS sum_total_wait_ms
FROM sys.query_store_wait_stats ws
INNER JOIN sys.query_store_plan p
    ON ws.plan_id = p.plan_id
INNER JOIN sys.query_store_query q
    ON p.query_id = q.query_id
INNER JOIN sys.query_store_query_text qt
    ON q.query_text_id = qt.query_text_id
INNER JOIN sys.query_store_runtime_stats AS rs
    ON p.plan_id = rs.plan_id
WHERE rs.last_execution_time > DATEADD(HOUR, -1, GETUTCDATE())
GROUP BY qt.query_text_id,
    q.query_id,
    p.plan_id
ORDER BY sum_total_wait_ms DESC;

참고 항목

Azure Synapse Analytics에서 이 섹션의 쿼리 저장소 샘플 쿼리는 Azure Synapse Analytics 쿼리 저장소 DMV에서 사용할 수 없는 대기 통계를 제외하고 지원됩니다.

최근에 성능이 저하된 쿼리

다음 쿼리 예제는 지난 48 시간에 계획 선택 변경으로 인해 실행 시간이 두 배가 된 모든 쿼리를 반환합니다. 이 쿼리는 모든 런타임 통계 간격을 나란히 비교합니다.

SELECT qt.query_sql_text,
    q.query_id,
    qt.query_text_id,
    rs1.runtime_stats_id AS runtime_stats_id_1,
    rsi1.start_time AS interval_1,
    p1.plan_id AS plan_1,
    rs1.avg_duration AS avg_duration_1,
    rs2.avg_duration AS avg_duration_2,
    p2.plan_id AS plan_2,
    rsi2.start_time AS interval_2,
    rs2.runtime_stats_id AS runtime_stats_id_2
FROM sys.query_store_query_text AS qt
INNER JOIN sys.query_store_query AS q
    ON qt.query_text_id = q.query_text_id
INNER JOIN sys.query_store_plan AS p1
    ON q.query_id = p1.query_id
INNER JOIN sys.query_store_runtime_stats AS rs1
    ON p1.plan_id = rs1.plan_id
INNER JOIN sys.query_store_runtime_stats_interval AS rsi1
    ON rsi1.runtime_stats_interval_id = rs1.runtime_stats_interval_id
INNER JOIN sys.query_store_plan AS p2
    ON q.query_id = p2.query_id
INNER JOIN sys.query_store_runtime_stats AS rs2
    ON p2.plan_id = rs2.plan_id
INNER JOIN sys.query_store_runtime_stats_interval AS rsi2
    ON rsi2.runtime_stats_interval_id = rs2.runtime_stats_interval_id
WHERE rsi1.start_time > DATEADD(hour, -48, GETUTCDATE())
    AND rsi2.start_time > rsi1.start_time
    AND p1.plan_id <> p2.plan_id
    AND rs2.avg_duration > 2 * rs1.avg_duration
ORDER BY q.query_id,
    rsi1.start_time,
    rsi2.start_time;

계획 선택 변경과 관련된 성능 저하뿐 아니라 성능 저하를 모두 확인하려면 이전 쿼리에서 AND p1.plan_id <> p2.plan_id 조건을 제거합니다.

성능에 기록 회귀가 있는 쿼리

최근 실행을 기록 실행과 비교하려는 경우, 다음 쿼리를 통해 실행 기간을 기준으로 쿼리 실행을 비교합니다. 이 특정 예제에서 쿼리는 최근 기간(1시간)과 기록 기간(마지막 날)의 실행을 비교하고 additional_duration_workload의 원인이 된 실행을 식별합니다. 이 메트릭은 최근 평균 실행과 기록 평균 실행 간의 차이와 최근 실행 수를 곱한 값으로 계산됩니다. 기록과 비교하여 이러한 최근 실행에서 나타난 추가 기간을 나타냅니다.

--- "Recent" workload - last 1 hour
DECLARE @recent_start_time DATETIMEOFFSET;
DECLARE @recent_end_time DATETIMEOFFSET;

SET @recent_start_time = DATEADD(hour, - 1, SYSUTCDATETIME());
SET @recent_end_time = SYSUTCDATETIME();

--- "History" workload
DECLARE @history_start_time DATETIMEOFFSET;
DECLARE @history_end_time DATETIMEOFFSET;

SET @history_start_time = DATEADD(hour, - 24, SYSUTCDATETIME());
SET @history_end_time = SYSUTCDATETIME();

WITH hist AS (
    SELECT p.query_id query_id,
        ROUND(ROUND(CONVERT(FLOAT, SUM(rs.avg_duration * rs.count_executions)) * 0.001, 2), 2) AS total_duration,
        SUM(rs.count_executions) AS count_executions,
        COUNT(DISTINCT p.plan_id) AS num_plans
    FROM sys.query_store_runtime_stats AS rs
    INNER JOIN sys.query_store_plan AS p
        ON p.plan_id = rs.plan_id
    WHERE (
        rs.first_execution_time >= @history_start_time
        AND rs.last_execution_time < @history_end_time
    )
    OR (
        rs.first_execution_time <= @history_start_time
        AND rs.last_execution_time > @history_start_time
    )
    OR (
        rs.first_execution_time <= @history_end_time
        AND rs.last_execution_time > @history_end_time
    )
    GROUP BY p.query_id
),
recent AS (
    SELECT p.query_id query_id,
        ROUND(ROUND(CONVERT(FLOAT, SUM(rs.avg_duration * rs.count_executions)) * 0.001, 2), 2) AS total_duration,
        SUM(rs.count_executions) AS count_executions,
        COUNT(DISTINCT p.plan_id) AS num_plans
    FROM sys.query_store_runtime_stats AS rs
    INNER JOIN sys.query_store_plan AS p
        ON p.plan_id = rs.plan_id
    WHERE (
        rs.first_execution_time >= @recent_start_time
        AND rs.last_execution_time < @recent_end_time
    )
    OR (
        rs.first_execution_time <= @recent_start_time
        AND rs.last_execution_time > @recent_start_time
    )
    OR (
        rs.first_execution_time <= @recent_end_time
        AND rs.last_execution_time > @recent_end_time
    )
    GROUP BY p.query_id
    )
SELECT results.query_id AS query_id,
    results.query_text AS query_text,
    results.additional_duration_workload AS additional_duration_workload,
    results.total_duration_recent AS total_duration_recent,
    results.total_duration_hist AS total_duration_hist,
    ISNULL(results.count_executions_recent, 0) AS count_executions_recent,
    ISNULL(results.count_executions_hist, 0) AS count_executions_hist
FROM (
    SELECT hist.query_id AS query_id,
        qt.query_sql_text AS query_text,
        ROUND(CONVERT(FLOAT, recent.total_duration / recent.count_executions - hist.total_duration / hist.count_executions) * (recent.count_executions), 2) AS additional_duration_workload,
        ROUND(recent.total_duration, 2) AS total_duration_recent,
        ROUND(hist.total_duration, 2) AS total_duration_hist,
        recent.count_executions AS count_executions_recent,
        hist.count_executions AS count_executions_hist
    FROM hist
    INNER JOIN recent
        ON hist.query_id = recent.query_id
    INNER JOIN sys.query_store_query AS q
        ON q.query_id = hist.query_id
    INNER JOIN sys.query_store_query_text AS qt
        ON q.query_text_id = qt.query_text_id
) AS results
WHERE additional_duration_workload > 0
ORDER BY additional_duration_workload DESC
OPTION (MERGE JOIN);

쿼리 성능 안정성 유지 관리

여러 번 실행되는 쿼리의 경우 SQL Server에서 다른 계획을 사용하여 리소스 사용률 및 기간이 달라짐을 알 수 있습니다. 쿼리 저장소를 사용하면 쿼리 성능이 회귀되는 시기를 감지하고 관심 기간 내에 최적의 계획을 결정할 수 있습니다. 그런 다음 향후 쿼리 실행을 위한 최적의 계획을 강제로 적용할 수 있습니다.

매개 변수가 있는 쿼리의 일관성 없는 쿼리 성능(자동 매개 변수화 또는 수동으로 매개 변수화됨)을 식별할 수도 있습니다. 여러 계획 중에 대부분의 매개 변수 값에 대해 빠르고 최적화된 계획을 식별하고 해당 계획을 강제 적용할 수 있습니다. 이를 통해 다양한 사용자 시나리오에 대해 예측 가능한 성능을 유지할 수 있습니다.

쿼리에 대한 계획 강제 적용(강제 적용 정책 적용)

특정 쿼리에 계획을 강제로 적용하면 SQL Server는 최적화 프로그램에서 계획을 강제로 적용하려고 시도합니다. 계획을 적용하는 데 실패하면 확장 이벤트가 발생하고, 최적화 프로그램이 일반적인 방법으로 최적화하도록 지시됩니다.

EXEC sp_query_store_force_plan @query_id = 48, @plan_id = 49;

sp_query_store_force_plan을 사용할 경우 쿼리 저장소에서 해당 쿼리에 대한 계획으로 기록된 계획만 강제로 적용할 수 있습니다. 즉, 쿼리에 사용할 수 있는 유일한 계획은 쿼리 저장소가 활성 상태인 동안 해당 쿼리를 실행하는 데 이미 사용된 계획뿐입니다.

참고 항목

쿼리 저장소에서 계획 강제 적용은 Azure Synapse Analytics에서 지원되지 않습니다.

계획에서 빠른 전달 및 정적 커서에 대한 강제 적용 지원

SQL Server 2019(15.x) 이상 버전 및 Azure SQL 데이터베이스(모든 배포 모델)에서 쿼리 저장소는 빠른 전달 및 정적 Transact-SQL 및 API 커서에 대한 쿼리 실행 계획을 강제 적용하는 기능을 지원합니다. 강제 적용은 sp_query_store_force_plan 또는 SQL Server Management Studio 쿼리 저장소 보고서를 통해 지원됩니다.

쿼리에 대한 계획 강제 적용 제거

SQL Server 쿼리 최적화 프로그램에서 최적의 쿼리 계획을 다시 계산하려면 sp_query_store_unforce_plan을 사용하여 쿼리에 대해 선택한 계획을 강제 해제합니다.

EXEC sp_query_store_unforce_plan @query_id = 48, @plan_id = 49;

관련 콘텐츠

• 쿼리 저장소를 사용한 성능 모니터링
• 쿼리 저장소에 대한 모범 사례
• 메모리 내 OLTP와 쿼리 저장소 사용
• 쿼리 저장소 사용 시나리오
• 쿼리 저장소에서 데이터를 수집하는 방법
• 쿼리 저장소 저장 프로시저(Transact-SQL)
• 쿼리 저장소 카탈로그 뷰(Transact-SQL)
• 작업 모니터 열기(SQL Server Management Studio)
• 활성 쿼리 통계
• 작업 모니터
• sys.database_query_store_options(Transact-SQL)
• 성능 모니터링 및 튜닝
• 성능 모니터링 및 튜닝 도구
• 쿼리 저장소 힌트
• 데이터베이스 엔진 튜닝 관리자로 쿼리 저장소에서 워크로드를 사용하여 데이터베이스 튜닝