I was asked at a recent Conchango SQL Server community session how much memory SQL Server Integration Services (SSIS) was able to use and I had to confess that although I had an inkling about the 3GB Windows limit I didn't know the full details. Thankfully Ash Sharma did and he supplied the following info:
On 32bit Windows
- Maximum memory is 2GB without the /3GB switch in boot.ini
- Maximum memory is 3GB WITH the /3GB switch in boot.ini
These figures are per process. Every SSIS package is a seperate process and therefore it is possible to split a package that attempts to grab more memory than is available given the above constraints into several smaller packages that don't grab as much memory.
On 64bit Windows
- Maximum memory is 16TB in a 64bit process
- Maximum memory is 4GB in a 32bit WOW*
One important thing to note. SSIS is not enabled to leverage Windows' Address Windowing Extensions (AWE).
* Windows on Windows - A technology to allow 32bit apps to run on 64bit operating system