What if the Actual Execution Plan was always available for any query?
Published Apr 03 2019 07:00 AM 15.2K Views
Microsoft

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.

 

Scenario

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 requestssys.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 planlast 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 planssys.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 queryComplex 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.

 

Notes

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

5 Comments
Copper Contributor

Amazing!

Copper Contributor

Great feature!

What's the performance impact for enabling it? Does it collect performance stats synchronously at run time?

Copper Contributor

So this is a preference under Tools/Options?  That would be cool if it was a permanent option for SSMS, though I too wonder about the performance impact

Microsoft

@LeoHu it collects stats based on lightweight profiling (link to docs on this feature are in the post). That said, it is an opt-in feature under preview precisely because there's only so much we can measure in our labs, and we count on you to help us determine if the expected low cost is true for different scenarios.

 

@jwms-1 You enable the feature via trace flag (in the post) and then just access it with the new DMF. SSMS is then able to render the XML into a graphical plan.

 

 

Copper Contributor

Can you do this with SSMS and when using MS Azure?

 

Can you point me to further articles you may have published on this topic?

 

This is EXACTLY what I am looking for to help me understand the cause of long running queries with MS Access as a front end and Azure as the back end.

I am interested in seeing how well Access adds selection criteria to the query request to minimize the amount of data returned (and maybe even processed on the server?).

Bob

Version history
Last update:
‎Apr 24 2019 10:55 AM
Updated by: