MSSQL Query Store

링크 :

http://rusanu.com/2016/04/01/understanding-sql-server-query-store/

https://www.brentozar.com/archive/2014/11/sql-server-query-store/

https://docs.microsoft.com/en-us/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store?view=sql-server-2017

https://docs.microsoft.com/en-us/sql/relational-databases/performance/best-practice-with-the-query-store?view=sql-server-2017

<< MSSQL Query Store >>

MS SQL 2014 버전 부터 지원됨 

: 지금까지 SQL Server는 일반적인 실행 계획 문제에 대한 변경 감지 또는 추적 기능을 제공하지 않았습니다.

  SQL Server 2014, 쿼리 및 실행 계획을 관리하는 방법

  2014 버전 부터, 쿼리 및 실행 계획을 영구적으로 보관되고, DMV 를 통해서 조회 가능함

  Query Store가 활성화되면 쿼리 SQL 텍스트, 실행 계획 및 실행 횟수, 실행 시간, 논리적 읽기 등을 저장합니다.

  이 정보는 데이터베이스에 저장되어 나중에 분석 할 수 있습니다.

  신규 데이터베이스에 대해 쿼리 저장소(Query Store)는 기본적으로 활성화되지 않습니다. ( Master, Tempdb 는 대해서는 활성화 불가 )

쿼리 저장소에는 다음 3개의 저장소가 있습니다.

1) 계획 저장소 - 실행 계획 정보 유지

2) 런타임 통계 저장소 - 실행 통계 정보 유지

3) 대기 통계 저장소 - 대기 통계 정보 유지

-. SQL Server 2016 쿼리 저장소 사용 하는 경우의 성능 저하  

  >>>   SQL Server 2016 s p 2 용 누적 업데이트 2 ( 링크 - https://support.microsoft.com/ko-kr/help/4340355 )

-. 쿼리 저장소 이전의 성능 및 작업 부하 분석은 

   주로 sys.dm_exec_query_stats 및 sys.dm_exec_procedure_stats와 같은 DMV에 의존했습니다. 

   이러한 DMV는 쿼리 계획 캐시를 기반으로하기 때문에 몇 가지 단점이 있습니다.

1) 일부 쿼리 계획이 캐시되지 않으므로 모든 쿼리를 캡처하지 않습니다.

2) 쿼리 계획 캐시에서 항목을 제거하면 쿼리 통계가 손실됩니다.

3) 오랜 시간 동안 통계를 사용할 수 없습니다.

Query Store는 각 명령문 컴파일 이후에 컴파일 기간이 통지되고 각 명령문 실행 후 실행 통계가 통지됩니다. 

Query Store는 통계를 메모리에 캐시하고 각 쿼리 및 계획에 대해 통계를 집계합니다.

정기적으로 축적 된 통계를 데이터베이스로 플러시합니다. 

통계를 플러시하는 빈도는 INTERVAL_LENGTH_MINUTES 쿼리 저장 옵션에 따라 다릅니다.

Query Store에서 사용되는 디스크 공간 은 MAX_STORAGE_SIZE_MB 설정을 통해 구성 할 수 있습니다.

아마 내가 쿼리 스토어에 대해 이야기 할 때 가장 많이 들리는 

질문은 "괜찮 았지만 성능에 어느 정도 영향을 줍니까?"라고 할 수 있습니다. 

짧은 대답은 "당신이 그것을 알아 차리지 못할 것"이지만, 세부 사항은 조금 있습니다.

Query Store는 항상 백그라운드 작업에 데이터를 비동기 적으로 저장하므로 사용자 작업 부하는 직접 영향을받지 않습니다.

전반적으로 Query Store는 일반적으로 0.5 % ~ 2.5 % 범위의 성능에 영향을 미칩니다. 

즉, 시스템에 초당 X 트랜잭션 처리량이있는 경우 Query Store가있는 경우 처리량을 0.5-2.5 % 줄입니다.

Plan forcing : 특정 Plan 을 강제함 !!!

SQL Server 2017 및 Azure SQL 데이터베이스에서 대기 통계를 추적하는 다른 차원을 쿼리 저장소에 추가했습니다.

Qusery Store 기능 활용 방안 

1. 실행 계획 변경으로 성능 저하 시, 예전 실행 계획으로 복귀

2. 이슈 발생 시, 이슈에 따른 Top Query List up (CPU, I/O, Memory, Elpased Time, Memory Usage 등)

3. 실행 계획 변경 모니터링 

쿼리 저장소에는 세 개의 저장소가 있습니다.

1. 실행 계획 정보를 유지하기위한 계획 저장소 .

2. 실행 통계 정보를 유지하기위한 런타임 통계 저장소 .

3. 지속 대기 통계 정보에 대한 대기 통계 저장소 .

계획 저장소의 조회에 대해 저장할 수있는 고유 계획의 수는 max_plans_per_query 구성 옵션으로 

제한됩니다. 

