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
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.