title: How to efficiently use LOCK_ESCALATION in SQL Server 2008
body: <p>I'm currently having troubles with frequent deadlocks with a specific user table in SQL Server 2008. Here are some facts about this particular table:</p>
  
  <ol>
  <li>Has a large amount of rows (1 to 2 million)</li>
  <li>All the indexes used on this table only have the <strong>"use row lock"</strong> ticked in their options
          <strong><em>Edit: There is only one index on the table which is its primary Key</em></strong></li>
  <li>rows are frequently updated by multiple transactions but are unique (e.g. probably a thousand or more update statements are executed to different unique rows every hour)</li>
  <li>the table does not use partitions.</li>
  </ol>
  
  <p>Upon checking the table on <code>sys.tables</code>, I found that the <code>lock_escalation</code> is set to <code>TABLE</code></p>
  
  <p>I'm very tempted to turn the lock_escalation for this table to <code>DISABLE</code> but I'm not really sure what side effect this would incur. From What I understand, using <code>DISABLE</code> will minimize escalating locks from <code>TABLE</code> level which if combined with the row lock settings of the indexes should theoretically minimize the deadlocks I am encountering..</p>
  
  <p>From what I have read in <a href="https://stackoverflow.com/questions/4679222/determining-threshold-for-lock-escalation">Determining threshold for lock escalation</a> it seems that locking automatically escalates when a single transaction fetches 5000 rows.. </p>
  
  <p>What does a single transaction mean in this sense? A single session/connection getting 5000 rows thru individual update/select statements?</p>
  
  <p>Or is it a single sql update/select statement that fetches 5000 or more rows?</p>
  
  <p>Any insight is appreciated, btw, n00b DBA here</p>
  
  <p>Thanks</p>