2

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?

9
  • 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? – Chad Mattox Apr 25 '16 at 20:12
  • Look in sys.dm_os_waiting_tasks whilst it is hung to see the wait type. – Martin Smith Apr 25 '16 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 '16 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 '16 at 20:49
  • 1
    Sp_whoisactive is much better... – Kin Shah Apr 25 '16 at 22:24
2

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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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