Using Multiple Filegroups in a SQL Server Database

By:   |   Updated: 2006-11-27   |   Comments (10)   |   Related: More > Database Administration


Problem

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.

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.

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

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.

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Jeremy Kadlec Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor's degree from SSU and master's from UMBC.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2006-11-27

Comments For This Article




Monday, April 21, 2014 - 6:48:27 AM - Valentina Back To Top (30254)
Hi, I have a database with 500 GB, where a sinlge table occupies 92% of the space, I want to move this table to another filegroup, however this table have LOB data, and if I recreate the clustered index the LOB data will not be moved along with the regular data. Can you recommend the best way to do this? Thanks.

Friday, March 16, 2012 - 2:19:20 PM - Jeremy Kadlec Back To Top (16445)

Gabi,

Yes - I have tested separating databases, transaction logs, tempdb, backups, swap space, snapshots, reporting vs. OLTP vs. SSAS, etc. with DASD and some SANs.  In the situations I was in, disk was a bottleneck on the DASD and separating the processing types yielded benefits. 

On the SANs it was a more complex situation.  More often than not the SANs can handle the IO.  In a few situations, the databases needed to be mapped back to their disks and we need to keep particular databases on disk separate from other databases.  In a few situations the larger databases with more intense IO would cause performance problems for smaller less IO intensive databases.  If you are faced with this situation you need to work with your SAN vendor and understand how their technology really works and figure out the best solution. 

HTH.

Thank you,
Jeremy Kadlec

Friday, March 16, 2012 - 1:37:16 PM - Gabi Back To Top (16442)
I believe we all come out with the mratna that we should put the data and logs on different drives just to shut people up. Has anyone tested this, and proved that it helps performance? If so, is it really worth the effort?

Wednesday, February 4, 2009 - 2:32:30 PM - dguillory Back To Top (2688)

M$ is the vendor, I will wait and hope they make changes.  I was hoping to improve performance by balancing the DB in multiple smaller files.  To improve insert and read times.  Thanks all!

Tuesday, February 3, 2009 - 6:56:07 PM - grobido Back To Top (2676)

Do you want to create additional files or filegroups? 

If you create additional files, but still use the same file group this shouldn't make a difference.  If you introduce a new file group than this would cause an issue, becuase objects are place on a particular file group, so if you start to move things around to other file groups this could be an issue and something you would need to manually adjust each time you get a software update.

Is there a particular reason why you want to break this up?

I would also get some more info from the vendor to find out what they think this could break.

Tuesday, February 3, 2009 - 3:19:49 PM - aprato Back To Top (2675)

 I would say don't do it until you speak with your vendor.  If you go ahead and do it and your upgrades fail (for whatever reason - related or not) - they could turn around and not support you or support you at a large cost.   This is a question that's better directed to your vendor.


Tuesday, February 3, 2009 - 2:06:38 PM - dguillory Back To Top (2674)

I have a purchased application that has only one file group (60 Gigs) and I need to update apply upgrades/ service packs to the application every year.  I was told not to break the large file into smaller files as this may prevent upgrades (sql 2005 standard).

 what do you guys think?  Thanks

Tuesday, June 24, 2008 - 11:21:18 AM - kdsturg51 Back To Top (1239)

Thanks for the info.

Monday, June 23, 2008 - 4:10:24 PM - aprato Back To Top (1236)

I've seen this in the past and I vaguely recall having to first move the tables and all indexes from the original filegroup to the destination filegroup by dropping and re-creating the indexes so they are re-created on the destination file group. 

Monday, June 23, 2008 - 1:02:49 PM - kdsturg51 Back To Top (1233)

I had a database with 13 filegroups and a previous DBA had set two files up in each filegroup across two different drives. I used DBCC ShrinkFile(EMPTYFILE) to combine the 2 files in each file group and place on a large SAN drive. I also set the new data file to unlimited growth. When I ran the Shrinkfile, I received a Query Executed Successfully on large .ndf files in a matter of seconds, and when I ran the ALTER Database  Remove File, I received the error that the file could not be removed as it was not empty. Shortly thereafter I received an alert we have set up for databases notifying me that the filegroup was full. Would you please advise on what occurred here?















get free sql tips
agree to terms