SQL Server Performance Forum – Threads Archive

SQL Server 2K on W2K3 x64 – memory ??

I have just installed Windows Server 2003 x64 and SQL Server 2K w/sp4. The server is a HP DL585 with 4 2.8 Opteron Processors and 32 Gig of RAM. I have done nothing special to the memory setting (AWE or /3G switch) and SQL Server seems to recognize all of the memory. With questions around the SP4 and the AWE in the 32bit environment I was wondering if there is anything special that I need to chech in order to make sure that SQL is indeed using the 32 Gig??? I am currently running test with StoredProcedures and nighly scheduled jobs, so far everything is running well. If there are any suggestion to this new OS and SQL configurations that I should consider please suggested them. I will be running testing on this machine for 2 weeks before giving it an ok to put into production. Thanks in advance. Wr.

---

The SP4 download site has now a warning about not installing SP4 on systems with AWE enabled.http://www.microsoft.com/sql/downloads/2000/sp4.asp and referred KBAhttp://support.microsoft.com/Default.aspx?kbid=899761 for your information. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

Thanks for the direction. I have read those articles along with others. When installing SQL2K on the x64 box it says that it does not support anything less than sp4, so I went that route. What I don’t understand is the following – I do not have AWE on and do not have any /3GB switches turned on in the boot process. But when I look at the memory setting in the SQL Server Properties box, it shows all 32 Gig. I have been pushing Stored Procedures and other processes to load up the memory and see how far it goes, but nothing past 4 Gig so far. I am using Quest Spotlight on SQL to monitor the server. Should I try and turn on AWE and see if it uses atleast the 50%(16Gig)?? Thanks Wr.

---

I recommend if you’re not having any performance issues then better not to try any switches or tweaks, better to stick with the optimization tips and tricks listed in this website. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

OK. If I find any valuable results I will post them to share with everyone. Thanks Wr.

---

Another KBAhttp://support.microsoft.com/kb/283037/ about large memory support in Win2k3, fyi. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

if you run the 32-bit Win OS with >4GB, you need the PAE and or 3GB flag in boot.ini,
then you set the AWE flag in SQL Server.
with the 64-bit Win OS, you don’t need any boot flags, but SQL Server should not be able to use more than 2-3GB without the AWE flag see what task manager and perfmon show for SQL Server memory use,
if only 2-3GB, then you may as well set the AWE flag so that atleast you can use 16GB, which is not as much as 32, but definitely more than 2-3

---

I agree that you probably need to enable AWE. The memory slider in SQL Server doesn’t mean it uses all that much.
When we had 4 GB, it showed 4 GB, configured to 4 GB, but really only uses 2 GB until we used /3GB. If you enable AWE you probably can only use 16GB due to SP4 bug.

---

Thanks. I will try to new setting and see what happens. Atleast I will be able to get 16GB out of the system until the hotfix is made available. Wr.

---

BP tip: I suggest you to test it before deploying it on the production, as it will give more insight if any problem occurs before hand. HTH Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

Just an update. I have verified that even with the 32 Gig of memory that SQL is only using 16 Gig. Hopefully SP4a comes out ant this will be resolved. In general I have seen atleast a 30% increase in retrieval of information. A couple of SPs that run on initial load took 50 sec to come back with a result and then 18 secs on the old server. The new server results were 40 sec on load and 7 seconds on the 2nd run. I found this true for several SPs. The overall database size is about 40 Gig, so most of it gets into memory. So far I haven’t run into any issue with Web Pages or our VB app accessing the new server. If anyone has questions let me know. WFalcon

---

Have you tried this hotfix http://support.microsoft.com/default.aspx?kbid=899761 Bert

---

I could be off base, but I thought that SQL server 2000 w/ sp4 on x64 windows used WOW, and only supports 4 Gig of memory per instance of SQL. I don’t recall where I read that, but it holds true on my x64 test server. Hope it helps…

---

On 32 bit wyou can think of memory as being two types VAS (virtual address space) and memory that is accessed via AWE. AWE is only used for datapages. VAS is limited to 2GB, or 3GB if you use the /3GB switch. The kewl thing about SQL2000 SP4 on x64 is that you get a seperate VAS for each instance of SQL Server. This is using WOW64 as you mentioned. So now each instance will get 4GB of VAS. you can access larger amounts of memry by using AWE. bert

---

Joe Chang mentions the use of task manager, which I have found to be useful in the 64-bit world. Under the task manager processes tab, 32-bit processes show up with a *32 at the end of the process name. John Langston
Cimarex Energy Co.
Tulsa, OK

---

/3GB AWE are all for 32 bit world and I belive there is nothing that needs to done for 64 bit. I should be able to take up the memory provided you put so much of load on SQl Server.
in 32 bit os 2^32 limits to 4GB and thus we have all work arounds /3GB /PAE.
But in 64 bit OS 2^64 has a more than 32 GB so all memory should be usable by applications.

---

]]>