March 4, 2008 at 3:16 am
Hi,
I have the following Server:
Dell PowerEdge 2900 with 2 x Quad Core Intel Xeon X5355 2.66 GHz processors and 8GB of RAM
I am running Sql Server 2000 Enterprise Edition on the Server with IIS for web interface to a database driven website. Nothing else. I want to know the ideal and performance based settings that i should do in the Memory and Processor tabs of the sql under Sql Server properties.
Should i reserve memory... If yes, then how much and it should be dynamic or fixed?
Thanks.
March 4, 2008 at 7:18 pm
It really depends. Since you have SQL Server and IIS on the same server, I would like to recommend you limit the memory used by SQL Server because it can eat up all memories. You may also consider to split the usage of CPU to SQL Server and others.
March 4, 2008 at 9:38 pm
You'll need to test and see if IIS or SQL needs more resources. You might be able to shade your usage one way or the other, but I'd be tempted to set AWE and let SQL use 4GB, then leave 4GB for IIS.
You'll need to see if affinity needs to be set (or IIS limits) to prevent one or the other from stealing all the CPU. SQL2008's resource governer would be great here.
It really means monitoring and checking the CPU and memory usage and adjusting things as needed to shade resources one way or the other. Different web site applications can use memory or CPU differently. Also watch IIS caching on the same disks as SQL. You definitely want to be careful about being IO bound.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
March 6, 2008 at 5:13 am
I have the following concepts in my mind. Please correct me if i am right or wrong.
Sql Server 2000 Enterprise running on Windows 2003 Server Enterprise Edition would normally take a maximum of 2GB memory. Some part of it can be in the physical memory and other can be paged.
Once the AWE is enabled, Sql Server 2000 Enterprise can take upto 4GB of memory.
And when the "/PAE switch" and/or "/NoExecute=Optout" is added to the boot.ini file, sql can take memory in access of 4GB.
Now back to my issue...
-----------------------
Initially i was running Sql Server 2000 Enterprise on Windows 2003 Server Enterprise edition without enabling the AWE and in absence of /PAE switch in the boot.ini file. Though /NoExecute=Optput was already added to the boot.ini file, maybe because my hardware supports DEP but it was not making any difference as AWE was disabled.
SQL was set by default to "Dynamically Configure Sql Memory" with minimum to "0" and max to "4GB". Reserve physical memory for sql was unchecked.
At that time SQL was consuming around 1.73 GB or RAM and i can verify it from the tast manager -> processes section. In addition the PF Usage for Sql was around 0.27GB. If we add both together it becomes 2GB.
Total amount of physical memory available for the server was around 6GB and i verified it from the task manager.
Now, i have added /PAE switch in the boot.ini file. I am not sure if i need to add it in presence of /NoExecute=Optout switch as i read that both will perform the same function of taking memory beyond 4GB for sql. Anyway, adding both is not making any problem with my server and its working. I enabled the AWE and set the memory to minimum of 487 MB and maximum of 5100 MB under "Dynamically Configure Sql Memory". Restarted the SQL and i can see "Address Windowing Extensions Enabled" in the logs.
Now when i check the memory under processes in the task manager of windows, i can see that SQL is only using 5364 K. On the other hand the amount of physical memory available has decreased to around 2GB as compared to 6GB when the AWE switch was not enabled and /PAE was not added. PF Usage has jumped to 5.70 GB.
After that i also checked the "Reserve Physical memory option" but i dont see any major performace difference. Infact running without /PAE switch and AWE was also just like the same as now.
I want to know that is this normal and have i done correct settings?
NOTE: IIS on my system has around 10 simulataneous users and it does not load my system resources and memory for more then 10%.
March 6, 2008 at 7:12 am
No Dear
/noexecute=optout use for enabling DEP for the operating system and all processes, including the Windows kernel and drivers.
/PAE uses for extend the memory upto 64GB.
March 6, 2008 at 9:54 pm
Turning on DEP using /NoExecute=optout surely enables PAE. This can be verified from the undermentioned paragraphs taken from http://msdn2.microsoft.com/en-us/library/ms791539.aspx.
"On 32-bit operating systems, hardware-enforced DEP requires Physical Address Extension (PAE). Therefore, when DEP is enabled on a computer that supports hardware-enforced DEP, 32-bit versions of the Windows operating system automatically enable PAE and ignores /nopae.
On Windows XP with SP2, , when you disable DEP by using /noexecute=alwaysoff, Windows disables both DEP and PAE. This is the equivalent of using /noexecute=alwaysoff /nopae. To enable PAE without DEP on a system with hardware-enforced DEP, use /noexecute=alwaysoff /pae. This explicitly enables PAE while disabling DEP.
However, on Windows Server 2003 with SP1, when you disable DEP by using /noexecute=alwaysoff, Windows disables only DEP. PAE is still enabled and the system ignores explicit attempts to disable PAE, such as the /nopae parameter. To disable both DEP and PAE on Windows Server 2003 with SP1, use /execute. "
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply