MS SQL which best data configuration for huge online datas?

Question

I have a database on SQL SERVER, which has a mix of operational data and huge amount of storage data:

1. Nr 15 table for configuration infos;
2. Nr 3 tables with huge amount of data for every month:
   • Data of current month are updated every night (in batch processes)
   • Data of past months, for 3 years, are read but not updated anymore

The complexities are:

• Each month has 30M to 50M rows;
• Data of past months should stay online;
• Nightly processes recalculate whole month data, so t-log after every night grown huge (GBs);
• Primary key of data is like that: month, keyvalue;
• Many queries insist on both current and previous months, reading many months together;
  • Each keyvalue spans for average one year (6 to 24 month);
  • Most common queries read by keyvalues, filtering or grouping keyvalues on period ranges

A query example is sum of amount for a group of keys:

    select t.kevalue, sum(t.amount) as total_value
    from HUGE_TABLE_DATA t
    inner join KEYLIST k
    on k.kevalue = t.kevalue
    where t.month between '2022-01-01' and '2022-12-31'

Which is the best configuration to store data, save time and keep backup/restore as light as possible?

---

My possible solutions, up to now, are two, but both have drawbacks:

1. Create different filegroups for each month data, and a Partition Scheme. This way, I can also backup different partitions in different files: But:
   • I need Full Recovery mode, and t-log will be very huge;
   • For every full backup I still need to backup everything, including past months;
2. Create different database, one for read/write data and the other with readonly data. This way, I can have different backup politics for different database. But:
   • I need views and union all to keep together data for different months, and they perform worse than a query on single table. Query with joins are far more worse;
   • Working on two or more databases is always tricky, has limitations, can involve many alias, can have permission issue, and so on...

Answer 1

Each night, roll up the day's data into a Summary Table (perhaps a "materialized view"). This table (or tables) will be much smaller than the raw data that you seem to be summaging through now. It sounds like a summary table will be a million times smaller than what you are working with now. Hence the following will be a lot faster:

"Nightly processes do a lot and recalculate whole month data" -- No. Instead augment the SUMs collected so far.

Then rewrite the Report queries to reach into a Summary Table to sum up the sums and sum up the counts and generate averages using avg(sum of sums / sum of counts), etc.

No partitioning. No UNION ALL. No lots-of-the-same table. Probably no big log files.

Well, if you need to delete "old" data, partitioning may help in that task. Partition by, say, month.