April 1, 2004 at 8:59 pm
John,
I tried to enable awe on one of our 3.8 gb machine without includingthe /3gb switch and i saw the same results which you saw. It showed me close to 3006000. Not sure where the value in the table comes from. I will try to do some research tomorrow and see if i can find something. But regarding /3GB, there has been lot of discussions alongwith the wonderful article from Ken Henderson. Thanks to Antares686 for sharing it with us.
April 1, 2004 at 10:43 pm
I also tried another test on a test server. W2K Enterprise, 4gb ram. Enabled AWE and saw over 3GB of memory being used. I also tried using the Min and Max Server memory settings. When you reduce the Max setting you have to restart SQL Server for it to become effective. It's not dynamic with AWE enabled (as documented). I've also been evaluating a tool from Quest Software (Spotlight for SQL Server) and the tool has some useful memory displays that all seem to confirm my other findings.
My initial question was because I was concerned at why my prod server was only using 1.4GB. I have since discovered that we have SET WORKING SET SIZE enabled. When SQL Server is restarted we get the message "Working Set size set to 1465536 kilobytes." plus "Warning: unable to allocate 'min server memory' of 2193MB". Has anybody any ideas how this figure is derived? Is is based on what memory is available after other processes get their share?
We also have MIN and MAX Server memory settings set (min 2193 and Max 2925). According to my research these settings are inconsistent with Working Set Size. Working Set Size should only be used with Min and Max being set to the same value otherwise it "will not work properly". Not sure what "will not work properly" means. I'm planning to disable Working Set Size and let SQL Server dynamically manage its memory.
With all the conflicting responses regarding AWE and /3GB I'm very reluctant to consider these options for a prod server.
April 2, 2004 at 1:11 am
bclevlnd - could you expand on what your problem was, your o/s and sql setup and exactly what ms said to do?
I had a similar issue with ther /3Gb switch allegedly causing performance drop off, we think that in actuality it was an application issue, we had the uk ms guys in and they merely said the /3Gb switch should not cause problems.
I've used the 3/Gb switch for years elsewhere without probs and have servers now using /3Gb and PAE ( with over 4 Gb ram ) so any info would be appreciated.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
April 2, 2004 at 8:35 am
We had several issues with extremely slow queries and high CPU utilization. We did several PSSDIAG dumps for MS, and they saw no issues. After some time and questions from us about the /3GB and /PAE switches, they came back and said that for SQL server you have to have both switches in the boot.ini file. Again, this is not for the OS, but for SQL itself. After puting in both switches, we saw a great performance improvement. The client we were working with has a Premiere account with MS, so we got SQL guys onsite. before putting both switches in, you could see errors in logs reporting that AWE could not properly load...which prompted our questions to MS.
This client has a 2 server cluster of Compaq dual processor 2.8 Gig Xeons, 6GB of RAM running on an EMC CX600 SAN. Windows 2000 Advanced Server, SQL 2000 Enterprise.
One other HUGE issue we found is with hyperthreading on intel chips. Having this turned on will cause real pains with large queries. If you have parallelism turned on in SQL, SQL will split large queries out to all processors for faster execution. However, with Hyperthreading, it will split the query out to physical and logical processors. Since you have a single processor now handling 2 threads from the same query, you get deadlocks. Apparently, Microsoft is aware of this and is working on a patch. We turned off hyperthreading and saw yet another performance gain. We are doing >100 transactions per second now with no problems.
April 2, 2004 at 8:47 am
That's quite interesting .. the probs I had were with a 4Gb ram cluster using zeon 3's. It was decided that adding the extra memory slowed down the server and caused severe locking .. I didn't really get enough time to run diags myself and every time we had the ms guys in we had the switch off and there were no probs. I now have a 4 x zeon 4 cluster with 8gb ram but I'm not allowed to use the memory just yet < grin > so I have 6 Gb of expensive compaq memory sat doing nothing !! On our dw servers w ehave the 3bg and \pae enabled on one and just \pae on the other both have 8 Gb ram.
I'll watch the hyperthreading, so far no probs .. most contention I've been able to solve by eliminating poor sql and adding indexes. my server is doing around 90 trans/sec currently but i'm expecting a large ramp up soon.
You're right with 6 Gb you need awe turned on - the 3Gb switch is optional as far as I can see and affects 1 gb of the first 4 Gb of available ram. Thanks for replying, appreciated.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply