Dealing With Multiple Filegroups

Everything is good in moderation, and when it comes to having multiple filegroups in your database, I believe this to be true there as well. Having multiple filegroups allows you to spread your data across multiple disks, and can also come in handy for features like partitioning. When applied properly and reasonably, multiple filegroups can lead to significant performance improvements.

When applied unreasonably, the result will probably be similar to what I have experienced lately – a large thorn in your side! There's a maximum limit of 32,767 filegroups per database, but I don't recommend having anywhere near that number. One of the systems I've been working with has over 100 filegroups, most of which serve little to no purpose because they contain very few tables and are all located on the same drive. Some are worth keeping as they contain data that's accessed in ways that could benefit from a separate filegroup, but it seems the logic used by those who came before me was "If I'm creating 2 or 3 tables about X, I'll make a filegroup for X as well." This large number of filegroups is not only a pain to manage, but a waste of disk space too, as most of them contain files sized much larger than they needed to be.

I've been spending some time working on paring down the number of filegroups by combining them based on a few factors including size, number of objects they contain, and usage patterns. I've come up with a few queries that have been most helpful with these tasks, and am happy to share them.

As always, these scripts come with ABSOLUTELY NO WARRANTY. It is your responsibility to read and understand these queries before running them, and I highly recommend getting familiar with them in a development environment before running in production.

Filegroup Summary

This will list all of the filegroups in the current database and tell you how many files it contains, how many objects are stored in it, total size of the files, total space used, total free space, and percentage of free space. It's a great starting point for seeing which filegroups might not belong, possibly because they contain very few objects and/or use a low percentage of space. Decisions should not be based totally on the results you see here though, other factors to consider are file activity (from the sys.dm_io_virtual_file_stats DMV) and your knowledge of the data contained in that filegroup.

What's In That Filegroup?

Now that we have our list of filegroups, you'll probably wonder what's in them. This query gives you some more detail about what the filegroup contains such as object & index names, and their size. It also gives the LOB (large object) filegroup for each table, if applicable. (LOB Filegroups can get pretty interesting and are worthy of their own post, so I won't discuss them here.) Since this query may return a large number of rows depending on how many objects the filegroup contains, I recommend only running this on one filegroup at a time.

What Files Make Up That Filegroup?

Finally, you might want to know what files make up each filegroup. This will return the name and path of each file in the filegroup as well as the size, used and free space. As with the previous query, I tend to run it on only one filegroup at a time.

There you have it, a few queries that might come in handy when doing a filegroup audit. If you've never done a filegroup audit before, these should help get you started. Happy querying!