Cache Transformation fails with Buffer Memory error during load on SQL 2014 SSDT 2013, potential upgrade issue.

My issue is loading 140 million records into a Cache Transformation fails constantly since upgrading to SSDT 2013 from 2010.

The error is reproducible 100% of the time while running the package in debug mode (dtsDebugHost.exe).

The server is rather large having the following configuration.

Windows Server 2012 Datacenter

Processor Xenon E5-4650 @ 2.70 GHz (4 proc)

768 GB RAM

SQL Server Max Memory 600 / Min 520GB

This issue has also been tested on a different server with similar results.

The source of the Cache is an OLE DB Connection to the local SQL server pulling 13 columns all having ID values.

The destination is a standard Cache Destination.

The errors are as follows and occur while both writing to file and full memory cache near 20.5 million records for both Cache types:

[SSIS.Pipeline] Information: The buffer manager detected that the system was low on virtual memory, but was unable to swap out any buffers. 14 buffers were considered and 14 were locked. Either not enough memory is available to the pipeline because not enough is installed, other processes are using it, or too many buffers are locked.

Information: Buffer manager allocated 4 megabyte(s) in 2 physical buffer(s).

Error: A buffer failed while allocating 1703856 bytes.

Error: The system reports 92 percent memory load. There are 412146331648 bytes of physical memory with 29133062144 bytes free. There are 4294836224 bytes of virtual memory with 8069120 bytes free. The paging file has 429326200832 bytes with 43001991168 bytes free.

[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "Cache LicenseMaster" (2) failed with error code 0xC0208252 while processing input "Cache Input" (5). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.  There may be error messages posted before this with more information about the failure.

Our initial thoughts are that SQL server is not configured correctly to handle the usage of so many buffers.

I have tried both extremes of Cache property settings for MAXROWS and MAXBUFFERSIZE.

I have read many articles, but none seems to discuss buffer management in detail.

We appreciate your insights and recommendations.

Scott M

September 23rd, 2014 2:58pm

I also tried sp_CONFIGURE optimize for ad hoc workloads,1 today with failures still occurring.


Free Windows Admin Tool Kit Click here and download it now
September 23rd, 2014 3:07pm

I also tried sp_CONFIGURE optimize for ad hoc workloads,1 today with failures still occurring.


You should revert this configuration unless you have predominantly ad hoc workloads in your server. It is not related to the issue you are experiencing.

About the main subject, your case is very interesting. I've had some trouble with memory management myself in SSIS when working with cache connection managers.

Since the error logs report there is only 4 GB VAS available, I can only guess you are running your package in 32-bit mode ;)

Change it to 64-bit and try again.

September 23rd, 2014 3:37pm

If you are unsure how to change it to 64bit, right click your project in solution explorer and click properties.

In the "debugging" section, change the appropriate setting to true.

Free Windows Admin Tool Kit Click here and download it now
September 23rd, 2014 3:43pm

Hi Scott,

Based on error message, the issue should be caused by insufficient memory for the package execution.

For a 32-bit SSIS on a 32-bit platform, each DTExec process can consume up to 2GB memory. For a 32-bit DTExec process on a 64-bit Operating System, it can consume up to 4GB memory. If the platform is 64-bit and there are sufficient memory available on the server, and the 64-bit driver required is installed on the server, while the package runs in 32-bit runtime, then we can run the package in 64-bit runtime by setting Run64BitRuntime property to True of the package project to resolve this issue.

If the server is a 32-bit platform or the package must run in 32-bit runtime, for example, there is no 64-bit driver available, we can work around this issue by split the package into several packages and use Execute Package Tasks to call child packages from a parent package. Besides, set the ExecuteOutOfProcess property to True, so that each child package process can claim its own 2GB or 4GB virtual memory.

Additional, SSIS package execution uses the memory that is not allocated by SQL Server. Therefore, if the SQL Server process takes too much memory on the server, you can limit the maximum memory size for the SQL Server instance. For more information, please see:
http://technet.microsoft.com/en-us/library/ms178067.aspx

Thanks,
Katherine Xiong

September 24th, 2014 11:20am

Thank you for your responses.

Project is running is 64 bit mode having the Run64BitRuntime property = True

Tried again while monitoring the RAM usage and please consider the following:

DtsDebugHost.exe

HardFaults/per sec = 0

Commit (Kb) = 3770945

Working Set (KB) = 3776048

Shareable (KB) = 23688

Private (KB) = 3752360

RAM In Use  = 358249 MB

Standby = 3705MB

Free = 30861 MB

So even though it states running in 64 bit mode isn't behaving as such.

Please keep the thoughts and ideas coming.

Scott M

