I am having a weird issue in SQL Server 2014. I cannot update or delete certain rows from a table.

I can insert rows fine, and select them, but when I try to update or delete a row, I get a timeout error. It is also strange, that some of the rows can be updated, while others cannot. The table is small, only a few thousand rows, and it's hard to see a pattern with which rows can and cannot be updated, but it seems the more recently inserted rows cannot be deleted/updated.

There are no triggers on the table, there are no locks on the database, and the table has a primary key.

What could be causing this?

  • 3
    How do you know there are no locks? This very much sounds like blocking. Also, how are you doing the Delete/Update? In SSMS? Apr 25, 2016 at 20:12
  • Look in sys.dm_os_waiting_tasks whilst it is hung to see the wait type. Apr 25, 2016 at 20:21
  • SSMS's activity monitor doesn't show any blocking on the table. Is there a better place to check for blocking?
    – jabe
    Apr 25, 2016 at 20:22
  • 1
    It did turn out to be blocking. We didn't know about the sp_who2 command, and were using the Activity Monitor to check for blocks, which wasn't showing them. We found out about sp_who2 and that did show blocking. Thanks for your help everyone.
    – jabe
    Apr 25, 2016 at 20:49
  • 1
    Sp_whoisactive is much better...
    – Kin Shah
    Apr 25, 2016 at 22:24

1 Answer 1


Few Quick Possibilities:

  1. Bad Statistics. Seems unlikely on a table under millions of rows, but technically possible. Try running UPDATE STATISTICS . If this does work look into why your statistics are getting out of whack and what you can do to mitigate the problem.

  2. Lack of a good Clustered Index. This can be related to #1, if your CI is bad it can slow down UPDATEs/DELETEs as the CI needs to be searched and adjusted during such changes, but an INSERT may just be adding to the end of the CI.

  3. Locks are being held too long over some sections of the table via other processes. Other transactions may be holding a lock on the table for excessive amounts of time, this may also be visible as deadlocking if you have the appropriate flags turned on for the SQL Logs.

Manually grabbing the query plan and taking a look at that may also help you figure out what is going on.

  • The rows were locked by another process, #3 on your list was correct.
    – jabe
    May 13, 2016 at 21:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.