Thursday, June 19, 2008

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.

About Me

The goal of this blog is to share my knowledge as a DBA/Developer in SQL Server area. Many years ago I started to collect some tips and tricks from many experts of SQL Server around the world as well as the scripts developed by myself. And I really want to share them with you. Also my intend is to help beginners as well as experienced people such I have been doing at microsoft forums. So please do not hesitate to write me comments or questions.