Why can't I update or delete certain records in SQL Server?

Question

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?

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.