Get all Forced Plans from the Query Store
SELECT
@@servername as 'Instance',
getdate() as 'DateCollected',
qt.query_sql_text,
q.query_id,
qp.plan_id,
qp.query_plan_hash,
q.query_hash,
qp.is_forced_plan,
qp.plan_forcing_type_desc,
qp.is_parallel_plan,
CAST(query_plan AS XML) AS 'Execution Plan',
rs.first_execution_time,
rs.last_execution_time,
rs.count_executions,
rs.avg_duration,
rs.avg_rowcount,
rs.last_logical_io_reads,
rs.avg_cpu_time,
rs.avg_query_max_used_memory,
qp.force_failure_count
FROM sys.query_store_plan qp
INNER JOIN sys.query_store_query q
ON qp.query_id = q.query_id
INNER JOIN sys.query_store_query_text qt
ON q.query_text_id = qt.query_text_id
INNER JOIN sys.query_store_runtime_stats rs
ON qp.plan_id = rs.plan_id
WHERE qp.is_forced_plan = 1
Order by rs.last_execution_time desc
|
|
|
|
|
|