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.
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.
WITH fg_sizes AS ( SELECT fg.data_space_id AS FGID, COUNT(f.file_id) AS FileCount, ROUND(CAST(SUM(f.size) AS FLOAT)/128,2) AS Reserved_MB, ROUND(CAST(SUM(FILEPROPERTY(f.name,'SpaceUsed')) AS FLOAT)/128,2) AS Used_MB, ROUND((CAST(SUM(f.size) AS FLOAT)/128)-(CAST(SUM(FILEPROPERTY(f.name,'SpaceUsed'))AS FLOAT)/128),2) AS Free_MB FROM sys.filegroups fg LEFT JOIN sys.database_files f ON f.data_space_id = fg.data_space_id GROUP BY fg.data_space_id ), fg_objs AS ( SELECT ID AS FGID, SUM(n) AS TotalObjects FROM ( SELECT au.data_space_id AS ID, COUNT(*) AS n FROM sys.allocation_units au WHERE au.[type] = 1 GROUP BY au.data_space_id UNION ALL SELECT t.lob_data_space_id AS ID, COUNT(*) AS n FROM sys.allocation_units au JOIN sys.partitions p ON au.container_id = p.partition_id JOIN sys.objects o ON p.object_id = o.object_id LEFT JOIN sys.tables t ON o.object_id = t.object_id WHERE au.[type] = 1 AND au.data_space_id <> t.lob_data_space_id GROUP BY t.lob_data_space_id ) q GROUP BY ID ) SELECT fg.[data_space_id] AS FilegroupID, fg.[name] AS FilegroupName, fgs.FileCount, ISNULL(fgo.TotalObjects,0) AS ObjectCount, CONVERT(VARCHAR,CONVERT(MONEY,MAX(fgs.Reserved_MB)),1) AS Reserved_MB, CONVERT(VARCHAR,CONVERT(MONEY,MAX(fgs.Used_MB)),1) AS Used_MB, CONVERT(VARCHAR,CONVERT(MONEY,MAX(fgs.Free_MB)),1) AS Free_MB, ROUND(MAX(fgs.Free_MB)/MAX(fgs.Reserved_MB)*100,2) AS Percent_Free FROM sys.filegroups fg INNER JOIN fg_sizes fgs ON fg.data_space_id = fgs.FGID LEFT JOIN fg_objs fgo ON fg.data_space_id = fgo.FGID GROUP BY fg.data_space_id, fg.name, fgs.FileCount, fgo.TotalObjects ORDER BY Percent_Free DESC;
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.
DECLARE @FGID INT; SET @FGID = ; -- Filegroup ID SELECT QUOTENAME(s.name) AS SchemaName, QUOTENAME(o.name) AS ObjName, o.object_id AS ObjID, p.index_id AS IndexID, QUOTENAME(i.name) AS IndexName, ROUND(CAST(au.data_pages AS FLOAT)/128,2) AS MB_Used, p.data_compression_desc, QUOTENAME(f.name) AS DataFilegroup, QUOTENAME(f2.name) AS LOBFilegroup FROM sys.allocation_units au INNER JOIN sys.partitions p ON au.container_id = p.partition_id INNER JOIN sys.objects o ON p.object_id = o.object_id INNER JOIN sys.indexes i ON p.index_id = i.index_id AND i.object_id = p.object_id INNER JOIN sys.schemas s ON o.schema_id = s.schema_id LEFT JOIN sys.tables t ON o.object_id = t.object_id LEFT JOIN sys.filegroups f ON au.data_space_id = f.data_space_id LEFT JOIN sys.filegroups f2 ON t.lob_data_space_id = f2.data_space_id WHERE au.[type] = 1 AND (au.data_space_id = @FGID OR t.lob_data_space_id = @FGID) ORDER BY SchemaName, ObjName, p.index_id;
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.
SELECT a.FILEID As FileID, a.groupid AS FileGroupID, a.[name] AS LogicalName, a.[filename] AS FilePath, ROUND(CAST(a.size AS FLOAT)/128,2) AS Reserved_MB, ROUND(CAST(FILEPROPERTY(a.name,'SpaceUsed') AS FLOAT)/128,2) AS Used_MB, ROUND(CAST(a.size AS FLOAT)/128 - CAST(FILEPROPERTY(a.name,'SpaceUsed') AS FLOAT)/128,2) AS Free_MB FROM dbo.sysfiles a WHERE a.groupID = 16 ORDER BY Reserved_MB DESC;
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!