Tuesday, June 9, 2009

SQL Server Autogrowth for SharePoint

I was aksed today what Autogrowth setting would be best for a SharePoint Content Database. The simple answer? None! Autogrowth is one of the biggest resource eaters on badly designed SharePoint SQL Farms and can cause serious performance and stability issues (plus wreck havoc with your Hard Disks). The best option is to start big enough for your initial load and plan growth ahead.

Example: You are starting a new SharePoint ECM project and are planning on migrating 5 GB worth of files into the SharePoint Sites at go live. You also realise that you have never used versioning before and your staff will be quite active on about 10% of those files. Expecting to create on average one new version every week. i.e 500MB worth of new data being added every week. Thus scheduling a db file growth of 500MB during every weekend would deal with versioning. Add 100MB for new content to total it up to 600MB for week 1, 610 for week 2, 620 for week 3 and so forth, taking into account the growth of the files and the increase on versioning.

Having a sound versioning strategy with a cap for Major and Minor versions will help reign in this excessive growth!!!!

Obviously you will not always get it right and thus a combination of large Autogrowth (for example in 100MB increments) and weekly scheduled growth will give you the best combination for maximum performance and stability.

The final statement? Try to minimise the amount of times the dbs autogrow as much as you can by planning ahead, and when they do auto grow mak sure the increments are large enough to keep you going withminimal disruption until the next scheduled growth. Don't make them too large either as fetching hard disk space for the db files can put a strain on the Disks at the most impractical times like during peak hours and impair the stability of the system

No comments: