Multiple Data files and Multiple File Groups

2020-04-18 07:12发布

问题:

Dear Experts. I have a question in mind regarding multiple file groups in SQL Server 2005. I am convinced that our database should have secondary data files, because of various reasons like: For availability reasons it's always best to keep only system data in your primary data file (with Sql2k5 and up, so long as the primary data file is available, the database can be brought online, allowing you to repair/restore/etc. non-system data while having as much online as possible).If we can separate out that system catalog data in the primary data file, and put our user data into a secondary file, the primary file is smaller, gets a lot less updates and inserts, and thus the chance of corruption by e.g. a bad disk sector is minimized.

My dilemma is how we can restrict user data from NOT getting into Primary data-file. The only way it appears possible to me is as below:

  • Keeping only the Primary data-file in Primary File-group
  • Creating a Secondary file-group with secondary data files and creating my physical objects like tables/indexes etc on this secondary file-group.

So, please suggest:

  1. Is it always advisable to have a secondary file-group with secondary data-files and leave the Primary file-group with only primary data-file in it? Please suggest otherwise.
  2. With the above configuration, is there any performance impact for small databases say, less than 10 GB in size?

Thanks in advance!

回答1:

Don't bother separating system and user data. It doesn't add anything. In real life, either your MDF is there or it isn't. There aren't many shades of grey.

As for splitting, I wouldn't bother with multiple filegroups until one or more of:

  • I'm approaching terabyte size
  • Very high load
  • More than one large table (not just one large table)
  • Maybe separate indexes based on load/size/large tables

And only if I can have separate LUNs or RAID arrays for each file. Otherwise it's pointless because you're dividing a finite resource between more files

Summary: for most databases, it isn't worth it