Josh Darnell takes us through how SQL Server manages ONLINE = ON operations (such as index building and rebuilding) using the version store:
The votes table has about 10 million rows in it, so this takes a bit of time (10-15 seconds if nothing else is happening). If I check
sys.dm_tran_version_store_space_usageandsp_WhoIsActive, I can see that:– the version store is not growing, and
– theALTERstatement is chugging along making progress
There are costs to setting ONLINE = ON. I think they’re almost always worth it, but it’s important to remember that they are there.