Sunday, May 1, 2011

DDL will block query having with (nolock)

DDL such as alter table T add column a int not null default 0 will block the query select * from T with (nolock).
If the table T has large number rows, the DDL will take long time. And the query having with (nolock) will be blocked and waiting for SCH-S lock.
So to reduce the lock time:
1. alter table T add column a int null defalut 0
2. update T set a =0
3. alter table T alter column a int not null --this step will block the DML with (nolock)

No comments:

Post a Comment