To keep SharePoint running with optimal performance, it’s recommended to run the following maintenance tasks for your SharePoint databases:
- Check database integrity
- Defragment indexes by reorganizing or rebuilding them
- Set the fill factor for a server
Those tasks may performed by Transact-SQL commands or using the Database Maintenance Wizard.
Check database integrity
DBCC CHECKDB checks the integrity of all objects in a database. It’s recommend to run DBCC CHECKDB instead of DBCC CHECKALLOC, DBCC CHECKTABLE and DBCC CHECKCATALOG commands. DBCC CHECKDB identifies the widest range of possible errors and is therefore safer to run in a production environment. Because of heavy load it’s not recommended to run this check during production hours.
Defragment indexes by reorganizing them or rebuilding them
Since SharePoint 2010 uses SharePoint Health Analyzer rules to automate index defragmentation and statistics maintenance it is no longer required to perform those tasks manual. A daily health evaluation is done automatically for the following databases:
- Config database
- Content databases
- User Profile Service Application Profile databases
- User Profile Service Application Social databases
- Web Analytics Service Application Reporting databases
- Web Analytics Service Application Staging databases
- Word Automation Services database
The following databases do not have an automated maintenance mechanism because they do not typically have much fragmentation. Monitor these databases for fragmentation and rebuild the indexes when fragmentation exceeds 30%.
- Search Administration Database
- Secure Store Database
- State Service Database
- Profile Sync Database
- Usage Database
- Managed Metadata Database
- Business Connectivity Services Database
- PerformancePoint Services Database
Use sys.dm_db_index_physical_stats to determine fragmentation for the indexes. The column avg_fragmentation_in_percent shows you the fragmentation in percent.
Set the fill factor for a server
A server-wide fill factor level of 80 is optimal to support growth and minimize fragmentation for SharePoint.
Use spconfigure (Transact-SQL) to view or change the server-wide fill factor value.
ALTER INDEX performs maintenance operations against an index on a table. Here’s a sample script to rebuild all indexes on a table:
ALTER INDEX ALL ON [database_name. [ schema_name ] . | schema_name. ]table_or_view_name
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = ON)
Shrinking causes index fragmentation and should not be done unless absolutely necessary. Only perform shrinking after you removed a lot of data from a database and you do not expect to use that free space again.
Some guidelines when you think about shrinking a database:
- Do not auto-shrink or schedule shrinking tasks
- Shrink database only when more than 50% of its content is removed
- Shrink only content databases
- Do not shrink during operation hours, it’s a highly resource-intensive operation
- Reorganize the index after shrinking
- Using the TRUNCATEONLY option is not supported
- Using the EMPTYFILE option is not supported
Creating a SQL Server maintenance plan
You can also automate and schedule the discussed maintenance tasks above. To do this open the SQL Server Management Studio, navigate to Management > Maintenance Plans and click on Maintenance Plan Wizard.
Click Next until you reach the Select Plan Properties page.
Specify a Name for your maintenance plan. Click on the Change… button to schedule the job.
If your environment has 10 or more content databases or more than 200 GB content it’s recommend to configure separate maintenance plans.
Check the box to delete Maintenance Plan text reports. Check Search folder and delete files based on an extension and specify a folder for your maintenance plan logs. In the File extension field type txt (without . in front of txt).
Please refer to SQL Server best practices in a SharePoint Server farm for more information.