When will my backup / restore / index reorganize finish?
The DMVs are a great way to get a closer look at what is actually going on in your system. In particular, for some operations, SQL Server populates the percent_complete and estimated_completion_time (in milliseconds) in the DMV sys.dm_exec_requests. This can be very useful if you want to know how long your BACKUP or RESTORE will take (if you didn't use WITH STATS or are using command-line or a 3rd party tool), or whether you should go to lunch while your ROLLBACK is, well, rolling back.
I've blogged about this in the past, but I frequently find it useful to come back and use this same type of query for things I'm working on today. For example, I am setting up log shipping for a largish database this afternoon, and I wanted to monitor its progress. Then I thought to expand my query to include any other query whose progress I *could* monitor. And then I thought to include a couple of other interesting data points, such as host name / I address, the actual command, and which procedure the command came from (if applicable). And then I thought, hey, maybe that would be useful to other people as well. So here is the script I have refreshed several times impatiently while writing this post:
SELECT r.[session_id], c.[client_net_address], s.[host_name], c.[connect_time], [request_start_time] = s.[last_request_start_time], [current_time] = CURRENT_TIMESTAMP, r.[percent_complete], [estimated_finish_time] = DATEADD ( MILLISECOND, r.[estimated_completion_time], CURRENT_TIMESTAMP ), current_command = SUBSTRING ( t.[text], r.[statement_start_offset]/2, COALESCE(NULLIF(r.[statement_end_offset], -1)/2, 2147483647) ), module = COALESCE(QUOTENAME(OBJECT_SCHEMA_NAME(t.[objectid], t.[dbid])) + '.' + QUOTENAME(OBJECT_NAME(t.[objectid], t.[dbid])), '<ad hoc>') FROM sys.dm_exec_requests AS r INNER JOIN sys.dm_exec_connections AS c ON r.[session_id] = c.[session_id] INNER JOIN sys.dm_exec_sessions AS s ON r.[session_id] = s.[session_id] CROSS APPLY sys.dm_exec_sql_text(r.[sql_handle]) AS t WHERE r.[percent_complete] <> 0;
As a recap, the types of operations that are exposed in the percent_complete and estimated_completion_time are:
- ALTER INDEX … REORGANIZE (not REBUILD)
- Many DBCC commands
These are not documented at all, so I thought I would take this opportunity to point you to a still-active Connect item I filed over two years ago:
#284207 : DOC : provide more info on percent_complete (sys.dm_exec_requests)
I also think that the Books Online topic "sys.dm_exec_requests," which had examples in SQL Server 2005 that were ripped out in SQL Server 2008, could be enhanced with some more useful examples:
Fellow MVP Grant Fritchey just wrote about an interesting and should-be-obvious-but-isn't approach to tracking statement-level execution within a batch – periodically storing the last_request_start_time so you could go back and map out exactly how long each request in a batch took. I'd be lying if I said his post didn't inspire this one in some ways… mostly to separate the batch-level start time from the statement-level start time (this makes the percent_complete calculation make a lot more sense). I don't have the time or motivation today to expand on his post, but I hope to do so sometime in the near future.
As an aside, do you find this kind of post useful, boring, something else? I often feel motivated to share the kinds of things I am doing from day to day, but I'd love to hear feedback about what kinds of things *you* want to read about.
Wonderful script Aaron. I had cobbled a much more basic version together, but like yours much better.
I can note that one of the commands that "reports" back, in SQL 2005, is DBCC shrink (the actual command in the exec is compressFile, if I recall correctly).
However, the accuracy of the "time remaining"/"% complete" can be very far out. Some examples I've had have shot through to 90% (5 minutes), then taken another 1h00 to complete, or error. And I've seen a case with a colleague who had a case where a 3h00+ shrink was at 30% – we predicted another 7 hours to go … and 30 minutes later it was done.
Linchi, I haven't tested how accurate it is on ROLLBACK. It's on my list, but it's a long list with priority seemingly changed daily and ordered by NEWID().
Paul, thanks for reminding me about 2000 compatibility. Since all but one of my systems is either 2005, 2008, or in the process of moving from 2005 to 2008, it is easy to forget about the leftovers other folks are often stuck with.
Yes I do find these interesting, day to day stuff is what most of us do ( I guess ) and anything which helps is always good. I spent some considerable time working on a client request in sql 2005 for the ability to know how long user queries had to run and could you identify potentially harmful queries which might slow the system etc. etc. In the end I wasn't able to satisfy this request for such a dashboard but it did open other avenues.
Thanks for the post. This could prove useful hunting down recalcitrant processes.
It should be noted that, because of the CROSS APPLY, this won't run against a database at compatability level 8. It fails with the unhelpful error "Incorrect syntax near '.'."
I guess the progress meter for BACKUP/RESTORE can be quite accurate. I wonder if you or anybody else have any empirical data/experience on how accurate this DMV entry is on ROLLBACK.