SQL Server Optimization tips

Set a reasonable size for your database.
    First of all, before database creation, you should estimate how large
  your database will be.
  To estimate the reasonable database size, you should first estimate
  the size of each table individually, and then add the values obtained.
  
Set a reasonable size for your 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 should set the size of the transaction log
    to 4-5Mb, but if the estimated database size is over 500Mb,
  then 50Mb can 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 increase allocated resources
  when necessary without DBA intervention.  The Autogrow feature is
  necessary when there is no DBA in your firm, or your DBA has limited
  experience.
  

Set a reasonable size for the Autogrow increment.
  Automatically growing does result in some performance degradation,
  therefore you should set a reasonable size for the autogrow increment
    to avoid automatic growing too often. Try to set the initial size
  of the database and the size of the autogrow increment so that
  automatic growing will only arise at most once per week.

Don't set the autoshrink feature.
    Autoshrinking results in some performance degradation, therefore you
  should shrink the database manually or create the schedule task
  to shrink database periodically during off-peak times, rather than
  setting the autoshrink feature.
  

Set the maximum size of the data and log files.
  Specify the maximum size for which the files can grow in order to
  prevent disk drives from running out of space.

Create a user-defined filegroup and make it the default filegroup.
    In general, it's a good decision to store and manage system and
  user objects separately from one another. This is so that the user
  objects will not compete with system objects for space in the primary
   filegroup.  Usually a user-defined filegroup is not created for the
   small database if, for example, your 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. This will allow you to group user
    objects with similar maintenance requirements into the same filegroup.

If you have several physical disks, try to create as many files
  per filegroup as there are physical disks, and put one file
    per disk.
  This will improve performance, because when table is accessed
  sequentially, a separate thread is created for each file to read
  the table's data in parallel.

Don't create many data and log files on the same physical disk.
    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.
  In most cases, it's enough to have 1-2 database files on the same
    physical disk.

For a heavily accessed table, place this table in one filegroup and
  place the table's indexes in a different filegroup on different
  physical disks.
  This will improve performance, because separate threads will be
    created for the table's and index's data in parallel.

For a heavily accessed table 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 a CREATE TABLE statement with TEXTIMAGE_ON keyword to
  place text/image columns in a different filegroup.
  See SQL BOL for details.

  Place the log files on different physical disk(s) than data files.
  Because logging is more write-intensive, it's important that
  the disks containing SQL Server log files have sufficient
   disk I/O performance.
 

If one of the join queries is used most often, place the tables
  used in this query in different filegroups on different
  physical disks.

If you have read-only tables, place these tables in different
    filegroups on different physical disks and use ALTER DATABASE
  statements to make just these filegroups READ ONLY.
  This not only increases read performance, but it prevents any
  data changes and allows you to control permissions to this data.
  

Use Windows NT Performance Monitor to determine the appropriate
  number of the data and log files by checking the Disk Queue Length
  counter.
  The more database files and filegroups, the more difficult
    administering this database will be. Consider reducing the number
  of files if Disk Queue length is above 3, and continue monitoring.