Performance Analysis in SSIS

Question

In SSIS Package, I have a Data flow fetching contents from XML Source and loading the data to SQLServer Table through OLEDB Destination.

Source : XML Source; Dest : OLEDB Destination.(Sqlserver DB).

1. In the Sqlserver DB, the destination table (table1) has 150 columns and all these columns are type nvarchar(max).
2. In SSIS Package(p1.DTSX) all the XML columns are type DT_NTEXT.
3. In SSIS Package(p2.DTSX) all the XML columns are type DT_WSTR with length set to 3000.

With this setup when I execute the job for an Input XML record count of 2000,the p1.DTSX gets executed at 30 seconds while p2.DTSX gets executed at 45 seconds.

But with the same setup, when I executed the job for an Input XML record count of 6000, the p1.DTSX gets executed at 1min 20 seconds while p2.DTSX gets executed faster than p1 (i.e., 50 seconds).

Please explain me this controversy when record count gets increased

Answer 1

SSIS process the data in the Default Buffer(GoTo Properties of Data Flow Task and we can Default Buffer Max Rows/Size).

In p1.DTSX file, while executing with Larger Input File with all the columns as DT_NTEXT(MAX), the Memory Buffer fills up and it process the data using temp log file (BLOBTEMPStorage) Path, Hence due to this it caused slowness.

In p2.DTSX , it did not cause any issue, since it processed everything in the Default Buffer as we have limited the column sizes to 3000.