Aug 20, 2011
tom

How much memory can SQL Server 2005 x86 use when installed on Windows Server 2008 x64?

Question

If I install SQL Server 2005 x86 on Windows Server 2008 x64 how much memory will SQL be able to use be default? How much after setting SQL server’s AWE switch?

This post talks about using /3gb, /PAE, and AWE to utilize SQL Server 2008 x86 memory appropriately on Windows Server 2008 x86.

My hypothesis based on that post and related information I have seen elsewhere is that by default a SQL Server 2005 x86 instance will be able to use 4GB of memory on Windows Server 2008 x64 and if I enable AWE then SQL will be able to use as much memory as the OS’s sees.

PS: Please note if your answer generalizes to other version of SQL and or Windows Server.

Thanks

Answer

x86 processes with the LargeAddressAware bit set (like sqlservr.exe) get a full 4 GB VAS for themselves. Enabling AWE (on OS and SQL editions that support it) allows the SQL server instance to map extra pages (up to 64GB) in and out of its VAS and use them for the buffer pool.

That being said, x86 is a dead end and you should switch to an x64 instance ASAP.

Related posts:

  1. Enabling AWE Memory for SQL Server 2008 on Windows 2008 R2
  2. Can’t connect to SQL Server 2005 x64 SP 4 on Windows 7 x64
  3. SQL Server R2 different versions on x86 and x64?
  4. SQL Server 2005 Memory Usage Low
  5. Severe Memory Pressure on SSAS 2008 on Windows Server 2008

Leave a comment