Although TimeXtender does provide the Index Generation “Automatic” setting under the Instance and Table Settings that allow TimeXtender to manage the SQL indexes on the TimeXtender data warehouse data, it is crucial to note that TimeXtender 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's 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, TimeXtender 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.