Q and A: Max memory with sql server 2000 / win2003 x64

Today, I received following question

Q: Slava,
I have a question for you. Sql 2000 is documented to support a maximum of 64GB when used with 2000 datacenter edition.
I assume this is the limit of 2000 Datacenter, but is it also a limit defined in sql server 2000?
We are purchasing a new server which can support a max of 128G of memory, and believe we have use for that much memory.
If we use an OS that can recognize it (such as 2003 enterprise x64), can sql server 2000 utilize 128G, or is it somehow limited to 64G?

A: I assume you are talking about SQL 2000 32 bit. Latest version of SQL 2000 SP4 won't be able to take advantage of memory more than 64GB. Moreover when running in WOW Windows supports max of 128GB on AMD64 and only 64GB on Intel x64. My general recommendation for installations running with large amount of memory is to run SQL Server natively on 64 bit platform. As you know, SQL 2000 only supports IA64, so you have couple of options:

- Get IA64 based hardware and run SQL2000 IA 64 version.

- Get x64, run SQL Server 2000 with 64GB in WOW. Once SQL 2005 is out, get it and run it natively.

One of the major reasons for not supporting 128GB is for buffer descriptors array becoming too big and we didn’t have a chance to test this configuration thoroughly. As you know buffer descriptors occupy process VAS and can’t be mapped / unmapped using AWE mechanisms. To support 128GB of amount of VAS consumed would have to be more than doubled. It means that just doubling VAS size, now its 4GB on x64's WOW, might not help.

Please let me know if you have more questions