sql server update single row taking very long time and not finishing

Posted on

Question :

I am trying to update single record for my small database but it take for ever and not finishing but if I restart the SQL server it work fine for short time other tables work fine

Total record count (12512)

UPDATE  payment SET martyr_name = 'TEST' WHERE ID=42101

Answer :

It is most likely that the update is being held back by an open transaction. Information about locks and what they are currently blocking is available in the system management views, though rather than poking them directly I usually use http://whoisactive.com/ or similar. There are other tools that perform a similar job (including the built-in-but-undocumented sp_who2) but thus far I’ve not found better than this one (including the ones I wrote myself some time ago!).

If you try using sp_whoisactive, the first area to look at in the output is using the value for blocking_session_id for the row representing the update. If that shows a number you should also see what session that represents in the procedures output. Note that this session itself might also be blocked waiting for something else: sometimes on a busy database there can be a lengthy chain of things waiting in turn for each other to release locks.

What to do about the issue is not something we can answer without a lot of knowledge about your application, though in general there are three classes of issue you are most likely to be seeing:

  1. Something or someone, perhaps an interactive user if you have support/dev/other people who connect directly to the DB, has left an inactive session with an open transaction that has touched that table. Hopefully sp_whoisactive (or looking at the DMVs more directly) will tell you which session that is so you can try identify where it came from and what it is/was doing. If it is truly errant you can get rid by calling kill <spid> but be careful here as that will rollback everything that happened during the active transaction up to that point.
  2. There is one or more long-running transactions that have touched that table and so hold locks. Unlike the idle sessions mentioned in point 1 these are actively working through something. These could be queries that are in need of optimisation or other correction, perhaps complex reporting queries or simpler queries that are using bad query plans for some reason.
  3. The server just being very busy so everything is normal but chugging along slowly and your update would eventually complete when other things have finished and released their locks. In this latter case you may need to upgrade, though it is far more likely that the problem is one of the above instead.

This may give you some relief.

UPDATE payment SET martyr_name = 'TEST' 
WHERE ID = 42101 
AND martyr_name <> 'TEST'

If it already has the correct value it will skip the write lock.

Leave a Reply

Your email address will not be published. Required fields are marked *