When Will Key SQL Server Processes End?

I grew up in the age of FM radio; back when there were still human disk jockeys and vinyl records. These disk jockeys had a very open secret when it come to long songs: long songs = freedom. That's right, why sit at the sound board playing three minute song after three minute song when you can cue up Led Zeppelin's Stairway to Heaven (8:02 minutes), The Eagle's Hotel California (6:30 minutes) or Rush's 2112 (20:34 minutes) then head out for a cigarette break, bathroom break, or perhaps something a bit more decadent.

Database professionals are busy people. We also spend a great deal of time at our desks. What if I told you, with a simple script that makes use of SQL Server's Dynamic Management Objects, you could get an idea how long certain key processes are going to take so that you could either multitask more successfully, or even better: get up from those desks and get some fresh air when you kick off a T-SQL equivalent of November Rain?

Request Type

There are some key processes that report the percent complete within the sys.dm_exec_requests dynamic management view. Using that information, coupled with the elapsed time the process has been running, will give you a fairly accurate ending time estimate. What are those processes? According to MSDN they are:

ALTER INDEX REORGANIZE
AUTO_SHRINK option with ALTER DATABASE
BACKUP DATABASE
DBCC CHECKDB
DBCC CHECKFILEGROUP
DBCC CHECKTABLE
DBCC INDEXDEFRAG
DBCC SHRINKDATABASE
DBCC SHRINKFILE
RECOVERY
RESTORE DATABASE
ROLLBACK
TDE ENCRYPTION

The Code

SELECT R.session_id, 
	R.percent_complete, 
	R.total_elapsed_time/1000 AS elapsed_secs, 
	DATEADD(s,100/((R.percent_complete)/ (R.total_elapsed_time/1000)), R.start_time) AS estim_completion_time,
	ST.text, 
	SUBSTRING
		(ST.text, R.statement_start_offset / 2, 
			(
				CASE WHEN R.statement_end_offset = -1 THEN DATALENGTH(ST.text)
				ELSE R.statement_end_offset
				END - R.statement_start_offset 
			) / 2
		) AS statement_executing
FROM sys.dm_exec_requests AS R
	CROSS APPLY sys.dm_exec_sql_text(R.sql_handle) AS ST
WHERE R.percent_complete > 0
	AND R.session_id <> @@spid;

The Code Explained

Per the introduction, I'm sourcing most of this information from the dm_exec_requests dynamic management view (aliased as R above). The base logic is that the rate of completion is consistent throughout the processing. This means that the estimated completion time is a direct corellation between the time elapsed since the process started and the percentage complete reported through R.percent_complete.

The only additional outside information included in the query comes from a CROSS JOIN out to the dm_exec_sql_text dynamic management function which returns statement text based from a sql_handle parameterized GUID. I also eliminate any results coming from the current session through the final line where I'm evaluating the R.session_id predicate (comparing it to the @@spid system variable which returns the current session_id number.)

Summary

Armed with this information you can unchain yourself from your desk and get that extra cup of coffee, stretch, perhaps take a walk outside or even move on to some other task. Cue up that database restore because, unlike what Don Henley told us, we CAN check out anytime we like.

Comments

Plain text