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.
Sql-server – How many files should I create for one filegroup and 9 weekly partitions
datafilefilegroupspartitioningsql serversql-server-2008-r2
Related Solutions
Yes, this is redundant. By default, SSMS will script these
The option is here:
1: yes.
2: depends;) The query execution is still driven by the optimizer. It wont do paralellization for small result sets.
3: Ah - what would that be good given that you are still reading one backup file ;)?
4: Not running the files on one underlying SAN and thus improoving your IO budget?
The most brutal thing I have ever seen like that had nearly 30 files all on separate SAN volumes (count of hard discs going close to 200) and it was done as every LUN had a queue limit in the driver of 255 outstanding requests, which the SAN (with a 32 gigabyte cache) was just not caring about ;) THat thing was pulling in nearly 1.5 gigabyte per second over multiple fiber connections.
Related Question
- Sql-server – One filegroup, multiple datafiles, how to get list of tables in each file
- Sql-server – Dynamic Table partitioning on a daily basis
- Sql-server – Database Migration – Create New Filegroup and Move Objects
- Do I lose query performance by using same filegroup for all partitions
- Sql-server – Create table partitions on different filegroups, the first boundary on a Memory Optimised filegroup
- Postgresql – What’s the difference between partitions and many tables
Best Answer
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.