As a SQL developer, I never had to worry much about storage. It was someone else’s responsibility to manage it, and my focus was on coding. However, when I transitioned to the admin side of things, I quickly realized how little I knew about storage management in SQL Server.

One of the key concepts in SQL Server storage is file groups. File groups are logical constructs that group the physical files that store the data. Every database has at least one file group, known as the PRIMARY file group. It is the default file group where everything gets dumped. However, there is a consensus in the SQL Server community that PRIMARY should be reserved for system objects, and additional file groups should be created to house user objects.

Creating additional file groups is important for better organization and performance. By specifying a secondary file group as the default, you can ensure that only system objects reside in the PRIMARY file group. There is a limit to the number of file groups that can be created, but it is highly unlikely to reach that limit in practice.

Each file group needs at least one file in it to be useful. The number of files in a file group can impact performance. SQL Server uses a “round-robin” approach to distribute data across files in a file group. Proportional fill ensures that files with more free space receive a larger share of data. Ideally, all files in a file group should be similar in size and have similar amounts of free space to evenly distribute incoming data.

So, how many files should you have in a file group? The answer is, “It depends.” For slow growth groups, such as domain values, one file might be sufficient. However, larger and busier groups should have more files. Some experts suggest using the number “eight” as a guideline, while others base it on the number of NUMA cores. Ultimately, the optimal number of files will vary based on your specific workload and requirements.

Having only one file group with a single file is not recommended, especially for databases larger than a few megabytes. It can lead to performance issues and difficulties in managing large amounts of data. I am currently facing the challenge of distributing 800GB of data from a single MDF file in two of my databases.

Another issue I have encountered is file groups, usually PRIMARY, with a large number of files. In one database, I have 37 files on the PRIMARY file group, all of varying sizes. This can result in inefficient file usage by SQL Server. To address this, I am considering creating more file groups with fewer files to achieve a more balanced distribution.

Storage management in SQL Server is a complex topic, and I am still learning as I go. I would love to hear from other DBAs about their experiences and challenges with storage. In future articles, I will share my progress and insights as I continue to explore and optimize SQL Server storage.

Stay tuned for more updates on SQL Server storage!