For a long time in my career I heard various advice on how many files you should create for a database. Since SQL Server 7, when it we had filegroups and multiple files, I've heard various pieces of advice, and honestly I'm not sure what the truth is and what is urban legend. Over the years I've heard
- One file per disk array
- Use multiple files for performane since each gets its own thread
- One file per CPU
- One file per core.
I've seen a few postings recently where people were asking questions and even some of the very technically savvy MVPs have been confused by this. While reading through a few of the posts, I found this entry from Paul Randal on Search Engine Q&A #12: Should you create multiple files for a user DB on a multi-core box?
The entry is great in that it tries to explain some of the reasoning behind the advice and debunk some of the advice from various sources, including MS and hardware vendors.
The bottom line is that multiple files per CPU (or core now) really applies to tempdb, when you have allocations occurring from multiple threads. And then it's like 1 file per 2 or 4 cores, something you'd want to test.
For your database files, you separate things out when it makes sense. When you can affect performance by either moving heavily used tables/indexes to separate arrays (not just separate files). Or when you can move very lightly loaded, or needed data, like archive data, out of the way (think partitioning here) to ensure that it doesn't need to be scanned when it's not needed.