Search This Blog

Tuesday, July 22, 2008

Calcualting estimated time for any running SQL command

you can use the following SQL statement to calculate estimated time for any sql command. This script is applicable for Sql Server 2005 and above.

1) you can get the command label by executing below procedures or sql statement, that you will use in estimated time calculation query.

Exec sp_Who
Or
Exec sp_Who2
Or
Select * From sys.sysprocesses
Or
Select * From sys.dm_exec_requests

2) script for estimated time calculation, in example it is calculating time for back up command.
-------------------------------------
SELECT
R.SESSION_ID
,R.COMMAND
,CONVERT(NUMERIC(6,2),R.PERCENT_COMPLETE) AS [PERCENT COMPLETE]
,CONVERT(VARCHAR(20),DATEADD(MS,R.ESTIMATED_COMPLETION_TIME,GETDATE()),20) AS [ETA COMPLETION TIME]
,CONVERT(NUMERIC(6,2),R.TOTAL_ELAPSED_TIME/1000.0/60.0) AS [ELAPSED MIN]
,CONVERT(NUMERIC(6,2),R.ESTIMATED_COMPLETION_TIME/1000.0/60.0) AS [ETA MIN]
,CONVERT(NUMERIC(6,2),R.ESTIMATED_COMPLETION_TIME/1000.0/60.0/60.0) AS [ETA HOURS]
,CONVERT(VARCHAR(100)
,(SELECT SUBSTRING(TEXT,R.STATEMENT_START_OFFSET/2
,CASE WHEN R.STATEMENT_END_OFFSET = -1 THEN 1000 ELSE (R.STATEMENT_END_OFFSET-R.STATEMENT_START_OFFSET)/2 END)
FROM SYS.DM_EXEC_SQL_TEXT(SQL_HANDLE)))
FROM SYS.DM_EXEC_REQUESTS R
WHERE COMMAND IN ('BACKUP DATABASE')

---------------------------------

5 comments:

Faisall said...

Very helpful article. Keep up the good work Tariq.

WebIllusionist said...

It'd be nice if we you could post the OUtput for this query as well on this post...

Anonymous said...

Very Good!

Alexander said...

"any running SQL command" OR COMMAND IN ('BACKUP DATABASE')??
Very misleading!

greg polk said...

Thank you very much!!