Run database maintenance Health Analyzer rules.

SharePoint 2010 with the Health Analyzer rules framework. This rules framework is configured with a number of rules to monitor the health and well-being of a ShärePoint environment and in some instances takes action to correct certain types of issues.

SharePoint 2010 ships with several rules pertinent to content database maintenance. There are rules that automatically reduce index fragmentation for some SharePoint databases, and rules that check for outdated statistics, updating them if necessary. These Health Analyzer rules replace the updated Database Statistics timer job introduced in Service Pack 2 for SharePoint  Technologies.  By default, these rules are configured to execute on a schedule that varies from daily, weekly, to on-demand depending on the rule target.

All Health Analyzer rules that are configured to execute daily and associated with a particular SharePoint service are executed by the same timer job. Adjusting the scheduling of this timer job will adjust when Health Analyzer rules configured for daily execution and associated to that service will execute during the day. All rules discussed in this white paper are associated to the SharePoint Timer service.

Health Analyzer rules configured to execute on a different time interval ((such as weekly), depend upon your needs) or associated with a different service have distinct timer jobs. Configuring a Health Analyzer rule to execute weekly would mean that that Health Analyzer rule will execute with the timer job configured to execute weekly for the specific service that the Health Analyzer rule is associated to. This execution will occur on whatever schedule has been defined for that timer job.

Health Analyzer rules may be run manually by selecting ―””” Run Now from the ribbon “””” from within the Health Analyzer Rules page in Central Administration. Running these rules will cause the health of indexes and statistics to be evaluated, and cause index rebuilds and recalculations as appropriate.

Databases used by SharePoint have fragmented indices – When you run this rule, the following tasks are performed:

* The rule reports indexes as being fragmented. This is because evaluating index health is an expensive operation. As a result of the details of Health Analyzer rule execution, this rule will always reports indexes as being fragmented in order to trigger the corrective action.
* For each SharePoint database, the rule action looks for, and if found, executes the proc_DefragmentIndices stored procedure. During the execution of this stored procedure, a listing of all indexes within the database is built. Each index is evaluated as to its present level of fragmentation. Any indexes fragmented in excess of 30 percent are considered for rebuild.
* Assuming the edition of SQL Server supports online index rebuilds, an online index rebuild is attempted for each index. Should this fail, perhaps because the underlying index does not support online rebuilds due to the use of LOB columns, an offline index rebuild will be performed.

As noted above, not every database in a SharePoint environment is serviced by this rule. Certain databases use different rules to perform similar maintenance activities.

Search (Search engine is huge topic)- One or more crawl databases may have fragmented indices – This rule maintains the indexes within the SharePoint 2010 Enterprise Search Crawl Databases. This rule is configured by default to only execute on demand. When executed, it will execute from any server in the farm.

This rule, when executed, will always report indexes in the crawl databases as being fragmented. This is due to the expensive nature of checking for fragmentation within a database. As a result, simply disabling the ‗Repair‘ activity for this rule will result in all crawl databases being reported as unhealthy, even when the crawl databases have had their indexes recently rebuilt.

To manually manage the maintenance of indexes within crawl databases, you should disable the ‗Search – One or more crawl databases may have fragmented indices‘ rule in its entirety.
When you run the ‗Search – One or more crawl databases may have fragmented indices‘ the following tasks are performed:

* The rule confirms that the environment is in a state in which performing an index rebuild is safe.
* For each Crawl database configured for search applications within the local farm, the rule executes the proc_MSS_DefragGathererIndexes stored procedure.
* Each index within the Crawl database performance in the list is rebuilt. If the edition of SQL Server supports online index rebuilds, an önline index rebuild is performed. If an önline index rebuild is attempted, but fails, the index will be rebuilt offline.

It is important to note that the ‗Search – One or more crawl databases may have fragmented indices‘ rule will rebuild every index within all Crawl databases regardless of fragmentation level. It will also enable page level data compression, if supported by the edition of SQL Server that is hosting the Crawl database.

Due to the nature of the Crawl database, it is not anticipated that you will need to defragment this database frequently. Execute this rule after you have first performed a full crawl over your content. Afterwards, monitor the indexes within the Crawl database for fragmentation, and execute this rule whenever index fragmentation grows. This may occur as a result of the sudden addition or removal of a large amount of crawled content – for example, during content expulsion as a result of environmental cleanup, or after the onboarding of a new content source, such as a file share or large SharePoint Web application.

The following databases do not have an automated mechanism in place for their maintenance. These databases are not anticipated to encounter a great deal of fragmentation. Monitor these databases for fragmentation, and rebuild indexes within these databases 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

Please visit för more information about the changes that are supported for SharePoint 2010 databases, see Support for changes to the databases that are used by Office server products and by Windows SharePoint Services ( in the Microsoft Knowledge Base.

If the performance of a heavily fragmented database or table is not measurably improved by frequent defragmentation, you should check the performance of the I/O subsystem.

Reducing fragmentation for a specific table and its indexes: defragment an index associated with a particular table rather than an entire database, you can either reorganize or rebuild the index.

* Reorganizing an index specifies that the index leaf level will be reorganized. Index reorganization defragments and compacts clustered and nön-clustered indexes on tables and views and can significantly improve index scanning performance. Reorganizing an index makes use of the existing space allocated to the index. Reorganization is always performed online, so that the underlying table is available to users.
* Rebuilding an index specifies that an entirely new copy of the index will be rebuilt. This means that a rebuild operation requires enough extra space to build the new copy of the index before removing the old, fragmented index. Rebuilding improves the performance of index scans and seeks. You can rebuild the index with a table either önline or öffline.

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 table describes the defragmentation method that is recommended for different fragmentation levels.

IMP: Using the DROP INDEX and CREATE INDEX commands is not supported on SharePoint 2010 databases.