I got two answers with two totally different opinions 😉
Unless all those files are on different physical drives, fewer bigger files is better. There's no gain from having lots of little files on the same RAID array, and there is a downside when you have *lots* of files in terms of time to open them, the overhead of calculating proportional fill and such.
The 24 LUNs is all I got 😉 Every filegroup would put it files in the same LUNs
I think that like the cost of parallelism at one point exceed the benefit there is a "break-even point" for database files.
But maybe there is the same effect on the file size and it would be contrary (too big file getting slower)
Probably a hybrid approach with more files and filegroups for the initial 6 months could work better , have you explored that option ?
Under the assumption that it has a benefit, that could work, but I would have to merge continually older partitions in to a big one.
Load and query:
I will write the data date by date in this table. Usually "today" I will not write data that is for older dates then "yesterday".
The queries will be more frequent for the newer dates - approx. up to 6 month.
Older dates will be queried in about 10-20% of the queries. However for those 10-20% I need the complete data.