I am getting bit confused regarding advantages of multiple files and filegroups in SQL Server
Using SQL Server 2008 R2, I have following filegroups and files configuration
Drive D: Primary Filegroup with two files File1.mdf and file2.ndf
Drive E: Secondary Filegroup with two files File3.ndf and File4.ndf
Both drive uses its own disk underneath a SAN (dedicated IO Path).
Questions:
If I create a table B on secondary file group, does this mean when inserting data proportional fill algorithm is used and data will be equally filled in both the files on secondary file group? answer in Yes or No
If there is a tableA on primary filegroup, if I perform SELECT query with a join between tableA and tableB (secondary filegroup) two threads will be spawn for the query as both tables resides on a separate volume?
Will restore database be any faster as it would spawn two threads for performing restore? I do have instant file initialization ON so to me no gain splitting file groups on separate drives.
What other benefits of having files in a filegroup on separate drives.