(Look in the Misconceptions blog category for the rest of the month’s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.)
Another really commonly-held belief…
Myth #23: lock escalation goes row-to-page and then page-to-table.
Nope, never. Lock escalation in SQL Server 2005 and before goes directly to a table lock always.
In SQL Server 2005 (and 2008) you can change the behavior of lock escalation (if you really know what you're doing) using these trace flags:
1211 – disables lock escalation totally and will allow lock memory to grow to 60% of dynamically allocated memory (non-AWE memory for 32-bit and regullar memory for 64-bit) and will then further locking will fail with an out-of-memory error
1224 – disables lock escalation until 40% of memory is used and then re-enables escalation
1211 takes precedence over 1224 if they're both set – so be doubly careful. You can find more info on these trace flags in Books Online.
In SQL Server 2008, you can change the behavior of lock escalation per table using the ALTER TABLE blah SET (LOCK_ESCALATION = XXX) where XXX is one of:
TABLE: always escalate directly to a table lock.
AUTO: if the table is partitioned, escalate to a partition-level lock, but then don't escalate any further.
DISABLE: disable lock escalation. This doesn't disable table locks – as the Books Online entry says, a table lock may be required under some circumstances, like a table scan of a heap under the SERIALIZABLE isolation level.
Back in January 2008 I blogged an example of setting up a partitioned table and showing partition-level lock escalation in action – see SQL Server 2008: Partition-level lock escalation details and examples.
You may ask why the AUTO option isn't the default in SQL Server 2008? It's because some early-adopters found that their applications started to deadlock using that option. So, just as with the lock escalation trace flags, be careful about turning on the AUTO option in SQL Server 2008.