I work with a lot of large tables (billions to tens of billions of records in each) in a database I recently inherited. I see a few clear DDL changes that would benefit the use-cases of the database but it's hard for me to implement them because the database can afford very minimal contention. (Essentially if a heavy query is running for more than a minute or two it has to be killed.)
Even during a maintenance window, these changes would be just way too long and would exceed my allocated time (at most 1 hour, since there aren't really any off hours).
Types of changes I want to make are create indexes, add persisted computed columns, create indexed views, and general index tuning. If there was a way to do any of these operations iteratively and pause between iterations then I could get away with the total time taking a while because at least I can allow other processes to run in between, instead of a backlog being built up.
The only idea I can think of is if I maintained a copy of the database on a separate server where I can make DDL changes, then re-point my applications to that server. Then update the first server with the DDL changes so it's in sync, and then the next time I need to make an update, I can repeat the process.
Edit: I'm on SQL Server 2016 Enterprise Edition.
Best Answer
The best thing I have found to help in big index creation is to (1) have enough RAM, (2) have a slow time before the index creation starts, and (3) perform a
SELECT...INTOof the index fields into a temp table with anORDER BYof the desired index order right before creating the index. This can speed the process by up to 75% in some cases (which I have not fully identified to date).In addition, if you can add an otherwise unused high-performance drive or drive array (SSD preferred) and create the index on a filegroup on that drive array, this can greatly improve the index creation performance.
For modifying an existing index, use the above tips to create a new index, then DROP the existing index after examining index usage on the new index for a time. (Why? I've modified some indexes in ways that should have increased performance, only to find overall performance degraded. When I create a new index, I can monitor if SQL Server wants to use it, and/or check performance by forcing the index.)
Of course, since you have SQL Server 2016 EE use the ONLINE=ON option. This minimizes locking when creating indexes, and is best for non-clustered index creation or alteration. Clustered indexes will reorganize the whole table, so it will take some time no matter what happens.