SQL 2000 and 2005: how many files to create in a database
The following KB: http://support.microsoft.com/kb/328551 talks about how many files the tempdb database should have. Is there similar suggestion for regular databases?
Here is my thought: It is hard to predict how many files are needed per file group. There are too many parameters to consider. I think the best approach right now is to do the following in a test system with your workload to find out the best configuration:
Start with one file (or as many as needed to utilize all the disk spindles), and gradually increase the number of files until allocation page contention is no longer an issue. Normally we do not recommend using more than 32 files because there are some downside related to more files: more IAM pages needed for even small tables, for example.
