SQL Server - How to tune insert/update/delete on very active Tables
I have some really active tables mainly with insert/update running against them.
There are lots of waits and as a result I often come across Deadlock. Once application hit deadlocks it often crashes some processes and I have to manually run those transactions. It's a pain.
How do I tune insert/update/delete so that these waits are gone for good.
userA update TableA wants to update tableB
userb updated tableB wants to update tableA
userA update tableA row1 wants to update another tableA row2
userb updated tableA row2 is still doing processing...
or is it normally more complex with with more than 2 users/connections involved?
you may be able to change the clustering/partitioning indexes to reduce contention if its page locks which are creating the problems...
you maybe able to influence the program design so that (in general) Table A is always updated before TableB
you may find that adding a logical deleted status column , instead of actually doing a DELETE (ie a delete becomes an UPDATE) will assist as DELETES are (usually) much more expensive than updates... (and run the physical delete in a batch period...)
the other standard TUNING mechanic apply ... ensure that the SQL actually needs to be performed... ensure that code delay between sql statements is minimised...
look to tune the actual statements usually involved...
update row 2
ccould that be changed
so that the lock for row1 is minimised....