One of our corporate standards to is have a separate filegroup/file for user tables/indexes. This is set as the default so no need to qualify CREATE TABLE statements.
So it looks like this
- fileid 1 = system tables, MDF
- fileid 2 = t-log = LDF
- fileid 3 = user stuff = NDF
Can anyone here help me understand the original justification why this was mandated?
I'll come clean and state I think it's voodoo. Am I wrong…?
Edit: I am aware of how to use filegroups for separation of indexes/partitions/archives, as well as how to restore piecemeal. This question is about the use of a separate filegroup on the same volume for system tables only.
Best Answer
Microsoft's 70-432 training book says "The main reason not to place any of your objects on the primary file group is to provide as much isolation in the I/O as possible. The data in the system objects does not change as frequently as data in your objects. By minimizing the write activity to the primary data file, you reduce the possibility of introducing corruption due to hardware failures. In addition, because the state of the primary filegroup also determines the state of the database, you can increase the availability of the database my minimizing the changes made to the primary filegroup."
So, take that as you will. Others say this is not necessary in certain circumstances and of course is more to maintain. Just thought I'd provide Microsoft's reasoning.