Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

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



Group: General Forum Members
Last Login: Thursday, October 9, 2014 11:30 PM
Points: 167, Visits: 427

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

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, March 30, 2016 3:20 PM
Points: 626, Visits: 1,058
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.

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

Hope it helps!

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



Group: General Forum Members
Last Login: Friday, June 24, 2016 4:38 PM
Points: 7,917, Visits: 14,266
It's also worth experimenting with the buffer size and rows settings of your Data Flow:

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