User Long running Sql Query Completion time

Question

Hello,
I would like know the completion time of Sql Long query as sometimes user trying to fetch the report or running Sql query and which is blocking the other process.
I have query for Administrative Query for DBCC, Backup, Index Maintenance etc but don't know the other select type of user query running and need to know the completion time before I kill it that session.
From other DMV queries, I can track or find the session and what kind of Sql statement is running but not the estimated completion time.
I used to run for Backup/ Restore Progress or during Index Rebuild but that query not providing any result when I am trying to run during other non-Admin long query to find it out the percent complete.

Thank you!

Answer 1

Unfortunately, there is no simple answer here.

If you have light-weight query profiling enabled, you can use sys.dm_exec_query_statistics_xml to get an execution plan with actual values so far. This can give you some idea of the progress. But it takes quite some experience to make those interpretation from the information. I have rarely tried this myself.

And you may find that all operators up to that Sort operator in the middle of the plan has complete, but non of the operators to the left. How long that Sorting will take - that's not easy to say.

Light-weight query profiling is enabled by default. On SQL 2016 and SQL 2017, you need to enable TF7412.

A second option is to use a Live Query Plan, which will give you more or less the same information, but you can see the numbers being updated. There is also tooling for this in SSMS. I have written about it in this section: https://www.sommarskog.se/query-plan-mysteries.html#livequeryplan.

Answer 2

Thanks Erland for the information.
query running by some tool.

Following Query somehow helpful.

SELECT er.start_time, datediff(ms, er.start_time, getdate()) elapsed_time_ms, datediff(ms, er.start_time, getdate())/60000 elapsed_time_MIN,
er.session_id, er.wait_type, er.wait_time,
SUBSTRING(st.text, (er.statement_start_offset/2)+1,
((CASE er.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE er.statement_end_offset
END - er.statement_start_offset)/2) + 1) AS statement_text,
CONVERT(XML, qp.query_plan) query_plan,
CASE WHEN sum(eqp.estimate_row_count) > 0 THEN
100 * SUM(eqp.row_count)/SUM(eqp.estimate_row_count)
ELSE
NULL
END AS percent_done
FROM sys.dm_exec_requests er
INNER JOIN sys.dm_exec_query_profiles eqp ON er.session_id = eqp.session_id
AND er.request_id = eqp.request_id
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) st
CROSS APPLY sys.dm_exec_text_query_plan(er.plan_handle, er.statement_start_offset, er.statement_end_offset) qp
WHERE er.session_id > 50 and er.session_id != @@spid
GROUP BY er.start_time, er.session_id, er.wait_type, er.wait_time, st.text,
er.statement_start_offset, er.statement_end_offset, qp.query_plan
HAVING SUM(eqp.estimate_row_count) > 0