SQL Server - report query progress with sys.dm_exec_requests

Question

Is there a way to report a percentage progress of an own query using a sys.dm_exec_requests field: percent_complete? msdn link

It works quite well for a BACKUP/RESTORE/SHRINK operations, for example:

SELECT percent_complete FROM sys.dm_exec_requests WHERE command = 'DbccFilesCompact'

25.677

but can it be used with user queries?

Answer 1

You MAY be able to get a VERY ROUGH approximation comparing the query plans estimated count vs the actual rows in sys.dm_exec_query_profiles.

This will be a very bad indicator if the statistics are not current or if the selected query plan is 'sub-optimal'.

And as noted, this DMV may not be available to you, as it is 2014+ specific.