Get best query plans

best_query_plans.sql

/*
-- get query id
SELECT
    q.query_id,
    qt.query_sql_text
FROM sys.query_store_query_text qt
INNER JOIN sys.query_store_query q ON
    qt.query_text_id = q.query_text_id
WHERE query_sql_text like N'%select top 1%'
*/
 
 
  DECLARE @query_id INT = 123; -- Your query_id

  WITH QueryPlanMetrics AS (
      SELECT
          qsp.plan_id,
          qsp.query_id,
          SUM(qsrs.count_executions) AS total_executions,
          -- Duration in microseconds → convert to ms
          SUM(qsrs.avg_duration * qsrs.count_executions) / NULLIF(SUM(qsrs.count_executions), 0)
              AS weighted_avg_duration_us,
          -- CPU in microseconds → convert to ms
          SUM(qsrs.avg_cpu_time * qsrs.count_executions) / NULLIF(SUM(qsrs.count_executions), 0)
              AS weighted_avg_cpu_time_us,
          -- Logical reads in 8-KB PAGES → multiply by 8 for KB
          SUM(qsrs.avg_logical_io_reads * qsrs.count_executions) / NULLIF(SUM(qsrs.count_executions), 0)
              AS weighted_avg_logical_reads_pages,
          qsp.is_forced_plan,
          qsp.last_compile_start_time
      FROM sys.query_store_plan AS qsp
      INNER JOIN sys.query_store_runtime_stats AS qsrs ON qsp.plan_id = qsrs.plan_id
      INNER JOIN sys.query_store_runtime_stats_interval AS qsrsi
          ON qsrs.runtime_stats_interval_id = qsrsi.runtime_stats_interval_id
      WHERE qsp.query_id = @query_id
          AND qsrsi.end_time >= DATEADD(DAY, -1, GETUTCDATE())
      GROUP BY qsp.plan_id, qsp.query_id, qsp.is_forced_plan, qsp.last_compile_start_time
  )
  SELECT
      plan_id,
      total_executions,
      CAST(weighted_avg_duration_us / 1000.0 AS DECIMAL(18,2)) AS avg_duration_ms,
      CAST(weighted_avg_cpu_time_us / 1000.0 AS DECIMAL(18,2)) AS avg_cpu_time_ms,
      CAST(weighted_avg_logical_reads_pages * 8 AS DECIMAL(18,0)) AS avg_logical_reads_kb,
      is_forced_plan,
      ROW_NUMBER() OVER (ORDER BY
          weighted_avg_duration_us ASC,
          weighted_avg_cpu_time_us ASC,
          weighted_avg_logical_reads_pages ASC
      ) AS performance_rank
  FROM QueryPlanMetrics
  WHERE total_executions >= 10
  ORDER BY performance_rank;

