I always wondered to have some ability to see how a command is progressing insider the SQL Server. It was always kind of frustrating that after I execute a simple command (like restore database), I am in a kind of black hole where I have no clue to see how the command execution is progressing. This always became more frustrating when other people (like developers) waits on me so that their environment becomes usable again and I have really no solid information to give them intermediate update on progress. (This is very true when you are restoring a big development database after something went horribly wrong)
Well, SQL 2005 onwards we have a solution now. Using sys.dm_exec_requests DMV gives us a inside scoop to see how the specific command execution is really progressing.
If you are interested to see real life use of this new feature, click below links to have direct examples…
To monitor how restore is progressing..
To monitor how much percentage of command execution is complete..
Lastly, if you are really interested to know about this DMV in details, visit http://msdn.microsoft.com/en-us/library/ms177648.aspx