Guidelines for Maintaining Clustered SQL Indexes on Incremental Load and History Tables
Although Jet Analytics Data Integration provides an Index Generation: Automatic setting under Instance and Table Settings for automatic management of SQL indexes on Prepare instance data, Jet Analytics Data Integration does not reorganise or rebuild SQL indexes for Incremental Load and History tables. These indexes must be managed manually.
As Prepare instance databases grow, their reliance on indexes increases and query complexity rises with the volume of data. For tables that are not truncated and rebuilt during execution — specifically Incremental Load and History tables — Jet Analytics Data Integration relies on administrators to manage the SQL maintenance of those tables.
Performing index maintenance on every execution would be unnecessary and detrimental to execution performance. Administrators are therefore responsible for deciding when to reorganise or rebuild indexes for optimal query performance.
Fragmentation guidelines
Use the following thresholds as general guidelines for index maintenance decisions:
- Fragmentation below 10%: No action required. At this level, fragmentation has a negligible impact on SQL Server query performance.
- Fragmentation between 10% and 30%: Perform an index reorganisation.
- Fragmentation above 30%: Perform an index rebuild.
SQL Server Maintenance Plans
For large databases, index maintenance is best handled through an SQL Server Maintenance Plan. A maintenance plan can be configured to reorganise and rebuild indexes on a schedule — for example, during off-hours — without manual intervention. Monitor the plan regularly to ensure it completes within its scheduled window and achieves the intended results.