One of the normally neglected areas of SharePoint implementations is maintenance related to the SharePoint database. This is an area that is not generally publicized but routine database maintenance is essential for optimized performance of SharePoint installations. According to a white paper by Bill Baer from Microsoft Corporation, the recommended maintenance tasks for SharePoint databases include:
§ Checking database integrity
§ Defragmenting indexes by reorganizing or rebuilding them
§ Setting the fill factor for a server
§ Shrinking databases to recover unused disk space
The following information is excepted from Bill's article, download the full document here: Database Maintenance for SharePoint White Paper
These database tasks can be performed by either executing T-SQL commands or running the Database Maintenance Wizard. NOTE: the maintenance plan should be run during non-production hours due to potential degradation of performance during the operation.
The database maintenance operations should start with consistency check to ensure the data and indexes are not corrupted. Database consistency may be affected when a db server is improperly shut down, a drive fails or there are noticeable performance and availability issues. The recommendation is to implement a weekly database consistency check through the DBCC CHECKDB operation.
Next, the maintenance plan should either reorganize the indexes or rebuild the indexes but not both. The recommended approach is to defragment indexes as this is more beneficial to database performance than defragmenting tables and this operation performs much faster. You should understand which tables and indexes are most fragmented via the variousdb reports available. The fragmentation level of an index determines the method you should use to defragment it and whether it can remain online or should be taken offline. The following table represents guidelines for the method used for defragmentation.
|
Fragmentation level |
Defragmentation method |
|
Up to 10% |
Reorganize (online) |
|
10-75% |
Rebuild (online) |
|
75% |
Rebuild (offline) |
Note: Using the DROP INDEX and CREATE INDEX commands is not supported on SharePoint databases.
The next step to improve index data storage and performance is to set a fill factor. For most situations, the default server-wide fill factor level of 0 is optimal, but for SharePoint a server-wide setting of 70 is optimal to support growth and minimize fragmentation.
Lastly, while it is not recommended to setup a maintenance plan to auto-shrink SharePoint databases, this action should be performed regularly. SharePoint databases do not automatically shrink although many activities create space within the database including deleting documents libraries, lists, list items and sites. Note shrinking databases is a resource intensive operation and must be scheduled accordingly. Also, shrink only content databases. The configuration, SSP and Search databases do not experience enough deletions to contain significant free space.
Regularly monitoring the statistics of the SharePoint databases and performing routine maintenance can result in significant improvements in the health and performance of SharePoint. As always before implementing a maintenance plan, make sure you have an appropriate SharePoint and SQL backup and recovery strategy in place and working properly. Also, test the impact of the maintenance plan operations on your system and the time required to execute the operations.
