Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSIS - Transfer SQL Server Objects Task - Performance Expand / Collapse
Author
Message
Posted Saturday, July 13, 2013 5:05 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 24, 2014 2:26 AM
Points: 167, Visits: 420
Hi,

We have daily schedule to move around 20 tables which have huge data to another Database. The issue is The task is taking more time, i.e, around 1.5 hours, which previously it was taking half an hour..

I tried few trial and errors and also change Database Recovery Model to BULKED-LOGGED.

Please suggest how to improve performance..


Post #1473289
Posted Tuesday, July 16, 2013 3:05 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, April 2, 2014 8:05 AM
Points: 285, Visits: 334
Hi
If you have indexes on destination tables, please drop them before transfer data and create again after.
Keep Identity - by default this setting is unchecked which means the destination table (if it has an identity column) will create identity values on its own. If you check this setting, the dataflow engine will ensure that the source identity values are preserved and same value is inserted into the destination table.

Keep Nulls - this setting is unchecked which means default value will be inserted during insert into the destination table if NULL value is coming from the source for that particular column. If you check this option then default constraint on the destination table’s column will be ignored and preserved NULL of the source column will be inserted into the destination.

Table Lock - by default this setting is checked and the recommendation is to let it be checked unless the same table is being used by some other process at same time. It specifies a table lock will be acquired on the destination table instead of acquiring multiple row level locks, which could turn into lock problems.

Check Constraints - this setting is checked by default, it's highly recommended to uncheck it if you are sure that the incoming data is not going to violate constraints of the destination table. This setting specifies that the dataflow pipeline engine will validate the incoming data against the constraints of target table. If you uncheck this option it will improve the performance.

Br
Mike
Post #1474303
Posted Tuesday, July 16, 2013 3:39 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 11, 2013 2:08 PM
Points: 2, Visits: 20
What destination you are using?

I recommend using OLEDB destination with fast load.
This will use Bulk Insert which eventually improve performance.

SQLCAT team published really nice paper about SSIS best practises .Worth reading

http://sqlcat.com/sqlcat/b/top10lists/archive/2008/10/01/top-10-sql-server-integration-services-best-practices.aspx

Hope it helps!

P
Post #1474316
Posted Sunday, July 28, 2013 1:43 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 8:57 AM
Points: 7,081, Visits: 12,575
It's also worth experimenting with the buffer size and rows settings of your Data Flow:

http://msdn.microsoft.com/en-us/library/ms141031(v=sql.105).aspx


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1478396
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse