Uneven File Growth Settings in One Filegroup
Blitz Result: Uneven File Growth Settings in One Filegroup
When we create tables or indexes in SQL Server, we specify the filegroup. It’s usually PRIMARY.
We can put multiple files inside that filegroup if we want to spread load across different storage devices. For example, we might have E:\Primary1.mdf and F:\Primary2.ndf, both in the PRIMARY filegroup, and we could use both the E: and F: storage for improved speed.
For best performance, we want both of those files to be equally sized, with the same amount of free space in each file, and for both of the files to have equal filegrowth settings. This part of our SQL Server sp_Blitz script checks sys.database_files looking for multiple files in the same filegroup with different growth settings (percentage, fixed, size, etc).
To learn more, read Using Files and Filegroups in Microsoft Books Online.
To Fix the Problem
Go into SQL Server Management Studio, drill into Databases, and right-click Properties. From there, you can review each data file’s size and growth settings.
In a perfect world, we’d size the data files equally, make sure they have equal free space inside, and set their auto-growth sizes evenly. The problem is that sizing the data files and rebalancing free space can be a disk-intensive operation. For starters, just to fix this particular error, set the growth settings equal for all files inside the same filegroup. If you’re using Instant File Initialization, this can be a fairly large number (say 1GB or higher for <100GB databases) because there’s no pause to write out the new empty space. Just keep in mind that you’ll need to have enough free space on the drive to actually grow the file.
After this change, you won’t see a performance boost immediately, but after you get the file size, free space, and autogrowth settings under control, you should see evenly balanced load across all of your drives. This avoids hotspots and bottlenecks.