How to optimize SQL Server 2008 to get best performance for SharePoint 2010 farm?
1. Use a dedicated server for SQL Server 2008
2. Configure specific SQL Server 2008 settings before you deploy SharePoint Server 2010
– Do not enable auto-create statistics on a SQL Server that is supporting SharePoint Server
– Set max degree of parallelism (MAXDOP) to 1 for SQL Server instances that host SharePoint Server 2010 databases to ensure that each request is served by a single SQL Server process.
3. Harden the database server before you deploy SharePoint Server 2010
4. Configure database servers for performance and availability
5. Design storage for optimal throughput and manageability
6. Proactively manage the growth of data and log files
7. Continuously monitor SQL Server storage and performance
8. Use backup compression to speed up backups and reduce file sizes