SQL Server: filegroup for system tables only?

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.

The isn't a performance gain to this, there is a recoverabily gain to be made. If file corruption happens in the system tables then the database is lost. If you keep the user data in a separate file group (or groups) then you can restore just those files keeping the rest of the database online during the restore (assuming Enterprise Edition here).

If this is why they state this, I can't say, but this would be a benefit of having multiple file groups with just the system objects in the PRIMARY filegroup.

You should however kick then in the junk for saying that AutoShrink should be enabled.

StackExchange Database Administrators Q#1359, answer score: 12