Search code examples
ssisout-of-memoryssis-2012

SSIS Data Flow Task: Out-of-memory exception

Here's my situation: I'm including as much detail as I can think of, but please tell me if you need additional information. I've searched for an hour or so online without finding any posts that will address my specific problem, so any help (even a link) would be appreciated.

I'm writing a Data Flow task to perform an SCD operation (using Task Factory's SCD Component) on my data source. I'm loading from an Oracle source in one network over an extremely slow connection into a SQL source on my new server.

The SCD operation requires me to extract data from both the source and the destination, and compare the two. However, the relative speeds of the two connections are causing problems for me: the SQL source is faster by almost a factor of 10, so the data flow will have pulled 8 million records from my new server by the time the old server has transferred 1 million.

Since both sources are coming together and flowing through the same SCD component, by the time I reach the 8m/1m mark, there are still 7m rows waiting from the SQL source. As I understand it, those 7m rows are waiting in-memory to get processed through the SCD.

I'm getting error messages that the SSIS Debug Host has stopped working, and that the system could not write any more new rows to the buffer. The messages are on different components and steps (I have around 30 tables with the same situation), so I'm pretty confident that this is a general problem with the memory available to me, not a specific issue with the code in my package. In addition, the package will always succeed if I run individual tables or even groups of tables - it only fails when trying to load certain tables in parallel.

Steps taken so far:

1) Split the tables being loaded to multiple sequential containers / child packages, to ease the pressure on the memory: this has mostly worked, but slows the overall execution down to an abysmally long time. In addition, some tables still throw the SSIS Debug Host error (below) when reaching a certain limit.

2) Adding a Script Component to compute a hash value on the source (as in this post), which I can then use to determine if the record has changed rather than comparing each column. This also means I don't need to pull in all the columns from my destination table, which I assume will help out with the memory shortage.

Problems that continue:

I still get a crash once the row count imbalance reaches extreme levels, even when only pulling a few (6 datetime, 3 int, 2 numeric(38,0)) columns from the source and destination. This happens on tables without the Hash value mentioned above. A screenshot of the data flow at the point of failure is below: note the row counts. enter image description here

Thoughts on what to do next: I'm wondering if there is a way to slow down the SQL source so that it pulls records at the same speed as the other? Any ideas how to implement this?

Full error descriptions: 1) SSIS Debug Host has stopped working (popup window).

Problem signature:
  Problem Event Name:   APPCRASH
  Application Name: DtsDebugHost.exe
  Application Version:  2011.110.2100.60
  Application Timestamp:    4f35e2b2
      Fault Module Name:    clr.dll
  Fault Module Version: 4.0.30319.18449
  Fault Module Timestamp:   528fdc93
  Exception Code:   c0000005
      Exception Offset: 0010d0aa
      OS Version:   6.2.9200.2.0.0.272.7
  Locale ID:    1033
  Additional Information 1: 9336
  Additional Information 2: 9336482019c99e3f312ddc2ccd6c9a04
  Additional Information 3: b33b
  Additional Information 4: b33bc67c4e950cb436b9dbebdd26abc8

2) SSIS Error messages:

[TF Dimension Merge Slowly Changing Dimension] Error: Internal error (Exception adding key to cache) in ProcessInput sending row to cache.
[TF Dimension Merge Slowly Changing Dimension] Error: Internal error (Exception adding key to cache) in ProcessInput adding rows to the cache.
[TF Dimension Merge Slowly Changing Dimension] Error: Internal error (Exception adding key to cache) in ProcessInput.
[TF Dimension Merge Slowly Changing Dimension] Error: Internal error (Unexpected exception in OrderedHashList.Remove removing entry from internal structures: Exception of type 'System.OutOfMemoryException' was thrown.) in ProcessKey thread dequeueing a key (03720200).
[TF Dimension Merge Slowly Changing Dimension] Error: Internal error (Unexpected exception in OrderedHashList.Remove removing entry from internal structures: Exception of type 'System.OutOfMemoryException' was thrown.) in ProcessKey thread dequeueing a key (28740100).
[TF Dimension Merge Slowly Changing Dimension] Error: Internal error (Internal error (Unexpected exception in OrderedHashList.Remove removing entry from internal structures: Exception of type 'System.OutOfMemoryException' was thrown.) in ProcessKey thread dequeueing a key (03720200).) in ProcessCache_Thread_ProcessKey.
[TF Dimension Merge Slowly Changing Dimension] Error: Internal error (Internal exceptions encountered.) in ProcessInput.
[TF Dimension Merge Slowly Changing Dimension] Error: Internal error (Internal error (Unexpected exception in OrderedHashList.Remove removing entry from internal structures: Exception of type 'System.OutOfMemoryException' was thrown.) in ProcessKey thread dequeueing a key (28740100).) in ProcessCache_Thread_ProcessKey.
[TF Dimension Merge Slowly Changing Dimension] Error: Internal error (Internal exceptions encountered.) in ProcessInput.

System Details:

Windows Server 2012

Installed memory (RAM): 64.0 GB

System type: 64-bit Operating System, x64-based processor

SSIS 2012 Executing from Visual Studio environment (problems also occur when running as scheduled job through SQL Server Agent

Solution

  • The answer, as usual, is far too simple for the question (**headdesk** here).

    The original developer, who asked for my help in improving the performance of the package, did not sort the inputs for the TaskFactory SCD. This resulted in all of the data being cached in memory, with no rows being allowed through the SCD until the end.

    I was prompted to check this by this comment thread where someone had a similar problem and was advised on tuning it. The relevant quote is as follows:

    "This component behaves differently from the SCD Wizard in that: it has multiple inputs instead of just one, it matches the rows on the inputs much like a Merge Join component as opposed to a Lookup component. The net effect of that architecture is that it must store incoming rows from all inputs until they are matched to rows from the other input(s), or it is known that there will be no match found from those other input(s). The SCD Wizard can process it's single input one row at a time without need to cache anything, because it processes the input one row at a time, issuing a "lookup" to the Existing Dimension table one row at a time to make the SCD processing decisions. Since this component caches rows, it needs more RAM to store the unmatched input rows. Unfortunately, this component does NOT monitor available RAM levels and take alternative action like the Sort component would (sending the cache to disk). As such, it will throw an error if RAM is exhausted."

    In my own packages, I follow the practice of sorting within the source component (i.e., with ORDER BY). I didn't check to see if this package already had that, and I rarely use the SSIS Sort component so wasn't alarmed by not seeing it.

    Once I had configured the sort within the package (this guy has a good summary here), the performance increased drastically and I have been able to run the package without error. I will be making this change on all packages developed by this individual, and expect this to resolve my problem.

    Thanks to @billinkc for his observations regarding the PW components. They are not designed to handle large unsorted inputs (or inputs with lopsided speeds), but look much better once correctly configured.