How to Enhance SSIS performance when running a data flow?

Question

Hi, I have two database: A and B, I would like to use SSIS to load the data from A to B. My dataflow consists of 2 steps only (load data from OLE DB resource and save data to OLE DB destination) and it seems like it's working but the process is very slow. I got these warning and message while the flow is running:

[SSIS.Pipeline] Information: The buffer manager detected that the system was low on virtual memory, but was unable to swap out any buffers. 0 buffers were considered and 0 were locked. Either not enough memory is available to the pipeline because not enough is installed, other processes are using it, or too many buffers are locked.

Information: Buffer manager allocated 14 megabyte(s) in 1 physical buffer(s).

Information: Component "OLE DB Source" (39) owns 14 megabyte(s) physical buffer.

I've tried to set the AutoAdjustBufferSize to True and DefaultBufferMaxRows is 10000

Is there any way for me to speed up the process?

Thank You

Answer 1

Hi @Sri Devi,Please try below methods.

1. Play with BufferSize and Max rows property values and run the Data Flow and set what works best
2. Reduce the SQL Server max memory so that devenv.exe gets more memory
3. Source data was sorted so used the ORDER BY hint at OLE DB Destination.
4. Try to run the same package from different server with more RAM

Once you've simplified the dataflow, but you are still seeing poor performance, isolate the bottleneck. Run the dataflow with just the source and see how long it takes. If that's slow, tune the source query. Add subsequent tasks to the dataflow and measure the performance. If you're only step that is slow is the destination, tune that.

For tuning a destination, try using a staging table with no indexes on it. Use TABLOCK which will tell SQL to use minimal logging. Once you've implemented the staging pattern, tune the SQL to load the data into the final table.

For tuning the source, avoid views because they may contain unnecessary joins and columns that you are not using, or use a view that is dedicated to your process so it can be written for exactly what you need. A proc can be a better choice overall if there are a lot of tables involved - performance can be improved by using temp tables to stage data before joining it to the final largest table for the select.

Check: SQL Server Integration Services Performance Best Practices

Regards，

Zoe Hui

---

If the answer is helpful, please click "Accept Answer" and upvote it.