question

BecchioMichele-4379 avatar image
0 Votes"
BecchioMichele-4379 asked BecchioMichele-4379 commented

DataWarehouse Performance indexes defragmentation after insert

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?

sql-server-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered BecchioMichele-4379 commented

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.

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you,
there are thousand of record deleted and inserted during the night I will try to disable all index and rebuild I hope that this solution will help the next etl process to query and transform data.
Thanks
Michele

0 Votes 0 ·
ErlandSommarskog avatar image ErlandSommarskog BecchioMichele-4379 ·

You should probably not disable the clustered index, because then you cannot insert. And dropping the clustered index build later, rarely pays off. But disabling NC indexes and re-enable later is absolutely the way to go.

2 Votes 2 ·

thank you,
I will follow your suggests.
Michele

0 Votes 0 ·