Deadlocks can be prevented by one or more of the following methods:
- Adding missing indexes to support faster queries
- Dropping unnecessary indexes which may slow down INSERTs for example
- Redesigning indexes to be "thinner", for example, removing columns from composite indexes or making table columns "thinner" (see below)
- Adding index hints to queries
- Redesigning tables with "thinner" columns like smalldatetime vs. datetime or smallint vs. int
- Modifying the stored procedures to access tables in a similar pattern
- Keeping transactions as short and quick as possible: "mean & lean"
- Removing unnecessary extra activity from the transactions like triggers
- Removing JOINs to Linked Server (remote) tables
- Implementing regular index maintenance; usually weekend schedule suffices; use FILLFACTOR = 80 for dynamic tables
- Setting MAXDOP=1 solves deadlocking in some cases
