Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Jun 25 '12 at 14:59

This question came from our site for professional and enthusiast programmers.

add comment

1 Answer

up vote 1 down vote accepted

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.