Today I had the pleasure to read about the new index rebuild option WAIT_AT_LOW_PRIORITY. It’s a great addition to the DBA arsenal and you can read all about it here: Testing ALTER INDEX REBUILD with WAIT_AT_LOW_PRIORITY in SQL Server 2014
I have written about how index operations can interrupt cursor operations. Now naturally I wondered whether the new index option makes any difference to this scenario. Let’s work through an example.
1. Create table and index
create table temp ( id int not null identity(1, 1) primary key , data varchar(10) not null default('test') ) go insert temp default values go 50 create index idx_temp on temp (data) go
2. Start a cursor loop
declare cur cursor for select id from temp open cur declare @id int fetch cur into @id while @@fetch_status = 0 begin print @id waitfor delay '00:00:01' fetch cur into @id end close cur deallocate cur
3. Rebuild index with the new option
alter index idx_temp on temp rebuild with ( online = on ( wait_at_low_priority ( max_duration = 1 minutes , abort_after_wait = self ) ) )
We still get the error:
1 2 3 4 5 6 Msg 16943, Level 16, State 4, Line 18 Could not complete cursor operation because the table schema changed after the cursor was declared.
I guess the lesson for today is that no matter how seemingly harmless/efficient the index operation is, you still need to test it thoroughly before applying to prod.