Free Windows Admin Tool Kit Click here and download it now
September 27th, 2014 6:37am

Scott,

You are running out of VAS, not physical memory itself. 32bit runtime will support 4 GB VAS as shown in your error log:

"There are 412146331648 bytes of physical memory with 29133062144 bytes free. There are 4294836224 bytes of virtual memory with 8069120 bytes free. The paging file has 429326200832 bytes with 43001991168 bytes free."

The reason your project running in 32bit mode still eludes me. I am able to allocate much more than 4 GB VAS in my test server without any further configuration.

September 29th, 2014 3:02pm

While you run the package, check if the path of the DtsDebugHost.exe leads to the 64-bit program files folder.

C:\Program Files\Microsoft SQL Server\110\DTS\Binn by default.

If it leads to C:\Program Files (x86)\Microsoft SQL Server\110\DTS\Binn, alas! There is your problem.

Free Windows Admin Tool Kit Click here and download it now
September 29th, 2014 3:07pm

The file location leads to:

C:\Program Files (x86)\Microsoft SQL Server\120\DTS\Binn

I tested another older package running on SSDT.exe in VS 2010 and it is also configured for Run64Bit=TRUE with the location of ...

C:\Program Files\Microsoft SQL Server\110\DTS\Binn

Now what?  Are we seeing the Project Property to run in 64 bit mode isn't working?

And what am I to do about it?

September 30th, 2014 2:46am

So after another 4 hours of testing, I concur that SSDT.exe 2013 using DTSDebugHost.exe is NOT using the 64 bit architecture as requested.  It seems the installation (post sql server 2014) does not fully integrate a shared architecture.

I have tested and confirmed that deploying the same fail package runs successfully in 64 bit mode, however the executables are completely different (aka. DTExec.exe and SSISExecutionUtility.exe).

How is one to install SSDT.exe for 2013 and leverage 64 bit architecture?

Thanks again for all your time.

Scott M

Free Windows Admin Tool Kit Click here and download it now
September 30th, 2014 6:17am

Scott,

First make sure you have the 64 bit SSIS runtime installed.

Is the debug executable really in the directory C:\Program Files\Microsoft SQL Server\110\DTS\Binn?

Secondly (applicable if above conditions are true only):

Check your "path" environment variable. In my server, the path to the C:\Program Files\Microsoft SQL Server\110\DTS\Binn folder comes BEFORE the path to C:\Program Files (x86)\Microsoft SQL Server\110\DTS\Binn. Dont change anything else, just switch the order of the x86 and x64 folders in your environment variable.

Edit: I did some testing myself, changing the environment variable didn't make much difference for me, but who knows? Try to change it and then restart your server. If it doesn't work, well... you can always try to reinstall SSIS services.


September 30th, 2014 2:15pm

Here is the current Path variables:

C:\Windows\system32;
C:\Windows;
C:\Windows\System32\Wbem;
C:\Windows\System32\WindowsPowerShell\v1.0\;
C:\localbin;
C:\debuggers;
C:\Program Files\HP\hponcfg;
C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\;
C:\Program Files\Microsoft SQL Server\100\Tools\Binn\;
C:\Program Files\Microsoft SQL Server\100\DTS\Binn\;
C:\Program Files\Microsoft SQL Server\120\DTS\Binn\;
C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn\;
C:\Program Files (x86)\Microsoft SQL Server\120\Tools\Binn\;
C:\Program Files\Microsoft SQL Server\120\Tools\Binn\;
C:\Program Files (x86)\Microsoft SQL Server\120\Tools\Binn\ManagementStudio\;
C:\Program Files (x86)\Microsoft SQL Server\120\DTS\Binn\;
C:\Program Files\Microsoft SQL Server\110\Tools\Binn\;
C:\Program Files (x86)\Windows Kits\8.1\Windows Performance Toolkit\;
C:\Program Files (x86)\Microsoft Visual Studio 12.0\Common7\IDE\PrivateAssemblies\

We are using SQL Server 2014 so I assume the reference to 110 change be read as 120.

So the DTS\Binn for Program files is already coming before the x86 path references.

C:\Program Files\Microsoft SQL Server\100\DTS\Binn\;
C:\Program Files\Microsoft SQL Server\120\DTS\Binn\;

Free Windows Admin Tool Kit Click here and download it now
October 3rd, 2014 5:45pm

Was this ever resolved?  Im seeing the same issue.  The 64bit drivers are installed, the Path is set up correctly, and Ive set the Run64BitRuntime property = True.  However Visual Studio 2013 refuses to run the 64bit version of DtsDebugHost.

Thanks

-John

March 27th, 2015 3:19pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics