Dynamic Min Max memory settings

I have a sql server where applications suddenly and unexpectedly time out.
I noticed that we have the memory configuration set up as "Fixed" with an amount of 2576MB. In the error logs there is an error on start up of "Warning: unable to allocate 'min server memory' of 2576MB."
I would like to set the memory configuration to dynamic, but in my research I am unable to determine what are the appropriate settings for Min and Max. We have two instances of sQL Server running, with no other applications. One instance is heavy on the r
ead/write and the other instance is an archive, that sees very little action throughout the day. There is 3.7GB RAM on the server.
Since I would like to set the memory configuration to dynamic, can you steer me in the right direction?
Message posted via http://www.sqlmonster.com
Hi
Have a look at
http://support.microsoft.com/default...b;en-us;274750
Make sure you are using the correct version of the OS.
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:454da8a5f79844d190b94f6cff488a26@.SQLMonster.c om...
> I have a sql server where applications suddenly and unexpectedly time out.
> I noticed that we have the memory configuration set up as "Fixed" with an
amount of 2576MB. In the error logs there is an error on start up of
"Warning: unable to allocate 'min server memory' of 2576MB."
> I would like to set the memory configuration to dynamic, but in my
research I am unable to determine what are the appropriate settings for Min
and Max. We have two instances of sQL Server running, with no other
applications. One instance is heavy on the read/write and the other instance
is an archive, that sees very little action throughout the day. There is
3.7GB RAM on the server.
> Since I would like to set the memory configuration to dynamic, can you
steer me in the right direction?
> --
> Message posted via http://www.sqlmonster.com
|||If the server is a dedicated SQL server (and no Analysis services, etc).
Simply set the max all the way to the max in SQL Enterprise manager...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:454da8a5f79844d190b94f6cff488a26@.SQLMonster.c om...
> I have a sql server where applications suddenly and unexpectedly time out.
> I noticed that we have the memory configuration set up as "Fixed" with an
amount of 2576MB. In the error logs there is an error on start up of
"Warning: unable to allocate 'min server memory' of 2576MB."
> I would like to set the memory configuration to dynamic, but in my
research I am unable to determine what are the appropriate settings for Min
and Max. We have two instances of sQL Server running, with no other
applications. One instance is heavy on the read/write and the other instance
is an archive, that sees very little action throughout the day. There is
3.7GB RAM on the server.
> Since I would like to set the memory configuration to dynamic, can you
steer me in the right direction?
> --
> Message posted via http://www.sqlmonster.com
|||Am I to assume that the min memory setting would be set at zero?
Message posted via http://www.sqlmonster.com
|||Robert - when you right-click the server & go to properties, Memory, then
click the radio button for Dynamic you see the slider for mem - the min will
be 0 meg, fine to leave there, & I would recommend against putting the max
all the way to the right - what you are saying is that SQL server can grab
100% memory on the box. MS Best practices recommend leaving approx 200-500
meg for the OS & other processes/apps you may have going on the box to avoid
an out of mem situation. Esp with SQL 7 I have seen issues with having the
max all the way over to 100% mem amount on the box.
John
"Robert Richards via SQLMonster.com" wrote:

> Am I to assume that the min memory setting would be set at zero?
> --
> Message posted via http://www.sqlmonster.com
>
|||I disagree. Microsoft actually recommends setting MAX SERVER MEMORY to 100%
of physical and 50% of swap sizes when running in Dynamic mode.
The reason is two-fold. First of all, when in Dynamic Management mode, SQL
Server can actually acquire more than the MAX SERVER SETTING value, it will
just try to get underneath that value as soon as it no longer requires.
Second, memory usage is always measured in terms of VIRTUAL MEMORY, how much
is backed by physical memory depends on what SQL Server is doing, at what
processer priority, and what the priority of the other processes are.
If you need to reserve a specific amount of physical memory, you need to
take a look at the -g start up parameter. This controls the size of the MEM
TO LEAVE region, which is 384 MB by default (128 MB for 255 MAX WORKER
THREADS plus a default value of -g at 256 MB).
Sincerely,
Anthony Thomas

"John F." <juantana@.newsgroups.microsoft.com> wrote in message
news:39491B1E-5F1A-40F2-A6FE-C97864A4713C@.microsoft.com...
Robert - when you right-click the server & go to properties, Memory, then
click the radio button for Dynamic you see the slider for mem - the min will
be 0 meg, fine to leave there, & I would recommend against putting the max
all the way to the right - what you are saying is that SQL server can grab
100% memory on the box. MS Best practices recommend leaving approx 200-500
meg for the OS & other processes/apps you may have going on the box to avoid
an out of mem situation. Esp with SQL 7 I have seen issues with having the
max all the way over to 100% mem amount on the box.
John
"Robert Richards via SQLMonster.com" wrote:

> Am I to assume that the min memory setting would be set at zero?
> --
> Message posted via http://www.sqlmonster.com
>