May 25, 2009 at 5:44 am
Does anyone know a way to do this.
I have a server with 64 gig of ram but cant find a way of allowing Analysis services to use more than 3 gig while it is processing the cubes.
This means it takes a very long time. I understand it is possible on a 64 bit installation but really need a hack for doing it on 32 bit.
Thanks for all help,
Jules
May 25, 2009 at 10:08 am
Have you enabled PAE?
That should allow you more RAM, but I'm not sure if it will get the entire 64. The 4GB limit is a fundamental limit of 32 bit architectures. There is a method of swapping RAM from upper reaches down into 4GB, but it isn't the same as using the entire 64 natively.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
May 27, 2009 at 1:10 am
May 27, 2009 at 3:56 am
in order for the OS to see more than 4 GB or RAM you need to give it eyes . this is PAE .Once you add /PAE virtual memory pointers will use the PTE's [page table entries] using MMU to point to RAM .
In order for an application to use more than 4 GB of RAM you need to enable AWE (if that application supports AWE .SQL does).
So just add /PAE in the boot.ini and enable AWE in SQL Server .
Then set the Max and min server momory (although SQL Server 2005 onwards we have dynamic AWE).
Recycle your SQL Server .
To see how much RAm SQL is consuming use perfmon : Memory Manager : total server memory and target server memory counters .
Now , /3Gb switch has nothing to do with physical memory or RAM .Its related to virtual memory .
Regards
Abhay
MCITP/MCTS [SQL Server 2005 ,SQL Server 2008]
Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)
May 28, 2009 at 7:27 am
Microsoft will not recognize >16gb with the 3gb switch enabled. For sql server you do not want /3gb enabled.
May 28, 2009 at 8:03 am
Let me correct this statement :
[[[[[[Microsoft will not recognize >16gb with the 3gb switch enabled. For sql server you do not want /3gb enabled. ]]]]
when you enable /PAE the 32 bit map becomes 36 bits and hence you can create pointers worth 2 to the power of 36 which is 64 GB .
But if you enable the /3GB switch the size of Page table entries will be reduced and it will be able to point only up to 16 GB of RAM.. that is 2 to the power of 34 .
there is no harm in enabling /3GB but i have seen many cases (i am EX-Microsoft) where /3GB and /PAE together causes some memory issues in SQL Server 2000 and there is a KB article asking not to use both (forgot the number though).We resolved such issues by removing /3GB switch.
there is nothing like you want or you do not want /3GB switch for SQL ...
its at the OS level ....it is just going to trim the Kernel address space to 1 GB and add that 1 GB to user adress space totalling it to 3 GB ..
remember /3GB is related to virtual memory and /PAE is related to physical memory .
you can browse thorough my Blog for better understanding : http://ms-abhay.blogspot.com/
if you still have doubts : post your queries here and i will be glad to clear them ...Memory / corruption / setup are my strengths ..
Regards
Abhay
[OCP 9i /MCITP (2005 and 2008)/MCTS (2005 and 2008)]
Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)
May 29, 2009 at 1:29 am
May 29, 2009 at 2:36 am
The OP is asking how to get 32-bit Analysis Services using more than 2GB memory.
This is not possible. SSAS is not PAE-aware so it cannot make use of this type of memory.
If the server had 16 GB or less memory you could use the /3GB switch in Windows and erstrict the Windows kernel to 1GB, leaving 3GB free for 32-bit applications like SSAS. But with 64 GB memory you cannot use the /3GB switch.
If Jules wants to get SSAS using more than 2GB memory he has to install a 64-bit version of SSAS.
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
May 29, 2009 at 3:01 am
Analysis Services can address a maximum of 3 GB of memory on any 32-bit version of Windows, regardless of the amount of actual memory that is installed on the computer.Normally it will use 2 GB as other processes do .But you can use 3 GB if you enable /3GB switch .
Secondly , you analysis services will also use SQL Server .So , if you enable /PAE with AWE for SQL Server it should help .
But Analysis services cannot use more than 3 GB .
to add the /PAE switch in boot.ini :
Start >> run >> c:/boot.ini >> after the /fastdetect switch give a space and write /PAE >> save the file
Then enable the AWE in SQL Server .
Reboot the Server for the boot.ini setting to be effective .
Regards
Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)
May 29, 2009 at 3:56 am
regardless of the amount of actual memory that is installed on the computer
The /3GB switch can only be used if the machine has less than 16 GB memory. On a heavily used system many people find 12 GB is the upper limit for safe use of /3GB.
This is because 32-bit Windows needs to use some memory to manage the storage above the 4GB line. If you have more then 16 GB memory, Windows needs nore than 1GB memory for itself, which is why the /3GB switch can not be used in this situation.
In a heavily used system, Windows will need to use more memory than a lightly used system, which is why some people have found the /3GB switch to make Windows unstable with more than 12 GB memory installed. Also see hi_abhay78's posts above
Also remember that if you have SSAS and SS Database engine on the same box, they both have to share the memory below the 4GB line. In this situation both of these routines are unlikely to get more than 1.5 GB real memory each, regardless of how much is installed on the box.
The only way to fully exploit the 64 GB memory installed on the box is to install 64-bit Windows and 64-bit SQL Server. With 64-bit SSAS on a 64GB box, SSAS will probably get above 20 GB real memory memory if it has to share with SQL Server database engine, and above 50 GB if it is on its own.
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
May 29, 2009 at 12:05 pm
EdVassie (5/29/2009)
The only way to fully exploit the 64 GB memory installed on the box is to install 64-bit Windows and 64-bit SQL Server. With 64-bit SSAS on a 64GB box, SSAS will probably get above 20 GB real memory memory if it has to share with SQL Server database engine, and above 50 GB if it is on its own.
Small correction if I am not mistaken. If you Windows 2003 Datacenter edition, then you can get SQL server to use 64 GIG RAM. Anything above you should have 64 bit combatible HW, SQL Server 64 BIT, OS can be Windows 2003 64 BIT (Max memory usage of 64 GIG) or OS Windows 2003 Datacenter edition 64 BIT, then it can go upto 1 TB RAM.
It is not just the RAM that you can increase but also the number of processors. That is the main advantage in my eyes for having 64 BIT Datacenter edition. It supports a maximum of up to 32 processors on 32-bit or 64 processors on 64-bit hardware.
-Roy
May 29, 2009 at 12:26 pm
That is correct because as of SP2 even the Enterprise edition can use 64gig of ram but you may also need more processor, so RTM Enterprise cannot use 64gig of ram but Sp2 can use 64gig of ram.
http://technet.microsoft.com/en-us/windowsserver/bb294403.aspx
Kind regards,
Gift Peddie
June 1, 2009 at 2:38 am
Sp2 can use 64gig of ram
True for SQL Server database engine, because the database engine is AWE-aware. Even then, SQL Server is unlikely to get above 55 GB of memory to play with, as Windows will need quite a bit of its own.
Not true for SSAS 32-bit because SSAS is not AWE-aware. On a 64-GB box then SSAS can not get more than 2GB memory. on a 8GB box with the /3GB switch, SSAS could get 3GB memory.
The OP needs a solution for SSAS, not for database services.
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
July 18, 2009 at 12:38 pm
Also remember that if you have SSAS and SS Database engine on the same box, they both have to share the memory below the 4GB line. In this situation both of these routines are unlikely to get more than 1.5 GB real memory each, regardless of how much is installed on the box
When /3GB is enabled, this additional 1GB (total 3GB for user application) is
1. per process for example 3GB for sqlserver.exe, 3GB for msmdsrc.exe
2. is shared 1.5GB for sqlserver.exe 1.5GB for msmdsrc.exe
I always thought that correct is first one?
Second thing, I running SSAS on 32bit Windows 2003 EE, 8GB, SQL Server 2005 SP2 SE,
/3GB enabled and /PAE enabled.
During processing one of the dimension msmdsrc.exe process take about 3GB virtual RAM,
and process task end with error, the strange thing is after service is restarted process task
end with success, few days later the same problem.
My database is no so big, about 4GB, SSAS has default configuration.
I know the best way will be install 64bit system, but I have to find some temporary resolution,
can I force SSAS to release memory before it get this 3GB?
I red about Memory\TotalMemoryLimit And Memory\LowMemoryLimit option, but SSAS sometimes
ignore this option:
SQL Server 2005 Analysis Services Memory Configurations
Memory\TotalMemoryLimit option
This parameter represents maximum limit of RAM to be used by SSAS for all its operations. However,
it's not a hard limit. If Analysis Services needs more RAM for some kind of heavy operation it'll try to reserve
RAM beyond the vale set in TotalMemoryLimit parameter.
If TotalMemoryLimit is high, SSAS in 32bit version might try to reserve more RAM than 3GB and you can
be in troubles...
July 20, 2009 at 2:57 am
Windows 32 bit can directly address 4GB RAM.
First, lets look at the situation ignoring Windows /PAE switch and SQL AWE memory...
Everything that uses memory must fit into the 4GB limit. By default, Windows reserves 2GB for itself, leaving 2GB for applications.
Windows gives each application its private memory space. This means that each application 'thinks' it has access to all 2GB memory it has allocated. However, all of these 2GB virtual memory areas have to fit into a physical 2GB memory area.
If applications want to use a total amount of virtual memory that is larger than the physical memory, Windows will move the least recently uses portions of memory out to disk, using the page file. This allow you to have more programs running than the machine can fit into memory, but the process of copying memory to and from the page file does slow down performance.
Most applications use far less than 2GB memory, so for most things it is 'safe' to give each application access to 2GB memory because it will not use it all.
The problems come if you have multiple applications that want to use a lot of memory. If you have SQL Server database engine running and it is using 1.5 GB physical memory, this leaves only 0.5 GB physical memory for everything else. If you want to run SSAS and it needs 1.5GB physical memory, then Windows will spend a LOT of time moving 1GB memory on and off of the disk page file. All time that Windows spends dealing with page file activity is time that cannot be spent dealing with your queries.
The /3GB switch forces Windows to restrict itself to 1GB memory, allowing applications to share 3GB instead of 2GB. In the example above, both SQL DB and SSAS could use 1.5GB memory (assuming absolutely nothing else is running on the server) without any time being wasted by Windows dealing with the page file. However, it you want to use a total of more than 3GB memory you again have paging problems.
The /PAE switch allows Windows 23-bit to access memory above the 4GB line, but only in a special way. Also, programs that want to use the memory above the 4GB line have to be specially written to do this. SQL DB can use memory above 4GB, but SSAS can not.
With /PAE, memory above the 4GB line is divided into pages and each page is given a number.
If SQL DB wants to write to memory above 4GB, it asks Windows to make the page available by giving Windows its page number. Windows will then copy this page to memory below the 4GB line so that SQL DB can read and write to it. When SQL DB asks for another page to be made available, Windows will copy the first page back to its original slot and get the next page requested.
As you can see, using memory above the 4GB line in 32-bit Windows is slow. But it is still much faster than reading the information from disk, which is the only other choice.
If you want both SQL DB and SSAS to use 3GB memory each, you have 2 choices...
a) Run them on different servers, so they both have 3GB physical memory.
b) Run them on 64-bit Windows using 64-bir SQL Server, where SQL DB and SSAS can both have 3GB physical memory on the same 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
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply