SQL Server - SSIS package transformation performance issues, follow up

To followup from The buffer manager failed a memory allocation call for 20485670 bytes, but was unable to swap out any buffers to relieve memory pressure blog post, here are few findings that I would like to share.

When you see that SQL Server instance throws the error as per above blog post, the amount of memory that can trigger this issue varies for different computers. If the package is really handling huge number of rows such as millions of rows during import/export operations, then allocate more physical memory to the computer and run the SSIS 2008 package on a computer that is not running an instance of SQL Server. Further SQL configuration setting as when you run the SSIS 2008 package, set the Maximum server memory option for the SQL Server 2008 instance to a smaller value.

Its always better to close/exit applications that consume lots of memory when you run the SSIS 2008 package that contains dataflow tasks. Within the package it is ideal to run the SSIS 2008 package and the dataflow tasks in series instead of in parallel to decrease memory usage. It is best to monitor the SQL & Windows instances that are involved in this SSIS package using Performance Monitor (SYSMON) tool, you can do that by monitoring the peak memory usage of the various SSIS run-time processes by using the Private Bytes counter of the Process object in Performance Monitor. On the instance where the SSIS package is executed using Task Manager have a look at SSIS run-time processes include the DTExec.exe process and the DTSHost.exe process. Adding both of these when the SSIS packages are running, find the maximum value for the Private Bytes counter. When multiple packages are running in parallel, add together the maximum values of all the processes that are running to obtain the maximum value for memory usage.

Also monitor the counters such as Total Server Memory (KB) counter of the SQL Server: Memory Manager performance object to measure the memory usage of the SQL Server buffer pool. Additionally, use the Private Bytes counter for the Sqlservr.exe process to find the memory allocations outside the buffer pool (MemToLeave). The sum of the Total Server Memory (KB) counter value and the MemToLeave value is a good estimate of the total amount of memory that SQL Server uses.  All of these tips are documented in BOL and further feedback from one of blog user confirmed that following the steps on the above blog link I would like to highlight that:

• If you are using some lookup transformations in your dataflow task, have a thorough look at them?
• Is that package querying required columns or trying to handle all the columns?
  
  Also as per that blog post have you checked those 2 highlighted properties:
  BOL specifies that both BufferTempStoragePath/BLOBTempStoragePath properties  from a SSIS pacakge will specify locations where the user executing the package (if the package is being executed by SQL Server Job, then SQL Server Agent service account) has access to these locations. A small example from documentation for example, the data in a buffer is copied by a Merge Join transformation. This operation occurs while the server is under a low-memory condition. However, the transformation does not recognize that a thread outside the transformation suddenly swapped out the destination buffer. Therefore, the copy operation fails and then causes the access violation. 

To solve the problem I have been feedback that after changing the BufferTempStoragePath to fast disk (raid 0 15K per second), reduced the footprint of the SQL Server instance to 10GB on a 32GB server) giving SSIS room to breath, and full cached everything, then the SSIS package working like a jiffy the number of rows loaded as 185,000 rows per minute!

These are simple parameters and configuration settings which can bring a greater deal of performance improvements on your data platform ETL activities.