Saturday, March 10, 2012

How to minimize Deadlock

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

No comments:

Post a Comment