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.
Best Answer
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