T-SQL - Query Store with Wait Stats data:
SELECT
qsws.Plan_ID,
qsrsi.start_time as 'Interval Start',
qsrsi.end_time as 'Interval End',
qsrsi.runtime_stats_interval_id as 'IntervalID',
qsrs.Runtime_Stats_ID,
qsws.Wait_Stats_ID,
qst.query_sql_text,
x.actual_xml,
qsp.is_parallel_plan,
qsp.is_trivial_plan,
qsp.is_forced_plan,
qsp.count_compiles,
count_executions,
qsp.last_execution_time as 'Plan Last Exec Time',
qsrs.first_execution_time as 'RunTimeStats First Exec Time',
qsrs.last_execution_time as 'RunTimeStats Last Exec Time',
qsws.execution_type_desc,
qsws.wait_category_desc,
qsws.total_query_wait_time_ms,
qsws.avg_query_wait_time_ms,
qsws.last_query_wait_time_ms,
qsws.min_query_wait_time_ms,
qsws.max_query_wait_time_ms
FROM
sys.query_store_plan qsp
JOIN sys.query_store_query qsq on qsq.query_id = qsp.query_id
JOIN sys.query_store_query_text qst on qsq.query_text_id = qst.query_text_id
LEFT JOIN sys.query_store_runtime_stats AS qsrs on qsrs.plan_id = qsp.plan_id
LEFT JOIN sys.query_store_wait_stats qsws on qsws.plan_id = qsp.plan_id and qsws.runtime_stats_interval_id = qsrs.runtime_stats_interval_id
JOIN sys.query_store_runtime_stats_interval qsrsi on qsrs.runtime_stats_interval_id = qsrsi.runtime_stats_interval_id
CROSS APPLY ( SELECT TRY_CONVERT( XML, qsp.query_plan) AS actual_xml ) AS x
WHERE qsp.plan_id = 4
ORDER BY
qsp.plan_id asc,
qsrsi.runtime_stats_interval_id asc,
qsrs.runtime_stats_id asc,
qsws.wait_stats_id asc
Resources:
What's New in SQL Server 2017
Monitoring performance by using the Query Store
sys.query_store_wait_stats (Transact-SQL)
|
|
|
|
|
|