Sql-server – SQL Server 2008 R2 : multiple files and filegroup

sql serversql-server-2008-r2

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:

  1. 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

  2. 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?

  3. 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.

  4. What other benefits of having files in a filegroup on separate drives.

Best Answer

1: yes.

2: depends;) The query execution is still driven by the optimizer. It wont do paralellization for small result sets.

3: Ah - what would that be good given that you are still reading one backup file ;)?

4: Not running the files on one underlying SAN and thus improoving your IO budget?

The most brutal thing I have ever seen like that had nearly 30 files all on separate SAN volumes (count of hard discs going close to 200) and it was done as every LUN had a queue limit in the driver of 255 outstanding requests, which the SAN (with a 32 gigabyte cache) was just not caring about ;) THat thing was pulling in nearly 1.5 gigabyte per second over multiple fiber connections.

Related Question