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