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 have 9 weekly partitions on one filegroup. How many files do I need to create?

There are 3 tables containing 46 million, 16 million and 10 million rows.

share|improve this question
    
Are you talking about table partitioning?? If so, why are you putting all partitions on a single filegroup? You're eliminating one of the most powerful parts of table partitioning (having filegroups on different disks, and/or make cold partitions read-only, etc.). –  Thomas Stringer Jun 3 '12 at 16:52
    
yes,cause the switching partition I need the staging table on the same filegroup and to avoid the drop and create staging table, prevent fragmentation, I need all partitions on one filegroup. –  wispa2001 Jun 3 '12 at 16:55
    
You haven't provided nearly enough information to properly answer this question... What is the size of the data? What are the access patterns? What I/O subsystem is backing the database? How much RAM is allocated to the server? etc... –  Jon Seigel Jun 3 '12 at 22:24
    
DB size is 400GB. RAM is 12 GB. Subscription server executes a dump every 5 minutes and the resulting file is ~1GB. –  user9208 Jun 4 '12 at 5:41
add comment

1 Answer 1

You need one file.

Unless you have specific requirements to have multiple files, you only need one file for the filegroup. The 'specific' requirements would be things like requirement to spread the filegroup on multiple disk locations or a requirement for round-robin allocation. You did not mention any such requirement, you only give information about the number of rows in the tables, which is largely irrelevant when it comes to deciding the number of files in the filegroups.

share|improve this answer
    
the problem is that to correct manage partitioned table I need to create for every partition the filegroup with one file. But now for the fragmentation issue I cannot manage it in this way. And I need to know what is the correct way to create file for a lot of data. –  wispa2001 Jun 3 '12 at 19:21
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.