views:

193

answers:

1

While altering a table (removing a column) in SQL Server 2008, I clicked the Generate Change Script button and I noticed that the change script it generated drops the column, says "go" and then runs an additional ALTER TABLE statement that appears to set the lock escapation for the table to "TABLE". Example:

ALTER TABLE dbo.Contract SET (LOCK_ESCALATION = TABLE)

I should also note that this is the last thing the change script is doing. What is it doing here and why is it setting the LOCK_ESCALATION to TABLE?

+3  A: 

"Lock Escalation" is how SQL handles locking for large updates-- when SQL is going to change a lot of rows, it's more efficient to lock something larger (e.g. entire table) instead of many smaller things (e.g. row locks).

But this can be problematic when you have a huge table, because taking a lock on the entire table may lock out other queries for a long time. But there's a tradeoff: many small-granularity locks are slower than fewer (or one) coarse-grained locks, and having multiple queries locking different parts of a table creates the possibility for deadlock if one process is waiting on another.

There is a table-level option, LOCK_ESCALATION, new in SQL 2008, which allows control of lock escalation. The default, "TABLE" allows locks to escalate all the way to the table level. DISABLE prevents lock escalation to the entire table in most cases. AUTO allows table locks except if the table is partitioned, in which case locks are only made up to the partition level. See this blog post for more info.

I suspect that the IDE adds this setting when re-creating a table because TABLE is the default in SQL 2008. Note that LOCK_ESCALATION isn't supported in SQL 2005, so you'll need to strip it if trying to run the script on a 2005 instance. Also, since TABLE is the default, you can safely remove that line when re-running your script.

Also note that, in SQL 2005 before this setting was present, all locks could escalate to table level-- in other words, "TABLE" was the only setting on SQL 2005.

Justin Grant
Duplicated post on the MSDN Forums as well: http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/e96aa19e-d72c-46af-b2e8-4bf54de09440
Jonathan Kehayias

related questions