current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 8 down vote favorite
1

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.

share|improve this question
add comment

3 Answers

up vote 6 down vote accepted

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.

share|improve this answer
    
Reasonable, some written justification for it. I'll accept this –  gbn Feb 1 '12 at 21:36
1  
Another reason is that a PARTIAL database restore allows the restoration of the PRIMARY filegroup plus selected other filegroups allowing quicker recovery of a correctly designed VLDB. Allowing the archived/secondary filegroups to be restored later. –  MartinC Feb 2 '12 at 0:15
    
@MartinC: I know about partial restores etc, but I never understood the logic of explicitly separating the system tables. Filegroups for performamce, archiving, maintenance, partitioning etc. But system tables? Jared offered the best explanation so far.. –  gbn Feb 2 '12 at 5:44
    
If the database as a whole is very large, the primary filegroup could have a more regular backup that the main data. The restore would only require a tail log backup and restore of the primary filegroup and transaction logs since the filegroup backup plus the tail. As the system tables are small this would be a quicker recovery process vs doing this for the whole database so it can reduce downtime in the event of an issue. –  MartinC Feb 2 '12 at 8:31
add comment
up vote 10 down vote

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.

share|improve this answer
    
To learn more about this, you can search for Online Piecemeal Restore in Books Online. –  Brent Ozar Feb 20 '11 at 23:24
1  
I've always thought this is voodoo too given the 2 filegroups are on the same volume (on a SAN). Is the risk of corruption so high? (The actual operational DBAs set AutoShrink false) –  gbn Feb 21 '11 at 5:21
    
Odds are if there is corruption it'll be a single page within a single file as the storage will hickup on writing the page to disk. Something like 99.9999% of database corruption is a storage problem. 1/2 of the rest of the problems are bad memory, the rest are SQL bugs. As the databases get larger (multi-TB) this becomes more important, as restoring a multi-TB database will take several days. –  mrdenny♦ Feb 21 '11 at 22:49
    
Wouldn't I be right in thinking that if the system objects are only in the primary filegroup should you need to in future you will be able to do the following. Create x additional files in another filegroup. Proportionally fill these files by migrating your data from your your existing data file? –  Ally Reilly May 17 '12 at 8:30
add comment
up vote 4 down vote

Not sure I understand, are you asking for someone to justify your corporate standard? I would think that whoever wrote that standards doc for your company would be able to shed some light as to why this would be done.

That being said, it is not unusual for some shops to want to break out system data from user data. And if used in conjunction with dedicated sets of disks, you could reap some performance gains.

share|improve this answer
    
Thanks. Not justify it, but explain it. This is the same DB Engineering team who say AutoShrink on. Given system tables occupy a few MBs and will be in memory anyway, do you believe in any performance gain? –  gbn Feb 20 '11 at 18:54
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.