SQL Server 2008 update statement hangs

Question

In my SQL Server 2008 Enterprise I have a database that contains several tables:

1. Departments - few rows
2. Persons - a 300+- rows
3. Permissions - thousands of rows

The primary key of Persons is the PersonId combined with the DepartmentId (which is a FK itself).

The Permissions table has a FK with cascade functionality (delete, update) to the PK of the Persons table.

When I execute this statement:

UPDATE Persons SET PersonId = PersonId + 1 WHERE DepartmentId = 789

using SSMS, ADO, ADO.NET I either get a timeout or it takes way too long (more than 10 minutes).

I tried updating the rows one by one with ADO.NET and found out this strange behaviour: when I get to a specific rows the update starts to hang. I tried restarting, drop and create, backup and restore, deleting the rows and reinserting but haven't solved it yet.

Thanks in advance for any answers. Hen

Answer 1

Almost all instances of excessive blocking can be fixed (or at least managed) by using the appropriate indexes.

In addition, you should ensure that your indexes are rebuilt and your statistics are updated (at a minimum).

Is your clustered index defined on the composite primary key (PersonId, DepartmentId) ?

There are several ways to identify which process is blocking. One of which is sp_who2. Look for an entry in the BlkBy column.

Also see: How To Find The Table Names Which Are Locked (Specific to any transaction)

Answer 2

If indexes have been added (manually of course, they are not automatically added with FKs) then the other major culprit is triggers firing.

Check for triggers on the tables involved.