SQL Server lock_escalation flag on ALTER TABLE -- can I really restructure without a table lock?

Question

SQL Server 2014 Standard Edition

In my situation, I have a huge table (100 million+ rows), and need to:

• add column to it
• set default value in that column

I see in https://technet.microsoft.com/en-us/library/ms190273(v=sql.110).aspx that there is a new lock_escalation option.

Does this allow me to, for example, do the following without a table lock:

e.g.

ALTER TABLE Protocols set (LOCK_ESCALATION = DISABLE);
go

ALTER TABLE Protocols
ADD ProtocolTypeID int NOT NULL DEFAULT(1)
GO

However, the concerns are:

a) LOCK_ESCALATION = DISABLE -- what exactly is it for, does it apply here? (The SQL Server docs are a bit thin... perhaps it only applies to partitioned tables?)

b) Does LOCK_ESCALATION = DISABLE affect the transaction size? (Appears it will still create one huge transaction?)

c) Will LOCK_ESCALATION = DISABLE even apply and avoid a table lock?

d) Is there a way to chunk it? (Can't imagine, but there is so much new magic in newer SQL versions... I should ask!)

Answer 1

You say the documentation is thin, but the answers are all there.

Lock Escalation

You really need to read that whole page, but essentially lock escalation only applies to 'regular' locks (e.g. shared, exclusive). Operations that change the structure of an object take much more restrictive schema locks (e.g. Sch-M) which prevent all concurrent access to the object being modified. As such, less restrictive 'ordinary' locks are not required. Ultimately, the question you ask is moot, because ALTER TABLE does not take row or page level locks that might be escalated to a partition or object level lock.

Regarding ALTER_TABLE (in the Remarks section):

Locks and ALTER TABLE

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.

and:

Adding NOT NULL Columns as an Online Operation

Starting with SQL Server 2012 Enterprise Edition, adding a NOT NULL column with a default value is an online operation when the default value is a runtime constant. This means that the operation is completed almost instantaneously regardless of the number of rows in the table. This is because the existing rows in the table are not updated during the operation; instead, the default value is stored only in the metadata of the table and the value is looked up as needed in queries that access these rows. This behavior is automatic; no additional syntax is required to implement the online operation beyond the ADD COLUMN syntax.

If you were using Enterprise Edition, the change you propose could be an instant, metadata only change (though any subsequent changes to data in the new column would require real allocations, data movement, and logging, of course).

As it is, adding this column will be an expensive, long-running, fully-logged, single transaction, size-of-data operation. There is no way to 'chunk' this change without changing the definition of the column (a nullable column can be added as a metadata-only change, then updated in chunks).