SQL Server – How to Measure Query Execution Time

querysql server

I am querying a SQL server 2008 database table having more than 500 million records.
The query I fired to move around 10 million records to temp table took 9 hours.
Similar query I fired which has been going for more than 10 hours.

How to know:

  1. time it will take to complete the running query
  2. time it will take to complete query before execution

Best Answer

Certain statements report the percent complete in sys.dm_exec_requests percent_complete column:

Percentage of work completed 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

For other statement, you'll have to wait it out and see.

First of all you'd have to check whether the 'query' is making any progress or is just sitting idle, blocked b something else. The same DMV mentioned above will immediately reveal this, the wait_time, wait_type and wait_resource are a dead give away: if they change, its making progress, if they stay fixed (and wait_time is growing) the query is stuck and the blocking _session_id is the blocker.

If the query was indeed making progress, and is one single query that is moving the rows, I gotta say: one does not go around and blindly moves 10M rows. If you did this in one single transaction, then your transaction log has been growing over the past 10 hours continuously. Every log growth event is a complete freeze of the database as it waits for the log to expand to zero out the new allocated disk space. Then the activity will resume, only to hit a new growth event in a short bit. By now, your database log must had grown several hundred times likely. This happens under all recovery models, including SIMPLE.

You could abort the query, but you'd have to wait until the 10 hours of the transaction will roll back. A 10h transaction can take another 10h to roll back, or more, or less, it depends on many many factors.

You could shutdown the server in panic and restart it, only to face the recovery of the database that is trying to undo the 10h transaction. Recovery will last, guaranteed, longer than rolling back the transaction.

So this is why one does never move 10M rows in a single 'query'. Batching the move in smaller sets is a must. Of course, this requires a carefully written program to do it.

Some users are lucky, they learn by stumbling on posts like this and making a note to self 'don't move 10M rows in one single operation'. Other learn the hard way... but they (hopefully) know better next time.

Related Question