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 partitioning a table by date (month). The server has four drives. The partition scheme is defined as

CREATE PARTITION SCHEME ByDate AS PARTITION ByDate TO (
    [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
add comment

2 Answers 2

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
add comment

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