4

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)
2
  • For swapping tables quickly, you may want to look at this sqlperformance.com/2021/09/sql-performance/… Commented Oct 31, 2021 at 10:44
  • Thanks Aaron and Charlie, will look into these. Yes, I did some analysis on the workload and the temp db grew massively which, for now, is fine but probably won´t as I am expecting the table to grow.. Maybe I have to look into the temp db mayhem first, so thanks for mentioning.
    – Bennimi
    Commented Oct 31, 2021 at 10:52

1 Answer 1

10

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.

2
  • 2
    Just want to emphasize this point: "You may be currently rebuilding your indexes more often than necessary." and I totally agree that's always worth being mindful of. I've rarely seen it as necessary except when dealing with heap tables, which in of itself is usually not wise.
    – J.D.
    Commented Oct 30, 2021 at 21:53
  • Thanks, will have a look into it. I did some non standardized testing, and the tempDB seemed to get out of control. Surely, I have to do some more sound testing on that. So from what I understood, simply using RCSI and update should be the most db-native solution? Is merge into favored in this case? PS: I do have Enterprise
    – Bennimi
    Commented Oct 31, 2021 at 11:00

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.