How to Plan SQL Server Database Files

• Log files:
  • Place log files on a different physical partition than data files.
  • It’s not true – using several log files will get a performance gain – log files are written sequentially which means that each log file is filled up before the next one is written to.
• Data files:
  • Place data files on a different physical partition from log files.
  • It’s true – using several data files can get a performance gain – data files use an equal fill algorithm (all of the files are filled equally as much as possible).
• File growth:
  • Do not accept the defaults for file growth.
  • The best is to set both data and log files as large as possible – it can be very expensive (performance wise) to grow files
  • The next best is to set files to autogrow by fairly large predicable increments (grow data file by at least 1 GB).  It’s best to grow files by a specific value than by a percentage.
  • Set all data files to grow at the same rate, and all log files to grow at the same rate.  Data files don’t have to grow at the same rate as log files.

More details see http://www.petri.co.il/sql-server-database-files.htm