Regardless of WAIT_AT_LOW_PRIORITY, index operations can still break cursor operations

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')

insert temp default values
go 50

create index idx_temp on temp (data)

2. Start a cursor loop

declare cur cursor
    select id from temp

open cur

declare @id int

fetch cur into @id

while @@fetch_status = 0
    print @id
    waitfor delay '00:00:01'
    fetch cur into @id

close cur
deallocate cur

3. Rebuild index with the new option

alter index idx_temp 
on temp 
rebuild with 
    online = on 
             max_duration = 1 minutes
             , abort_after_wait = self

We still get the error:

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.

Watch me code

I have been playing with an idea in my head for a while. I think it is time to write it down before I forget.

I have always envisioned that one day in the office we can have big overhead monitors that everyone can see. The monitors will display each team member’s screen for a pre-configured duration.

I imagined that this set up can achieve several goals:

Mentoring – By looking at how someone else does his work, you may pick up something new, whether it is a new shortcut or seeing how a tester tests your code. I always believe that the best way of learning to program is to observe someone else in action. Learning from a real life mentor is far more effective than reading a technical book.

Hawthorne Effect – Team members’ productivity can be easily observed since everyone’s activities are available for everyone else to see.

Supervision – Time wasting activities will be discouraged. Team leaders will also no longer need to worry about team members “strategic” seating arrangements.

Collaboration – It will be very efficient to conduct demos and presentations, especially if the team members are scattered around the office.


The possibilities are truly endless.