September 22, 2004 at 11:08 pm
Hello All,
I have the following setttings on a Win2003 Enterprise Server, 8 GB RAM, running SQL Server 2000 SP3:
Boot.ini - /3GB & /PAE switches set
SQL Server - Set to use fixed memory(dynamic memory min & max settings not used) 6144MB
Set working set = 0 (in EM, the checkbox for Reserve Physical Memory for SQL Server is not checked)
I am unclear as to whether or not these are the correct settings as there seems to be some confusion on this issue from everything I have read. Also, if I use the set working set option I tend to get Buffer Latch timeout errors. Any thoughts on these settings as I have them configured would be greatly appreciated, especially the set working set option.
Thank you,
Michael
mhweiss
September 24, 2004 at 3:51 pm
It looks correct. Though, I haven't had a need to change the default set working set size. As long as the sql server startup account has the right to lock pages in memory (a W2K requirement, didn't check if I really needed that with W2K3...think so), AWE should work. It will state in the ERRORLOG if it enables and/or cannot lock pages in memory.
A few months ago, when looking at a similar configuration with 16 GB of memory, I did not enable AWE because after looking at the SQL Server on 2003 documentation, it appeared that it could address up to 32 GB of memory without AWE and I set it to use dynamic memory allocation and that appeared to work as expected (min server memory 0 and max server memory 16000 MB--16 GB).
Check the SQL Server Performance counter under Memory for Target Server Memory and Total Server Memory to see the amount actually being used by SQL Server. There are also some AWE specific counters available.
In the mean time, in the 16 GB configuration, an admin changed it to mirror memory and use AWE. So, now we have 8 GB, AWE on, and dynamic memory allocation on and the counters show it's using 6.9 GB of memory with the old min / max server memory still set as if had 16 GB of memory. I'm not sure if AWE or dynamic memory is overriding one or the other--I don't think I need both.
September 24, 2004 at 4:08 pm
Thank you, Shawn. I will check the perf mon counters for the memory to see what is going on for certain. From all the documentation I have come across, it appears that AWE has to be enabled even under Win2003 Server. I am in AWE that yours was working without the settings (sorry, cheap pun!)...I may test that as well as I would much rather not run AWE and just let SQL Server allocate the memory. However your experiences does seem to substantiate my feelings that this subject is not widely understood and there is need for clarification.
Thanks,
Michael
mhweiss
September 24, 2004 at 4:09 pm
Thank you, Shawn. I will check the perf mon counters for the memory to see what is going on for certain. From all the documentation I have come across, it appears that AWE has to be enabled even under Win2003 Server. I am in AWE that yours was working without the settings (sorry, cheap pun!)...I may test that as well as I would much rather not run AWE and just let SQL Server allocate the memory. However your experiences does seem to substantiate my feelings that this subject is not widely understood and there is need for clarification.
Thanks,
Michael
mhweiss
September 25, 2004 at 6:48 am
Shawn, where did you find the information regarding SQL Server 2000 on Windows Server 2003 ? I believe you still have to turn on AWE in SQL Server to utilize the additional memory regardless of the version of OS.
The difference is on Windows 2003, \PAE switch may be enabled by the OS automatically
http://support.microsoft.com/default.aspx?scid=kb;en-us;283037
Yih-Yoon Lee
lee@yihyoon.com
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply