SQL SERVER – View Percentage Completed for A Long Executing Query

Today we will see a query which I have written for my client who has recently upgraded to SQL Server 2019 and as soon as they upgraded to the new version they were getting slow performance for their query. We fixed their performance issue during the recent Comprehensive Database Performance Health Check. During the consultation, we needed to check the status (View Percentage) of the few queries and for that, I quickly wrote a query which we will see today.

The following query will work out of the box for SQL Server 2019. If you are running any other version of SQL Server, you may have to enable a lightweight query profiler with trace flag 7412 to make it work.

View Percentage Completed for A Long Executing Query

SELECT	er.session_id AS SessionID,
		DB_NAME(er.database_id) DatabaseName, er.start_time, 
		DATEDIFF(ss,er.start_time,GETDATE()) AS ElapsedTime_Sec,
		er.wait_type AS WaitType, 
		er.wait_time WaitTime_MS, 
		SUBSTRING(est.TEXT, (er.statement_start_offset / 2) + 1,
				((CASE er.statement_end_offset WHEN -1 
						THEN DATALENGTH(est.[TEXT]) 
						ELSE er.statement_end_offset END 
						- er.statement_start_offset) / 2) + 1) AS statement_text, 
		CONVERT(XML, qp.query_plan) QueryPlan, 
		100.0 * SUM(eqp.row_count) / SUM(eqp.estimate_row_count) AS PercentComplete
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) est
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, est.[TEXT], 
			er.statement_start_offset, er.statement_end_offset, 
			qp.query_plan, er.database_id
HAVING		SUM(eqp.estimate_row_count) > 0

Now when you run the query above, it will give you a list of all the long-running and long executing queries on your system and you can see the percentage completed on the very last column.

Here is the image from my own system for your reference. Please note that while this query sometimes gives inaccurate results but most of the time it has done fairly well for me on my client’s machine. Let me know how this query is behaving on your server. Meanwhile, do not forget to check out my popular service Comprehensive Database Performance Health Check. 

Here is a few relevant blog post on the same topic:

• SQL SERVER – Introduction to Log Space Usage DMV – sys.dm_db_log_space_usage
• DMV to Replace DBCC INPUTBUFFER Command – Interview Question of the Week #100
• SQL SERVER – DMV to Get Host Information – sys.dm_os_host_info
• SQL SERVER – Table Space Allocation Details using DMV
• SQL SERVER – New DMV in SQL Server 2017 – sys.dm_os_enumerate_fixed_drives. A Replacement of xp_fixeddrives

If you have any suggestions please leave a comment.

Reference: Pinal Dave (https://blog.sqlauthority.com)