Forum Discussion
Solved
Deadlocks on High Frequency Updates
Using SQL Server 2022, I'm stress testing an UPDATE statement. I'm using a python script to send parallel requests to the database. The problem is that, as soon as the number of parallel requests exc...
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