Databases
StackOverflow2013
Postsdbo
POHow to efficiently use LOCK_ESCALATION in SQL Server 2008
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to efficiently use LOCK_ESCALATION in SQL Server 2008
    text
    copied!<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>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload