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:

• BACKUP
• RESTORE
• ALTER INDEX … REORGANIZE (not REBUILD)
• ROLLBACK
• 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:

• #354545 : DOC : sys.dm_exec_requests topic enhancements

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.