Optimize SQL FOR SP2010

• Respect software boundaries & limits, review the support article and read the maintenance whitepaper
• Use PowerShell to create your farm, the farm configuration wizard is bad
• Dedicate a SQL instance to SharePoint
• Differentiate between & prioritize your disks: tempdb, logs, search, other
• Align all partitions correctly (1024K)  and format SQL partitions with the right cluster size (64K)
• Test your SQL disks before using them with sqlio
• Only install the features you need (database engine & maintenance tools at minimum)
• Use a domain user for service account
• Enable instant file initialization
• Configure maximum memory
• Fill factor to 80
• MAXDOP to 1
• Consider multiple data files for your content databases
• Tune autogrowth to a fixed number
• Presize your data & log files
• Use SQL aliases to connect SharePoint to SQL
• Check database integrity weekly with dbcc checkdb
• Learn about index fragmentation, how SharePoint deals with it and what you need to do manually
• Don’t shrink databases unless you’re very sure (causes fragmentation)
• Autoshrink is pure evil, never activate it – certainly not with SharePoint
• Learn about the full & simple recovery models and take a look at how logging & recovery works
• In full recovery model you always need to take log backups or your log file will grow indefinitely  – no matter how many full backups you run