Query Store je užitečnou novinkou v Azure SQL, resp. Microsoft SQL Server 2016+. Ukládá užitečné statistiky o spuštěných dotazech, jejich execution planech a mnoho dalšího. (Umí dokonce vynutit používání konkrétního execution planu a dokonce toto řešit automaticky, pokud detekuje výkonovou regresi. O tom ale někdy jindy.)

Jeden ze scénářů, který vám může být užitečný, je porovnání dvou období co se počtu spuštění jednotlivých dotazů týče, jejich průměrné doby trvání, atp. Hodit se to může při nenadálých poklesech výkonnosti, po nasazení nových verzí aplikací, atp.:

DECLARE @Period1Start datetime, @Period1End datetime, @Period2Start datetime, @Period2End datetime

SET @Period1Start = '20171212 00:00'
SET @Period1End = '20171213 00:00'
SET @Period2Start = '20171214 00:00'
SET @Period2End = '20171215 00:00'

;WITH period1 AS
(
	SELECT
		query_id,
		AVG(avg_duration) AS avg_duration,
		SUM(count_executions) AS count_executions_total
	FROM sys.query_store_runtime_stats
		INNER JOIN  sys.query_store_runtime_stats_interval ON (query_store_runtime_stats_interval.runtime_stats_interval_id = query_store_runtime_stats.runtime_stats_interval_id)
		INNER JOIN sys.query_store_plan ON query_store_plan.plan_id = query_store_runtime_stats.plan_id
	WHERE
		(sys.query_store_runtime_stats_interval.start_time >= @Period1Start)
		AND (sys.query_store_runtime_stats_interval.end_time <= @Period1End)
	GROUP BY query_id
),
period2 AS
(
	SELECT
		query_id,
		AVG(avg_duration) AS avg_duration,
		SUM(count_executions) AS count_executions_total
	FROM sys.query_store_runtime_stats
		INNER JOIN  sys.query_store_runtime_stats_interval ON (query_store_runtime_stats_interval.runtime_stats_interval_id = query_store_runtime_stats.runtime_stats_interval_id)
		INNER JOIN sys.query_store_plan ON query_store_plan.plan_id = query_store_runtime_stats.plan_id
	WHERE
		(sys.query_store_runtime_stats_interval.start_time >= @Period2Start)
		AND (sys.query_store_runtime_stats_interval.end_time <= @Period2End)
	GROUP BY query_id
)
SELECT
		query_store_query.query_id,
		period1.avg_duration AS period1_avg_duration,
		period2.avg_duration AS period2_avg_duration,
		CASE WHEN period1.count_executions_total IS NOT NULL THEN (period2.avg_duration - period1.avg_duration) * 100.0 / period1.avg_duration ELSE NULL END AS avg_duration_increase_percent,
		period1.count_executions_total AS period1_count_executions_total,
		period2.count_executions_total AS period2_count_executions_total,
		CASE WHEN period1.count_executions_total IS NOT NULL THEN (period2.count_executions_total - period1.count_executions_total) * 100.0 / period1.count_executions_total ELSE NULL END AS count_execution_increase_percent,
		query_sql_text
	FROM period2
		LEFT JOIN period1 ON period1.query_id = period2.query_id
		LEFT JOIN sys.query_store_query ON query_store_query.query_id = period2.query_id
		LEFT JOIN sys.query_store_query_text ON query_store_query_text.query_text_id = query_store_query.query_text_id
	--ORDER BY period2.count_executions_total DESC
	ORDER BY count_execution_increase_percent DESC
	--ORDER BY avg_duration_increase_percent DESC

References

• Query Store Catalog Views (Transact-SQL)