Troubleshooting production performance issues is often challenging because detailed query execution data can vanish before you realize there\u2019s a problem. Many times, you end up piecing together incomplete clues from runtime statistics or the plan cache. Fortunately, SQL Server 2019<\/strong> introduced a powerful feature to address this pain point: the Last Known Actual Query Plan<\/strong>.<\/p>\n SQL Server 2019<\/strong> leverages lightweight query execution statistics profiling to capture the last known actual execution plan for a query. This plan includes critical runtime details\u2014like actual rows processed for each operator\u2014without the overhead of full profiling or tracing. You can think of it as a \u201csnapshot\u201d 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.<\/p>\n Why does this matter?<\/strong><\/p>\n Before SQL Server 2019\u2019s enhancement, DBAs often used the following workarounds:<\/p>\n With the Last Known Actual Query Plan<\/strong> feature, you can directly examine runtime metrics of the most recent query execution. This means:<\/p>\n Enable the feature at the database level:<\/p>\n 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.<\/p>\n Use dynamic management objects to query the stored plan data. For example:<\/p>\n Query Store<\/strong> in SQL Server 2019 also taps into this functionality. You can cross-reference the Query Store<\/strong> runtime statistics and the last known actual plan for deeper insight into query performance trends:<\/p>\n 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.<\/p>\n SQL Server 2019\u2019s Last Known Actual Query Plan<\/strong> 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\u2019re dealing with parameter sniffing or unexpected plan regressions, this feature provides an immediate view into what actually happened\u2014no guesswork required.<\/p>\n If you\u2019re using SQL Server 2019 or considering an upgrade, enable this feature to streamline your performance investigations. It\u2019s a powerful addition to your toolbox that lets you stop guessing and start fixing<\/strong>.<\/p>\n","protected":false},"excerpt":{"rendered":" Troubleshooting production performance issues is often challenging because detailed query execution data can vanish before you realize there\u2019s 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.<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[12,5,91,400,73],"tags":[421,269,422,43,322,131],"class_list":["post-899","post","type-post","status-publish","format-standard","hentry","category-internals","category-performance","category-query-optimization","category-query-store","category-troubleshooting","tag-actual-query-plan","tag-parameter-sniffing","tag-query-execution-plans","tag-query-performance","tag-query-store","tag-sql-server"],"yoast_head":"\nWhat Is the Last Known Actual Query Plan?<\/h3>\n
\n
Common Challenges in Query Troubleshooting<\/h3>\n
\n
\n
\n
\n
\n
How SQL Server 2019 Simplifies Troubleshooting<\/h3>\n
\n
How to Use the Feature<\/h3>\n
1. Enable Last Known Actual Query Plan<\/h4>\n
ALTER DATABASE SCOPED CONFIGURATION \nSET LAST_QUERY_PLAN_STATS = ON;<\/code><\/pre>\n2. Retrieve the Last Known Actual Query Plan<\/h4>\n
SELECT qps.query_plan,\n qs.execution_count,\n qs.total_elapsed_time\nFROM sys.dm_exec_query_stats qs\nCROSS APPLY sys.dm_exec_query_plan_stats(qs.plan_handle) qps\nWHERE qs.query_hash = HASHBYTES('SHA2_256', '');<\/code><\/pre>\n\n
sys.dm_exec_query_plan_stats(qs.plan_handle)<\/code> is the key function exposing the last known actual query plan.<\/li>\nquery_hash<\/code> or sql_handle<\/code> to find the specific query you\u2019re investigating.<\/li>\n<\/ul>\n3. Integrate with Query Store<\/h4>\n
WITH hist AS (\n SELECT q.query_id,\n q.query_hash,\n MAX(rs.max_duration) AS MaxDuration\n FROM sys.query_store_query q\n INNER JOIN sys.query_store_plan p ON q.query_id = p.query_id\n INNER JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id\n INNER JOIN sys.query_store_runtime_stats_interval rsi ON rs.runtime_stats_interval_id = rsi.runtime_stats_interval_id\n WHERE start_time < DATEADD(HOUR, -1, GETDATE())\n GROUP BY q.query_id, q.query_hash\n),\nrecent AS (\n SELECT q.query_id,\n q.query_hash,\n MAX(rs.max_duration) AS MaxDuration\n FROM sys.query_store_query q\n INNER JOIN sys.query_store_plan p ON q.query_id = p.query_id\n INNER JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id\n INNER JOIN sys.query_store_runtime_stats_interval rsi ON rs.runtime_stats_interval_id = rsi.runtime_stats_interval_id\n WHERE start_time > DATEADD(HOUR, -1, GETDATE())\n GROUP BY q.query_id, q.query_hash\n),\nregressed_queries AS (\n SELECT hist.query_id,\n hist.query_hash\n FROM hist\n INNER JOIN recent ON hist.query_id = recent.query_id\n WHERE recent.MaxDuration > 1.2 * hist.MaxDuration\n)\nSELECT st.text,\n OBJECT_NAME(st.objectid) AS ObjectName,\n qs.last_execution_time,\n qps.query_plan\nFROM sys.dm_exec_query_stats qs\nCROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st\nOUTER APPLY sys.dm_exec_query_plan_stats(qs.plan_handle) qps\nWHERE query_hash IN (SELECT query_hash FROM regressed_queries);<\/code><\/pre>\nReal-World Scenarios Where This Helps<\/h3>\n
\n
Nested Loops<\/code> operator actually processed millions of rows instead of the estimated thousands, pointing to a suboptimal plan choice.<\/li>\nBest Practices<\/h3>\n
\n
\n
\n
\n
\n
Conclusion<\/h3>\n