Although TimeXtender Data Integration does provide the Index Generation Automatic setting under the Instance and Table Settings that allows for automatic management of SQL indexes on the Prepare instance data, it is crucial to note that TimeXtender Data Integration does not reorganize or rebuild the SQL Indexes for Incremental Load and History tables. This article clarifies why these indexes are best managed manually and provides some general guidelines for when and how these indexes should be reorganized or rebuilt.

As data warehouses, along with their underlying SQL databases, steadily expand, their reliance on indexes intensifies. Query complexities escalate with the influx of more data. For tables that are not truncated and rebuilt during execution, TimeXtender Data Integration relies on users to oversee the SQL maintenance of these tables.

Conducting these tasks with each execution might be needless and detrimental to execution performance. Thus, users are responsible for managing SQL indexes for Incremental Load and History tables, deciding when to reorganize or rebuild indexes for optimal performance.

Here are the general guidelines for index optimization:

• Fragmentation is less than 10% – no de-fragmentation is required. It is generally accepted that in the majority of environments index fragmentation is less than 10% in negligible and its performance impact on the SQL Server is minimal
• Fragmentation is between 10-30% – it is suggested to perform index reorganization
• Fragmentation is higher than 30% – it is suggested to perform index rebuild

For extensive databases, it may be best to manage the indexes manually. However, reorganizing and rebuilding indexes can be integrated into an SQL Server Maintenance Plan. These plans should be monitored to ensure that they are completing in the time allowed and achieving the desired results. When suitable, a maintenance plan can reorganize and rebuild indexes during after-hours based on a schedule.