DataWarehouse Performance indexes defragmentation after insert

Question

Hello everybody,

I ask for advice on this issue. We have an Azure SQL DB that every night retrieves, after deleting, thousands of data from different data sources and immediately afterwards a series of ETL Stored Procedures are performed to process and present data to users through reporting programs. The problem is that despite a defragmentation and updating of statistics is performed every day, the following day the indices of the various tables are defragmented again above 50%, putting the performance of the ETL procedures at risk (which in our case means an increase in the execution). Is it possible to somehow avoid or reduce this problem?

Answer 1

One option is to disable all indexes on start, performing the data load and then enabling all index = rebuild. Depending on the data amount this procedure can be fast & better then e.g. an index reorg.