Best practices for updating data on a "live" table

Scenario:

I am running a user application hooked to a SQL Server as the data backend.
During a normal day, the data in the table associated with the application can be subject to frequent change, holds currently 15 million plus records (and does further grow). However, in- and upserts should only be applied and accessible by the app once the entire update statement finished successfully, plus dirty reads should never occur.

To being able to provide the app with constant accessibility to the tables, I use a helper table where the actual update and index rebuilds take place so the main table stays unlocked. After it is done, the tables are just swapped.

I also looked into:

• Enabling read committed snapshot (it seemed fairly resource hungry, though)

• Using horizontal partitioning

Question:

As I am fairly new to this and not too sure whether there are any "better" ways of resolving the situation (either performance, database-native, setup-wise, etc.), a food of thought is appreciated.

Update:

• App´s business hours are from hours 9 to 5

• SQL Server Enterprise hosted on Azure (AzureSQL)

Enabling read committed snapshot (it seemed fairly resource hungry, though)

Have you tested it with a version of your workload? If not, I recommend doing so. You'll typically be better off if you can use the technology that's already built into SQL Server that's designed to solve your problem. You may also feel better knowing that Azure SQL databases use RCSI by default. If it can work for all of those databases, then perhaps it can work for yours.

You can test the performance impact of RCSI without changing application behavior by enabling snapshot isolation on the database. That will create all of the same tempdb row versions needed for RCSI, but the snapshot isolation level will only be used for code that opts into it. Kendra Little has a blog post with more details here.

For index maintenance, I assume that you're using Standard Edition? If so, RCSI won't really help you there. You'll need to do your index rebuilds either during a time period of low user activity (is your application 24/7?) or during a maintenance window. It may seem inconvenient, but this is a problem that everyone with standard edition deals with. You may be currently rebuilding your indexes more often than necessary.

StackExchange Database Administrators Q#301919, answer score: 9