Elapsed Time in QEP returned from sys.dm_exec_query_plan_stats is different from the one in Actual Execution Plan returned by SSMS

Question

I am trying to use the LAST_QUERY_PLAN_STATS property to save the QEP Actual of each executed statement in my repository, using DMV sys.dm_exec_query_plan_stats I am able to intercept it and save it without any problems.

However, when I open one of them to check the elapsed timer in the properties tab, I find an elapsed times different from the ones found in statistics and in the QEP Actual requested at run time.

Am I reading them wrong or it's an expected behavior? Do you have any ideas?

I can reproduce it with a simple script:

My script with statistics time

The same script, but the tab with Execution plan requested run time

Here is how I obtained the QEP from sys.dm_exec_query_plan_stats:

select
   [qt].[text],
   [query_plan] = (SELECT TOP 1 [query_plan] FROM sys.dm_exec_query_plan_stats (a.[plan_handle])),
   [last_elapsed_time] = a.[last_elapsed_time] / 1000.0,
   a.[last_execution_time]
from (
SELECT *
FROM sys.dm_exec_query_stats qs
WHERE creation_time > '2024-07-01 08:00:00') a
CROSS APPLY (SELECT * FROM sys.dm_exec_sql_text(a.[plan_handle]) t ) qt
WHERE qt.[text] LIKE '--My script%'
order by 4 desc

This is the QEP, with the strange Elapsed Time:

This is the comparison, when we can see that the two QEP are identical so I don't get why they have different Elapsed Time

Answer 1

No need to fiddle with LAST_QUERY_PLAN_STATS. You can see this difference with just enabling actual execution plan and run the query.

There is a very simple reason for the big difference you get. It is called SSMS. That is, it takes time to collect and render all those rows. And that time is included in the total execution time. However, the query execution ends much earlier.

I would recommend that you try this with a more complex query that produces less output. Or for that matter, change the query to SELECT INTO. There will still be a difference, but not that radical

Answer 2

Hi @Andrea D'Elia

Refer to this doc: sys.dm_exec_query_plan_stats (Transact-SQL)

When the query is simple enough, it is usually categorized as part of an OLTP workload.

A simplified Showplan output is returned in the column of the returned table for: query_plansys.dm_exec_query_plan_stats

Try some queries that are complex enough.

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".