One or more Search property databases have fragmented indices (SharePoint Server 2010)

 

Applies to: SharePoint Server 2010

Rule Name:   Search - One or more property databases have fragmented indices

Summary:   This article gives more information about the SharePoint Health Analyzer rule for Search service application property database defragmentation. The fragmentation of property data indexes occurs on a different level than regular disk fragmentation; it occurs within each property database as data is created, updated, or deleted during normal crawl activity. Running the property database health rule makes used storage contiguous, eliminates unused storage, and compacts the database.

Symptoms:   One or more of the following symptoms might appear:

  • Query latency might increase.

  • Crawl rate may decrease as more time is spent writing metadata to the property database.

  • The property database defragmentation health rule detects that one or more property databases have fragmented indexes and attempts to correct the fragmentation. Note: when this rule is correcting the fragmentation, query latency and crawl rate are affected.

Cause:   Fragmentation exists when indexes have pages in which the logical ordering, based on the key value, does not match the physical ordering inside the data file. All leaf pages of an index contain pointers to the next and the previous pages in the index. This forms a doubly linked list of all index/data pages. Ideally, the physical order of the pages in the data file should match the logical ordering. Overall disk throughput is significantly increased when the physical ordering matches the logical ordering of the data.

Refer to Microsoft SQL Server documentation for more details about database fragmentation, including how to manually detect and repair fragmented indexes.

Resolution:   Enable and run the property database defragmentation health rule

  1. In Central Administration, on the home page, click Monitoring.

  2. On the Monitoring page, in the Health Analyzer section, click Review rule definitions.

  3. On the All Rules page, in the Performance section, click the Search - One or more property databases have fragmented indices rule.

  4. If the rule is not enabled, in the Health Analyzer Rule Definitions dialog box, click Edit Item on the ribbon. Ensure that the Enabled check box is selected, and then click Save on the ribbon.

  5. Click Run Now on the ribbon.

  6. Click Close.

By default, the property database defragmentation rule is enabled to run once a week over all property databases in all Search service applications. When the rule runs, it determines whether a given property database exceeds the preset fragmentation percentage. If a property database exceeds the preset fragmentation percentage, the health rule attempts to perform an online defragmentation first. Then it switches to offline defragmentation where required. In online defragmentation, only the Microsoft SQL Server leaf pages are defragmented, not the SQL Server locked pages. In offline defragmentation, the locked pages and all the leaf pages are defragmented.

Note

On Microsoft SQL Server 2008 Enterprise, the health rule automatically enables the Page Compression feature to optimize I/O and disk usage.