-- <<관련 Command >>
-- Query Store 설정 확인
SELECT actual_state, actual_state_desc, readonly_reason, current_storage_size_mb, max_storage_size_mb
FROM sys.database_query_store_options;
-- 실행 계획 확인
SELECT Txt.query_text_id, Txt.query_sql_text, Pl.plan_id, Qry.*
FROM sys.query_store_plan AS Pl JOIN sys.query_store_query AS Qry ON Pl.query_id = Qry.query_id
JOIN sys.query_store_query_text AS Txt ON Qry.query_text_id = Txt.query_text_id ;
-- Query Store 데이터의 메모리 내 부분을 디스크로 플러시합니다.
EXEC sp_query_store_flush_db;
-- 쿼리를 삭제하고 쿼리 저장소에서 관련된 모든 계획과 런타임 통계를 제거합니다. ( Clears the runtime stats for a specific query plan from the query store )
EXEC sp_query_store_remove_query 3;
-- 쿼리 저장소에서 실행계획에 대한 실행 통계 삭제 ( Clears the runtime stats for a specific query plan from the query store )
EXEC sp_query_store_reset_exec_stats 3;
-- 쿼리 저장소에서 단일 계획을 제거합니다.
EXEC sp_query_store_remove_plan 3;
-- 특정 쿼리에 대한 특정 계획을 강제 실행합니다.
-- sp_query_store_force_plan 을 사용하면 쿼리 저장소에서 기록한 계획 만 해당 쿼리의 계획으로 강제 실행할 수 있습니다.
-- 즉, 쿼리에 사용할 수있는 유일한 계획은 쿼리 저장소가 활성화되어있는 동안 해당 쿼리를 실행하는 데 이미 사용 된 계획뿐입니다.
-- QUERY_ID , PLAN_ID 순서
EXEC sp_query_store_force_plan 3, 3;
-- 특정 쿼리에 대해 특정 PLAN 설정 해제 하기 ( Enables unforcing a particular plan for a particular query )
-- QUERY_ID , PLAN_ID 순서
EXEC sp_query_store_unforce_plan 3, 3;
-- 실행 계획 확인
SELECT Txt.query_text_id, Txt.query_sql_text, Pl.plan_id, Qry.*
FROM sys.query_store_plan AS Pl JOIN sys.query_store_query AS Qry ON Pl.query_id = Qry.query_id
JOIN sys.query_store_query_text AS Txt ON Qry.query_text_id = Txt.query_text_id ;
ALTER DATABASE TGTEST SET QUERY_STORE (INTERVAL_LENGTH_MINUTES = 30);
ALTER DATABASE TGTEST SET QUERY_STORE (MAX_STORAGE_SIZE_MB = 200);
ALTER DATABASE TGTEST SET QUERY_STORE
( OPERATION_MODE = READ_WRITE, CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30)
, DATA_FLUSH_INTERVAL_SECONDS = 300
, MAX_STORAGE_SIZE_MB = 500
, INTERVAL_LENGTH_MINUTES = 15
, SIZE_BASED_CLEANUP_MODE = AUTO
, QUERY_CAPTURE_MODE = AUTO
, MAX_PLANS_PER_QUERY = 1000
, WAIT_STATS_CAPTURE_MODE = ON );
SELECT * FROM sys.database_query_store_options;
-- 수행 횟수가 2보다 작고, 마지막 수행이 24전 보다 오랜 쿼리를 QUERY STORE 에서 삭제하기
DECLARE @id int
DECLARE adhoc_queries_cursor
CURSOR FOR SELECT q.query_id
FROM sys.query_store_query_text AS qt JOIN sys.query_store_query AS q
ON q.query_text_id = qt.query_text_id JOIN sys.query_store_plan AS p
ON p.query_id = q.query_id JOIN sys.query_store_runtime_stats AS rs
ON rs.plan_id = p.plan_id
GROUP BY q.query_id
HAVING SUM(rs.count_executions) < 2 -- 수행 횟수가 2보다 작고
AND MAX(rs.last_execution_time) < DATEADD (hour, -24, GETUTCDATE()) -- 마지막 수행 시간이 24간 이전
ORDER BY q.query_id ;
OPEN adhoc_queries_cursor ;
FETCH NEXT FROM adhoc_queries_cursor INTO @id;
WHILE @@fetch_status = 0
BEGIN PRINT @id EXEC sp_query_store_remove_query @id
FETCH NEXT FROM adhoc_queries_cursor INTO @id
END
CLOSE adhoc_queries_cursor ;
DEALLOCATE adhoc_queries_cursor;
-- 가장 최근까지 수행 쿼리
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;
-- 최근 24시간 내에 IO가 가장 높은 쿼리 10
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;
-- 실행계획 변경으로 성능 저하 쿼리 ( 수행이 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;
-- 가장 오래 대기한 쿼리
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
--- 최근 1시간, 24시간 쿼리 성능 비교
--- "Recent" workload - last 1 hour
DECLARE @recent_start_time datetimeoffset;
DECLARE @recent_end_time datetimeoffset;
SET @recent_start_time = DATEADD(hour, -1, SYSUTCDATETIME()); -- 1시간전
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()); -- 24시간전
SET @history_end_time = SYSUTCDATETIME();
WITH hist AS
( SELECT p.query_id query_id, CONVERT(float, SUM(rs.avg_duration*rs.count_executions)) total_duration
, SUM(rs.count_executions) count_executions
, COUNT(distinct p.plan_id) num_plans
FROM sys.query_store_runtime_stats AS rs JOIN sys.query_store_plan 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, CONVERT(float, SUM(rs.avg_duration*rs.count_executions)) total_duration
, SUM(rs.count_executions) count_executions, COUNT(distinct p.plan_id) num_plans
FROM sys.query_store_runtime_stats AS rs JOIN sys.query_store_plan 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 query_id, results.query_text query_text
, results.additional_duration_workload additional_duration_workload
, results.total_duration_recent total_duration_recent
, results.total_duration_hist total_duration_hist
, ISNULL(results.count_executions_recent, 0) count_executions_recent
, ISNULL(results.count_executions_hist, 0) count_executions_hist
FROM ( SELECT hist.query_id query_id, qt.query_sql_text 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) total_duration_recent
, ROUND(hist.total_duration, 2) total_duration_hist, recent.count_executions count_executions_recent, hist.count_executions 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);