64 bit max memory setting for SQL 2005

We have 32GB of RAM on x64 bit Windows 2003 SP2 and SQL 2005 SP2 64 bit as
well.
This box only serves for SQL Server application
So I have been receiving some mixed recommendations about max and min memory
settings
They are
1) Just leave it as default which is 0 for min and 2147483647 for max
2) Put a default of around 15 or 20GB for min and 2147483647 for max i.e. do
not set a max setting but set a min setting so that when SQL Server starts
up, it has enough memory committed at the beginning.
3) Leave the default of 0 for min but set max to say 28GB i.e. have a max
setting
Please let me know whats the best way to go about this.
Thanks
What is best for you may not be best for others but in general there is no
need to set a min setting if SQL Server is the only app on the server. But I
always set a MAX to ensure the OS and any other temporary processes such as
Term Services, Notepad, WinZip etc. that usually get run have enough all the
time. With 32GB I would set the MAX at 28 or 29 and see how that works out.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Hassan" <hassan@.hotmail.com> wrote in message
news:Op6o550PIHA.4712@.TK2MSFTNGP04.phx.gbl...
> We have 32GB of RAM on x64 bit Windows 2003 SP2 and SQL 2005 SP2 64 bit as
> well.
> This box only serves for SQL Server application
> So I have been receiving some mixed recommendations about max and min
> memory settings
> They are
> 1) Just leave it as default which is 0 for min and 2147483647 for max
> 2) Put a default of around 15 or 20GB for min and 2147483647 for max i.e.
> do not set a max setting but set a min setting so that when SQL Server
> starts up, it has enough memory committed at the beginning.
> 3) Leave the default of 0 for min but set max to say 28GB i.e. have a max
> setting
> Please let me know whats the best way to go about this.
> Thanks
>
|||Andrew,
What about leaving it to the default of 2147483647 ?
Would that cause SQL to use all 32 GB if it needs to ?
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23V2%23TM1PIHA.3676@.TK2MSFTNGP06.phx.gbl...
> What is best for you may not be best for others but in general there is no
> need to set a min setting if SQL Server is the only app on the server. But
> I always set a MAX to ensure the OS and any other temporary processes such
> as Term Services, Notepad, WinZip etc. that usually get run have enough
> all the time. With 32GB I would set the MAX at 28 or 29 and see how that
> works out.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Hassan" <hassan@.hotmail.com> wrote in message
> news:Op6o550PIHA.4712@.TK2MSFTNGP04.phx.gbl...
>
|||64 bit has less overhead than 32 bit when dealing with large amounts of
memory but the OS still needs x amount of memory to operate properly. Even
though the memory in 64 bit can be dynamic sql server can still get to a
point where it may want to fight with the OS for every last bit of memory.
If you have Lock Pages in Memory set (which you should) it can be slow to
respond. There is no need to put it into that situation.
http://blogs.msdn.com/slavao/archive/2006/11/13/q-a-does-sql-server-always-respond-to-memory-pressure.aspx
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Hassan" <hassan@.hotmail.com> wrote in message
news:OAsXbW1PIHA.4752@.TK2MSFTNGP05.phx.gbl...
> Andrew,
> What about leaving it to the default of 2147483647 ?
> Would that cause SQL to use all 32 GB if it needs to ?
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23V2%23TM1PIHA.3676@.TK2MSFTNGP06.phx.gbl...
>
|||I suggest you to read the following article, it'll make your mind clear
about min\max server memory options.
http://msdn2.microsoft.com/en-us/library/ms180797.aspx
Ekrem nsoy
"Hassan" <hassan@.hotmail.com> wrote in message
news:Op6o550PIHA.4712@.TK2MSFTNGP04.phx.gbl...
> We have 32GB of RAM on x64 bit Windows 2003 SP2 and SQL 2005 SP2 64 bit as
> well.
> This box only serves for SQL Server application
> So I have been receiving some mixed recommendations about max and min
> memory settings
> They are
> 1) Just leave it as default which is 0 for min and 2147483647 for max
> 2) Put a default of around 15 or 20GB for min and 2147483647 for max i.e.
> do not set a max setting but set a min setting so that when SQL Server
> starts up, it has enough memory committed at the beginning.
> 3) Leave the default of 0 for min but set max to say 28GB i.e. have a max
> setting
> Please let me know whats the best way to go about this.
> Thanks
>