Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
add comment

migrated from stackoverflow.com Jan 21 '12 at 23:30

This question came from our site for professional and enthusiast programmers.

1 Answer

up vote 10 down vote accepted

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.

share|improve this answer
    
+1. Remus makes several excellent points. –  Mitch Wheat Dec 6 '10 at 5:55
    
BTW, I did not even touch the subject of whether the 10M rows to move are dependent on 10M rows to read, or the query has to first scan 500M rows just to select the candidates for the move. –  Remus Rusanu Dec 6 '10 at 5:57
    
Thanks Remus for detail explanation. –  user219628 Dec 11 '10 at 16:24
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.