Query Store를 사용하여 성능 모니터링
DB Skill/MSSQL 2021. 6. 10. 00:25
최소 버전 : SQLServer 2016 SP2 CU2 이상
권장 버전 : SQLServer 2017 이상( wait type 및 temp 경합 확인 가능 )
SSMS : Management Studio 버전 16 이상
구성
SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE);GUI
쿼리 저장소를 사용하도록 설정한 후 개체 탐색기 창의 데이터베이스 부분을 새로 고쳐 쿼리 저장소 섹션을 추가
재발된 쿼리 를 선택하여 에서 재발된 쿼리 SQL Server Management Studio창을 엽니다. 재발된 쿼리 창에는 쿼리 저장소의 쿼리 및 계획이 표시됩니다. 맨 위의 드롭다운 상자를 사용하여 다양한 기준으로 쿼리를 필터링합니다. 기간(밀리초) (기본값), CPU 시간(밀리초), 논리적 읽기(KB), 논리적 쓰기(KB), 물리적 읽기(KB), CLR 시간(ms), DOP, 메모리 사용량(KB), 행 수, 사용된 메모리(KB), 사용된 임시 DB 메모리(KB), 대기 시간(밀리초).
계획을 선택하면 그래픽 쿼리 계획이 표시됩니다. 단추를 사용하여 원본 쿼리를 보고, 쿼리 계획을 강제로 적용 및 적용 해제하고, 그리드 형식과 차트 형식 간에 전환하고, 선택한 계획을 비교하고(두 개 이상 선택한 경우), 디스플레이를 새로 고칠 수 있습니다.
쿼리 저장소 카탈로그 뷰
sys.database_query_store_options(Transact-SQL)
sys.query_context_settings(Transact-SQL)
sys.query_store_plan(Transact-SQL)
sys.query_store_query(Transact-SQL)
sys.query_store_query_text(Transact-SQL)
sys.query_store_runtime_stats(Transact-SQL)
sys.query_store_wait_stats(Transact-SQL)
sys.query_store_runtime_stats_interval(Transact-SQL)쿼리 저장소 저장 프로시저
sp_query_store_flush_db(Transact-SQL)
sp_query_store_reset_exec_stats(Transact-SQL)
sp_query_store_force_plan(Transact-SQL)
sp_query_store_unforce_plan(Transact-SQL)
sp_query_store_remove_plan (Transact-SQL)
sp_query_store_remove_query(Transact-SQL)쿼리 저장소 옵션 가져오기
-- 옵션 조회
SELECT * FROM sys.database_query_store_options;
-- 공간 사용량 조회
SELECT current_storage_size_mb, max_storage_size_mb
FROM sys.database_query_store_options;
쿼리 저장소 옵션 설정
ALTER DATABASE <database name>
SET QUERY_STORE (
OPERATION_MODE = READ_WRITE,
CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),
DATA_FLUSH_INTERVAL_SECONDS = 3000,
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 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시간 동안 평균 물리적 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;최근에 성능이 저하된 쿼리(다른 시점과 비교)는 무엇입니까?
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참고 자료
쿼리 저장소를 사용하여 성능 모니터링 - SQL Server
SQL Server 쿼리 저장소는 쿼리 계획 선택 및 성능에 대한 인사이트를 제공합니다. 쿼리 저장소는 쿼리, 계획 및 런타임 통계의 기록을 캡처합니다.
docs.microsoft.com
'DB Skill > MSSQL' 카테고리의 다른 글
| MS SQLServer max worker threads 카운트 확인 및 수정 (0) | 2021.07.12 |
|---|---|
| perfmon에서 SQL Server 카운트가 보이지 않을때 (0) | 2021.07.06 |
| MSSQL 중복 인덱스 확인 쿼리 (0) | 2021.06.09 |
| MSSQL SP 리컴파일로 실행 계획 초기화 (0) | 2021.06.07 |
| MSSQL Error Log 초기화 (0) | 2021.06.07 |
