SQL Server Task Status

Have you ever been performing a DBCC SHRINKFILE and wondered how long it was going to take?  The Backup/Restore screen shows percent complete, how about DBCC CHECKDB?

Here's a neat query that can help with that:

Select * from sys.dm_exec_requests

This query returns information about every request that the SQL Server is executing.

One of the columns returned is Percent_Complete.  However, this column is only populated for the following commands:

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

I have four large databases that I have to shrink when they are copied to the development.  These can take quite a while, so I use this query to monitor status.

Check out the documentation for more details!

peace

Performance Tuning and Cache

When tuning queries and indexes for performance, it's a good idea to clear the cache when running benchmarks.
For example, when you first run a query, a plan is created, and then data is fetched from the drive.
On the next execute, your query plan won't need to be created, and the results will probably come from the cache, giving a false indication of how long the query will run.

So to clear the cache, execute these two statements:

DBCC DROPCLEANBUFFERS

DBCC FREEPROCCACHE

Then you can test with each execution being treated equally.

Take care on the FreeProcCache, as this is a server level command.   Do not run this on a production box unless you really know what you are doing!

peace