What settings or configuration on the Informatica server, in the Informatica software itself, or on the database servers can be changed to increase Informatica ETL throughput? What are some benchmarks we can set to troubleshoot performance? We are specifically using Netezza as a source and SQL Server as a target.
Please exclude multi-threading and Informatica partitioning from this question.
This we've done in the past:
- restart servers every so often
- remove indexes on target tables in SQL Server before ETL load
- increase commit level
Best Answer
If powercenter informatica is the bottleneck (and not netezza, sql server, or the network) then there is a setting in the powercenter Session that may help. IIRC it's "record buffer size" or something similar. Change it from Default to 512MB. If that helps, it is best to reduce it to something more sensible by experimenting with lower values.
This setting is not the size it uses to hold a single record in memory, but it needs to be at least big enough to fit one record in it. The built-in help is a bit vague on this point.
In the opposite scenario (SQL Server -> Netezza) I've noticed that datatypes can play a part as well. powercenter can grossly overestimate the amount of memory it needs to reserve for a single record if it the source table layout contains LONG/NTEXT/VARBINARY datatypes. Netezza doesn't have those, but perhaps it also matters if the target contains large fields.
The powercenter session log file should contain some information about how much memory it reserves to transfer the data. If it's too low, it can become the bottleneck.