October 9, 2016 at 8:35 pm
Hello folks,
I was reviewing a execution plan which I found from procedure cache. I found all details except - How much time it took to complete query?
Is there anyway to find query completion time (how much time query took to complete) FROM Actual Query Plan?
Ram
MSSQL DBA
October 10, 2016 at 12:31 am
Ramdas Baghel (10/9/2016)
Hello folks,I was reviewing a execution plan which I found from procedure cache. I found all details except - How much time it took to complete query?
Is there anyway to find query completion time (how much time query took to complete) FROM Actual Query Plan?
Quite certain that this information is not in the execution plan so you may be better of using the management view sys.dm_exec_query_stats which has the plan_handle of the execution plan.
😎
This query brings back all columns from sys.dm_exec_query_stats, sys.dm_exec_query_plan and sys.dm_exec_sql_text respectfully.
SELECT
DEQS.sql_handle
,DEQS.statement_start_offset
,DEQS.statement_end_offset
,DEQS.plan_generation_num
,DEQS.plan_handle
,DEQS.creation_time
,DEQS.last_execution_time
,DEQS.execution_count
,DEQS.total_worker_time
,DEQS.last_worker_time
,DEQS.min_worker_time
,DEQS.max_worker_time
,DEQS.total_physical_reads
,DEQS.last_physical_reads
,DEQS.min_physical_reads
,DEQS.max_physical_reads
,DEQS.total_logical_writes
,DEQS.last_logical_writes
,DEQS.min_logical_writes
,DEQS.max_logical_writes
,DEQS.total_logical_reads
,DEQS.last_logical_reads
,DEQS.min_logical_reads
,DEQS.max_logical_reads
,DEQS.total_clr_time
,DEQS.last_clr_time
,DEQS.min_clr_time
,DEQS.max_clr_time
,DEQS.total_elapsed_time
,DEQS.last_elapsed_time
,DEQS.min_elapsed_time
,DEQS.max_elapsed_time
,DEQS.query_hash
,DEQS.query_plan_hash
,DEQS.total_rows
,DEQS.last_rows
,DEQS.min_rows
,DEQS.max_rows
,DEQS.statement_sql_handle
,DEQS.statement_context_id
,DEQS.total_dop
,DEQS.last_dop
,DEQS.min_dop
,DEQS.max_dop
,DEQS.total_grant_kb
,DEQS.last_grant_kb
,DEQS.min_grant_kb
,DEQS.max_grant_kb
,DEQS.total_used_grant_kb
,DEQS.last_used_grant_kb
,DEQS.min_used_grant_kb
,DEQS.max_used_grant_kb
,DEQS.total_ideal_grant_kb
,DEQS.last_ideal_grant_kb
,DEQS.min_ideal_grant_kb
,DEQS.max_ideal_grant_kb
,DEQS.total_reserved_threads
,DEQS.last_reserved_threads
,DEQS.min_reserved_threads
,DEQS.max_reserved_threads
,DEQS.total_used_threads
,DEQS.last_used_threads
,DEQS.min_used_threads
,DEQS.max_used_threads
,DEQP.dbid
,DEQP.objectid
,DEQP.number
,DEQP.encrypted
,DEQP.query_plan
,DEST.dbid
,DEST.objectid
,DEST.number
,DEST.encrypted
,DEST.text
FROM sys.dm_exec_query_stats DEQS
OUTER APPLY sys.dm_exec_query_plan(DEQS.plan_handle) DEQP
OUTER APPLY sys.dm_exec_sql_text(DEQS.sql_handle) DEST
WHERE DEQP.dbid = DB_ID(N'DATABASE_NAME');
October 10, 2016 at 2:38 am
Ramdas Baghel (10/9/2016)
Is there anyway to find query completion time (how much time query took to complete) FROM Actual Query Plan?
No, there is not.
Besides, a plan extracted from cache is an estimated plan, not an actual. It has no run-time information in it
The DMV that Eirikur mentioned can give you the average and max execution times, but that's all you can get from the DMVs. If you are on SQL 2016, turn the Query Store on. While it still gives aggregated information, it aggregates over small periods of time (default an hour), not the 'since the query's plan was cached' which is what the DMV gives you.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 13, 2016 at 10:57 am
thanks Folks!
Ram
MSSQL DBA
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply