1

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...
14
  • What are you using or think you need Partitioning for though?...what kind of queries / operations are you hoping to improve by using it? Example queries would be helpful.
    – J.D.
    Jul 13, 2023 at 2:26
  • On the first solution the both drawbacks are not true. Why do you think t-log will be very huge? It depends on the DB load but not on its structure. About backups read: Partial Database Backups and Restores with Read-Only Filegroups. I would create one filegroup for a year to decrease its count. Jul 13, 2023 at 7:31
  • Each keyvalue spans for about one year. I work seekeing or grouping by keyvalues. Nightly processes do a lot and recalculate whole month data, so t-log after night is huge (GBs). I update topic text.
    – Radioleao
    Jul 13, 2023 at 8:24
  • 1
    "Most common queries read by keyvalues, filtering or grouping keyvalues on period ranges" - Partitioning doesn't improve query performance for these kind of queries (including your example query). Partitioning isn't meant to be used as a performance tool, rather it's meant for improved data management (e.g. if you wanted to TRUNCATE whole partitions at one time). This is why I question your need for it. And if you don't need it, and you don't need granular backups, then you can probably switch your database to Simple Recovery Model to solve your other Post's questions.
    – J.D.
    Jul 13, 2023 at 12:45
  • 1
    Here's a link to the docs on partial backups. Your situation sounds very fitting for it.
    – J.D.
    Jul 14, 2023 at 13:00

1 Answer 1

0

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.

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.