How to efficiently use LOCK_ESCALATION in SQL Server 2008

Question

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:

1. Has a large amount of rows (1 to 2 million)
2. All the indexes used on this table only have the "use row lock" ticked in their options Edit: There is only one index on the table which is its primary Key
3. 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)
4. the table does not use partitions.

Upon checking the table on sys.tables, I found that the lock_escalation is set to TABLE

I'm very tempted to turn the lock_escalation for this table to DISABLE but I'm not really sure what side effect this would incur. From What I understand, using DISABLE will minimize escalating locks from TABLE level which if combined with the row lock settings of the indexes should theoretically minimize the deadlocks I am encountering..

From what I have read in Determining threshold for lock escalation it seems that locking automatically escalates when a single transaction fetches 5000 rows..

What does a single transaction mean in this sense? A single session/connection getting 5000 rows thru individual update/select statements?

Or is it a single sql update/select statement that fetches 5000 or more rows?

Any insight is appreciated, btw, n00b DBA here

Thanks

Answer 1

LOCK Escalation triggers when a statement holds more than 5000 locks on a SINGLE object. A statement holding 3000 locks each on two different indexes of the same table will not trigger escalation.

When a lock escalation is attempted and a conflicting lock exists on the object, the attempt is aborted and retried after another 1250 locks (held, not acquired)

So if your updates are performed on individual rows and you have a supporting index on the column, then lock escalation is not your issue.

You will be able to verify this using the Locks-> lock escalation event from profiler.

I suggest you capture the deadlock trace to identify the actual cause of the deadlock.