In my last post on filegroups (it seems like ages ago), I argued that separating tables and their non-clustered indexes between two different filegroups, with each filegroup on a separate I/O path can improve performance. That article was met with skepticism. I think that's a great, healthy thing. So for those that aren't sold on filegroups for performance, consider the following: use filegroups to separate your tables.
The first question that probably jumps into one's mind is - why separate tables? For those that recall with melancholy the SQL 6.5 days (is that even possible?), creating additional filegroups for specific tables partially emulates one of SQL 6.5's features: backing up and restoring a single table. Many used that unique backup feature simple to export a table, easily replaced by DTS and later SSIS. I would imagine that it was pulled simply because all too often an attempt was made to then restore that table, resulting in foreign key chaos. While filegroups have a role in backup/recovery, I'm certainly not going to endorse it as a means of backing up a single table, *without consequence*, as was sometimes the practice with SQL 6.5. I do believe there still are reasons to use filegroups to separate objects though...
For the most part the main driver for separating objects surrounds backup and recovery. I'm not going to talk about backup/recovery of files and filegroups in this post. Even if you don't have a filegroup-based backup scheme, I still believe that these separating objects is a good ongoing practice.
1) Keep user objects out of the PRIMARY filegroup - I can't stress that enough. Most of the I/O will be to user tables, not to metadata tables that automatically go into the PRIMARY filegroup. If there are issues and data gets corrupted, you're in some sort of restore situation. If you loose the PRIMARY filegroup with data corruption, the restore scenario may be more complex and time consuming that it needs to be. Create a separate filegroup just for user data and make that the default filegroup (when creating a table, when was the last time you remember the ON [PRIMARY] clause at the end of it).
2) Seperate functionally similar tables - If you have an application that has discrete areas of operation (say a human resources area, a billing area, an AP area, etc.,), you can decide exactly where to store each group depending on importance. If you have multiple data arrays available, you might put the more active areas on the higher performing disk arrays. Even if you don't have separate disk subsystems to leverage, you're at least putting yourself in a position to be able to do so in the future. Separating existing tables out into filegroups after the fact can be extremely difficult. Tie your functional design so that it aligns to your physical storage design.
3) Separate active from inactive tables - If you have control tables or code tables that shouldn't be updated by users, move them on to their own filegroup and make that filegroup read-only. This isn't really from a performance perspective as it is a data protection standpoint. For example, you could have tax rate tables on a particular filegroup and keep that filegroup read-only, preventing anyone from accidentally changing rates (yes, I've seen that exact thing happen). When you need to update those tables, take it out of read-only, make your changes and then immediately place it back to read-only.
4) Separate current from archival data - Lower cost storage devices are becoming more popular for storing archival data. These devices are high on disk space but poor on performance. With more and more legislation coming out requiring the archival of various types of data, you may need to separate out your active data from your archival data while maintain transparent access (I remember the day when archival meant putting everything in a box and storing it in a warehouse). Filegroups are the way to go. One could point to the production storage environment, the other to the lower cost storage device used for archival. Add in a partitioned view or table and presto, total data access with the current data unhampered by years and years of historical data.
These are just a few ideas on using filegroups to logically separate data - you can probably come up with a dozen more thinking creatively about your own environment.
In the next entry in this series on filegroups, we'll look at specific features with SQL Server that *require* the use of filegroups.