Sezai’s been doing a fantastic job of attracting some high-end speakers to the Perth SharePoint User Group and we had the immense pleasure of attending Greg Low‘s presentation on SQL Server optimisation for SharePoint admins this month.
Greg is a god among men in my mind and one of those can’t-miss speakers; apart from being a SQL Server MVP and Microsoft Regional Director, he really knows his stuff and delivers a nuanced presentation. I’d seen Greg in Adelaide on previous occasions and since he’d made his way all the way to Perth just for us, there’s no way I’d miss him.
There were some key take-aways from Greg’s talk I’m paraphrasing here for future reference… note I’m not a DBA and don’t necessary understand all of this—in other words, don’t apply this advice blindly without additional research!
- The autogrow settings should be configured appropriately on both content databases and the tempdb—the SQL Server defaults are inappropriate. In general, this means turning off autogrow and managing database file size manually. Alternatively, autogrow can be left enabled for contingency but should be reconfigured to grow using an appropriate value (in MB, by ~100MB).
- tempdb is rebuilt to the configured autogrow value every time the sqlserver process restarts; the start value should be large enough to avoid file system fragmentation.
- Never, ever auto shrink a SharePoint database as it will only have to regrow again and may increase fragmentation.
- Use DBCC CHECKDB but beware fixing problems may incur data loss.
- Whack your disk subsystem using benchmarking utilities like SQLIO and Iometer. Reasonable latency falls between 5-15ms, for example.
- Instant file initialisation allows SQL to write to files that have not been zeroed out by the OS, thereby avoiding the performance hit incurred by that activity. The MSSQLSERVER service account must be granted the SE_MANAGE_VOLUME_NAME right by virtue of being added to the Perform Volume Maintenance Tasks security policy.
- Multiple HBAs can lead to write ordering and disk subsystem issues; SQL Server 2005 introduced page checksums to help with this issue but the feature is turned off by default; it should be enabled.
- Virtualising database servers faced a lot of bad press in the past but those days are behind us. Hyper-V R2 and ESX are the way forward.
- Don’t even think about SQL Server 2000.
- The gap between SQL Standard and Enterprise is getting wider with SQL Server 2008.
- As most SQL Server instances are disk and memory-bound, consider enabling table/row/page compression in 2008 to reduce IO and memory usage at the expense of CPU. You mileage may vary as this will obviously depend on the content to be stored as photos and Office documents are already compressed. Backup compression is also possible.
- Configure databases with one file per CPU core.
- Different databases have different IO profiles; tempdb should be hosted on a dedicated spindle (see http://technet.microsoft.com/en-us/library/cc262731.aspx for more info about locating the various SharePoint databases).
- Ensure statistics are configured correctly.
- Index fill factor should typically be ~70% for a typical SharePoint environment.
- The default Windows Server 2003 sector size is too small; set it to 64k when formatting drives.
- Clustering SQL won’t boost performance like mirroring will. Clustering happens at the server level whereas mirroring must be configured for every database.
- If configuring a SQL alias, use TCP/IP.
- Create a separate SQL Server instance if an existing database server’s collation doesn’t match SharePoint’s specific requirements. Changing an incorrect collation is a lot of work.