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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I am partitioning a table by date (month). The server has four drives. The partition scheme is defined as

    [PRIMARY], [fg2], [fg3], [fg4],
    [PRIMARY], [fg2], [fg3], [fg4],
    [PRIMARY], [fg2], [fg3], [fg4], 

Which of following option is better?

  1. Create one file for each file group [PRIMARY], [fg2], [fg3], [fg4]. And put each file in different drive.
  2. Create four files for each file group. And save each files in different drive.
share|improve this question

One file per filegroup, one file per volume.
So a partition read/write goes to one volume: which is one reason to partition.

Otherwise, you are simply using all 4 drives all the time. So why bother with partitioning

I personally don't like multiple files per filegroup (except tempdb of course).

I assume "4 drives" means 4 discrete SAN LUNs or RAID arrays...

share|improve this answer

It depends on what the goal of the table partitioning is. If your goal of table partitioning is simply partition elimination then you want all the file groups on all the disks. If you are trying to put specific partitions on specific drives then you want one partition per disk.

share|improve this answer

Your Answer


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.