info@techdevops.com
|
437-991-3573
|
Data Engineering Services
|
TechDevOps.com
|
| | | |
|
Experts in Microsoft SQL Server on Windows, Linux, Containers | Clusters, Always On, FCI | Migrations, Cloud, Performance
|
| | | |
|
|
Get all Forced Plans from the Query Store
by BF (Principal Consultant; Architecture; Engineering)
2023-01-10
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
|
|
|
|
|
|
|
|
|
|