Whenever we kill a session, first of all, we should be very carefully check the complete percentage in sys.dm_exec_requestes and evaluate the rollback time..
The complete percentage is only for backups and operations like that. Normal queries don't get a complete percentage, and hence the time to complete can't be evaluated.
| percent_complete | real | 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 |
Yes, you are correct. The complete_percent has its big limitation. And I agree it's very hard to evaluate the rollback time. My point is, we need to do it if possible. For example, if Insert/Update operations with CreateTime/InsertTime/LastUpdateTime info, we can check the row count with NOLOCK. I agree, it's probably not so helpful in many cases. But in some cases, it might prevent us from doing some bad decisions.
GASQL.com - Focus on Database and Cloud