Root - Rebuild Indexes

The 'Fragmentation Threshold' property determines which database indexes will be rebuilt by the command, based on their current fragmentation level. Only indexes with a fragmentation percentage equal to or greater than this value will be selected for rebuilding. This allows you to focus maintenance efforts on indexes that are most likely to benefit from optimization, reducing unnecessary processing and downtime.

Configuration

• Enter a value between 0% and 100%, representing the minimum required fragmentation as a decimal percentage.
• The default value is 25% fragmentation.
• Lowering the threshold will include more indexes in the rebuild, potentially increasing maintenance time.
• Raising the threshold will limit rebuilds to only the most fragmented indexes, reducing impact but possibly leaving moderately fragmented indexes untouched.

Impact

Setting an appropriate fragmentation threshold helps balance system performance with maintenance efficiency:

• Lower values (e.g., 10%) may improve overall database performance but increase the duration and resource usage of the rebuild operation.
• Higher values (e.g., 40%) reduce maintenance time but may allow some performance degradation to persist in moderately fragmented indexes.

The 'Table Filter' property allows you to select one or more database tables whose indexes will be rebuilt by the command. If left empty, all tables in the Grooper repository will be included by default.

Configuration

• Use the checklist to select specific tables for targeted index maintenance.
• Leave the list empty to rebuild indexes on every table in the repository.

Impact

Limiting the operation to selected tables can reduce maintenance time and minimize impact on system performance. Use this option when only certain tables require optimization, such as after large data changes in specific areas.

The 'Exclude Primary Keys' property controls whether primary key indexes are included when rebuilding indexes on the selected tables. When enabled, any index identified as a primary key (typically named with a PK_ prefix) will be skipped during the rebuild process. This allows you to focus maintenance on non-primary (secondary) indexes, which are often more prone to fragmentation due to frequent data modifications.

Configuration

• Set to true to exclude all primary key indexes from the rebuild operation.
• Set to false to include both primary and secondary indexes, rebuilding all indexes that meet the other criteria.

Impact

• Excluding primary keys can reduce maintenance time and minimize the risk of locking critical indexes during periods of high activity.
• In most environments, primary key indexes are less susceptible to fragmentation, so excluding them is often appropriate for routine maintenance.
• For comprehensive optimization, set this property to false to ensure all indexes are rebuilt as needed.

The 'Timeout' property sets the limit (in seconds) for how long each SQL index rebuild command may run before being terminated. This helps prevent long-running operations from blocking system resources or causing timeouts at the database level.

Configuration

• Enter a value in seconds. The default is 300 (5 minutes).
• Increase the value for large tables or slow database environments.
• Decrease the value to avoid long waits in case of database issues.

Impact

If an index rebuild exceeds the specified timeout, the operation will be stopped and an error will be logged. Adjust this value based on your database size and performance requirements.

Example

To allow up to 10 minutes per index rebuild:

600

For most environments, the default of 300 seconds is sufficient.