October 16, 2006 at 1:09 am
"For a dedicated database server, the default memory settings for SQL Server should suffice."
Not sure I agree with this (the context is having AWE enabled)
This means a SQL 2000 instance with AWE enabled will leave only 128MB for the OS.
Having seen this happen, I can say with confidence that the OS will start paging like mad and cause some frantic phone calls from the WIndows team...
Always set min and max memory with AWE enabled on SQL 2000.
October 16, 2006 at 2:04 am
A bit more detail on how all this effects 64bit machines (memory page locking etc) I think would of been useful
October 16, 2006 at 6:46 am
I agree with the comment of not leaving defaults, as I have seen that that usually means take all be 1 MB of memory.
I also believe that if you go above 2 or 4 GB, dynamic memory utilization is no longer possible, SQL Server has to go into fixed memory size. It may not start out with that much memory, but it will not give it back. I have also seen Enterprise manager refuse to switch to dynamic memory when there was that much available.
The first part about the OS and memory was good.
One question, does this mean I should always leave at least 1GB physical Ram for the os?
October 16, 2006 at 6:52 am
"This means a SQL 2000 instance with AWE enabled will leave only 128MB for the OS."
I always set the maximum memory to allow for the OS to have at least 1GB. If I remember correctly, the default max configured memory for AWE on SQL Server appears to be much more than what you've suggested.
As to coverage on 64bit machines, maybe someone with some hands on experience can write an article on this topic.
October 16, 2006 at 1:48 pm
This is actually one of those topics I've seen discussed and rediscussed extensively, yielding more confusion than answers mostly.
What I want to know is if I set the /3gb switch on a 4GB server, is that all I need to do? From what I've read, this alone will not give all 3GB to SQL Server because the 32 bit architecture will still not address space beyond 2GB to any one application.
If this is the case, do we need to configure /3gb and PAE with AWE even on a 4GB machine?
October 16, 2006 at 5:59 pm
To answer your particular question, for a 4GB production server, you are limited to using the /3GB switch. The /PAE switch is only applicable to OSs having more than 4GB of RAM. For this reason, you will most likely be using a Standard Edition of SQL Server, which does not support AWE.
A number of things to keep in mind:
Firstly, the article starts by discussing the two switches, which is related to the OS. Use the hyperlinks to check that your particular operating system support the two switches.
Secondly, determine if your particular version of SQL Server supports AWE. Again use the hyperlinks to check.
Thirdly, determine if you wish to configure (i) BOTH switches with AWE enabled for SQL Server for systems with between 4 and 16 GB of RAM, (ii) just the /3GB switch for systems with less than 4GB RAM or (iii) just the /PAE switch with AWE enabled on SQL Server for systems with more than 4GB RAM.
Lastly, set the max memory setting for SQL Server to a value that is right for your system. This is the 'tuning' part, where you may need to change the default value when AWE is enabled on SQL Server. The default value may be appropriate for a 'dedicated' Database server. My production servers are on SS2K EE with 7-8 GB RAM, and I have the max memory setting set to around 1 GB. Whatever value you set, you may need to monitor it for a few days. HTH.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply