1

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

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

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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