Well, what if the Actual Execution Plan was always available for any query? The good news is: it can be!
In SQL Server 2019 CTP 2.4, there’s a new DMF sys.dm_exec_query_plan_stats that allows you to always access the last actual execution plan for any query. But why do you want that? This is what this post is about.
Query execution plans, otherwise known as actual execution plans or just Showplan, are a treasure trove of information about how SQL Server executed the required operations to get the query output, which includes runtime performance statistics.
Think of a scenario where a query has just turned bad (as in its performance tanked), and you got a call informing the application has poor performance as a result. If it’s a long running query then you can use Live Query Statistics, but if it’s something that’s been executing repeatedly (and poorly) in the last few minutes, then accessing the *actual execution plan* allows you to troubleshoot that query immediately without starting any tracing. Note: I’m talking xEvents, no one should be using Profiler anymore :smiling_face_with_smiling_eyes:.
Of course, query plans are available through the Query Store (QS). However, QS aggregates performance data in configurable time periods, which is excellent to do performance troubleshooting over time (think time series), and analysis of workload trends (you can see more in Monitoring performance by using the Query Store).
For our current scenario we need to get the specific occurrence of the query’s execution plan to analyze and troubleshoot. Besides the xEvents we discussed before in this post which would require me to start a trace, I now have this new DMF to get the plan.
How does it work?
First, this DMF will only output information if trace flag 2451 is enabled or the new LAST_QUERY_PLAN_STATS database scoped configuration (just for CTP 2.5). If it is, then SQL Server leverages the lightweight query execution statistics profile infrastructure – or lightweight profiling for short (read more about it here).
You can enable trace flag 2451 globally even without restarting SQL Server using this command:
DBCC TRACEON(2451, -1);
With that, let’s see an example. My workload is running now, and I can use the new DMF together with other familiar DMVs/DMFs to quickly search for the last actual execution plan on queries that are currently executing. The point of entry here are the in-flight requests from sys.dm_exec_requests:
SELECT er.session_id, er.start_time, er.status, er.command, st.text, qp.query_plan AS cached_plan, qps.query_plan AS last_actual_exec_plan FROM sys.dm_exec_requests AS er OUTER APPLY sys.dm_exec_query_plan(er.plan_handle) qp OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st OUTER APPLY sys.dm_exec_query_plan_stats(er.plan_handle) qps WHERE session_id > 50 AND status IN ('running', 'suspended'); GO
Which yields the following result:
sys.dm_exec_query_plan_stats from in-flight requestsFrom where I can click any of the links in the last_actual_exec_plan column, and see the actual plan:
last actual executed planOr for queries that are not currently executing, but have executed before, where the point of entry may be the cached plans from sys.dm_exec_cached_plans:
SELECT DB_NAME(qp.dbid), cacheobjtype, objtype, qp.query_plan AS cached_plan, qps.query_plan AS last_actual_exec_plan FROM sys.dm_exec_cached_plans AS cp OUTER APPLY sys.dm_exec_query_plan(cp.plan_handle) qp OUTER APPLY sys.dm_exec_query_plan_stats(cp.plan_handle) qps; GO
Which yields the following result:
sys.dm_exec_query_plan_stats from cached plansAnd similarly, I can click any of the links in the last_actual_exec_plan column, and see the actual plan.
From there, I can start troubleshooting my query based on actual runtime statistics. Looking at the actual execution plan for something that has already executed, I have actionable insight: I can create a narrow index on Sales.SalesOrderDetail, or even think about creating an indexed view on my query that includes the complex expression highlighted below.
Complex expression in queryIn the Tiger Toolbox you can see a much more comprehensive script to report useful information on in-flight requests that uses this and other newer DMVs/DMFs, as well as stored procedures/triggers/functions/query stats. Also available in Jupyter Notebook format for use with Azure Data Studio.
In CTP 2.4 not all actual execution plans will be available, you can see more details on that here.
For CTP 2.5, mostly all queries will be available with the equivalent of the actual execution plan. At least those where the plan was cached in the first place, or those where the plan has not been evicted from cache.
Pedro Lopes ( @SQLPedro ) – Senior Program Manager