Lock Escalation - What's happening here?

"Lock Escalation" is how SQL handles locking for large updates. When SQL is going to change a lot of rows, it's more efficient for the database engine to take fewer, larger locks (e.g. entire table) instead of locking 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. That's the 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.

The answer by Justin Grant explains what LOCK_ESCALATION setting does in general, but misses one important detail and it doesn't explain why SSMS generates the code that sets it. Especially, it looks very strange that the LOCK_ESCALATION is set as a last statement in the script.

I did few tests and here is my understanding of what is happening here.

Short version

The ALTER TABLE statement that adds, drops or alters a column implicitly takes a schema modify (SCH-M) lock on the table, which has nothing to do with the LOCK_ESCALATION setting of a table. LOCK_ESCALATION affects locking behaviour during the DML statements (INSERT, UPDATE, DELETE, etc.), not during the DDL statements (ALTER). SCH-M lock is always a lock of the whole database object, table in this example.

This is likely where the confusion comes from.

SSMS adds the ALTER TABLE <TableName> SET (LOCK_ESCALATION = ...) statement to its script in all cases, even when it is not needed. In cases when this statement is needed, it is added to preserve the current setting of the table, not to lock the table in some specific way during the change to the table schema that happens in that script.

In other words, the table is locked with the SCH-M lock on the first ALTER TABLE ALTER COLUMN statement while all the work of changing the table schema is done. The last ALTER TABLE SET LOCK_ESCALATION statement doesn't affect it. It affects only future DML statements (INSERT, UPDATE, DELETE, etc.) for that table.

At a first glance it does look as if SET LOCK_ESCALATION = TABLE has something to do with the fact that we are changing the whole table (we are altering its schema here), but it is misleading.

Long version

When altering the table in some cases SSMS generates a script that re-creates the whole table and in some simpler cases (like adding or dropping a column) the script doesn't re-create the table.

Let's take this sample table as an example:

CREATE TABLE [dbo].[Test](
    [ID] [int] NOT NULL,
    [Col1] [nvarchar](50) NOT NULL,
    [Col2] [int] NOT NULL,
 CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Each table has a LOCK_ESCALATION setting, which is set to TABLE by default. Let's change it here:

ALTER TABLE dbo.Test SET (LOCK_ESCALATION = DISABLE)

Now, if I try to change the Col1 type in SSMS table designer, SSMS generates a script that re-creates the whole table:

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_Test
    (
    ID int NOT NULL,
    Col1 nvarchar(10) NOT NULL,
    Col2 int NOT NULL
    )  ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_Test SET (LOCK_ESCALATION = DISABLE)
GO
IF EXISTS(SELECT * FROM dbo.Test)
     EXEC('INSERT INTO dbo.Tmp_Test (ID, Col1, Col2)
        SELECT ID, CONVERT(nvarchar(10), Col1), Col2 FROM dbo.Test WITH (HOLDLOCK TABLOCKX)')
GO
DROP TABLE dbo.Test
GO
EXECUTE sp_rename N'dbo.Tmp_Test', N'Test', 'OBJECT' 
GO
ALTER TABLE dbo.Test ADD CONSTRAINT
    PK_Test PRIMARY KEY CLUSTERED 
    (
    ID
    ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
COMMIT

You can see above that it sets LOCK_ESCALATION for the newly created table. SSMS does it to preserve the current setting of the table. SSMS generates this line, even if the current value of the setting is the default TABLE value. Just to be safe and explicit and prevent possible future problems if in the future this default changes, I guess. This makes sense.

In this example it is really needed to generate the SET LOCK_ESCALATION statement, because the table is created afresh and its setting has to be preserved.

If I try to make a simple change to the table using SSMS table designer, such as adding a new column, then SSMS generates a script that doesn't re-create the table:

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.Test ADD
    NewCol nchar(10) NULL
GO
ALTER TABLE dbo.Test SET (LOCK_ESCALATION = DISABLE)
GO
COMMIT

As you can see, it still adds the ALTER TABLE SET LOCK_ESCALATION statement, even though in this case it is not needed at all. The first ALTER TABLE ... ADD doesn't change the current setting. I guess, SSMS developers decided that it is not worth the effort to try to determine in what cases this ALTER TABLE SET LOCK_ESCALATION statement is redundant and generate it always, just to be safe. There is no harm in adding this statement every time.

Once again, the table-wide LOCK_ESCALATION setting is irrelevant while the table schema changes via the ALTER TABLE statement. LOCK_ESCALATION setting affects only the locking behaviour of DML statements, like UPDATE.

Finally, a quote from ALTER TABLE, emphasise mine:

The changes specified in ALTER TABLE are implemented immediately. If the changes require modifications of the rows in the table, ALTER TABLE updates the rows. ALTER TABLE acquires a schema modify (SCH-M) lock on the table to make sure that no other connections reference even the metadata for the table during the change, except online index operations that require a very short SCH-M lock at the end. In an ALTER TABLE…SWITCH operation, the lock is acquired on both the source and target tables. The modifications made to the table are logged and fully recoverable. Changes that affect all the rows in very large tables, such as dropping a column or, on some editions of SQL Server, adding a NOT NULL column with a default value, can take a long time to complete and generate many log records. These ALTER TABLE statements should be executed with the same care as any INSERT, UPDATE, or DELETE statement that affects many rows.

You can check if you need to include the LOCK_ESCALATION statement in your script by comparing this value before and after running the main part of your script:

SELECT lock_escalation_desc FROM sys.tables WHERE name='yourtablename'

In my case, altering table to drop or add a constraint doesn't seem to modify this value.