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.

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 Without Drop the index data loading ??

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 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




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?

Go to Top of Page

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2013-03-02 : 05:29:01
A couple of other things , is the target db set to Minimally logged ? do you have the data and log files optimised on different disks? Some other suggestions on : http://www.sqlserver-dba.com/2011/11/data-loading-performance-guide.html

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

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 index
fragmentation level between 5 and 30 reorganize index
Go to Top of Page

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
Go to Top of Page

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...
Go to Top of Page
   

- Advertisement -