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

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.

appletv-suit-070703-1

The possibilities are truly endless.