You should probably just go say thanks to Gianluca in his blog post comments now. You’re gonna need this technique someday, trust me. Then, your ALTER TABLE still needs locks, but it’s only for a really short amount of time (think milliseconds.) If I’d had an index on OwnerUserId, I could immediately start creating that index again (even with ONLINE = ON to minimize the outage.) People can still be deleting/updating/inserting into the table while the REBUILD runs. So what this means is that you just need to prepare the ALTER TABLE command for a time window where you can deal with the higher writes due to data & log file work, buuuuut it doesn’t need to be a zero-load window. If I try to do a SELECT against the same table, it’s going to be blocked until I commit the ALTER TABLE command: The change does finish instantly and does no page reads, as evidenced by SET STATISTICS TIME, IO ON:īut it isn’t pain-free: we still do need a schema modification lock in order to do this, as shown by sp_WhoIsActive. In my case above, I did it in a transaction because I wanted to show you the locks that are required in order to make this change. (If there are indexes on that column, I still have to drop that index first – but now at least we’re talking about a smaller outage window, because I only need to drop the index at the moment in time when I need to alter the table – not when I do the rebuild and rewrite all the 8KB pages.) Let’s say I want to alter the OwnerUserId column on the Posts table, which would normally be a giant pain in the rear. Let’s see it in action on the Stack Overflow database. I’m not happy about that, but I’m just writing it out here because I’ve hit that myself when trying this trick. If you use page compression or if you try to downsize a datatype, it’s a size-of-data operation that scans the entire table. Just a couple of gotchas: it only works with row compression (not page), and it only works for upsizing datatypes, not downsizing. (although it does require a schema modification lock) Second, alter the datatype, which will be instantaneous.First, turn on row data compression on the table (which can be done ahead of time, and on Enterprise Edition, can even be done with ONLINE = ON, and even works when there are indexes on OwnerUserId).However, Gianluca Sartori ( on Twitter) wrote about a brilliant technique: ALTER COLUMN OwnerUserId BIGINT WITH ( ONLINE = ON )
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |