쿼리 저장소를 사용하여 성능 조정
적용 대상:
SQL Server 2016(13.x) 이상 Azure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics
SQL Server 쿼리 저장소 기능은 SQL Server Management Studio 시각적 인터페이스 및 T-SQL 쿼리를 통해 워크로드에서 쿼리를 검색하고 튜닝할 수 있는 기능을 제공합니다. 이 문서에서는 사용 통계 및 강제 계획을 기반으로 쿼리를 식별하는 방법을 포함하여 데이터베이스에서 쿼리 성능을 향상시키기 위해 실행 가능한 정보를 취할 수 있는 방법을 자세히 설명합니다. 쿼리 저장소 힌트 기능을 사용하여 애플리케이션 코드를 변경하지 않고 쿼리를 식별하고 쿼리 계획을 셰이프할 수도 있습니다.
- 이 데이터를 수집하는 방법에 대한 자세한 내용은 쿼리 저장소에서 데이터를 수집하는 방법을 참조하세요.
- 쿼리 저장소를 구성하고 관리하는 방법에 대한 자세한 내용은 쿼리 저장소를 사용하여 성능 모니터링을 참조하세요.
- Azure SQL Database에서 쿼리 저장소를 운영하는 방법에 대한 자세한 내용은 Azure SQL Database에서 쿼리 저장소 운영을 참조 하세요.
성능 튜닝 샘플 쿼리
쿼리 저장소는 쿼리 실행 전체에서 컴파일 및 런타임 메트릭의 기록을 유지하므로 워크로드에 대한 질문을 할 수 있습니다.
다음 샘플 쿼리는 성능 기준 및 쿼리 성능 조사에 유용할 수 있습니다.
데이터베이스에서 실행된 마지막 쿼리
데이터베이스에서 실행된 마지막 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
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 p.plan_id = rs.plan_id
ORDER BY rs.last_execution_time DESC;
실행 수
각 쿼리에 대한 실행 수:
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
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 p.plan_id = rs.plan_id
GROUP BY q.query_id, qt.query_text_id, qt.query_sql_text
ORDER BY total_execution_count DESC;
가장 긴 평균 실행 시간
지난 1시간 이내에 평균 실행 시간이 가장 긴 쿼리 수:
SELECT TOP 10 rs.avg_duration, qt.query_sql_text, q.query_id,
qt.query_text_id, p.plan_id, GETUTCDATE() AS CurrentUTCTime,
rs.last_execution_time
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 p.plan_id = rs.plan_id
WHERE rs.last_execution_time > DATEADD(hour, -1, GETUTCDATE())
ORDER BY rs.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
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 p.plan_id = rs.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, -24, GETUTCDATE())
ORDER BY rs.avg_physical_io_reads DESC;
여러 계획을 사용하는 쿼리
이러한 쿼리는 계획 선택 변경으로 인한 회귀 후보이므로 특히 흥미롭습니다. 다음 쿼리는 모든 계획과 함께 이러한 쿼리를 식별합니다.
WITH Query_MultPlans
AS
(
SELECT COUNT(*) AS cnt, q.query_id
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 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, plan_id, p.query_plan AS plan_xml,
p.last_compile_start_time, p.last_execution_time
FROM Query_MultPlans AS qm
JOIN sys.query_store_query AS q
ON qm.query_id = q.query_id
JOIN sys.query_store_plan AS p
ON q.query_id = p.query_id
JOIN sys.query_store_query_text qt
ON qt.query_text_id = q.query_text_id
ORDER BY query_id, plan_id;
가장 높은 대기 기간
이 쿼리는 대기 기간이 가장 긴 상위 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
JOIN sys.query_store_plan p ON ws.plan_id = p.plan_id
JOIN sys.query_store_query q ON p.query_id = q.query_id
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
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
JOIN sys.query_store_query AS q
ON qt.query_text_id = q.query_text_id
JOIN sys.query_store_plan AS p1
ON q.query_id = p1.query_id
JOIN sys.query_store_runtime_stats AS rs1
ON p1.plan_id = rs1.plan_id
JOIN sys.query_store_runtime_stats_interval AS rsi1
ON rsi1.runtime_stats_interval_id = rs1.runtime_stats_interval_id
JOIN sys.query_store_plan AS p2
ON q.query_id = p2.query_id
JOIN sys.query_store_runtime_stats AS rs2
ON p2.plan_id = rs2.plan_id
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
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
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
JOIN recent
ON hist.query_id = recent.query_id
JOIN sys.query_store_query AS q
ON q.query_id = hist.query_id
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는 최적화 프로그램에서 계획을 강제로 적용하려고 합니다. 계획을 적용하는 데 실패하면 XEvent가 발생하고, 최적화 프로그램이 일반적인 방법으로 최적화하도록 지시됩니다.
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 Database(모든 배포 모델)부터 쿼리 저장소는 빠른 전달 및 정적 Transact-SQL 및 API 커서에 대한 쿼리 실행 계획을 강제 적용하는 기능을 지원합니다. 강제는 SQL Server Management Studio 쿼리 저장소 보고서를 통해 sp_query_store_force_plan 또는 통해 지원됩니다.
쿼리에 대한 계획 강제 적용 제거
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)
다음 단계
피드백
출시 예정: 2024년 내내 콘텐츠에 대한 피드백 메커니즘으로 GitHub 문제를 단계적으로 폐지하고 이를 새로운 피드백 시스템으로 바꿀 예정입니다. 자세한 내용은 다음을 참조하세요. https://aka.ms/ContentUserFeedback
다음에 대한 사용자 의견 제출 및 보기