Filegroup Optimization Tips
To optimize the performance of your SQL Server, consider the following:
Set a reasonable size of your database.
First of all, before database creation, you should estimate how big your database will be. To estimate the reasonable database size, you should estimate the size of each table individually, and then add the values obtained. See this link for more information: Estimating the Size of a Table
Set a reasonable size for the transaction log.
The general rule of thumb for setting the transaction log size is to set it to 20-25 percent of the database size. The smaller the size of your database, the greater the size of the transaction log should be, and vice versa. For example, if the estimated database size is equal to 10MB, you can set the size of the transaction log to 4-5MB, but if the estimated database size is over 500MB, the 50MB may be enough for the size of the transaction log.
Leave the Autogrow feature on for the data files and for the log files.
Leave this feature to let SQL Server to automatically increase allocated resources when necessary without DBA intervention. The Autogrow feature is necessary when there is no DBA in your firm or if your DBA doesn’t have a lot of experience.
Set a reasonable size of the Autogrow increment.
Setting a database to automatically grow results in some performance degradation, therefore you should set a reasonable size for the Autogrow increment to avoid automatically growing too often. Try to set the initial size of the database, and the size of the Autogrow increment, so that automatic growth will occur once per week or less.
Don’t set the Autoshrink feature.
Autoshrinking results in some performance degradation, therefore you should shrink the database manually or create a scheduled task to shrink the database periodically during off-peak times, rather than set Autoshrink feature to on.
Set the maximum size of the data and log files.
Specify the maximum size to which the files can grow to prevent disk drives from running out of space.
Create a user-defined filegroup and make it the default filegroup.
It’s a good decision in most cases to store and manage system and user objects separately from one another, so the user objects will not compete with system objects for space in the primary filegroup. Usually, a user-defined filegroup is not created for small databases, for example, if the database is less than 100Mb.
Create a user-defined filegroup and create some tables in it to run maintenance tasks (backups, DBCC, update statistics, and so on) against these tables.
LOAD TABLE and DUMP TABLE are no longer supported in SQL Server 7.0 (and higher), but you can place a table in its own filegroup and can backup and restore only this table. So you can group user objects with similar maintenance requirements into the same filegroup.
If you have several physical disk arrays, try to create as many files as there are physical disk arrays so that you have one file per disk array.
This will improve performance, because when a table is accessed sequentially, a separate thread is created for each file on each disk array in order to read the table’s data in parallel.