Monitoring the progress of an SQL query in SQL SERVER

Question

Hello,

I saw a similar question which asked how to monitor the progress of a backup / restore operation: http://stackoverflow.com/questions/152447/is-there-a-sql-script-that-i-can-use-to-determine-the-progress-of-a-sql-server-ba

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 elapsed time of 5 minutes. I would like to know how much time is left until it will end DURING the query's execution.

Thanks in advance, Roni.

Answer 1

there is no way to know how much time is left. a query's run time 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 an variable time frame.