Tell me more ×
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 2 down vote favorite

What is the pros and cons of creating the database for a operational system on different file groups? I know it is useful if you want to keep the static part of a database online when the other part (which is on a different File Group) can be restored. However this scenario is not useful for us (according to the nature of the system).

Based on your experience, is there any other benefit that we can get if create the database on the different File Groups? If so, what would be the best practice in assigning file groups?

Thanks a lot.

share|improve this question

1 Answer

up vote 5 down vote accepted

One of the main benefits of utilizing multiple files and filegroups is that you have great control over file growth. Also, prominently you can control and optimize I/O performance, as putting database files on separate physical disks can lead to faster I/O.

If you have an I/O expensive query across two tables, putting then on different disks can lead to better performance as there won't be a single physical disk servicing the query.

Take a look at this resource for more information on the benefits: http://technet.microsoft.com/en-us/library/ms187087(v=sql.105).aspx

share|improve this answer
Thanks for your answer. Does it cause any complication on the database maintenance though? Is there anything we should be careful about when we utilize multiple filegroups? – Nazila Beikpour Sep 11 '12 at 4:14
If you aren't so much after multiple filegroups for piecemeal restorations, then your normal backup routine should be sufficient. Just be a bit more cognizant about your autogrowth settings and your default filegroup (it's normal to have system objects on the PRIMARY filegroup and user objects on separate filegroup(s)). – Thomas Stringer Sep 11 '12 at 12:47
What do you mean by system objects vs user objects? By user objects you mean views, assemblies, stored proc etc. ? – Nazila Beikpour Sep 11 '12 at 21:16
By "user objects" I mean user-created and schema-scoped objects. System objects ship with the database and are "built in". – Thomas Stringer Sep 12 '12 at 12:03

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.