Tweaking your Sharepoint databases ?

Have you ever wondered how to optimize your SharePoint databases ?

But you are afraid that any changes will result in loss of support from Microsoft, right ?

Well, here is the good news. Some changes are supported (and some are not)

Mainly what you are not allowed to do is making changes to the schema. Changes to the schema are any operations that involves an ALTER, CREATE,DROP statement like:

Not allowed Schema changes

  • Adding triggers to tables
  • Changing primary keys
  • Changing stored procedures or views
  • Modifying tables in anyway
  • Changing key relationships
  • Adding tables
  • Adding indices
  • Changing DB collation

Get the whole list

Database Console Commands



But here comes the nice part !!

You are allowed to run DBCC_CHECKDB WITH REPAIR_FAST and REPAIR_REBUILD. These commands are supported as they update the indexes of the associated databases. Which means that you are allowed to maintain the Indexes that SharePoint keeps.

May be you were told that theres is no need to do that as SharePoint maintains its own indexes ! It is a big fat lie.

SharePoint maintains indexes and statistics in some of its databases. To be sure which databases just crack them open and look after a stored procedure called : proc_DefragmentIndices


This stored procedure is not to be found on every databases that SharePoint uses. For example it is missing from all the Search databases even though they are quite write intensives.

The same goes for statistic maintenance.

So the first thing you need to do is to create a maintenance plan for rebuilding / reorganizing indexes and maintaining statistics on your SharePoint databases  !!

I usually use Ola Hallengren’s solution available at But feel free to use any other solution.

The point here is that you shouldn’t trust SharePoint for a doing a DBA job !

Stay tuned for more SharePoint performance tips.

Happy SharePointing

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: