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