Partitioning Large MS SQL-Server FACT table

Scenario:
• Data should be retained for 5 years or 60 months.
• Table will have about 800 million records
• Have adapted monthly partition.
• Each File group have 8 files to match the Number of CPU improve parallelism.
• Each partition will be kept in one File group.
• The above scenario requires 62 file groups need to be created (60 for partition and two for window sliding)

Question:[u]
Do I need to create 62 file groups and required data files using DDLs ? Any better way of handling this situation? Any ideas or scripts would be great?

This is probably a dead topic.

On Project REAL, Microsoft's reference implementation from a few years back, they set up many disks across many HBA's, (say they had 6 HBA's, not sure exactly). They had a filegroup per HBA, with multiple files in it. Then they rotated the usage of the FG such that the monthly partitions were offset (i.e. Jan 2011 tables for 5 fact groups sat on 5 different filegroups to improve parallelism).

However, during testing they found there was hardly any read/write contention, so ended up merging down all of the HBA's into a single massive disk (better IOPS and bandwidth due to massive spindle count). These performance gains outweighed that filegroup optimsation.

They still ended up doing some FG work to move old data to a less expensive disk array, but that was all.

As with all these things, testing testing testing, but I would suggest that 60+ FG's are unnecessary. If you have 3 fact groups, try 4 filegroups (one for each fact table and one for dims and other stuff). Have the FG's rotate so each fact group for a given month are on different FG's.