Monitoring the progress of an SQL query in SQL SERVER

I saw a similar question which asked how to monitor the progress of a backup/restore operation: Is there a SQL script that I can use to determine the progress o开发者_开发问答f a SQL Server backup or restore process?

I would like to know if there's a similar query/way to see how much time the query has left until it will end. For example, one query usually has an elapsed time of 5 minutes. I would like to know how much time is left until it will end DURING the query's execution.

What you want are Live Query Statistics.

You can activate it in the most recent version of SSMS with a button next to the one that gives you the normal query plan:

This then gives you a live query plan:

At the bottom you see the total progress:

There is no way to know how much time is left. A query's runtime depends on many things beyond the actual query itself: locking/blocking of other queries, other processes consuming resources (CPU/disk usage), the operating system, network, etc. What if your 5-minute query is running, yet someone else kicks off a large report, your query may run 5:30 now. What if the someone starts to download a large file and hogs all the network bandwidth? What if the OS decides to do something in the background, etc. Until all the rows are returned, the query isn't done, but it can run in a variable time frame.

sys.dm_exec_requests has that info, so something like that will give you the progress:

SELECT 
percent_complete
FROM sys.dm_exec_requests
--where session_id=51 or command like 'restore%'

Yes you can know the estimated elapsed time unless there would be some unexpected situation affecting the execution of the process.

Select total_elapsed_time,
 * from sys.dm_exec_sessions where session_id="your Id here"