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