SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 Without Drop the index data loading ??
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kond.mohan
Posting Yak Master

India
196 Posts

Posted - 03/01/2013 :  00:28:08  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 03/01/2013 :  13:03:47  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
2067 Posts

Posted - 03/02/2013 :  05:29:01  Show Profile  Visit jackv's Homepage  Reply with Quote
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

India
667 Posts

Posted - 03/22/2013 :  02:00:31  Show Profile  Send ahmeds08 a Yahoo! Message  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
2067 Posts

Posted - 03/24/2013 :  02:56:58  Show Profile  Visit jackv's Homepage  Reply with Quote
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

India
667 Posts

Posted - 03/26/2013 :  04:43:19  Show Profile  Send ahmeds08 a Yahoo! Message  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000