SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Optimising SQL Server for Sharepoint 2010

I attended the SharePoint Best Practices Conference this week in London. As a complete newbie, this event was superb - a lot of quality speakers, experts in the world of SharePoint and loads of knowledge to share.

One of the sessions on Day 2 revolved around what DBAs need to be aware of in relation to the many databases that a SharePoint implementation may have.  Speaker was Brian Alderman

What is immediately apparent is the need for DBAs and SharePoint guys to integrate with one another – somewhat alien in my world !!

A few interesting points mentioned:-

90% of SharePoint content is stored in SQL Server ???

When configuring SharepPoint farms – be wary because the Databases are created with horrible GUIDs !

Central Admin content is stored in its own database.

Most service applications will have at least one Content DB. And all web applicatons will have at least one Content DB

A farm has several DBs (around 20!!! if run through the farm configuration wizard)

Site Collections can reside in only one Content DB. Content DBs can contain many site collection

NEED TO CONTROL SIZE OF DATABASE ! (for performance & recoverability) – You can control its size via quota templates and max no of site collections etc

DBAs need to be cautious over TempDB ina normal DB world, with Sharepoint, there is extensive “alphatising” – it uses tempdb for filtering numerous documnets etc – so as always  storage considerations are required.

Recommended size on content DB – 200GB !! (use to be 100GB) – not a hard limit though! needs to be driven by SLA

SQL Configurations – must dos

Model DB file settings – increase initial size, increase autogrowth.

TempDB – increase initial size, increase autogrowth (use MB not % – gives consistency) create 1 file per server processor, use Simple Recovery model, place on drive different than content database.

Recommended SQL Server 2008 configuration

Dedicated instance – default file locations (move off c:\ to SAN) – min & max memory. Must use collation – Latin1_GeneralCI_as_ks_ws 

Default tempdb size 8mb – this will be prone to fragmentation – so change it. Need to determine good initial size – think about every DB for every element of SP

Need to avoid clash/contention for memory. Keep DBs evenly distributed.

These are just a few points notes I scribbled down, I’m now off to research SharePoint and in particular what us DBAs need to be aware of, if any of you have any gems of useful information – please pass them my way.


Posted by MothInTheMachine on 5 December 2011

We had a microsoft field support rep come in recently. he said the optimal Fill Factor for pages should be set to 80% on the server..that is for all SharePoint 2010 databases. This, according to our rep, is the best setting for SP2010.

Also the Max Degree of Parallelism should be set to 1 for SharePoint 2010. This is not something you'd do normally but for SP it's the bomb. It improved our performance right away.

It's also important to limit the number of site collections you put on a database. Be familiar with technet.microsoft.com/.../cc262787.aspx. If a contentDB grows too large it is time to rethink the architecture or if multiple site collections are housed on a contentDB move them to their own db.

Leave a Comment

Please register or log in to leave a comment.