We have few queries which check their status into a table every 5 seconds and update their status..

Below is how the query looks like(all table columns are in update query)

update table1
where id=14

Recently this table has run into blocking issues and above update query is lead blocker.When i ran Sp_Blitzindex, it showed no missing indexes,bur row lock contention of 284 minutes..

Below are few steps that i did so far..
1.Ensured Foreign keys are indexed(they are also Primary key)
2.Create indexes for select statements which can benefit from new index,so that lock contention on this index is reduced
3.I also reduced Fillfactor to 10, earlier each page used to store 93 rows, now it stores only 7 rows per page(still not able to give myself a 100% logical explanation on how this change would help... )

Any suggestions further..Let me know if you need any further details

Below is the schema of table(changed column names) and table has only 350 rows and queries update this table at frequency of 20/30 queries every 5 seconds...

create table dbo.table1
[ID] [int] NOT NULL,
[Name] [varchar](500) NULL,
[DetailMessage] [nvarchar](max) NOT NULL ,  
[LastUpdate] [datetime] NOT NULL

PS: This is a third party query and we can't modify source database with changes like changing isolation level...I can add indexes only

SQl version:SQl 2012

  • 2
    Thanks! A couple of other questions: is the row lock contention on the clustered primary key on dbo.Table1.ID? Can you provide the index definitions for the nonclustered indexes on this table? Jun 29, 2020 at 13:46
  • Yes, Sp_blitzindex, pointed out the same Jun 29, 2020 at 13:51
  • Is there some triggers on this table? (example: is some work done when rows are updated?) I've had similar problems but it was due to triggers doing stuff every time a row was updated. Jun 29, 2020 at 18:38
  • No, no triggers,Table it self is only 350 rows Jun 30, 2020 at 2:37
  • could you please add a couple of examples of blocked queries? Jun 30, 2020 at 16:25

2 Answers 2


If this third party app is holding those row locks long enough to cause contention, and

  • you can't change that behavior,
  • and the blocked queries are read queries, then

One option would be to enable Read Committed Snapshot Isolation in that database:

ALTER DATABASE [YourDatabaseName] 

This should stop those UPDATE queries from blocking your read queries.

Note that this will affect the results of the read queries, since they will be seeing a point-in-time snapshot of the data being read, rather than being blocked waiting for the update. It will also increase load on tempdb. For a detailed treatment of implementing RCSI, check out Kendra Little's post here:

Implementing Snapshot or Read Committed Snapshot Isolation in SQL Server: A Guide


I've seen this before, or something very similar ... just don't remember all the details. I think the solution was to either rebuild the clustered index or even to select the rows into another table, drop the original, recreate and re-insert the rows from the copy table.

probably / possibly something to do with ghost cleanup records, as you are continually updating the table a lot of records will be logically deleted (aka ghosted), then these get removed by the ghost record cleanup task (see for example https://mssqlwiki.com/tag/what-is-ghost-cleanup-task/) I suspect this is the source of the blocking.

It would be interesting to know if there are system spids (session_Id < 50) involved in the blocking chain.

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.