Thursday, June 19, 2008

How do you maintain SharePoint databases?

My latest post is about how maintaining MSDB database, today I would like to share with you some thoughts about maintaining Sharepoint databases.

Last week I visited our client who has been working with SharePoint (MOSS7) and started complain about performance of SQL Server. I identified long running queries as well as very good number of deadlocks that happened every hour to databases which belong to SharePoint product. However , when I recommend to add some indexes on the tables people start almost crying not to do that as they were told that SharePoint databases are self managed product and DBA should not be touched it at all. It looks to me very strange , but that was my first experience with MOSS and I decided to do some searching on internet.

I found a couple of documents (even published by MS) to read them as if Sharepoint SQL Server performance can be managed by Shrinking & Defragging the DB.
I also asked some Sharepoint people and they say that accessing the database directly, changing anything on their databases
aside from what's provided out of the box, etc. is not supported unless you
do it thru the Sharepoint API. Hmm.... looks strange , does not?

Finally I ended up with sample script to identify very fragmented indexes and running ALTER INDEX index_name ON tablename REORGANIZE;

PS.
I could not imagine a customer being unwilling to create whatever indexes are
necessary to ensure reasonable performance of a production Sharepoint
system.

2 comments:

Unknown said...

The same thing is happening to me, but it is on CRM4.0

John Vivian said...

Though it may seem odd that they don't want you to touch the SharePoint databases especially to add Indexes and improve performance(Things that do not affect the structure or content)It is Microsoft's policy that they will not support a SharePoint Installation if the databases have been modified.
This is probably put in place to stop people like my self who are Developers from changing things in the database and breaking the system.
Unfortunately this leaves us with a database that is deadlocking and a client unwilling to modify it with out Microsoft's stamp of approval.
An important thing to note is that it is not possible to manage or maintain the database through the API

Just one of the joys of working with SharePoint I guess :)

john

PS thanks for the suggestion of reorganizing the fragmented indexes.