The most common performance bottleneck occurs when the Integration Service writes to a target database. Small checkpoint intervals, small database network packet sizes, or problems during heavy loading operations can cause target bottlenecks.
How to identify a Target Bottlenecks?
Read the thread statistics in the session log. When the Integration Service spends more time on the writer thread than the transformation or reader threads, you have a target bottleneck.
How to Eliminate Target Bottlenecks?
1. Increase the database network packet size.
2. Have the database administrator optimize database performance by optimizing the query.
How to Optimize the Target?
1. Dropping Indexes and Key Constraints.
2. Use constraint-based loading only if necessary.
3. Increase Database Checkpoint Intervals (Decrease the number of checkpoints).
4. Configure the flat file target that is local to the Integration Service process node.
5. Use Bulk load option to inserts a large amount of data into relational tables.
6. Minimize deadlock by using different database connection name for each target instance.
7. Increasing Database Network Packet Size
Will see each point in detail in the next blog …