Leveraging SQL Server 2019’s Last Known Actual Query Plan for Troubleshooting

Introduction

Troubleshooting production performance issues is often challenging because detailed query execution data can vanish before you realize there’s a problem. Many times, you end up piecing together incomplete clues from runtime statistics or the plan cache. Fortunately, SQL Server 2019 introduced a powerful feature to address this pain point: the Last Known Actual Query Plan.

What Is the Last Known Actual Query Plan?

SQL Server 2019 leverages lightweight query execution statistics profiling to capture the last known actual execution plan for a query. This plan includes critical runtime details—like actual rows processed for each operator—without the overhead of full profiling or tracing. You can think of it as a “snapshot” of the most recent actual plan used by SQL Server, providing more concrete data than the typical estimated plan or the cached plan might reveal.

Why does this matter?

• Diagnose parameter sniffing problems by seeing if certain parameters result in suboptimal plan choices.
• Investigate skewed joins to confirm if an operator processed significantly more rows than expected.
• Pinpoint plan regressions without needing to reproduce the exact workload in a test environment.

Common Challenges in Query Troubleshooting

Before SQL Server 2019’s enhancement, DBAs often used the following workarounds:

1. Retrieve the estimated plan from the plan cache.
   • Useful for a quick look at query structure, but lacks real runtime metrics (actual row counts, execution time).
2. Analyze Query Store data.
   • Valuable for trends but aggregates runtime statistics over an interval (often one hour by default), making it harder to pinpoint sudden dips in performance.
3. Attempt to reproduce the issue.
   • Can be impractical. Test environments rarely mirror production workloads, and finding the right parameters to replicate a performance problem is time-consuming.
4. Force recompiles or update statistics.
   • Might temporarily fix the issue but doesn’t shed light on why the problem happened in the first place.

How SQL Server 2019 Simplifies Troubleshooting

With the Last Known Actual Query Plan feature, you can directly examine runtime metrics of the most recent query execution. This means:

• You no longer rely solely on estimated plans or aggregated Query Store data.
• Operators, row counts, memory usage, and other actual runtime metrics are readily available.
• You can avoid re-executing problematic queries in production just to gather data.

How to Use the Feature

1. Enable Last Known Actual Query Plan

Enable the feature at the database level:

ALTER DATABASE SCOPED CONFIGURATION 
SET LAST_QUERY_PLAN_STATS = ON;

SQL Server then begins collecting the most recent actual plan data. This collection is lightweight, so the performance overhead is minimal compared to traditional tracing or extended events.

2. Retrieve the Last Known Actual Query Plan

Use dynamic management objects to query the stored plan data. For example:

SELECT qps.query_plan,
       qs.execution_count,
       qs.total_elapsed_time
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_query_plan_stats(qs.plan_handle) qps
WHERE qs.query_hash = HASHBYTES('SHA2_256', '');

• sys.dm_exec_query_plan_stats(qs.plan_handle) is the key function exposing the last known actual query plan.
• Filter on query_hash or sql_handle to find the specific query you’re investigating.

3. Integrate with Query Store

Query Store in SQL Server 2019 also taps into this functionality. You can cross-reference the Query Store runtime statistics and the last known actual plan for deeper insight into query performance trends:

WITH hist AS (
    SELECT q.query_id,
           q.query_hash,
           MAX(rs.max_duration) AS MaxDuration
    FROM sys.query_store_query q
         INNER JOIN sys.query_store_plan p ON q.query_id = p.query_id
         INNER JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
         INNER JOIN sys.query_store_runtime_stats_interval rsi ON rs.runtime_stats_interval_id = rsi.runtime_stats_interval_id
    WHERE start_time < DATEADD(HOUR, -1, GETDATE())
    GROUP BY q.query_id, q.query_hash
),
recent AS (
    SELECT q.query_id,
           q.query_hash,
           MAX(rs.max_duration) AS MaxDuration
    FROM sys.query_store_query q
         INNER JOIN sys.query_store_plan p ON q.query_id = p.query_id
         INNER JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
         INNER JOIN sys.query_store_runtime_stats_interval rsi ON rs.runtime_stats_interval_id = rsi.runtime_stats_interval_id
    WHERE start_time > DATEADD(HOUR, -1, GETDATE())
    GROUP BY q.query_id, q.query_hash
),
regressed_queries AS (
    SELECT hist.query_id,
           hist.query_hash
    FROM hist
    INNER JOIN recent ON hist.query_id = recent.query_id
    WHERE recent.MaxDuration > 1.2 * hist.MaxDuration
)
SELECT st.text,
       OBJECT_NAME(st.objectid) AS ObjectName,
       qs.last_execution_time,
       qps.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
OUTER APPLY sys.dm_exec_query_plan_stats(qs.plan_handle) qps
WHERE query_hash IN (SELECT query_hash FROM regressed_queries);

This sample identifies queries that have regressed in the last hour (recent runtime is more than 20% higher than historical), then retrieves their last known actual query plans.

Real-World Scenarios Where This Helps

• Parameter Sniffing: Quickly confirm if certain parameters led to unexpectedly large row counts or a poor join strategy.
• Operator Missteps: Identify if a Nested Loops operator actually processed millions of rows instead of the estimated thousands, pointing to a suboptimal plan choice.
• Production Performance Dips: See exactly how a query was running most recently, instead of waiting and hoping the performance issue reoccurs.

Best Practices

1. Combine with Query Store:
   • Use Query Store to track performance trends and the Last Known Actual Query Plan for real-time insights into problematic queries.
2. Test with DBCC CLONEDATABASE (Optional):
   • If you need to replicate production issues, use a cloned copy of your database with schema and statistics. This approach ensures minimal risk to the live environment.
3. Monitor Overhead:
   • The feature is designed to be lightweight, but always monitor system performance after enabling any new diagnostic tool.
4. Regular Baselines:
   • Baseline query performance using the combination of Query Store and Last Known Actual Query Plan. This makes deviations more obvious.

Conclusion

SQL Server 2019’s Last Known Actual Query Plan is a significant leap forward in query troubleshooting. By capturing real execution details with minimal overhead, it allows DBAs and developers to diagnose issues more accurately and quickly. Whether you’re dealing with parameter sniffing or unexpected plan regressions, this feature provides an immediate view into what actually happened—no guesswork required.

If you’re using SQL Server 2019 or considering an upgrade, enable this feature to streamline your performance investigations. It’s a powerful addition to your toolbox that lets you stop guessing and start fixing.