성능을 향상시키기 위해 정보는 비동기 적으로 저장소에 기록됩니다. 

공간 사용을 최소화하기 위해 런타임 통계 저장소의 런타임 실행 통계가 고정된 시간 동안 발생한 누적값으로 저장됩니다.

<< 쿼리 저장소의 쿼리 및 계획 조회 >>

Use Database 

Go

SELECT Txt.query_text_id, Txt.query_sql_text, Pl.plan_id, Qry.* 

FROM            sys.query_store_plan AS Pl 

     INNER JOIN sys.query_store_query AS Qry     ON Pl.query_id = Qry.query_id 

     INNER JOIN sys.query_store_query_text AS Txt ON Qry.query_text_id = Txt.query_text_id ;

SQL Server 2017 (14.x) CTP 2.0 및 SQL 데이터베이스부터 쿼리 별 대기 쿼리 통계를 쿼리 저장소에서 사용할 수 있습니다. 

Query Store에서 대기 유형은 대기 카테고리 별로 구분 됩니다.

QUERY STORE 옵션

: sys.database_query_store_options 로 확인 가능

<< 주요대기 >>

RESOURCE_SEMAPHORE 대기  --> 메모리 대기

LCK_M_X 대기             --> 잠금 대기

PAGEIOLATCH_SH 대기      --> 버퍼 I/O 대기

SOS_SCHEDULER_YIELD 대기 --> CPU 대기

<< 주요 설정 값 >>

1. STALE_QUERY_THRESHOLD_DAYS  

     - QUERY STORE 의 보율 일수, 기본값 30일 

      변경 ) ALTER DATABASE <database> SET 

                QUERY_STORE ( CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = <value> ) )

2. DATA_FLUSH_INTERVAL_SECONDS 

    - 메모리 QUERY STORE를 DISK 에 비동기적으로 WRITE 하는 주기 , 기본값 900 초 ( 15분 )

       변경 ) ALTER DATABASE <database> SET QUERY_STORE

                 (DATA_FLUSH_INTERVAL_SECONDS = <interval>)

3. MAX_STORAGE_SIZE_MB         

     - QUERY STORE의 최대 크기 구성, 기본값 100MB

       , MAX_STORAGE_SIZE_MB 도달시, QUERY STORE 상태를 읽기/쓰기 

       -> 읽기 전용으로 변경 하고, 새 데이터 수집 중단됨 

        변경 ) ALTER DATABASE <database> SET QUERY_STORE (MAX_STORAGE_SIZE_MB = <size>)

4. INTERVAL_LENGTH_MINUTES  

      - 런타임 실행 통계 데이터가 쿼리 저장소에 집계되는 시간 간격 기본값은 60분 

       ( SNAPSHOT INTERVAL 의미하는듯 )  1,5,10,15,30,60,1440분(하루)

5. SIZE_BASED_CLEANPU_MODE   

      - 총 데이터 량이 최대 크기에 가까워지면 정리 프로세스를 자동으로 활성화할지 여부 제어

        , 기본값은 AUTO

        ,  크기 기반 정리는 가장 비싸고 가장 오래된 쿼리를 먼저 제거합니다. 

           max_storage_size_mb의 약 80 %에서 멈춥니다.

6. QUERY_CAPTURE_MODE      

      - QUERY STORE 가 모든 쿼리 또는 실행 횟수 및 리소스 소비를 기반으로 관련 쿼리를 캡쳐하는지

        또는 새 쿼리 추가를 중단하고 현재 쿼리를 추적하는지 지정

      -  ALL(모든 쿼리 캡쳐), Azure SQL Database 는 AUTO(자주 수행되지 않고, 컴파일 및 실행 시간적으로

          중요하지 않은 쿼리는 무시됨)

7. MAX_PLANS_PER_QUERY       

     - 각 쿼리에 대해 유지 관리되는 최대 계획(Plan) 수, 기본값은 200

       변경 ) ALTER DATABASE<database> SET QUERY_STORE (MAX_PLANS_PER_QUERY = <n>)

8. WAIT_STATS_CAPTURE_MODE     

      - QUERY_STORE 가 대기 통계 정보를 캡처하는지 여부, 켜짐(기본)

<< 참고사항 >>

강제 계획으로 쿼리가있는 경우 데이터베이스 이름 바꾸기 

실행 계획은 세 부분으로 구성된 이름 인 database.schema.object 사용하여 개체를 참조합니다.

데이터베이스 이름을 바꾸면 계획 강제력이 실패하고 모든 후속 쿼리 실행에서 다시 컴파일됩니다.

<<2017 무료 평가판 설치 후, 설정 화면 >>

-. Database 에서 쿼리 저장소 확인 

-. 쿼리 저장소 작동 모드 요청 해제 -> 읽기/쓰기 변경으로 활성화

-. 아래처럼, GUI 에서 확인 가능하고, 특정 플랜을 강제 할수도 있음

<< 주요 SQL >>

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