July 8, 2009 at 7:20 am
MichaelJasson (7/8/2009)
... Take a scenario where we have 8 GB memory. My OS is 32 bit and sql server application is also 32 bit.Sql server application will be able to address 4 GB and OS will be able to address 4 GB. So if we have 8GB and then both can address complete memory...
You will only be able to address all of the 8GB physical RAM if you have either Windows Server Enterprise or DataCenter. Your first starting point has to be the operating system. If the OS can't see all the RAM installed, then there's no way SQL Server will be able to.
Here's a nice table that will show you the limits:
Operating system - Maximum memory support with PAE
Windows 2000 Advanced Server - 8 GB of physical RAM
Windows 2000 Datacenter Server - 32 GB of physical RAM
Windows XP (all versions) - 4 GB of physical RAM*
Windows Server 2003 (and SP1), Standard Edition - 4 GB of physical RAM*
Windows Server 2003, Enterprise Edition - 32 GB of physical RAM
Windows Server 2003, Datacenter Edition - 64 GB of physical RAM
Windows Server 2003 SP1, Enterprise Edition - 64 GB of physical RAM
Windows Server 2003 SP1, Datacenter Edition - 128 GB of physical RAM
* Total physical address space is limited to 4GB on these versions of Windows
Infact 32 bit CPU can address 4 GB at the max.
Processors can address more than 4GB of memory with the PAE (physical address extension) - but again this is dependent on your operating system. Also, your processor has to be Pentium Pro or above in order to address more than 4GB (although if you're saying it's a dual processor (and I'm assuming dual core) then you should be OK).
If there are dual processor then OS can use 1 CPU and another application can use another CPU. This goes like this:
Sql server Application: CPU1
Operating System : CPU2
CPU1 addresses 4 GB and CPU2 will also address 4 GB. Now if I make sure that the /3GB switch is on then SQL can address 3 GB as this is 32 bit while OS will be able to take advantage of 5 GB where it can address 4 GB RAM.
You can split this 50/50 if you want, but the likelihood is that the OS wont need 4GB RAM. So let's assume you have a supporting OS that allows PAE, so the OS can see 8GB RAM. If you apply the /3GB switch then applications (like SQL) can use up to 3GB RAM. However, SQL can use even more RAM if you enable AWE.
July 8, 2009 at 8:13 am
I think you are slightly off Michael.
This is from my experience, rather than theory (which you can google):
In a 32 bit OS, first of all SQL can only address a maximum of 2Gb (without AWE). So if you have 8Gb RAM, SQL will still only use 2Gb (you will find if you look at perfmon total server memory it will actually be closer to 1.7Gb)
As the previous poster mentioned, Windows is unlikely to need (or use) 4Gb, so if you don't enable AWE, memory isn't being fully utilised in this scenario.
If you enable AWE, then SQL can use virtually any amount of memory (hence the need to set the max memory setting). What most DBAs will do is set the max memory to something like 6Gb and leave Windows to operate and manage the 2Gb that remains.
This is only discussing virtual memory, and doesn't consider buffer pool etc.
ps. I would be very careful if you are considering binding one CPU to SQL and leaving the other for Windows (as mentioned).
July 8, 2009 at 10:33 am
Many thanks to Phil Melling and David. Great learning for me...
Ciao
-MJ
Please do not print mails and docx unless it is absolutely necessary. Spread environmental awareness.
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply