Problem
With the recent filegroup tip (SQL Server Filegroups), 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.
Solution
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.
SQL Server Filegroups Best Practices
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 change significantly. Or when 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
SQL Server Archiving
One potential alternative to moving to multiple filegroups is archiving unneeded data. This could have the effect 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. Archiving data to another database will not 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.
Next Steps
- 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.
Jeremy Kadlec is a Founder, Editor and Author at MSSQLTips.com with more than 300 contributions and 25+ years of SQL Server experience. Jeremy leads a team of more than 300 authors helping millions of SQL Server professionals around the globe every second of the day for the last 20 years. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP based on his community contributions. Jeremy brings 25+ years of SQL Server DBA and Developer knowledge to the community and holds a bachelor’s degree from SSU and master’s degree from UMBC.
