2

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.

Improve this question
5
  • 3
    Could be worth stating in the question what edition you are using.
    – Paul Holmes
    Commented Jan 17, 2020 at 16:34
  • 1
    Not sure I fully follow the copy database method you're considering. Wouldn't that require the active database to be read-only to avoid data loss? What is the purpose of applying the same DDL changes after the swap since the database would get overwritten by next copy?
    – Dan Guzman
    Commented Jan 17, 2020 at 17:20
  • Sorry I tagged SQL-Server-2016 but it's enterprise edition, I'll add it to the question.
    – J.D.
    Commented Jan 17, 2020 at 17:40
  • 1
    Is this an OLTP application with new data coming in continuously or a data warehouse with data coming in via batch process in a specific window?
    – MattyZDBA
    Commented Jan 17, 2020 at 19:22
  • OLTP, fairly highly transactional (e.g. a few thousand new records added to every table every 5-10 seconds, and existing data changing constantly.)
    – J.D.
    Commented Jan 17, 2020 at 22:15

2 Answers 2

Reset to default
4

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...INTO of the index fields into a temp table with an ORDER BY of 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.

Improve this answer
1
  • Yes. Having maintenance or design changes that don't fit in your change windows is an indication that the system is probably a bit undersized.
    – David Browne - Microsoft
    Commented Jan 17, 2020 at 19:15
3

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.

Instead, consider just building a new table, and loading it incrementally from your existing table (things like Change Tracking or even Triggers can help here). Then during your short window, perform a final sync, and rename the tables.

Improve this answer
2
  • This seems to be the common method for a lot of online DDL changes that the big boys (FANG) are using for their large systems.
    – LowlyDBA - John M
    Commented Jan 17, 2020 at 20:58
  • I thought about doing this, but even loading data from one of the big tables to another is eating up too many server resources and causing contention which ends up causing our existing app processes to end up in a backlog waiting on those server resources. It was actually this very strategy I was last trying and wasn't having much success with which led me to ask the question here, ironically. (I tried doing an iterative insert of a batch of records at a time, and I also tried BCP commands but both were too slow or consumed too many server resources.)
    – J.D.
    Commented Jan 17, 2020 at 22:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.