How Long is that SQL Command Going to Take?

Have you ever needed to restore a large database while someone is standing over your shoulder asking “How long is that going to take"? If that hasn't happened to you yet, then it’s only a matter of time.

Let’s throw out all the reasons why you need to do the restore and just discuss the technical part. Obviously the easiest way to know how long the restore will take is to use the “WITH STATS” option in the restore database command. But let’s say in the heat of the moment you forgot that little piece of the statement. Now what?

In older versions of SQL Server, there was really no way to tell how long a database restore operation would take to complete. You could make a rough guesstimate that if it took SQL Server one hour to backup the database, then it’s likely the restore would take the same amount of time. But in reality, that’s just a guess, and the person standing over your shoulder probably wants a more accurate estimation.

First introduced in SQL Server 2005, DMVs give us a wealth of new information on the internals of SQL Server, and for our dilemma above, we can use sys.dm_exec_requests. This DMV returns one row for each session that actively executing a command. One of the columns returned by the DMV is percent_complete which returns the percent complete for the currently executing command.

USE master;
GO
SELECT
   session_id
  ,start_time
  ,status
  ,command
  ,percent_complete
FROM sys.dm_exec_requests
WHERE session_id = 56;
GO

It looks like the database restore is about 33% complete. Now you have a more accurate idea of how long it will take to complete.

This may seem like a useless tidbit of information, since you can use the “WITH STATS” option in the restore database command to get the same information, but what happens when the command your running doesn't have that option; for example, DBCC SHRINKFILE.

On the rare occasion when you need to shrink a database to free up disk space, SQL Server needs to physically move data pages from one part of the file to another. Depending on how much data needs to be moved, this could take a long time. Using the same TSQL statement from above, we can query the sys.dm_exec_requests DMV.

This is awesome! A percent complete value for every command executing on SQL Server? Not so fast. The percent_complete column in sys.dm_exec_requests only works a few commands.

From Books Online:

• ALTER INDEX REORGANIZE
• AUTO_SHRINK option with ALTER DATABASE
• BACKUP DATABASE
• CREATE INDEX
• DBCC CHECKDB
• DBCC CHECKFILEGROUP
• DBCC CHECKTABLE
• DBCC INDEXDEFRAG
• DBCC SHRINKDATABASE
• DBCC SHRINKFILE
• KILL (Transact-SQL)
• RESTORE DATABASE
• UPDATE STATISTICS

What a bummer that it doesn't work for every command. But from a DBA’s point of view, this list comprises quite a few of the administrative commands you’d use on a regular basis. These are commands that you would run instead of an end user, and knowing that you can relay a “percent complete” value back to them will assure them you are taking good care of their database.

For more details on this DMV, check out Books Online.