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

Allowed : DBCC_CHECKDB WITH REPAIR_FAST and REPAIR_REBUILD

Not allowed : DBCC_CHECKDB WITH REPAIR_ALLOW_DATA_LOSS

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

image

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 http://ola.hallengren.com/ 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

Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: