SQL Server - Top 100 Worst Performing Queries From Query Store

gistfile1.txt

DECLARE @Starttime DATETIME
DECLARE @EndTime DATETIME
SET @Starttime = DATEADD(hour ,-1,GETDATE())
SET @Endtime = GETDATE()

DROP TABLE IF EXISTS #query1 

--- Max Duration
SELECT TOP (100)
    p.query_id query_id,
	p.plan_id plan_id,
    q.object_id,
	ISNULL(OBJECT_NAME(q.object_id),'''') object_name,
    qt.query_sql_text query_sql_text,
	ROUND(CONVERT(float, SUM(rs.avg_duration*rs.count_executions))/NULLIF(SUM(rs.count_executions), 0)*0.001,2) avg_duration_ms,
	MAX(rs.last_execution_time) AS 'LastExecutiontime',
    SUM(rs.count_executions) count_executions,
    COUNT(DISTINCT p.plan_id) num_plans
INTO
	#query1
FROM sys.query_store_runtime_stats rs
    JOIN sys.query_store_plan p ON p.plan_id = rs.plan_id
    JOIN sys.query_store_query q ON q.query_id = p.query_id
    JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
WHERE 
	(rs.last_execution_time > @Starttime AND rs.last_execution_time < @EndTime)
GROUP BY 
	p.query_id, p.plan_id, qt.query_sql_text, q.object_id
-- 30 Second Timeouts
HAVING 
	ROUND(CONVERT(FLOAT, SUM(rs.avg_duration*rs.count_executions))/NULLIF(SUM(rs.count_executions), 0)*0.001,2)>15000
ORDER BY 
	6 DESC


SELECT 
	*,
	(
	SELECT
		CAST(CONVERT(varchar(max), p.query_plan, 1) AS XML)
	FROM
		sys.query_store_plan p
	WHERE 
		p.plan_id = q.plan_id
	) AS [XMLQueryPlan]
FROM
	#query1 Q