Friday, 27 June 2014

SSIS Out of Memory/Buffer Manager errors

Often I get queries from my developers regarding “out of memory exception” or Buffer Manager errors during data flow task, the frequent query they ask is “I am running the package in the SQL server box itself and if the SQL server is granted say 128 GB of memory why are we getting memory issues.
Valid question but is your SSIS Package actually using the memory allocated to SQL Server?

To understand better on how memory is used during SSIS execution you find interesting details in below article,

To summarise the above query SSIS uses two components run-time engines(To control the package flow) and data flow engine(To control the data flow tasks)

The Data flow engine uses buffer to move  and work with the data, Meaning the data is actually physically moved to your memory or page file and part of the memory used for this is the memory allocated outside of SQL Server and not SQL server buffer manager .This is why we get the memory exceptions even though we run the package on SQL server as SQL server is more aggressive with memory allocation and willy occupy most of the memory allocated to it. SSIS has to fight for memory allocated outside of SQL server which is usually very less on a dedicated SQL box.

To control the buffer size and number of rows in each buffer you can use the DefaultMaxBufferSize  and DefaultMaxBufferRows property in the dataflow task. The default size for buffer is 10 MB and default number of rows in a buffer is 10000.

Microsoft recommends the following  “Tweak the values for DefaultMaxBufferRows and DefaultMaxBufferSize to get as many records into a buffer as possible. Setting these values too low causes SSIS to create many small buffers instead of fewer but larger buffers, which is a great scenario if you have enough memory“.


In case you could not avoid lack of memory resource, SSIS can page the buffers in the disk, this event usually slows down the process. But you can control the location where these buffers are paged in the disk using the property BufferTempStoragePath. The default value for this property is the TEMP/TMP system variable location.

5 comments:

  1. Useful information!
    Vinoth, thank you, that really save my time!
    Richard Brown electronic data room

    ReplyDelete
  2. This comment has been removed by the author.

    ReplyDelete
  3. Unfortunately, the link does't work ( http://technet.microsoft.com/library/Cc966529 ).

    ReplyDelete
  4. Hi Vinoth,
    Can you help me with below issue.
    When I try to run any SSIS 2012 packages in Visual Studio, it throws the error shown below; all the packages were running fine before, but none is running now:

    Insufficient memory to continue the execution of the program. (Microsoft Visual Studio)
    Program Location:

    at System.Runtime.InteropServices.Marshal.ThrowExceptionForHRInternal(Int32 errorCode, IntPtr errorInfo)
    at System.Runtime.InteropServices.Marshal.ThrowExceptionForHR(Int32 errorCode)
    at Microsoft.DataWarehouse.VsIntegration.Interop.NativeMethods.ThrowOnFailure(Int32 hr, Int32[] expectedHRFailure)
    at Microsoft.DataWarehouse.VsIntegration.Shell.DataWarehouseOutputWindowFactory.CreateOutputWindow(Guid windowGuid, String windowCaption)
    at Microsoft.DataWarehouse.VsIntegration.Shell.DataWarehouseOutputWindowFactory.GetStandardOutputWindow(StandardOutputWindow standardOutputWindow)
    at Microsoft.DataTransformationServices.Project.DtsPackagesFolderProjectFeature.ExecuteTaskOrPackage(ProjectItem prjItem, String taskPath)

    Can someone help?

    ReplyDelete