How to check progress / estimated percentage done for a SELECT query / statement in SQL Server

Assuming you have a long-running SELECT statement
or SELECT query and you want to see its progress,
like how many percents already done
(percentage done/the percentage of work completed
/Progress (% Complete)).



Is there a way to see the progress of a SELECT statement
in SQL Server?

I will say it again, we are talking about a SELECT query and not operations that
populate the “percent_complete” field in sys.dm_exec_requests

Like below:

ALTER INDEX REORGANIZE
AUTO_SHRINK option with ALTER DATABASE
BACKUP DATABASE
DBCC CHECKDB
DBCC CHECKFILEGROUP

DBCC CHECKTABLE
DBCC INDEXDEFRAG
DBCC SHRINKDATABASE
DBCC SHRINKFILE
RECOVERY
RESTORE DATABASE
ROLLBACK
TDE ENCRYPTION

Well, there is a way (with some limitations, of course:)).
it is not perfect, but it will definitely give you some visibility.

The approach is to compare the estimate row counts
vs the actual row counts for each node in the execution plan.

It will not work accurately in case you have an incorrect estimated number of rows
Also if there are some lock waits (blocking), they will break the calculations.

To use this query/approach you need to be on SQL Server 2019 (15.x)
Or to enable trace flag 7412 (lightweight profiling) starting from SQL 2016 (13.x) SP1
This trace flag will tell SQL Server to expose additional information
For each node in the execution plan, like how many rows already processed.

Note: Lightweight profiling is enabled by default in SQL Server 2019 (15.x)

SELECT er.session_id,

er.start_time, 

DATEDIFF(ms,er.start_time,GETDATE()) AS elapsed_time_ms,

DATEDIFF(ss,er.start_time,GETDATE()) AS elapsed_time_Sec,

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

Try it 😊
Of course, it works also for SELECT statements with multiple JOINs.

Source:
http://blog.sqlgrease.com/how-to-view-percent-done-of-a-long-executing-query/?unapproved=27776&moderation-hash=d582e3993f3b1f9ed5a1e1c3dc012222#comment-27776