hi we are loading the data from oracle to sql server.using ssis packages(no transforamtions are used in the packages .my few staging tables having more than 100 millions data.we are following below structure 1.drop index 2)loading the Data 3) create index out of 5hrs ETL create index container is taking more than 2 hrs. if i follow this structure will create any issue 1)loading the Data 2) create index . tables are having huge volume of data. pls suggest the best way in the perforamance point of view
My suggestion: 1.drop index 2)loading the Data 3) create index
But, how are you loading your data with SSIS? Did you set a batch size or are you doing a single commit? If you haven't set a batch size, then I'd suggest that you set one. Assuming the table is clustered on one or mor columns, is the data sorted or does the index creation have to re-organize the entire table?
only rebuild/reorganize the index based on their fragmentation level. fragmentation level>30 rebuild index fragmentation level between 5 and 30 reorganize index
ahmeds08 - why use "30" as the fragmentation level for index rebuild? I've done loads of tests and found a higher level to be more effective - I do a rebuild if frag levels are higher than 50.
ahmeds08 - why use "30" as the fragmentation level for index rebuild? I've done loads of tests and found a higher level to be more effective - I do a rebuild if frag levels are higher than 50.
Without Drop the index data loading ??
Posted - 03/01/2013 : 00:28:08
