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.

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

We are trying to partition a large history table. Being new to partitioning, I was trying to understand the concept of files and file groups. File groups can be set up in following three ways :

1. Each file group on different disk 
2. File groups on same disk
3. One file group on one disk with all files

MSDN says that you can take advantage of parallel I/O access in first method, if the disk has RAID set up, thus enhancing performance. Second method does not have additional performance benefit, however, maintenance is faster as each file group separates out the file from rest of the database. I searched for performance benefits of the third method. I did not get the definite picture. For the third method, since the files are not divided in file groups, will it affect maintenance, thereby increasing downtime? Does it have any performance gain over the second method?

share|improve this question
up vote 1 down vote accepted

There shouldn't be a performance gain by the third option over the second. When you think about it, it's almost the same as having all your user objects in the PRIMARY filegroup. One performance advantage you can achieve with the second option is to flag the older historical filegroups as read-only once you've confirm no additional data modifications are expected, indexes are rebuilt and statistics updated. Once read-only has been implemented, locking doesn't become an issue when querying data in those filegroups.

share|improve this answer
So this should mean that third option will have more locking issues for highly concurrent systems. Thus not recommended for a table handling concurrent queries. Will there be issue during maintenance (backup, drop/delete a partition) as queries will be on the only available file group ? – kanu Jan 27 '14 at 17:04
Locking is essential to maintain database consistency. The query workload applied to the table will determine the nature and intensity of the locking. This post explains it well. Altering a partition function/scheme may require an exclusive lock; if possible, define the function/scheme to define the most time periods possible with the disk space you have available. I've used small data file sizes for time periods in the distant future with the intention to expand them just prior to population. – MattyZDBA Jan 27 '14 at 17:46

The 3rd method does not have performance advantages. There are two reasons I can think of that one would be doing this:

  1. You plan to spread files to multiple disks later (move to option 1)
  2. You would have allocation contention with just one file. This is a common problem with high-load tempdb's. It is rare with user databases.

Multiple files on the same disk are detrimental to performance because they cause more random and less sequential IO. This is because a single partition can easily become spread (fragmented) across files.

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.