SharePoint uses a single table (i.e. dbo.AllUserData) with columns like nvarchar1, nvarchar2, ..., nvarchar64 or int1, int2, ..., int16 (193 columns altogether) to store all items from all lists in all site collections.

  • Can creating new indexes, recomputing statistics, etc. help?
    Let's forget for now that it very likely to lose Microsoft support for SharePoint installation modified like this.

  • Am I right in thinking that such a design makes all classic databases tuning techniques useless?

  • Has anyone ever tried to solve performance problems of SharePoint in any other way than adding more/faster hardware?


There is not a lot you can do to tune the database it self. You aren't allowed to create indexes, or statistics.

Keep your content databases small. If they get to 100 Gigs you'll want to add another content database (there's ways to do this through the SharePoint UI).

As the tables grow you will want to be sure that you are doing index rebuilds and updating stats regularly.

You can look at using the EBS feature if you are using SharePoint 2010 (might be in SharePoint 2007 as well) which will allow you to move the blob data out of the database and store it on the file system using SQL Server's FILESTREAM feature.

Short of that there isn't much you can do besides throwing more hardware at the problem.

| improve this answer | |
  • MrDenny, may you tell me mich services in Sharepoint handle index rebuilds and update stats? Also How can I split the content DB's? through Sharepoint or modify SQL files to have multiple data files? – RayofCommand Nov 30 '15 at 17:38
  • SharePoint doesn't handle index rebuilds and stats updates. You have to do that in SQL Server directly. As for splitting the content databases, that has to be done through SharePoint. – mrdenny Nov 30 '15 at 21:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.