A Crash Course in Optimizing SQL Server for SharePoint

• Do not enable auto-create statistics on a SQL Server that is supporting SharePoint Server
• To ensure optimal performance, it is recommend that you set max degree of parallelism (MAXDOP) to 1
• To improve ease of maintenance, configure SQL Server connection aliases for each database server in your farm
• Create a secondary FILEGROUP for each database and mark it as DEFAULT*
• Only create files in the primary file group for the content database
• Spread database files on separate disks
• Use RAID 1 or RAID 10 when possible
• For collaboration or update-intensive sites, use the following ranking for storage distribution: http://technet.microsoft.com/en-us/library/hh292622.aspx
  1. tempdb data files and transaction logs on the fastest disks
  2. Content database transaction log files
  3. Search databases, except for the Search administration database
  4. Content database data files
• In a heavily read-oriented portal site, prioritize data and search over transaction logs as follows: http://technet.microsoft.com/en-us/library/hh292622.aspx

1. tempdb data files and transaction logs on the fastest disks
2. Content database data files
3. Search databases, except for the Search administration database
4. Content database transaction log files

• Set the autogrow property of database files to a percentage. A general rule of thumb you can use for testing is to set your autogrow setting to about one-eight the size of the file. http://support.microsoft.com/kb/315512
• Rebuild indices daily
• Limit a content database size to 200GB

* updated the post as per the guidance from the technet article http://technet.microsoft.com/en-us/library/cc298801.aspx (Storage and SQL Server capacity planning and configuration)