Here is another question I just heard during a recent interview in the multinational company. I have observed over 1000s of interview, but this is the first time I have heard this is being discussed and I was very much glad to hear it. I have listed the answer of the candidate, right below it as well.
Question: What are the best practices for filegroups in SQL Server?
Answer:
It is suggested to place transaction logs on separate physical hard drives. In this manner, data can be recovered up to the second in the event of a media failure.
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.
If tables and their corresponding indexes in a different file group, they must be backed up the two file groups as a single unit as they cannot be backed up separately.
Set a reasonable size of your database and transaction log (25% of database size).
Leave the Autogrow feature ON for the data files and for the log files with reasonable size of autogrow increment.
Place the log files on another physical disk arrays than those with the data files to improve I/O Performance.
Reference: Pinal Dave (https://blog.sqlauthority.com)
4 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.
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.
Interview Question of the Week #033 – How to Invalid Procedure Cache of SQL Server?