I have watched many SQL Server interviews, and this question stood out because I had rarely heard a candidate asked about filegroups. The candidate gave practical advice, but several points needed a qualification. That is what makes it a good discussion question.
Question: What are the best practices around SQL Server filegroups?
Answer: Use a filegroup when it serves a clear data placement, management, or restore plan. A filegroup is a logical collection of data files. The transaction log is not part of a data filegroup, so log placement and sizing deserve their own decisions.
My original answer suggested placing tables and nonclustered indexes in different filegroups to make writes occur in parallel. That is not a guarantee. Two different drive letters may even share the same physical storage. Measure the workload and understand the actual storage layout before expecting a performance gain.
Another line said a table and its indexes on different filegroups must be backed up as one unit. SQL Server supports file and filegroup backups. The recovery model and the complete restore sequence determine what can be recovered consistently. Under the simple recovery model, a filegroup backup is allowed only for a read-only filegroup. Under full recovery, log backups and the restore plan matter. Test the restore you intend to rely on. Putting the log on a different device does not, by itself, guarantee recovery to the last second; successful backups, the available log and the complete recovery procedure determine the recovery point.
It is still sensible to size files deliberately and leave appropriate autogrowth available as a safety net. The original “log size equals 25 percent of database size” was a guess, not a universal rule. Transaction volume, long-running work, backup cadence, and recovery requirements determine the needed log capacity. Use measured growth and headroom rather than one fixed percentage.
In an interview, I would ask the candidate for one example: a read-only archive filegroup with a documented backup and restore procedure, or a storage placement choice supported by measurements. That tells me more than a list of drive-letter rules.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
3 Comments. Leave new
>> Tables and their non-clustered indexes separated into separate file groups can improve performance, because modifications to the table can be written to both the table and the index at the same time.
I asked Paul Randall about this a few years back. While it would seem to make sense, there does not appear to be a parallelism benefit to separating heap/clustered indexes from non-clustered indexes into separate file groups on separate volumes on separate disks.
Very valid point. Trust me – if Paul says something – he is always right. I trust his words over MSDN.
Separate file group for NC indexes, even though there would be no improvement in terms of writing to disk, because NC indexes pointers will still point a clustered leaf of a heap leaf. In that case until the clustered or heap is written, NC index would have to wait to get the pointers exact location (I guess).
But, reading would definitely gain by separating NC indexes from clustered/ table’s file group.