Forum Discussion
Deadlocks on High Frequency Updates
Right off the bat: do you have a nonclustered index on the columns in the UPDATE statement's WHERE clause?
CREATE INDEX UsersAnswer_ncix ON UsersAnswer(For_Question,For_Quiz,FK_Answer);
If not, create it and monitor for changes in deadlock occurrence.
P.S. I'd recommend against turning the table into a heap by turning the PK into a NC index.
I'm curious. What happens if you set the primary key to nonclustered?
CONSTRAINT [PK_UsersAnswer] PRIMARY KEY NONCLUSTERED
Thanks for your reply! Unfortunately, it doesn't solve the problem.
I also tried on a different table that has a clustered primary key not composite (single column) and it gave the same error. Also note that I don't reach 100% of my CPU while stress testing (so the database is not even under extreme stress). I really don't get the problem here... I'll try on another machine just to check.
How do your database properties look like?
Have you tried to set "read commited snapshot on" to true? (but read the documentation about that)Just for another try: If your fill-factor is set to 100 or 0, maybe reduce to 80. (think about regular rebuilding then)
"cost treshold for parallism" changed in the sql server setting? I recommend a value 50 and not the default of 5.
If the single update then still using parallel execution (for the single update), you can try to use the option maxdop with a value of 1.
How does your physical/virtual disk look like? TEMPDB+database+log on same physical drive ? Have you measured Disk-IO/ Page faults ? Disk Queue Length`?
