Data load for one of the table in SQL Server is taking around 22-24 hrs and index rebuild is taking 4-6 hrs. Below are some additional details about the size and data load process.
- Table Size: 800 GB
- Total No.Of Indexes: 11
- No.Of Partitions on the Table :50
- Frequency of Data Load: Monthly Once
- Data Load Pattern: Truncate and Load.Disable all Index and then rebuild post data load
- Source System: Teradata
- Target System: SQL SERVER 2016
- ETL Tool: AB-Initio
Please suggest any optimizations options available such that we can reduce the time for data load and index rebuild process.