Using Multiple Filegroups in a SQL Server Database
With the recent filegroup tip (Filegroups in SQL Server 2005), the next natural question is: when should I start thinking about using multiple file groups? Since SQL Server uses a single filegroup for each database, moving to multiple filegroups is not a simple decision that should be made on Monday morning. Understanding the key indicators to move to multiple filegroups should serve as a means for implementing multiple filegroups both proactive and reactive IO bound scenarios.
As is the case with any portion of SQL Server, the more proactive you are the better. Unfortunately, you are not always fortunate enough to be able to work on systems that have been able to be planned accordingly or have had unexpected significant growth. With this being said, if you are planning for a new system that you expect to have significant growth, consider multiple filegroups during the database creation and hardware configuration. If you are not so fortunate, do the best you can with the tables and hardware you have to support the IO load from the application(s) because most databases use only the default PRIMARY filegroup when the database is created.
Key indicators to move to multiple filegroups:
- 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
For filegroup implementation related information listed below, please reference Filegroups in SQL Server 2005.
On a side note...
One potential alternative to moving to multiple filegroups is archiving unneeded data. This could have the affect of slimming down the database and alleviating some of the IO issues. This may open up another set of challenges, but maintain the simplicity in your original database.
If the data can be archived, consider this as a means to slim down your database without having to introduce additional filegroups. Archiving the data can be especially beneficial if you do not have new disks where the new filegroups can reside to spread the IO issues. For database archiving related information, please reference Archiving Data in SQL Server.
- Review your databases to understand which tables store the most data and calculate the overall growth rate
- Think about the databases that are rapidly growing and the business changes that could further impact the growth
- Perform capacity planning on your key databases and tables
- Think about the new project that are coming down the pike that can have a significant impact on the database growth
- Based on the information, consider one of more of the techniques in the Filegroups in SQL Server 2005 tip
- Check out the following MSSQLTips.com:
About the author
View all my tips