Getting the actual execution plan, that is the plan with run-time statistics for a query from an application has always been a little difficult. It’s fine if you can get the query running in Management Studio and reproducing the behaviour from the app, but that can be difficult.

There’s the query_post_execution_showplan event in Extended Events, but that’s a pretty heavy event and not something that I’d like to run on a busy server.

No more! SQL 2019 adds a new plan-related function to get the last actual plan for a query: sys.dm_exec_query_plan_stats.

The function is not available by default, Last_Query_Plan_Stats database scoped configuration has to be set  to allow it to run, and it’s going to add some overhead, how much is still to be determined.

ALTER DATABASE SCOPED CONFIGURATION SET LAST_QUERY_PLAN_STATS = ON

It’s a function which takes a parameter of a plan handle or a sql handle. Hence it can be used alone, or it can be on the right-hand side of an apply from any table or DMV that has a plan handle or sql handle in it. As an example it can be used with QueryStore.

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, 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, 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)

The above query checks query store for any query that has regressed in duration in the last hour (defined as max duration > 120% of previous max duration) and pulls the last actual plan for that query out.

And a look at that plan tells me that I have a bad parameter sniffing problem, a problem that might have been missed or mis-diagnosed with only the estimated plan available.

Previously I looked at using Query Store to compare execution plans, but it’s not the only way that two execution plans can be compared. The other method requires a saved execution plan and the Management Studio execution plan viewer.

Let’s start by assuming I have a saved execution plan for a query and I want to compare it to the execution plan that the same query currently has. First step is to run the query with actual execution plan on. Right-click on the execution plan and select ‘Compare Showplan’

Pick a saved execution plan. It doesn’t have to be for the same query, but the comparison will be of little use if the two plans being compared are not from the same query.

And then we get the same comparison screen as we saw last time with the comparison via Query Store. Similar portions of the plan are marked by coloured blocks, and the properties window shows which properties differ between the two plans.

One feature that was added in the 2016 version of SSMS that hasn’t received a lot of attention, is the ability to compare execution plans.

There’s two ways of doing this, from Query Store and from saved files.

Let’s start with Query Store, and I’m going to use a demo database that I’ve been working on for a few months – Interstellar Transport (IST). I’ve got a stored procedure in there that has a terrible parameter sniffing problem (intentionally). I’m going to run it a few times with one parameter value, then run it a few more times with another parameter value, remove the plan from cache and repeat the executions in the reverse order.

With that done, the query should show up in the ‘Queries with High Variance’ report (SQL 2017)

The query has the two expected plans, and they are quite different from each other.

I can click on the points on the graph individually to see the plans, but comparing the plans in that way is difficult and requires that I make notes somewhere else. What I can do instead is select two different points on the graph and chose the ‘compare plans’ option.

This brings up a window where the two plans are displayed one above the other, and areas in the plan which are similar are highlighted.

Select an operator and pull up the properties, and the properties of the operator from both plans are shown, with the differences highlighted.

This isn’t the only way to compare query plans. The next post will show how it can be done without using Query Store at all.