May 19, 2026 at 4:05 am
Recently, our dev teams approach me for advice on improving their huge heap table which are causing issues on their DBs IO usage. This particular DB is hosting an old in-house application and the dev teams has been doing a lot of work modernizing & improving it's table structure. They hit the wall when trying to create a clustered index on this large heap table (approx 400GB), running index creation with ONLINE parameter will cause some locking, same goes to table rebuild.
So now, with almost no way out, they're asking DBA for help as that particular DB aren't monitored or managed by DBAs. I provided some solution such as doing a side by side table migration, where a new table with clustered index be created then move the data in batches into the new table before setting a short downtime to cutover. This could work normally but various rows in the table will be updated from time to time, so there might be changes even during the copying period. Due to it's size it's not possible for production to actually freeze for days just to allow the copy to complete. Any ideas how to work around on this?
Another issue is the clustered index key, that table doesn't have any PK or unique keys. Furthermore most columns contains VARCHAR, datetime data type which will not be a suitable candidate to be a clustered index key.
Viewing post 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply