쿼리 저장소 (Query Store)

-- <<관련 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);