SQL Server Best Practices: File Layouts

In a previous post, I mentioned that you should use Filegroups and I talked a little about those. In this post, the best practice I'll point out is where those files should live.

As a general rule of thumb, more "spindles" in a database are better. What that means is that a larger number of physical drives with the files spread out on them perform better than more files on a single drive. That's within the database itself. For the larger picture, you need to think about the other kinds of files involved in the database as well.

At a minimum, I always recommend that these kinds of files be separated onto different physical drives - not driver letters, mind you - actual hardware:

1. Data
2. Logs
3. TempDB
4. Indexes
5. Backups
6. Operation System
7. Operating System Paging Files

Even within these, Filegroups help you separate the Data and Indexes onto even more physical drives. Other files, like Filestream or Full Text Indexes, also need their own hardware.

Does it really take this kind of planning? Isn't all this a little much? Yes, it really does take this kind of planning, and it isn't too much to ask to plan for good performance, even down at the file level. This is all part of "it's not the database platform, it's you" discussion. Take the time, learn the facts, layout your plan and bask in the glow of the results. OK, maybe that last part *was* a bit much, but you get the idea.