October 14, 2009 at 8:34 am
I have a 32bit OS Windows 2008 ent with 16 GB RAM.
Taskmgr sees all 16 GB of RAM because PAE is enabled. AWE, lock pages in memory, Create Global Objects are enabled for Everyone.
I have set MAX memory on SQL to use 7GB. It is using only 1.3 GB (as if getting memory under the 4GB)
Running the package from BIDS, I don't see Physical Memory used beyond 4GB and the package runs very slow.
Is there step that is missing to let SSIS use memory beyond the 4GB?
Thanks.
October 14, 2009 at 8:50 am
since it's 32 bit, i don't think it will use RAM beyond 4GB. with the db engine it's limited to the RAM you can use beyond 4GB as well
October 14, 2009 at 10:02 am
it go beyond 4GB on my win2003 32bit ent.
October 14, 2009 at 10:42 am
From http://consultingblogs.emc.com/jamiethomson/archive/2005/05/29/SSIS_3A00_-Memory-Usage.aspx:
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).
October 14, 2009 at 10:58 am
thanks for the linky.
😀
October 15, 2009 at 10:40 am
Hi Erik,
You know I suppose to use 2GB of process, right?
When I look into the task manager, I don't see anything that's using close to it while SSIS package was running in BIDS.
So, I guess memory isn't my bottle neck. What troubles me now, is, I am loading 20 million rows with joins in TSQL statement using OLE DB Source and memory doesn't hit 2GB (not that I wish), disk q length is 1.
why is it so slow?
October 16, 2009 at 3:25 am
In a 32-bit OS, ALL processes need to share the memory below the 4GB line.
If you have a process sthat is PAE-aware (such as SQL database engine), then it can make use of memory above the 4GB line for designated tasks, but even PAE-aware processes will need to use some memory below the 4GB line.
SSIS is not PAE-aware, so if you are running a SSIS package that needs a lot of memory, you will reduce the memory available for other processes including reducing the memory available for SQL database engine.
The fix for this is to move to a 64-bit OS running 64-bit SQL Server.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
October 16, 2009 at 6:26 am
Thank you for the explanation.
It is crystal clear to me, now. 😀
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply