Hi, I have a question about file & filegroups in point of performance:
I have a log table, lots of new rows inserted constantly, 60000 in total.
I move 3 months old rows from log table to an archive table. to keep the log table small (~5 - 6 million rows)
archive table keeps growing, it holds rows for several years. my application decides to use which table: log, archive or log+archive (a view unions log and archive)
I have 2 separate physical disks,
on disk-1: log.data (and clustered index), archive.data (and clustered index)
on disk-2: log.indexes, archive.indexes, transaction_logs
1) Do you think is it a good plan?
2) If I have 3 disks, should I put transaction_logs to disk-3 separately?
Note that I have no option to use partitioned tables.
Thank you,