SQL Server Performance Forum – Threads Archive
Large memory adviceHi all,
We are currently running Win 2003 Server Enterprise Edition / SQL Server Enterprise Edition 2000, 8GB memory. I want to go to 16GB memory, but I’ve heard lots of conflicting discussion. Some documentation says that it is OK, others say that AWE at this level is unstable. 1. is it stable to run with 16GB memory on Windows 2003 Server Enterprise Edition / SQL Server 2000 Enterprise Edition? 2. are there any registry settings or configurations? Anything special needed for the kernel? 3. what should be the setting for ‘max memory’ for the sql server? 4. what should be the setting for the paging file? 5. what should be the setting, if any, for the mem-to-leave space (-g option startup param) Thanks for your input, in advance!
(moved from Performance Tuning DBA section)
The Windows 2000 and Windows Server 2003 memory managers use PAE to provide more physical memory to a program. This reduces the need to swap the memory of the page file and results in increased performance. The program itself is not aware of the actual memory size. All the memory management and allocation of the PAE memory is handled by the memory manager independently of the programs that run. Although this allows SQL Server to increase the RAM that can be accessed in the virtual address space available to it, the size of the address space cannot be dynamically managed because there are certain restrictions in mapping AWE addresses, such as
– You have to de-commit the entire region of address space and cannot de-commit a certain portion like SQL Server does to de-commit individual BPool buffers.
– You can unmap only data or index pages. Another restriction is that the memory pool of SQL Server 2000 using AWE cannot be swapped out to the page file. Windows 2000 has to swap out other applications if it needs to use additional physical memory, which may hinder the performance of the other applications. 1) I haven’t come across this setting, so no comments on stability. But one thing is sure if the settings are properly defined then it should work as Win2K3 with SQL 2K EE will support upto 32GB. 2) KBAhttp://support.microsoft.com/Default.aspx?id=274750 to configure memory and also refer corresponding links. 5) To measure the size of the MemToLeave on AWE-enabled SQL Servers, look at the Private Bytes counter under the Process object.
HTH Satya SKJ
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.
I’ve been advised that I should remove my /3GB in boot.ini when I go to 16GB, because at that level the OS really has more trouble managing the AWE memory. However, this doc http://www.sql-server-performance.com/awe_memory.asp says that the /3GB switch should be used at 16GB. This seems to contradict the advice to remove /3GB when the server is at 16GB exactly. Comments?
The restriction applies to Windows 2000, not Windows 2003 Enterprise Edition. You’ll be fine still having the switch on there. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
This is exactly what I had requested Brad to write about a few days ago- Unless you are planning to use 64 Bit version, the 2k and 2003 server memory management needs to remove the /3GB switch if you have 16 GB or more RAM. Its the very nature of memory Architect and management that resources needs to be available to handle memory bit allocations. Although this is relatively better handled at 2003 level as you get better control over Kernal and System PTEs, I would suggest that you look into 64 Bit version at this level. Thanks