Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
kond.mohan
Posting Yak Master
213 Posts |
 Posted - 2013-03-01 : 00:28:08
|
| 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 structure1.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 issue1)loading the Data 2) create index .tables are having huge volume of data.pls suggest the best way in the perforamance point of view |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2013-03-01 : 13:03:47
|
| 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? |
 |
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
|
|
ahmeds08
Aged Yak Warrior
737 Posts |
Posted - 2013-03-22 : 02:00:31
|
| only rebuild/reorganize the index based on their fragmentation level.fragmentation level>30 rebuild indexfragmentation level between 5 and 30 reorganize index |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2013-03-24 : 02:56:58
|
| 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. Jack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
ahmeds08
Aged Yak Warrior
737 Posts |
Posted - 2013-03-26 : 04:43:19
|
quote:
Originally posted by jackv
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. Jack Vamvas--------------------http://www.sqlserver-dba.com
This value is as per microsoft best practice..I use ola Hallengren's script to do this job... |
|
|
|
|
|
|
Without Drop the index data loading ??