Sql-server – How query execution time and concurrent users affect DTU

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.

So this was an oddity and I have yet to get down to the nuts and bolts. In a nutshell I enabled MARS on sql server connections a while back to fix an issue I was having with IoC container connections.

This fixed an exception that was similar to "You must close the current command yaddayaddayadda". However, Multiple Active Result Sets seems to lead to a concurrency issue in my case. I actually fixed the original issue that should have been fixed instead of enabling MARS. The fix was to implicitly create a new Data Context per call.