Don’t create many data and log files on the same physical disk array.
Leaving the Autogrow feature on for the data and for the log files can cause fragmentation of those files if there are many files on the same physical disk array. In most cases, it’s enough to have 1-2 database files on the same physical disk.
For heavily accessed tables, place these tables in one filegroup and place the table’s indexes in a different filegroup on different physical disk arrays.
This will improve performance, because separate threads will be created to access the tables and indexes.
For heavily accessed tables with text/image columns, place this table in one filegroup and place text/image columns in a different filegroup on different physical disks.
You can use CREATE TABLE statement with TEXTIMAGE_ON keyword to place text/image columns in a different filegroup. See the SQL Server BOL for details.
Place the log files on other physical disk arrays than those with the data files.
Because logging is more write-intensive, it’s important that the disk arrays containing the SQL Server log files have sufficient disk I/O performance.
If one of your join queries is used much more often than others, place the tables used in this query in different filegroups on different physical disk arrays.
If you have read-only tables, place these tables in different filegroups on different physical disk arrays and use the ALTER DATABASE statement to make just this filegroup READ ONLY.
This not only increases read performance, but it prevents any data changes and allows you to control permissions to this data.
Use the Windows NT Performance Monitor to determine the appropriate number for the data and log files on your server by checking the Disk Queue Length counter.
Consider reducing the number of files and filegroups you have for your databases if the Disk Queue length on your server averages above 3, and continue monitoring once you have made your changes to ensure that your disk I/O is optimum over the long term.
Literature
1. SQL Server Books Online.
2. Microsoft SQL Server 7.0 Performance Tuning Guide
http://msdn.microsoft.com/library/techart/msdn_sql7perftune.htm
3. Microsoft SQL Server 7.0 Storage Engine Capacity Planning Tips
http://msdn.microsoft.com/library/techart/storageeng.htm
]]>