2

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
set 
Name='somename'
DetailMessage='Error'
LastUpdate=getdate()
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? – Josh Darnell Jun 29 at 13:46
  • Yes, Sp_blitzindex, pointed out the same – TheGameiswar Jun 29 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. – Danielle Paquette-Harvey Jun 29 at 18:38
  • No, no triggers,Table it self is only 350 rows – TheGameiswar Jun 30 at 2:37
  • could you please add a couple of examples of blocked queries? – Nikita Jun 30 at 16:25
7
+200

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] 
SET READ_COMMITTED_SNAPSHOT ON;

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

| improve this answer | |
2

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.

| improve this answer | |

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.