4

I have package which has 1 for each loop container which has bunch of inserts and selects.

The list that the loop iterates is about few million complex rows or so..

Package is in Integration Services catalog, where it's ran by simply executing in SSMS (no agent job).

When i look in the resource monitor, memory for the ISServerExec.exe (comparable for dtsexec.exe) is growing every second (it takes about 1 second of the for each loop to complete).

After awhile all the memory in the windows server is used and server ends up paging to disk. And then the waiting times for the loop's queries become huge, 20 - 30 seconds per query.

What I am doing wrong?

user1800552
  • 370
  • 4
  • 18
  • 3
    Why do you think this is a memory *leak* vs you're consuming a metric ton of memory to perform your task? Once it completes, is the memory returned to the OS? – billinkc Nov 13 '13 at 15:10
  • it leaks: every other second the memory consumption grows. All the finished SSIS tasks should release the memory after they're done, no? – user1800552 Nov 14 '13 at 14:54
  • When the *entire* package execution has completed, is memory released? – billinkc Nov 14 '13 at 15:04
  • The package executuion crashes in the end. Probably due to memory leak – user1800552 Nov 15 '13 at 13:11
  • (ISSErverexec crashes) – user1800552 Nov 15 '13 at 13:17
  • have you tried a test setup where you run the package on a smaller dataset? one thing you might want to try is to end each loop with a checkpoint command on the server. – Tristan Jul 07 '14 at 10:34
  • @billinkc SSMS does not release memory back to the OS. It will use all the memory you allow it to use. [sql server 2012 memory leak](http://stackoverflow.com/questions/24785929/sql-server-2012-memory-leak/24786292?noredirect=1#comment38514281_24786292) – Oliver Jul 29 '14 at 15:19
  • 2
    @Oliver I think you're confusing things. SSMS, SQL Server Management Studio, is a client side tool for interacting with SQL Server. The memory it consumes, generally in the form of large result sets, some for intellisense, etc, is returned to OS when you close the app. An SSIS, SQL Server Integration Services, package consumes a lot of memory during processing but then returns that memory to the OS upon exit. It does not run in SQL Server's memory space. SQL Server itself, stays resident and clings to its memory as Thomas explains on the DBA link. – billinkc Jul 29 '14 at 15:28
  • @billinkc Yes, I should have said SQL server instance does not release memory not SSMS. However this is still relevant if SSIS is installed on the same machine as the SQL server and no limit has been set on the SQL server memory consumption. You can get resource contention between SQL and SSIS. – Oliver Jul 29 '14 at 15:50
  • Are you performing any Sort, Grouping or Aggregation operations in SSIS? SSIS performs best when it can process data in batches, but these operations require the entire dataset, which can cause massive memory spikes in large datasets. In these cases, you can be better to write back to a staging table in SQL and group/sort there. Just a thought, appreciate this may not apply in this case. – GShenanigan Sep 05 '14 at 16:11
  • If your code tells it to use lots of memory.... then it's not a memory leak, it's just doing what it's told. If you give us a more specific idea of what you are doing perhaps we can suggest a method which is less memory intensive. – Nick.Mc Oct 06 '14 at 06:13

1 Answers1

0

I would write the list to a SQL table, then loop using a For Loop container wrapped around your For Each container.

At the start of the For Loop container I would read a single record from the list table using SELECT TOP 1, and deliver it into the Recordset variable. The scope of that variable should be moved to the For Loop container.

At the end of the For Loop Container I would update a flag and/or a datetime column to indicate that the row has been processed and should not be included in the next iteration of the initial SELECT.

Along the way you can update the list table to indicate progress/status of each row.

This design is also useful for logging and restart requirements.

Mike Honey
  • 14,523
  • 1
  • 24
  • 40