Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips













































   Got a SQL tip?
            We want to know!

Using Multiple Filegroups in a SQL Server Database

MSSQLTips author Jeremy Kadlec By:   |   Read Comments (10)   |   Related Tips: 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



Last Update: 11/27/2006


About the author
MSSQLTips author Jeremy Kadlec
Jeremy Kadlec is the CTO @ Edgewood Solutions, co-founder of MSSQLTips.com, Baltimore SSUG co-leader and SQL Server MVP since 2009.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Monday, April 21, 2014 - 6:48:27 AM - Valentina Read The Tip
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 Read The Tip

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 Read The Tip
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 04, 2009 - 2:32:30 PM - dguillory Read The Tip

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 03, 2009 - 6:56:07 PM - grobido Read The Tip

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 03, 2009 - 3:19:49 PM - aprato Read The Tip

 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 03, 2009 - 2:06:38 PM - dguillory Read The Tip

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 Read The Tip

Thanks for the info.


Monday, June 23, 2008 - 4:10:24 PM - aprato Read The Tip

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 Read The Tip

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?




 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.