28

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 of 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.

Rohit Sharma
  • 3,304
  • 2
  • 19
  • 34
Roni Vered
  • 747
  • 2
  • 8
  • 18
  • Hi Roni, to clarify, are you looking to monitor the progress of a query that is performing a backup / restore? Or a query in general? – Alex KeySmith Jan 03 '13 at 11:47

4 Answers4

29

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:

enter image description here

This then gives you a live query plan:

enter image description here

At the bottom you see the total progress:

enter image description here

John
  • 6,693
  • 3
  • 51
  • 90
  • 4
    I feel like this may negatively impact query performance somehow. I mean - why would it otherwise be impossible to obtain the progress information without enabling live query plans? EDIT: Turns out there's a big fat warning label on the [live query stats documentation](https://learn.microsoft.com/en-us/sql/relational-databases/performance/live-query-statistics?view=sql-server-2017). Indeed, it does negatively impact performance. Use with caution! – Dan Nov 22 '18 at 19:06
20

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.

Rohit Sharma
  • 3,304
  • 2
  • 19
  • 34
KM.
  • 101,727
  • 34
  • 178
  • 212
  • Indeed. On some (badly configured/overloaded) systems, a single intensive query can lock a crucial table - and all of a sudden, queries that usually finish under a second run for tens of minutes. You could watch the query times and provide a guess - "on average, this query completes in 5:41, it's been running for 3:15 now", but it's just that: a guess (not even an estimate). – Piskvor left the building Jul 20 '10 at 12:14
  • 1
    I've seen 3D rendering screen savers running on production database boxes! – KM. Jul 20 '10 at 12:16
  • In Oracle for example, we have the view v$session_longops, which displays the status of operations. http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/dynviews_2092.htm I you sure there's no similar thing in SQL SERVER ? Roni. – Roni Vered Jul 20 '10 at 12:22
  • @Roni Vered, there are numerous ways to get info from SQL Server about running queries, but no "TIME_RAMINING" value. You may be able to calculate an average value based on previous runs, see: http://stackoverflow.com/questions/617170/query-duration-estimation-in-sql-server – KM. Jul 20 '10 at 13:11
  • 3
    The only people that don't already expect time estimates to fluctuate based on server load are very new to computers indeed. Accepting that a time estimate is an estimate then try `SELECT percent_complete FROM sys.dm_exec_requests`, which can then be used to estimate how much time the query *should* have left to complete. – WonderWorker Aug 30 '18 at 13:13
20

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%'
Andrey.ca
  • 217
  • 2
  • 2
  • 4
    This query is only useful if you have VIEW SERVER STATE permission. If you do not you will only see the results for the SELECT statement you just submitted, which is a bit useless! To establish if you have permissions run the following "SELECT * FROM fn_my_permissions(NULL, 'SERVER');" - If you don't see an entry with permission_name 'VIEW SERVER STATE' then you don't have the permission. – samaspin Mar 13 '13 at 12:25
  • 11
    Also, this information is provided only when specific commands are being executed. These are for example `ROLLBACK`, `RECOVERY` or `BACKUP DATABASE`. – user35443 Nov 09 '13 at 17:11
6

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"
Abin
  • 2,868
  • 1
  • 23
  • 59
JameSQL
  • 89
  • 1
  • 5
  • sys.dm_exec_sessions.total_elapsed_time tells you the time since a session was established. This has nothing to do with the (estimated) remaining time of a SQL statement, which was what the OP asked about. – David Liebeherr Aug 23 '22 at 00:06
  • iif(req.percent_complete>0, (((100/req.percent_complete)-1)*(req.total_elapsed_time/1000))/60/60,0) HoursLeft – IvoTops Aug 15 '23 at 08:36