How will SQL Server queue this request (eg First In First Out)? Can I
be sure the DROP COLUMN will truly only take a few moments?
The way the locking works for ALTER TABLE is that you need a Schema Modification Lock (Sch-M) to perform the ALTER. Every other query that touches the table needs a Schema Stability Lock (Sch-S). Sch-M and Sch-S are incompatible.
Also while you are waiting to acquire the Sch-M lock, you block any new requests for a Sch-S lock. So you wait for all in-flight queries and transactions to release their Sch-S locks on the table, and block all new queries while you wait. This is the normal way DDL locking works, and so long as you don't have any long-running queries on your table it works fine.
But if you have long running queries that your DDL can get stuck behind, your DDL in turn blocks all new queries. So running DDL on a busy system with both short-running and long-running operations was a problem.
Low Priority Waits solves this problem by allowing your DDL statement to wait at reduced priority, not blocking new queries. The DDL session will just wait for a "window" where no other session has any lock on the table, and then run. But since the DDL session is not blocking new queries, it might wait forever.
Unfortunately low priority waits have not been enabled for dropping columns. You can create a feedback item to ask for it to be implemented here. Has been enabled for online index rebuild and partition switching.
You can mostly emulate this behavior with LOCK_TIMEOUT and retries. So configure your DDL session with a lock timeout, so it will abandon the ALTER TABLE if the lock can't be acquired quickly.
So run the alter like this:
set lock_timeout 1000 --1 second
alter table SomeTable
drop column if exists ColumnToDrop
And it will either succeed quickly or fail with a lock timeout error. If you want to run this in an unattended job, you can use a retry loop to keep trying until the ALTER TABLE succeeds quickly.
Something like this:
set lock_timeout 1000 --1 second
declare @tries int = 0
while 1=1
begin
begin try
set @tries = @tries + 1;
alter table SomeTable
drop column if exists ColumnToDrop
print 'complete'
break;
end try
begin catch
if @tries > 10 or error_number() <> 1222 throw;
declare @msg nvarchar(2000) = concat('retry ', @tries);
print @msg
end catch
end
