We are trying to partition a large history table. Being new to partitioning, I was trying to understand the concept of files and file groups. File groups can be set up in following three ways :
1. Each file group on different disk 2. File groups on same disk 3. One file group on one disk with all files
MSDN says that you can take advantage of parallel I/O access in first method, if the disk has RAID set up, thus enhancing performance. Second method does not have additional performance benefit, however, maintenance is faster as each file group separates out the file from rest of the database. I searched for performance benefits of the third method. I did not get the definite picture. For the third method, since the files are not divided in file groups, will it affect maintenance, thereby increasing downtime? Does it have any performance gain over the second method?