Production Application Running Very Slow

Chaitanya Kiran 696 Reputation points
2022-10-15T05:16:12.977+00:00

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?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,852 questions
0 comments No comments
{count} votes

Accepted answer
  1. Erland Sommarskog 102.1K Reputation points MVP
    2022-10-15T07:13:56.243+00:00

    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.


0 additional answers

Sort by: Most helpful