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:
USE ContentDatabaseName
GO
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)
GO

Shrinking Database
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

DBCC SHRINKDATABASE shrinks the data and log files for a specific database.
To shrink individual files use DBCC SHRINKFILE.

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.

2015-10-27_140427

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.
2015-10-27_141408

Specify the frequency, the day and the time to run the task. A weekly execution should be enough. Make sure you run the maintenance plan outside working hours.
2015-10-27_141621

Click Next.
2015-10-27_141748

Select Check Database Integrity, Rebuild Index and Maintenance Cleanup Task. Please note: You should either run a index reorganization or index rebuilding, not both. Do not shrink a database.
2015-10-27_141841

You can change the order of the tasks. Click Next.
2015-10-27_142041

Select the databases you want to check for integrity. You can select all SharePoint databases. Click Next.
2015-10-27_142400

Select the databases you want to rebuild the indexes. Click Next.
Select Change free space per page percentage to and type 80. Check Sort results in tempdb. Click Next.
2015-10-27_142725

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).
2015-10-27_144251

Specify a folder where you want to Write a report to a text file.
2015-10-27_144532

Click Finish.
2015-10-27_144811

If all actions completed with success you’re ready to go.
2015-10-27_144859

Please refer to SQL Server best practices in a SharePoint Server farm for more information.