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.
I am sorry to shatter your idolisation of your God but...
ReplyDeleteFirstly, there is no peformance gains to be had by creating one file per CPU on SQL Server 2005/2008 as SQL will quite happily use as many threads as it can to maximise your performance. Unless you are using multiple spindles, but then you'd still be better off creating an appropriate RAID array across the spindles instead. Can you imagine manage managing a database with 32 files because you have 32 cores?
Secondly, database mirroring will not boost performance, it might actually degrade it. It is designed for high-availability purposes, not as a performance boost. You will degrade performance, whether it's asynchronous or synchronous, as there is more resource consumption (including 2 threads per database). Synchronous mirroring will degrade your OLTP peformance on the primary.
Thirdly, I would be concerned with using a fill factor of 70% as all your data in the database would consume 30% more space and crucially memory. which will impact performance! The "correct setting" would require further investigation. It is dangerous to throw up such blanket recommendations without explaining the consequences.
Remember:
"You shall not make for yourself an idol, whether in the form of anything that is in heaven above, or that is on the earth beneath, or that is in the water under the earth."
I don't know where you got some of this advice, but apart from the obvious ones that every DBA knows about, most of the other bits are incorrect or can cause you a lot of problems. Take the HBA advice "Multiple HBAs can lead to write ordering and disk subsystem issues"... not only is this incorrect, it can also cause availability problems if you 1) use only one HBA and it fails, or 2) have a dual port HBA that uses the same bus and circuitry which causes throughput issues. The reality of the situation is that multiple HBA's are recommended in MOST high availability situations providing they support 1) Storport driver model and 2) MPIO Device Specific Modules (DSM's). These are the requirement for any HBA to be certified for Windows 2003 and above anyway. Multiple HBAs can improve availability and performance through load balancing, as well as redundancy in the event of a fault. Just because Greg is an MVP and a Regional Director, it doesn't mean that everything he says is correct... so take care and evaluate eveything that you are told first, that advice in itself is worth more than all the advice you got on your SQL training. IMHO, most SQL MVP's work for either SolidQ or SQLSkills, so you must question whether or not they have any real world experience.
ReplyDeleteThanks for you comments anonymous--as I mention in the original post, I'm not a DBA and am paraphrasing (perhaps badly) from Greg's generalised presentation. Critical evaluation of everything on the internet is a must and I generally gather information by cross-referencing multiple sources (much of this information leads to one or more blog posts); this post was an exception to that rule although I have seen many of Greg's recommendations echoed by other repuatable sources (like MS). See Database Maintenance for SharePoint, SQL Server Best Practices, and Planning and Monitoring SQL Server Storage for SharePoint.
ReplyDeleteFinally, for those interested, here's the slide deck Greg presented.
SharePoint is a difficult beast spanning many systems and areas of niche expertise; I've not been fortunate enough to work with a top notch DBA in a long time so being a pseudo "DBA" is simply one of the many hats I have to wear every day. My advice is to always consider the environment, consider the information and resources you have available (perhaps a really good DBA sitting down the hall?), do a good job, and learn what you can from the experience to do a better job next time!
Ps. Having my own blog means I can worship whoever I want to ;-)
yes, i was at the same session and i think the problem is the paraphrasing of what Greg said. For example, with the one file per CPU, he was talking about the standard recommendations on tempdb, not on other database files.
ReplyDeletethere was no suggestion that mirroring would improve performance. comments were actually about sharepoint not supporting mirroring properly.
he also didn't recommend avoiding multiple hbas. on the contrary, he was talking about using tools like sqliosim to shake out i/o subsystem problems such as multi-path i/o problems before deploying sql server into production.
@Anonymous - Thanks for the additional points. Mirroring was certainly discussed in the context of HA during the presentation but, if memory serves, I also put a question to Greg at the end of the presentation about the differences between clustering and mirroring in terms of performance; the fairly generic response was to favour mirroring for that scenario but whether this holds true in the skewed reality which is SharePoint seems to be up for debate. I've noticed some (dated?) technet documentation indicating "MOSS 2007 does not support the use of SQL Server database mirroring" [http://technet.microsoft.com/en-us/library/cc262910.aspx].
ReplyDeleteI agree with your other points and the Planning and Monitoring SQL Server Storage for SharePoint link in my previous comment clarifies the "one file per cpu" remark: "The number of *tempdb* data files should equal the number of core CPUs".