Can we reduce row lock contention on an index

Posted on

Question :

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

Answer :

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 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.

Leave a Reply

Your email address will not be published. Required fields are marked *