Blocking on a heavily updated table

Question

Post reply

Blocking on a heavily updated table

Mike Metcalf

SSCrazy Eights

Points: 8763
More actions
September 13, 2006 at 3:22 am

#92498

Hi Guys,
We've identified a table in our system as the main hotspot in the entire database. It's relatively small (1000 - 50000 rows) but is constantly recieving update and read requests, along with inserts. My question is what indexing strategy would you recommend for it?
The number of updates means that there are always processes blocked by other items further up in the queue.
It's currently on its own filegroup with plenty of room to grow and is also on it's own disk so theres no external contention to speak of. All of the read activity is done at a read-uncommited level so it's just the inserts and updates that are causing us a headache.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jo Pattyn SSC-Dedicated Points: 32348 More actions · Answer 1

I'm lately facing locking too. For one particulary table it was solved by changing the clustered index.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Can you post the table structure please, columns, constraints, current indexes. also if possible, the update statements that run.

Thanks

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Mike Metcalf SSCrazy Eights Points: 8763 More actions · Answer 3

Thanks for the offers and suggestions, in the end i managed to get enough time to look at it myself and fixed what was up (which is what I should have done in the first place but was snowed under with other 'issues').

Basically I changed all of the stored procedures that were doing the updates to use the ROWLOCK option which effectively eliminated the blocks on the other 50 rows in the data page that were being blocked.

I also changed the fill factor in the indexes to significantly reduce the number of rows held in each data page which also helped to reduce the contention for the inserts.

Thanks again

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 4

If you have a clustered index, change it to a non-clustered index. That'll help a highly transactional table a bit, as well, especially if you have a lot of inserts that may be "out of order".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)