User Long running Sql Query Completion time

pdsqsql 391 Reputation points

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!

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
10,974 questions
4,388 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Erland Sommarskog 87,761 Reputation points

    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:

    0 comments No comments

  2. pdsqsql 391 Reputation points

    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)
    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
    0 comments No comments