Production Application Running Very Slow

Question

Hello Everyone,
Production Live Application is running very slow, and users are not able to use it. I see that there is a large UPDATE query running from 2 hours that's changing lots of records. If I kill it, then rollback will also take 2 hours. How to resolve?

Answer 1

Actually, with an amount of bad luck, the rollback could take even longer time. Rolling back can often be slower than going forward.

Then again, the query could also die on the spot when you issue the KILL command. It all depends on where it is stuck. Often it is not the writing operations that takes time, but what takes time is to find all rows needed for the update.

If you are on SQL 2019 (or on SQL 2016/17 and have enabled TF 7412), you can get an idea of the situation by using my beta_lockinfo which will give you an actual execution plan of the values so far. If there are 0 actual rows for the UPDATE operator, the rollback will be instant. https://www.sommarskog.se/sqlutil/beta_lockinfo.html.