The decision to split up a database up in different filegroups should be taken after having analyzed the current size and future growth of your tables. In my opinion unless you have a large database or tables with millions of rows, you should carefully consider pros and cons, since you may end up creating more performance problems than you fix.
There are some scenarios which could be interesting under certain premises:
- 2 filegroups: data and index
- 3 filegroups: read-only tables, read-write tables, index
- multiple filegroups: read-only, read-write, index, key table 1, key table 2, ...
You have to analyze your environment to decide if filegroups will help with your SQL Server growth, usage and performance needs.
Some key indicators to move to multiple filegroups (from this article):
- When disk queuing is causing application and user experience issues
- If this is the case, consider leveraging additional disk drives with new filegroups housing IO intensive tables
- When particular tables are 10% or more of the database
- If this is the case, consider moving these particularly large tables to separate filegroups on separate underlying disk drives
- Depending on the table size in proportion to the remainder of the tables, consider building a filegroup for individual table(s)
- When non clustered index and data space are equal on large tables
- If this is the case, consider splitting the data and clustered index from the non-clustered indexes
- When an almost equal percentage of read-only and read-write data exist in the database
- If this is the case, consider splitting the read-only data in a separate filegroup as the read-write data
- When insufficient time is available to perform database maintenance
- If this is the case, consider splitting the large tables into separate filegroups on different underlying disks and perform maintenance in parallel
- When the business or application will be changing significantly and the data is going to grow at a much higher rate
- If this is the case, consider working with the users to understand the potential growth
- When archived data resides in the same database as the production data
- If this is the case, consider separate file groups or one or more of the techniques in this tip - Archiving Data in SQL Server
If you find that filegroups could be improve your database's performance, write the code and test the process in a staging environment before you implement the changes on your production servers. Prepare some measurements before you implement the changes and compare them before/after. Since these processes can be very resource intensive and time consuming, perform these procedures during a maintenance period.
Don't forget, when creating new objects (tables and indexes), be sure that the objects are created in the correct filegroup to ensure expected performance and periodically validate the database objects are in the correct filegroups and correct as needed.