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.