queries_needing_plan_forcing.sql

  /*                                                                                                                                          ================================================================================                                                          
  TOP 10 QUERIES NEEDING PLAN FORCING                                                                                                         ================================================================================                                                          
  Identifies queries where:
  1. Multiple plans exist with significant performance variance
  2. The currently-used plan is NOT the best performing plan
  3. No plan is currently forced
  4. Query has meaningful execution count for statistical significance
  ================================================================================
  */

  WITH QueryPlanMetrics AS (
      -- Calculate weighted performance metrics per plan (last 7 days)
      SELECT
          qsp.plan_id,
          qsp.query_id,
          SUM(qsrs.count_executions) AS total_executions,
          SUM(qsrs.avg_duration * qsrs.count_executions) / NULLIF(SUM(qsrs.count_executions), 0)
              AS weighted_avg_duration_us,
          SUM(qsrs.avg_cpu_time * qsrs.count_executions) / NULLIF(SUM(qsrs.count_executions), 0)
              AS weighted_avg_cpu_time_us,
          SUM(qsrs.avg_logical_io_reads * qsrs.count_executions) / NULLIF(SUM(qsrs.count_executions), 0)
              AS weighted_avg_logical_reads_pages,
          MAX(qsrs.last_execution_time) AS last_execution_time,
          qsp.last_compile_start_time,
          qsp.is_forced_plan
      FROM sys.query_store_plan AS qsp
      INNER JOIN sys.query_store_runtime_stats AS qsrs
          ON qsp.plan_id = qsrs.plan_id
      INNER JOIN sys.query_store_runtime_stats_interval AS qsrsi
          ON qsrs.runtime_stats_interval_id = qsrsi.runtime_stats_interval_id
      WHERE qsrsi.end_time >= DATEADD(DAY, -7, GETUTCDATE())
        AND qsp.is_forced_plan = 0
      GROUP BY qsp.plan_id, qsp.query_id, qsp.last_compile_start_time, qsp.is_forced_plan
      HAVING SUM(qsrs.count_executions) >= 10
  ),

  QueryPlanRanking AS (
      SELECT
          *,
          ROW_NUMBER() OVER (PARTITION BY query_id ORDER BY weighted_avg_duration_us ASC) AS plan_rank_by_duration,
          COUNT(*) OVER (PARTITION BY query_id) AS plan_count,
          MIN(weighted_avg_duration_us) OVER (PARTITION BY query_id) AS best_duration_us,
          MAX(weighted_avg_duration_us) OVER (PARTITION BY query_id) AS worst_duration_us,
          ROW_NUMBER() OVER (PARTITION BY query_id ORDER BY last_execution_time DESC) AS recency_rank
      FROM QueryPlanMetrics
  ),

  QueriesWithRegression AS (
      SELECT
          query_id,
          MAX(CASE WHEN plan_rank_by_duration = 1 THEN plan_id END) AS best_plan_id,
          MAX(CASE WHEN plan_rank_by_duration = 1 THEN weighted_avg_duration_us END) AS best_plan_duration_us,
          MAX(CASE WHEN plan_rank_by_duration = 1 THEN weighted_avg_cpu_time_us END) AS best_plan_cpu_us,
          MAX(CASE WHEN plan_rank_by_duration = 1 THEN weighted_avg_logical_reads_pages END) AS best_plan_reads_pages,
          MAX(CASE WHEN plan_rank_by_duration = 1 THEN total_executions END) AS best_plan_executions,
          MAX(CASE WHEN plan_rank_by_duration = 1 THEN last_compile_start_time END) AS best_plan_compile_time,
          MAX(CASE WHEN recency_rank = 1 THEN plan_id END) AS recent_plan_id,
          MAX(CASE WHEN recency_rank = 1 THEN weighted_avg_duration_us END) AS recent_plan_duration_us,
          MAX(CASE WHEN recency_rank = 1 THEN weighted_avg_cpu_time_us END) AS recent_plan_cpu_us,
          MAX(CASE WHEN recency_rank = 1 THEN total_executions END) AS recent_plan_executions,
          MAX(CASE WHEN recency_rank = 1 THEN last_execution_time END) AS last_execution_time,
          MAX(CASE WHEN plan_rank_by_duration = plan_count THEN plan_id END) AS worst_plan_id,
          MAX(CASE WHEN plan_rank_by_duration = plan_count THEN weighted_avg_duration_us END) AS worst_plan_duration_us,
          MAX(plan_count) AS total_plan_count,
          MAX(best_duration_us) AS min_duration_us,
          MAX(worst_duration_us) AS max_duration_us,
          SUM(total_executions) AS total_query_executions
      FROM QueryPlanRanking
      GROUP BY query_id
      HAVING MAX(plan_count) > 1
         AND MAX(CASE WHEN plan_rank_by_duration = 1 THEN plan_id END)
             <> MAX(CASE WHEN recency_rank = 1 THEN plan_id END)
  ),

  ForcingCandidates AS (
      SELECT
          qwr.*,
          qt.query_sql_text,
          (qwr.recent_plan_duration_us - qwr.best_plan_duration_us) AS duration_improvement_us,
          CASE WHEN qwr.best_plan_duration_us > 0
              THEN ((qwr.recent_plan_duration_us - qwr.best_plan_duration_us) * 100.0)
                   / qwr.best_plan_duration_us
              ELSE 0
          END AS pct_duration_improvement,
          (qwr.recent_plan_duration_us - qwr.best_plan_duration_us) * qwr.recent_plan_executions
              AS total_time_savable_us,
          CASE WHEN qwr.min_duration_us > 0
              THEN qwr.max_duration_us / qwr.min_duration_us
              ELSE 1
          END AS plan_variance_ratio
      FROM QueriesWithRegression qwr
      INNER JOIN sys.query_store_query q ON qwr.query_id = q.query_id
      INNER JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
      WHERE NOT EXISTS (
          SELECT 1 FROM sys.query_store_plan qsp_forced
          WHERE qsp_forced.query_id = qwr.query_id AND qsp_forced.is_forced_plan = 1
      )
  )

  SELECT TOP 10
      fc.query_id,
      LEFT(fc.query_sql_text, 200) AS query_text_preview,
      fc.best_plan_id AS recommended_plan_to_force,
      fc.recent_plan_id AS current_plan_id,
      fc.total_plan_count AS available_plans,
      CAST(fc.recent_plan_duration_us / 1000.0 AS DECIMAL(18,2)) AS current_plan_avg_ms,
      CAST(fc.best_plan_duration_us / 1000.0 AS DECIMAL(18,2)) AS best_plan_avg_ms,
      CAST(fc.duration_improvement_us / 1000.0 AS DECIMAL(18,2)) AS improvement_ms,
      CAST(fc.pct_duration_improvement AS DECIMAL(10,1)) AS improvement_pct,
      CAST(fc.total_time_savable_us / 1000000.0 AS DECIMAL(18,2)) AS total_time_savable_seconds,
      fc.total_query_executions,
      CAST(fc.plan_variance_ratio AS DECIMAL(10,2)) AS plan_variance_ratio
  FROM ForcingCandidates fc
  ORDER BY fc.total_time_savable_us DESC;