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
>
Showing posts with label memory. Show all posts
Showing posts with label memory. Show all posts
Wednesday, March 21, 2012
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 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.comHi
Have a look at
http://support.microsoft.com/default.aspx?scid=kb;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.com...
> 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.com...
> 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
>
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.comHi
Have a look at
http://support.microsoft.com/default.aspx?scid=kb;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.com...
> 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.com...
> 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
>
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 am
ount 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.droptable.comHi
Have a look at
http://support.microsoft.com/defaul...kb;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 droptable.com" <forum@.droptable.com> wrote in message
news:454da8a5f79844d190b94f6cff488a26@.SQ
droptable.com...
> 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.droptable.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 droptable.com" <forum@.droptable.com> wrote in message
news:454da8a5f79844d190b94f6cff488a26@.SQ
droptable.com...
> 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.droptable.com|||Am I to assume that the min memory setting would be set at zero?
Message posted via http://www.droptable.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 droptable.com" wrote:
> Am I to assume that the min memory setting would be set at zero?
> --
> Message posted via http://www.droptable.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 droptable.com" wrote:
> Am I to assume that the min memory setting would be set at zero?
> --
> Message posted via http://www.droptable.com
>
I noticed that we have the memory configuration set up as "Fixed" with an am
ount 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.droptable.comHi
Have a look at
http://support.microsoft.com/defaul...kb;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 droptable.com" <forum@.droptable.com> wrote in message
news:454da8a5f79844d190b94f6cff488a26@.SQ
droptable.com...
> 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.droptable.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 droptable.com" <forum@.droptable.com> wrote in message
news:454da8a5f79844d190b94f6cff488a26@.SQ
droptable.com...
> 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.droptable.com|||Am I to assume that the min memory setting would be set at zero?
Message posted via http://www.droptable.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 droptable.com" wrote:
> Am I to assume that the min memory setting would be set at zero?
> --
> Message posted via http://www.droptable.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 droptable.com" wrote:
> Am I to assume that the min memory setting would be set at zero?
> --
> Message posted via http://www.droptable.com
>
Dynamic memory setting
I have inherited a SQL 2K server that is having some severe performance
problems. The server has 8Gb memory with AWE enabled and 3GB/PAE setting in
the boot.ini. No other applications are running on the server. The available
memory is showing 1.2GB.
The previous administrator has the server set with a fixed memory setting of
8120MB.
My question, is there a benefit that I am unaware of in having the server se
t
with fixed memory versus dynamic? Is not dynamic memory a better
configuration when SQL Server is not competing with other applications?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200601/1If you are using AWE and you have 8GB you should set the MAX memory to no
more than 7GB since AWE is not dynamic in SQL2000.
Andrew J. Kelly SQL MVP
"Robert R via droptable.com" <u3288@.uwe> wrote in message
news:5ad0d04f5b9b2@.uwe...
>I have inherited a SQL 2K server that is having some severe performance
> problems. The server has 8Gb memory with AWE enabled and 3GB/PAE setting
> in
> the boot.ini. No other applications are running on the server. The
> available
> memory is showing 1.2GB.
> The previous administrator has the server set with a fixed memory setting
> of
> 8120MB.
> My question, is there a benefit that I am unaware of in having the server
> set
> with fixed memory versus dynamic? Is not dynamic memory a better
> configuration when SQL Server is not competing with other applications?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200601/1|||OK. Thanks, for making me aware of that, but what about the question(s) I
posed:
"Is there a benefit that I am unaware of in having the server set
with fixed memory versus dynamic? Is not dynamic memory a better
configuration when SQL Server is not competing with other applications?"
Andrew J. Kelly wrote:[vbcol=seagreen]
>If you are using AWE and you have 8GB you should set the MAX memory to no
>more than 7GB since AWE is not dynamic in SQL2000.
>
>[quoted text clipped - 11 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200601/1|||Ordinarily, allowing SQL Server to dynamically manage the memory
available to it is the better (more efficient) option, however your
options are more limited with AWE memory. SQL Server 2000 does not swap
pages out of memory when using AWE memory (it locks pages in memory), so
you cannot use the dynamic memory management in SQL Server 2000 when you
allow SQL Server address space over 4GB - you must specify a max server
memory size (the pages in memory committed by SQL Server won't be
released until the SQL Server service shuts down).
SQL 2005 EE on Win 2003 takes advantage of changes to the underlying OS
and allows dynamic memory management with AWE, but with SQL 2000 (no
matter what OS) you're stuck with fixed AWE memory unfortunately.
So figure out how much memory is needed for the non-SQL stuff on the box
and allocate the rest to SQL Server. As Andrew suggested, a dedicated
SQL server with 8GB of physical RAM probably doesn't need more than
about 1GB for the OS et al. (monitoring agents, etc.), so you should be
OK allocating about 7GB to SQL Server.
*mike hodgson*
http://sqlnerd.blogspot.com
Robert R via droptable.com wrote:
>I have inherited a SQL 2K server that is having some severe performance
>problems. The server has 8Gb memory with AWE enabled and 3GB/PAE setting in
>the boot.ini. No other applications are running on the server. The availabl
e
>memory is showing 1.2GB.
>The previous administrator has the server set with a fixed memory setting o
f
>8120MB.
>My question, is there a benefit that I am unaware of in having the server s
et
>with fixed memory versus dynamic? Is not dynamic memory a better
>configuration when SQL Server is not competing with other applications?
>
>|||Hello Robert
I administer myself a Windows 2000 / SQL 2000 server with 8 GB of memory
( this erver has superior performance )
if you use PAE icw AWE enabled you should first calculate how manny memory
your server needs in a typicall load ( i use 512 MB for my windows 2000
advanced server )
now fix the rest of the memory size in the AWE setting for SQL use
if you start your task manager you should see that the 7,5 gigabytes is
constantly claimed by SQL server
> My question, is there a benefit that I am unaware of in having the server
> set
> with fixed memory versus dynamic? Is not dynamic memory a better
> configuration when SQL Server is not competing with other applications?
In my opinion it is the other way around ,,,, however with PAE and AWE this
is simply not an option
regards
Michel Posseth [MCP]
"Robert R via droptable.com" <u3288@.uwe> wrote in message
news:5ad0d04f5b9b2@.uwe...
>I have inherited a SQL 2K server that is having some severe performance
> problems. The server has 8Gb memory with AWE enabled and 3GB/PAE setting
> in
> the boot.ini. No other applications are running on the server. The
> available
> memory is showing 1.2GB.
> The previous administrator has the server set with a fixed memory setting
> of
> 8120MB.
> My question, is there a benefit that I am unaware of in having the server
> set
> with fixed memory versus dynamic? Is not dynamic memory a better
> configuration when SQL Server is not competing with other applications?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200601/1|||Hello, Robert!
To decide if you will use fixe the memory to SQL Server, you need to know
your environment. The SQL Server works better if it has memory enough to
process the information and transaction. If your SQL Server has any other
application installed you should not set fixed memory, because other process
must require more memory and the server will not have memory enough to
supress this request.
So, you should analyze it before and after that define if you will use fixed
memory or not.
I have problems too with hyper-memory allocation to SQL Server, or better, I
had define more memory that it needed.
I recomend for you, that you set a min memory and a maximum memory before,
monitor its performance and if you have many memory free, cache hit ratio
below 90% and other important counters to determine if SQL Server is using
memory efficiently.
If you need a help, please send me a message. Ok?
Take care, when you define fixed memory. You should monitor in System Monito
r
(W2k ou W2k3) how many Pages/sec your server is paging because it. Ok?
Bye
Juliano Horta
Robert R wrote:
>I have inherited a SQL 2K server that is having some severe performance
>problems. The server has 8Gb memory with AWE enabled and 3GB/PAE setting in
>the boot.ini. No other applications are running on the server. The availabl
e
>memory is showing 1.2GB.
>The previous administrator has the server set with a fixed memory setting o
f
>8120MB.
>My question, is there a benefit that I am unaware of in having the server s
et
>with fixed memory versus dynamic? Is not dynamic memory a better
>configuration when SQL Server is not competing with other applications?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200601/1|||I thought that when I stated the memory was not dynamic it made this
irrelevant. It's rare that the Fixed memory option is used and I would
uncheck this but the memory in AWE is always static in SQL2000.
Andrew J. Kelly SQL MVP
"Robert R via droptable.com" <u3288@.uwe> wrote in message
news:5ad1454f51cda@.uwe...
> OK. Thanks, for making me aware of that, but what about the question(s) I
> posed:
> "Is there a benefit that I am unaware of in having the server set
> with fixed memory versus dynamic? Is not dynamic memory a better
> configuration when SQL Server is not competing with other applications?"
>
> Andrew J. Kelly wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200601/1|||I think you missed the bit where Robert said he has 8GB in his box and
therefore must use AWE (and therefore, by implication, cannot use
dynamic memory management).
*mike hodgson*
http://sqlnerd.blogspot.com
Juliano H via droptable.com wrote:
>Hello, Robert!
>To decide if you will use fixe the memory to SQL Server, you need to know
>your environment. The SQL Server works better if it has memory enough to
>process the information and transaction. If your SQL Server has any other
>application installed you should not set fixed memory, because other proces
s
>must require more memory and the server will not have memory enough to
>supress this request.
>So, you should analyze it before and after that define if you will use fixe
d
>memory or not.
>I have problems too with hyper-memory allocation to SQL Server, or better,
I
>had define more memory that it needed.
>I recomend for you, that you set a min memory and a maximum memory before,
>monitor its performance and if you have many memory free, cache hit ratio
>below 90% and other important counters to determine if SQL Server is using
>memory efficiently.
>If you need a help, please send me a message. Ok?
>Take care, when you define fixed memory. You should monitor in System Monit
or
>(W2k ou W2k3) how many Pages/sec your server is paging because it. Ok?
>Bye
>Juliano Horta
>
>Robert R wrote:
>
>
>|||Hello! Mike.
Why, He cannot use dynamic memory management? What's the relationship betwee
n
AWE enabled and memory options?
Thanks
Mike Hodgson wrote:[vbcol=seagreen]
>I think you missed the bit where Robert said he has 8GB in his box and
>therefore must use AWE (and therefore, by implication, cannot use
>dynamic memory management).
>--
>*mike hodgson*
>http://sqlnerd.blogspot.com
>
>[quoted text clipped - 36 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200601/1|||When using AWE memory SQL Server locks pages in memory. It will not
swap pages out to disk if another application makes a request for memory
and there is not enough available at the time (like it normally does
when it is using dynamic memory management). Using min & max server
memory is fine but once SQL marks the memory page as committed, it stays
that way until SQL Server shuts down (ie. it will not be released).
Dynamic memory management (at least in my book) also involves releasing
memory, when needed, to maintain a minimum free memory threshold (off
the top of my head I think the default threshold is 10MB).
Perhaps I misunderstood your post but I thought you were advocating
setting a max & min level and letting SQL Server allocate and release
memory as needed.
*mike hodgson*
http://sqlnerd.blogspot.com
Juliano H via droptable.com wrote:
>Hello! Mike.
>Why, He cannot use dynamic memory management? What's the relationship betwe
en
>AWE enabled and memory options?
>
>Thanks
>Mike Hodgson wrote:
>
>
>sql
problems. The server has 8Gb memory with AWE enabled and 3GB/PAE setting in
the boot.ini. No other applications are running on the server. The available
memory is showing 1.2GB.
The previous administrator has the server set with a fixed memory setting of
8120MB.
My question, is there a benefit that I am unaware of in having the server se
t
with fixed memory versus dynamic? Is not dynamic memory a better
configuration when SQL Server is not competing with other applications?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200601/1If you are using AWE and you have 8GB you should set the MAX memory to no
more than 7GB since AWE is not dynamic in SQL2000.
Andrew J. Kelly SQL MVP
"Robert R via droptable.com" <u3288@.uwe> wrote in message
news:5ad0d04f5b9b2@.uwe...
>I have inherited a SQL 2K server that is having some severe performance
> problems. The server has 8Gb memory with AWE enabled and 3GB/PAE setting
> in
> the boot.ini. No other applications are running on the server. The
> available
> memory is showing 1.2GB.
> The previous administrator has the server set with a fixed memory setting
> of
> 8120MB.
> My question, is there a benefit that I am unaware of in having the server
> set
> with fixed memory versus dynamic? Is not dynamic memory a better
> configuration when SQL Server is not competing with other applications?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200601/1|||OK. Thanks, for making me aware of that, but what about the question(s) I
posed:
"Is there a benefit that I am unaware of in having the server set
with fixed memory versus dynamic? Is not dynamic memory a better
configuration when SQL Server is not competing with other applications?"
Andrew J. Kelly wrote:[vbcol=seagreen]
>If you are using AWE and you have 8GB you should set the MAX memory to no
>more than 7GB since AWE is not dynamic in SQL2000.
>
>[quoted text clipped - 11 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200601/1|||Ordinarily, allowing SQL Server to dynamically manage the memory
available to it is the better (more efficient) option, however your
options are more limited with AWE memory. SQL Server 2000 does not swap
pages out of memory when using AWE memory (it locks pages in memory), so
you cannot use the dynamic memory management in SQL Server 2000 when you
allow SQL Server address space over 4GB - you must specify a max server
memory size (the pages in memory committed by SQL Server won't be
released until the SQL Server service shuts down).
SQL 2005 EE on Win 2003 takes advantage of changes to the underlying OS
and allows dynamic memory management with AWE, but with SQL 2000 (no
matter what OS) you're stuck with fixed AWE memory unfortunately.
So figure out how much memory is needed for the non-SQL stuff on the box
and allocate the rest to SQL Server. As Andrew suggested, a dedicated
SQL server with 8GB of physical RAM probably doesn't need more than
about 1GB for the OS et al. (monitoring agents, etc.), so you should be
OK allocating about 7GB to SQL Server.
*mike hodgson*
http://sqlnerd.blogspot.com
Robert R via droptable.com wrote:
>I have inherited a SQL 2K server that is having some severe performance
>problems. The server has 8Gb memory with AWE enabled and 3GB/PAE setting in
>the boot.ini. No other applications are running on the server. The availabl
e
>memory is showing 1.2GB.
>The previous administrator has the server set with a fixed memory setting o
f
>8120MB.
>My question, is there a benefit that I am unaware of in having the server s
et
>with fixed memory versus dynamic? Is not dynamic memory a better
>configuration when SQL Server is not competing with other applications?
>
>|||Hello Robert
I administer myself a Windows 2000 / SQL 2000 server with 8 GB of memory
( this erver has superior performance )
if you use PAE icw AWE enabled you should first calculate how manny memory
your server needs in a typicall load ( i use 512 MB for my windows 2000
advanced server )
now fix the rest of the memory size in the AWE setting for SQL use
if you start your task manager you should see that the 7,5 gigabytes is
constantly claimed by SQL server
> My question, is there a benefit that I am unaware of in having the server
> set
> with fixed memory versus dynamic? Is not dynamic memory a better
> configuration when SQL Server is not competing with other applications?
In my opinion it is the other way around ,,,, however with PAE and AWE this
is simply not an option
regards
Michel Posseth [MCP]
"Robert R via droptable.com" <u3288@.uwe> wrote in message
news:5ad0d04f5b9b2@.uwe...
>I have inherited a SQL 2K server that is having some severe performance
> problems. The server has 8Gb memory with AWE enabled and 3GB/PAE setting
> in
> the boot.ini. No other applications are running on the server. The
> available
> memory is showing 1.2GB.
> The previous administrator has the server set with a fixed memory setting
> of
> 8120MB.
> My question, is there a benefit that I am unaware of in having the server
> set
> with fixed memory versus dynamic? Is not dynamic memory a better
> configuration when SQL Server is not competing with other applications?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200601/1|||Hello, Robert!
To decide if you will use fixe the memory to SQL Server, you need to know
your environment. The SQL Server works better if it has memory enough to
process the information and transaction. If your SQL Server has any other
application installed you should not set fixed memory, because other process
must require more memory and the server will not have memory enough to
supress this request.
So, you should analyze it before and after that define if you will use fixed
memory or not.
I have problems too with hyper-memory allocation to SQL Server, or better, I
had define more memory that it needed.
I recomend for you, that you set a min memory and a maximum memory before,
monitor its performance and if you have many memory free, cache hit ratio
below 90% and other important counters to determine if SQL Server is using
memory efficiently.
If you need a help, please send me a message. Ok?
Take care, when you define fixed memory. You should monitor in System Monito
r
(W2k ou W2k3) how many Pages/sec your server is paging because it. Ok?
Bye
Juliano Horta
Robert R wrote:
>I have inherited a SQL 2K server that is having some severe performance
>problems. The server has 8Gb memory with AWE enabled and 3GB/PAE setting in
>the boot.ini. No other applications are running on the server. The availabl
e
>memory is showing 1.2GB.
>The previous administrator has the server set with a fixed memory setting o
f
>8120MB.
>My question, is there a benefit that I am unaware of in having the server s
et
>with fixed memory versus dynamic? Is not dynamic memory a better
>configuration when SQL Server is not competing with other applications?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200601/1|||I thought that when I stated the memory was not dynamic it made this
irrelevant. It's rare that the Fixed memory option is used and I would
uncheck this but the memory in AWE is always static in SQL2000.
Andrew J. Kelly SQL MVP
"Robert R via droptable.com" <u3288@.uwe> wrote in message
news:5ad1454f51cda@.uwe...
> OK. Thanks, for making me aware of that, but what about the question(s) I
> posed:
> "Is there a benefit that I am unaware of in having the server set
> with fixed memory versus dynamic? Is not dynamic memory a better
> configuration when SQL Server is not competing with other applications?"
>
> Andrew J. Kelly wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200601/1|||I think you missed the bit where Robert said he has 8GB in his box and
therefore must use AWE (and therefore, by implication, cannot use
dynamic memory management).
*mike hodgson*
http://sqlnerd.blogspot.com
Juliano H via droptable.com wrote:
>Hello, Robert!
>To decide if you will use fixe the memory to SQL Server, you need to know
>your environment. The SQL Server works better if it has memory enough to
>process the information and transaction. If your SQL Server has any other
>application installed you should not set fixed memory, because other proces
s
>must require more memory and the server will not have memory enough to
>supress this request.
>So, you should analyze it before and after that define if you will use fixe
d
>memory or not.
>I have problems too with hyper-memory allocation to SQL Server, or better,
I
>had define more memory that it needed.
>I recomend for you, that you set a min memory and a maximum memory before,
>monitor its performance and if you have many memory free, cache hit ratio
>below 90% and other important counters to determine if SQL Server is using
>memory efficiently.
>If you need a help, please send me a message. Ok?
>Take care, when you define fixed memory. You should monitor in System Monit
or
>(W2k ou W2k3) how many Pages/sec your server is paging because it. Ok?
>Bye
>Juliano Horta
>
>Robert R wrote:
>
>
>|||Hello! Mike.
Why, He cannot use dynamic memory management? What's the relationship betwee
n
AWE enabled and memory options?
Thanks
Mike Hodgson wrote:[vbcol=seagreen]
>I think you missed the bit where Robert said he has 8GB in his box and
>therefore must use AWE (and therefore, by implication, cannot use
>dynamic memory management).
>--
>*mike hodgson*
>http://sqlnerd.blogspot.com
>
>[quoted text clipped - 36 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200601/1|||When using AWE memory SQL Server locks pages in memory. It will not
swap pages out to disk if another application makes a request for memory
and there is not enough available at the time (like it normally does
when it is using dynamic memory management). Using min & max server
memory is fine but once SQL marks the memory page as committed, it stays
that way until SQL Server shuts down (ie. it will not be released).
Dynamic memory management (at least in my book) also involves releasing
memory, when needed, to maintain a minimum free memory threshold (off
the top of my head I think the default threshold is 10MB).
Perhaps I misunderstood your post but I thought you were advocating
setting a max & min level and letting SQL Server allocate and release
memory as needed.
*mike hodgson*
http://sqlnerd.blogspot.com
Juliano H via droptable.com wrote:
>Hello! Mike.
>Why, He cannot use dynamic memory management? What's the relationship betwe
en
>AWE enabled and memory options?
>
>Thanks
>Mike Hodgson wrote:
>
>
>sql
Dynamic memory setting
I have inherited a SQL 2K server that is having some severe performance
problems. The server has 8Gb memory with AWE enabled and 3GB/PAE setting in
the boot.ini. No other applications are running on the server. The available
memory is showing 1.2GB.
The previous administrator has the server set with a fixed memory setting of
8120MB.
My question, is there a benefit that I am unaware of in having the server set
with fixed memory versus dynamic? Is not dynamic memory a better
configuration when SQL Server is not competing with other applications?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200601/1
If you are using AWE and you have 8GB you should set the MAX memory to no
more than 7GB since AWE is not dynamic in SQL2000.
Andrew J. Kelly SQL MVP
"Robert R via droptable.com" <u3288@.uwe> wrote in message
news:5ad0d04f5b9b2@.uwe...
>I have inherited a SQL 2K server that is having some severe performance
> problems. The server has 8Gb memory with AWE enabled and 3GB/PAE setting
> in
> the boot.ini. No other applications are running on the server. The
> available
> memory is showing 1.2GB.
> The previous administrator has the server set with a fixed memory setting
> of
> 8120MB.
> My question, is there a benefit that I am unaware of in having the server
> set
> with fixed memory versus dynamic? Is not dynamic memory a better
> configuration when SQL Server is not competing with other applications?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200601/1
|||OK. Thanks, for making me aware of that, but what about the question(s) I
posed:
"Is there a benefit that I am unaware of in having the server set
with fixed memory versus dynamic? Is not dynamic memory a better
configuration when SQL Server is not competing with other applications?"
Andrew J. Kelly wrote:[vbcol=seagreen]
>If you are using AWE and you have 8GB you should set the MAX memory to no
>more than 7GB since AWE is not dynamic in SQL2000.
>[quoted text clipped - 11 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200601/1
|||Ordinarily, allowing SQL Server to dynamically manage the memory
available to it is the better (more efficient) option, however your
options are more limited with AWE memory. SQL Server 2000 does not swap
pages out of memory when using AWE memory (it locks pages in memory), so
you cannot use the dynamic memory management in SQL Server 2000 when you
allow SQL Server address space over 4GB - you must specify a max server
memory size (the pages in memory committed by SQL Server won't be
released until the SQL Server service shuts down).
SQL 2005 EE on Win 2003 takes advantage of changes to the underlying OS
and allows dynamic memory management with AWE, but with SQL 2000 (no
matter what OS) you're stuck with fixed AWE memory unfortunately.
So figure out how much memory is needed for the non-SQL stuff on the box
and allocate the rest to SQL Server. As Andrew suggested, a dedicated
SQL server with 8GB of physical RAM probably doesn't need more than
about 1GB for the OS et al. (monitoring agents, etc.), so you should be
OK allocating about 7GB to SQL Server.
*mike hodgson*
http://sqlnerd.blogspot.com
Robert R via droptable.com wrote:
>I have inherited a SQL 2K server that is having some severe performance
>problems. The server has 8Gb memory with AWE enabled and 3GB/PAE setting in
>the boot.ini. No other applications are running on the server. The available
>memory is showing 1.2GB.
>The previous administrator has the server set with a fixed memory setting of
>8120MB.
>My question, is there a benefit that I am unaware of in having the server set
>with fixed memory versus dynamic? Is not dynamic memory a better
>configuration when SQL Server is not competing with other applications?
>
>
|||Hello Robert
I administer myself a windows 2000 / SQL 2000 server with 8 GB of memory
( this erver has superior performance )
if you use PAE icw AWE enabled you should first calculate how manny memory
your server needs in a typicall load ( i use 512 MB for my windows 2000
advanced server )
now fix the rest of the memory size in the AWE setting for SQL use
if you start your task manager you should see that the 7,5 gigabytes is
constantly claimed by SQL server
> My question, is there a benefit that I am unaware of in having the server
> set
> with fixed memory versus dynamic? Is not dynamic memory a better
> configuration when SQL Server is not competing with other applications?
In my opinion it is the other way around ,,,, however with PAE and AWE this
is simply not an option
regards
Michel Posseth [MCP]
"Robert R via droptable.com" <u3288@.uwe> wrote in message
news:5ad0d04f5b9b2@.uwe...
>I have inherited a SQL 2K server that is having some severe performance
> problems. The server has 8Gb memory with AWE enabled and 3GB/PAE setting
> in
> the boot.ini. No other applications are running on the server. The
> available
> memory is showing 1.2GB.
> The previous administrator has the server set with a fixed memory setting
> of
> 8120MB.
> My question, is there a benefit that I am unaware of in having the server
> set
> with fixed memory versus dynamic? Is not dynamic memory a better
> configuration when SQL Server is not competing with other applications?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200601/1
|||Hello, Robert!
To decide if you will use fixe the memory to SQL Server, you need to know
your environment. The SQL Server works better if it has memory enough to
process the information and transaction. If your SQL Server has any other
application installed you should not set fixed memory, because other process
must require more memory and the server will not have memory enough to
supress this request.
So, you should analyze it before and after that define if you will use fixed
memory or not.
I have problems too with hyper-memory allocation to SQL Server, or better, I
had define more memory that it needed.
I recomend for you, that you set a min memory and a maximum memory before,
monitor its performance and if you have many memory free, cache hit ratio
below 90% and other important counters to determine if SQL Server is using
memory efficiently.
If you need a help, please send me a message. Ok?
Take care, when you define fixed memory. You should monitor in System Monitor
(W2k ou W2k3) how many Pages/sec your server is paging because it. Ok?
Bye
Juliano Horta
Robert R wrote:
>I have inherited a SQL 2K server that is having some severe performance
>problems. The server has 8Gb memory with AWE enabled and 3GB/PAE setting in
>the boot.ini. No other applications are running on the server. The available
>memory is showing 1.2GB.
>The previous administrator has the server set with a fixed memory setting of
>8120MB.
>My question, is there a benefit that I am unaware of in having the server set
>with fixed memory versus dynamic? Is not dynamic memory a better
>configuration when SQL Server is not competing with other applications?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200601/1
|||I thought that when I stated the memory was not dynamic it made this
irrelevant. It's rare that the Fixed memory option is used and I would
uncheck this but the memory in AWE is always static in SQL2000.
Andrew J. Kelly SQL MVP
"Robert R via droptable.com" <u3288@.uwe> wrote in message
news:5ad1454f51cda@.uwe...
> OK. Thanks, for making me aware of that, but what about the question(s) I
> posed:
> "Is there a benefit that I am unaware of in having the server set
> with fixed memory versus dynamic? Is not dynamic memory a better
> configuration when SQL Server is not competing with other applications?"
>
> Andrew J. Kelly wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200601/1
|||I think you missed the bit where Robert said he has 8GB in his box and
therefore must use AWE (and therefore, by implication, cannot use
dynamic memory management).
*mike hodgson*
http://sqlnerd.blogspot.com
Juliano H via droptable.com wrote:
>Hello, Robert!
>To decide if you will use fixe the memory to SQL Server, you need to know
>your environment. The SQL Server works better if it has memory enough to
>process the information and transaction. If your SQL Server has any other
>application installed you should not set fixed memory, because other process
>must require more memory and the server will not have memory enough to
>supress this request.
>So, you should analyze it before and after that define if you will use fixed
>memory or not.
>I have problems too with hyper-memory allocation to SQL Server, or better, I
>had define more memory that it needed.
>I recomend for you, that you set a min memory and a maximum memory before,
>monitor its performance and if you have many memory free, cache hit ratio
>below 90% and other important counters to determine if SQL Server is using
>memory efficiently.
>If you need a help, please send me a message. Ok?
>Take care, when you define fixed memory. You should monitor in System Monitor
>(W2k ou W2k3) how many Pages/sec your server is paging because it. Ok?
>Bye
>Juliano Horta
>
>Robert R wrote:
>
>
>
|||Hello! Mike.
Why, He cannot use dynamic memory management? What's the relationship between
AWE enabled and memory options?
Thanks
Mike Hodgson wrote:[vbcol=seagreen]
>I think you missed the bit where Robert said he has 8GB in his box and
>therefore must use AWE (and therefore, by implication, cannot use
>dynamic memory management).
>--
>*mike hodgson*
>http://sqlnerd.blogspot.com
>[quoted text clipped - 36 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200601/1
|||When using AWE memory SQL Server locks pages in memory. It will not
swap pages out to disk if another application makes a request for memory
and there is not enough available at the time (like it normally does
when it is using dynamic memory management). Using min & max server
memory is fine but once SQL marks the memory page as committed, it stays
that way until SQL Server shuts down (ie. it will not be released).
Dynamic memory management (at least in my book) also involves releasing
memory, when needed, to maintain a minimum free memory threshold (off
the top of my head I think the default threshold is 10MB).
Perhaps I misunderstood your post but I thought you were advocating
setting a max & min level and letting SQL Server allocate and release
memory as needed.
*mike hodgson*
http://sqlnerd.blogspot.com
Juliano H via droptable.com wrote:
>Hello! Mike.
>Why, He cannot use dynamic memory management? What's the relationship between
>AWE enabled and memory options?
>
>Thanks
>Mike Hodgson wrote:
>
>
>
problems. The server has 8Gb memory with AWE enabled and 3GB/PAE setting in
the boot.ini. No other applications are running on the server. The available
memory is showing 1.2GB.
The previous administrator has the server set with a fixed memory setting of
8120MB.
My question, is there a benefit that I am unaware of in having the server set
with fixed memory versus dynamic? Is not dynamic memory a better
configuration when SQL Server is not competing with other applications?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200601/1
If you are using AWE and you have 8GB you should set the MAX memory to no
more than 7GB since AWE is not dynamic in SQL2000.
Andrew J. Kelly SQL MVP
"Robert R via droptable.com" <u3288@.uwe> wrote in message
news:5ad0d04f5b9b2@.uwe...
>I have inherited a SQL 2K server that is having some severe performance
> problems. The server has 8Gb memory with AWE enabled and 3GB/PAE setting
> in
> the boot.ini. No other applications are running on the server. The
> available
> memory is showing 1.2GB.
> The previous administrator has the server set with a fixed memory setting
> of
> 8120MB.
> My question, is there a benefit that I am unaware of in having the server
> set
> with fixed memory versus dynamic? Is not dynamic memory a better
> configuration when SQL Server is not competing with other applications?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200601/1
|||OK. Thanks, for making me aware of that, but what about the question(s) I
posed:
"Is there a benefit that I am unaware of in having the server set
with fixed memory versus dynamic? Is not dynamic memory a better
configuration when SQL Server is not competing with other applications?"
Andrew J. Kelly wrote:[vbcol=seagreen]
>If you are using AWE and you have 8GB you should set the MAX memory to no
>more than 7GB since AWE is not dynamic in SQL2000.
>[quoted text clipped - 11 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200601/1
|||Ordinarily, allowing SQL Server to dynamically manage the memory
available to it is the better (more efficient) option, however your
options are more limited with AWE memory. SQL Server 2000 does not swap
pages out of memory when using AWE memory (it locks pages in memory), so
you cannot use the dynamic memory management in SQL Server 2000 when you
allow SQL Server address space over 4GB - you must specify a max server
memory size (the pages in memory committed by SQL Server won't be
released until the SQL Server service shuts down).
SQL 2005 EE on Win 2003 takes advantage of changes to the underlying OS
and allows dynamic memory management with AWE, but with SQL 2000 (no
matter what OS) you're stuck with fixed AWE memory unfortunately.
So figure out how much memory is needed for the non-SQL stuff on the box
and allocate the rest to SQL Server. As Andrew suggested, a dedicated
SQL server with 8GB of physical RAM probably doesn't need more than
about 1GB for the OS et al. (monitoring agents, etc.), so you should be
OK allocating about 7GB to SQL Server.
*mike hodgson*
http://sqlnerd.blogspot.com
Robert R via droptable.com wrote:
>I have inherited a SQL 2K server that is having some severe performance
>problems. The server has 8Gb memory with AWE enabled and 3GB/PAE setting in
>the boot.ini. No other applications are running on the server. The available
>memory is showing 1.2GB.
>The previous administrator has the server set with a fixed memory setting of
>8120MB.
>My question, is there a benefit that I am unaware of in having the server set
>with fixed memory versus dynamic? Is not dynamic memory a better
>configuration when SQL Server is not competing with other applications?
>
>
|||Hello Robert
I administer myself a windows 2000 / SQL 2000 server with 8 GB of memory
( this erver has superior performance )
if you use PAE icw AWE enabled you should first calculate how manny memory
your server needs in a typicall load ( i use 512 MB for my windows 2000
advanced server )
now fix the rest of the memory size in the AWE setting for SQL use
if you start your task manager you should see that the 7,5 gigabytes is
constantly claimed by SQL server
> My question, is there a benefit that I am unaware of in having the server
> set
> with fixed memory versus dynamic? Is not dynamic memory a better
> configuration when SQL Server is not competing with other applications?
In my opinion it is the other way around ,,,, however with PAE and AWE this
is simply not an option
regards
Michel Posseth [MCP]
"Robert R via droptable.com" <u3288@.uwe> wrote in message
news:5ad0d04f5b9b2@.uwe...
>I have inherited a SQL 2K server that is having some severe performance
> problems. The server has 8Gb memory with AWE enabled and 3GB/PAE setting
> in
> the boot.ini. No other applications are running on the server. The
> available
> memory is showing 1.2GB.
> The previous administrator has the server set with a fixed memory setting
> of
> 8120MB.
> My question, is there a benefit that I am unaware of in having the server
> set
> with fixed memory versus dynamic? Is not dynamic memory a better
> configuration when SQL Server is not competing with other applications?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200601/1
|||Hello, Robert!
To decide if you will use fixe the memory to SQL Server, you need to know
your environment. The SQL Server works better if it has memory enough to
process the information and transaction. If your SQL Server has any other
application installed you should not set fixed memory, because other process
must require more memory and the server will not have memory enough to
supress this request.
So, you should analyze it before and after that define if you will use fixed
memory or not.
I have problems too with hyper-memory allocation to SQL Server, or better, I
had define more memory that it needed.
I recomend for you, that you set a min memory and a maximum memory before,
monitor its performance and if you have many memory free, cache hit ratio
below 90% and other important counters to determine if SQL Server is using
memory efficiently.
If you need a help, please send me a message. Ok?
Take care, when you define fixed memory. You should monitor in System Monitor
(W2k ou W2k3) how many Pages/sec your server is paging because it. Ok?
Bye
Juliano Horta
Robert R wrote:
>I have inherited a SQL 2K server that is having some severe performance
>problems. The server has 8Gb memory with AWE enabled and 3GB/PAE setting in
>the boot.ini. No other applications are running on the server. The available
>memory is showing 1.2GB.
>The previous administrator has the server set with a fixed memory setting of
>8120MB.
>My question, is there a benefit that I am unaware of in having the server set
>with fixed memory versus dynamic? Is not dynamic memory a better
>configuration when SQL Server is not competing with other applications?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200601/1
|||I thought that when I stated the memory was not dynamic it made this
irrelevant. It's rare that the Fixed memory option is used and I would
uncheck this but the memory in AWE is always static in SQL2000.
Andrew J. Kelly SQL MVP
"Robert R via droptable.com" <u3288@.uwe> wrote in message
news:5ad1454f51cda@.uwe...
> OK. Thanks, for making me aware of that, but what about the question(s) I
> posed:
> "Is there a benefit that I am unaware of in having the server set
> with fixed memory versus dynamic? Is not dynamic memory a better
> configuration when SQL Server is not competing with other applications?"
>
> Andrew J. Kelly wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200601/1
|||I think you missed the bit where Robert said he has 8GB in his box and
therefore must use AWE (and therefore, by implication, cannot use
dynamic memory management).
*mike hodgson*
http://sqlnerd.blogspot.com
Juliano H via droptable.com wrote:
>Hello, Robert!
>To decide if you will use fixe the memory to SQL Server, you need to know
>your environment. The SQL Server works better if it has memory enough to
>process the information and transaction. If your SQL Server has any other
>application installed you should not set fixed memory, because other process
>must require more memory and the server will not have memory enough to
>supress this request.
>So, you should analyze it before and after that define if you will use fixed
>memory or not.
>I have problems too with hyper-memory allocation to SQL Server, or better, I
>had define more memory that it needed.
>I recomend for you, that you set a min memory and a maximum memory before,
>monitor its performance and if you have many memory free, cache hit ratio
>below 90% and other important counters to determine if SQL Server is using
>memory efficiently.
>If you need a help, please send me a message. Ok?
>Take care, when you define fixed memory. You should monitor in System Monitor
>(W2k ou W2k3) how many Pages/sec your server is paging because it. Ok?
>Bye
>Juliano Horta
>
>Robert R wrote:
>
>
>
|||Hello! Mike.
Why, He cannot use dynamic memory management? What's the relationship between
AWE enabled and memory options?
Thanks
Mike Hodgson wrote:[vbcol=seagreen]
>I think you missed the bit where Robert said he has 8GB in his box and
>therefore must use AWE (and therefore, by implication, cannot use
>dynamic memory management).
>--
>*mike hodgson*
>http://sqlnerd.blogspot.com
>[quoted text clipped - 36 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200601/1
|||When using AWE memory SQL Server locks pages in memory. It will not
swap pages out to disk if another application makes a request for memory
and there is not enough available at the time (like it normally does
when it is using dynamic memory management). Using min & max server
memory is fine but once SQL marks the memory page as committed, it stays
that way until SQL Server shuts down (ie. it will not be released).
Dynamic memory management (at least in my book) also involves releasing
memory, when needed, to maintain a minimum free memory threshold (off
the top of my head I think the default threshold is 10MB).
Perhaps I misunderstood your post but I thought you were advocating
setting a max & min level and letting SQL Server allocate and release
memory as needed.
*mike hodgson*
http://sqlnerd.blogspot.com
Juliano H via droptable.com wrote:
>Hello! Mike.
>Why, He cannot use dynamic memory management? What's the relationship between
>AWE enabled and memory options?
>
>Thanks
>Mike Hodgson wrote:
>
>
>
Dynamic memory setting
I have inherited a SQL 2K server that is having some severe performance
problems. The server has 8Gb memory with AWE enabled and 3GB/PAE setting in
the boot.ini. No other applications are running on the server. The available
memory is showing 1.2GB.
The previous administrator has the server set with a fixed memory setting of
8120MB.
My question, is there a benefit that I am unaware of in having the server set
with fixed memory versus dynamic? Is not dynamic memory a better
configuration when SQL Server is not competing with other applications?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200601/1If you are using AWE and you have 8GB you should set the MAX memory to no
more than 7GB since AWE is not dynamic in SQL2000.
--
Andrew J. Kelly SQL MVP
"Robert R via SQLMonster.com" <u3288@.uwe> wrote in message
news:5ad0d04f5b9b2@.uwe...
>I have inherited a SQL 2K server that is having some severe performance
> problems. The server has 8Gb memory with AWE enabled and 3GB/PAE setting
> in
> the boot.ini. No other applications are running on the server. The
> available
> memory is showing 1.2GB.
> The previous administrator has the server set with a fixed memory setting
> of
> 8120MB.
> My question, is there a benefit that I am unaware of in having the server
> set
> with fixed memory versus dynamic? Is not dynamic memory a better
> configuration when SQL Server is not competing with other applications?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200601/1|||OK. Thanks, for making me aware of that, but what about the question(s) I
posed:
"Is there a benefit that I am unaware of in having the server set
with fixed memory versus dynamic? Is not dynamic memory a better
configuration when SQL Server is not competing with other applications?"
Andrew J. Kelly wrote:
>If you are using AWE and you have 8GB you should set the MAX memory to no
>more than 7GB since AWE is not dynamic in SQL2000.
>>I have inherited a SQL 2K server that is having some severe performance
>> problems. The server has 8Gb memory with AWE enabled and 3GB/PAE setting
>[quoted text clipped - 11 lines]
>> with fixed memory versus dynamic? Is not dynamic memory a better
>> configuration when SQL Server is not competing with other applications?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200601/1|||This is a multi-part message in MIME format.
--010904070403070902020900
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Ordinarily, allowing SQL Server to dynamically manage the memory
available to it is the better (more efficient) option, however your
options are more limited with AWE memory. SQL Server 2000 does not swap
pages out of memory when using AWE memory (it locks pages in memory), so
you cannot use the dynamic memory management in SQL Server 2000 when you
allow SQL Server address space over 4GB - you must specify a max server
memory size (the pages in memory committed by SQL Server won't be
released until the SQL Server service shuts down).
SQL 2005 EE on Win 2003 takes advantage of changes to the underlying OS
and allows dynamic memory management with AWE, but with SQL 2000 (no
matter what OS) you're stuck with fixed AWE memory unfortunately.
So figure out how much memory is needed for the non-SQL stuff on the box
and allocate the rest to SQL Server. As Andrew suggested, a dedicated
SQL server with 8GB of physical RAM probably doesn't need more than
about 1GB for the OS et al. (monitoring agents, etc.), so you should be
OK allocating about 7GB to SQL Server.
--
*mike hodgson*
http://sqlnerd.blogspot.com
Robert R via SQLMonster.com wrote:
>I have inherited a SQL 2K server that is having some severe performance
>problems. The server has 8Gb memory with AWE enabled and 3GB/PAE setting in
>the boot.ini. No other applications are running on the server. The available
>memory is showing 1.2GB.
>The previous administrator has the server set with a fixed memory setting of
>8120MB.
>My question, is there a benefit that I am unaware of in having the server set
>with fixed memory versus dynamic? Is not dynamic memory a better
>configuration when SQL Server is not competing with other applications?
>
>
--010904070403070902020900
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>Ordinarily, allowing SQL Server to </tt><tt>dynamically </tt><tt>manage
the memory available to it is the better (more efficient) option,
however your options are more limited with AWE memory. SQL Server 2000
does not swap pages out of memory when using AWE memory (it locks pages
in memory), so you cannot use the dynamic memory management in SQL
Server 2000 when you allow SQL Server address space over 4GB - you must
specify a max server memory size (the pages in memory committed by SQL
Server won't be released until the SQL Server service shuts down).<br>
<br>
SQL 2005 EE on Win 2003 takes advantage of changes to the underlying OS
and allows dynamic memory management with AWE, but with SQL 2000 (no
matter what OS) you're stuck with fixed AWE memory unfortunately.<br>
<br>
So figure out how much memory is needed for the non-SQL stuff on the
box and allocate the rest to SQL Server. As Andrew suggested, a
dedicated SQL server with 8GB of physical RAM probably doesn't need
more than about 1GB for the OS et al. (monitoring agents, etc.), so you
should be OK allocating about 7GB to SQL Server.<br>
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2"><a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
Robert R via SQLMonster.com wrote:
<blockquote cite="mid5ad0d04f5b9b2@.uwe" type="cite">
<pre wrap="">I have inherited a SQL 2K server that is having some severe performance
problems. The server has 8Gb memory with AWE enabled and 3GB/PAE setting in
the boot.ini. No other applications are running on the server. The available
memory is showing 1.2GB.
The previous administrator has the server set with a fixed memory setting of
8120MB.
My question, is there a benefit that I am unaware of in having the server set
with fixed memory versus dynamic? Is not dynamic memory a better
configuration when SQL Server is not competing with other applications?
</pre>
</blockquote>
</body>
</html>
--010904070403070902020900--|||Hello Robert
I administer myself a windows 2000 / SQL 2000 server with 8 GB of memory
( this erver has superior performance )
if you use PAE icw AWE enabled you should first calculate how manny memory
your server needs in a typicall load ( i use 512 MB for my windows 2000
advanced server )
now fix the rest of the memory size in the AWE setting for SQL use
if you start your task manager you should see that the 7,5 gigabytes is
constantly claimed by SQL server
> My question, is there a benefit that I am unaware of in having the server
> set
> with fixed memory versus dynamic? Is not dynamic memory a better
> configuration when SQL Server is not competing with other applications?
In my opinion it is the other way around ,,,, however with PAE and AWE this
is simply not an option
regards
Michel Posseth [MCP]
"Robert R via SQLMonster.com" <u3288@.uwe> wrote in message
news:5ad0d04f5b9b2@.uwe...
>I have inherited a SQL 2K server that is having some severe performance
> problems. The server has 8Gb memory with AWE enabled and 3GB/PAE setting
> in
> the boot.ini. No other applications are running on the server. The
> available
> memory is showing 1.2GB.
> The previous administrator has the server set with a fixed memory setting
> of
> 8120MB.
> My question, is there a benefit that I am unaware of in having the server
> set
> with fixed memory versus dynamic? Is not dynamic memory a better
> configuration when SQL Server is not competing with other applications?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200601/1|||Hello, Robert!
To decide if you will use fixe the memory to SQL Server, you need to know
your environment. The SQL Server works better if it has memory enough to
process the information and transaction. If your SQL Server has any other
application installed you should not set fixed memory, because other process
must require more memory and the server will not have memory enough to
supress this request.
So, you should analyze it before and after that define if you will use fixed
memory or not.
I have problems too with hyper-memory allocation to SQL Server, or better, I
had define more memory that it needed.
I recomend for you, that you set a min memory and a maximum memory before,
monitor its performance and if you have many memory free, cache hit ratio
below 90% and other important counters to determine if SQL Server is using
memory efficiently.
If you need a help, please send me a message. Ok?
Take care, when you define fixed memory. You should monitor in System Monitor
(W2k ou W2k3) how many Pages/sec your server is paging because it. Ok?
Bye
Juliano Horta
Robert R wrote:
>I have inherited a SQL 2K server that is having some severe performance
>problems. The server has 8Gb memory with AWE enabled and 3GB/PAE setting in
>the boot.ini. No other applications are running on the server. The available
>memory is showing 1.2GB.
>The previous administrator has the server set with a fixed memory setting of
>8120MB.
>My question, is there a benefit that I am unaware of in having the server set
>with fixed memory versus dynamic? Is not dynamic memory a better
>configuration when SQL Server is not competing with other applications?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200601/1|||I thought that when I stated the memory was not dynamic it made this
irrelevant. It's rare that the Fixed memory option is used and I would
uncheck this but the memory in AWE is always static in SQL2000.
--
Andrew J. Kelly SQL MVP
"Robert R via SQLMonster.com" <u3288@.uwe> wrote in message
news:5ad1454f51cda@.uwe...
> OK. Thanks, for making me aware of that, but what about the question(s) I
> posed:
> "Is there a benefit that I am unaware of in having the server set
> with fixed memory versus dynamic? Is not dynamic memory a better
> configuration when SQL Server is not competing with other applications?"
>
> Andrew J. Kelly wrote:
>>If you are using AWE and you have 8GB you should set the MAX memory to no
>>more than 7GB since AWE is not dynamic in SQL2000.
>>I have inherited a SQL 2K server that is having some severe performance
>> problems. The server has 8Gb memory with AWE enabled and 3GB/PAE setting
>>[quoted text clipped - 11 lines]
>> with fixed memory versus dynamic? Is not dynamic memory a better
>> configuration when SQL Server is not competing with other applications?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200601/1|||This is a multi-part message in MIME format.
--020809020907060301070206
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
I think you missed the bit where Robert said he has 8GB in his box and
therefore must use AWE (and therefore, by implication, cannot use
dynamic memory management).
--
*mike hodgson*
http://sqlnerd.blogspot.com
Juliano H via SQLMonster.com wrote:
>Hello, Robert!
>To decide if you will use fixe the memory to SQL Server, you need to know
>your environment. The SQL Server works better if it has memory enough to
>process the information and transaction. If your SQL Server has any other
>application installed you should not set fixed memory, because other process
>must require more memory and the server will not have memory enough to
>supress this request.
>So, you should analyze it before and after that define if you will use fixed
>memory or not.
>I have problems too with hyper-memory allocation to SQL Server, or better, I
>had define more memory that it needed.
>I recomend for you, that you set a min memory and a maximum memory before,
>monitor its performance and if you have many memory free, cache hit ratio
>below 90% and other important counters to determine if SQL Server is using
>memory efficiently.
>If you need a help, please send me a message. Ok?
>Take care, when you define fixed memory. You should monitor in System Monitor
>(W2k ou W2k3) how many Pages/sec your server is paging because it. Ok?
>Bye
>Juliano Horta
>
>Robert R wrote:
>
>>I have inherited a SQL 2K server that is having some severe performance
>>problems. The server has 8Gb memory with AWE enabled and 3GB/PAE setting in
>>the boot.ini. No other applications are running on the server. The available
>>memory is showing 1.2GB.
>>The previous administrator has the server set with a fixed memory setting of
>>8120MB.
>>My question, is there a benefit that I am unaware of in having the server set
>>with fixed memory versus dynamic? Is not dynamic memory a better
>>configuration when SQL Server is not competing with other applications?
>>
>
>
--020809020907060301070206
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>I think you missed the bit where Robert said he has 8GB in his box
and therefore must use AWE (and therefore, by implication, cannot use
dynamic memory management).</tt><br>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2"><a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
Juliano H via SQLMonster.com wrote:
<blockquote cite="mid5ad45d0f6773c@.uwe" type="cite">
<pre wrap="">Hello, Robert!
To decide if you will use fixe the memory to SQL Server, you need to know
your environment. The SQL Server works better if it has memory enough to
process the information and transaction. If your SQL Server has any other
application installed you should not set fixed memory, because other process
must require more memory and the server will not have memory enough to
supress this request.
So, you should analyze it before and after that define if you will use fixed
memory or not.
I have problems too with hyper-memory allocation to SQL Server, or better, I
had define more memory that it needed.
I recomend for you, that you set a min memory and a maximum memory before,
monitor its performance and if you have many memory free, cache hit ratio
below 90% and other important counters to determine if SQL Server is using
memory efficiently.
If you need a help, please send me a message. Ok?
Take care, when you define fixed memory. You should monitor in System Monitor
(W2k ou W2k3) how many Pages/sec your server is paging because it. Ok?
Bye
Juliano Horta
Robert R wrote:
</pre>
<blockquote type="cite">
<pre wrap="">I have inherited a SQL 2K server that is having some severe performance
problems. The server has 8Gb memory with AWE enabled and 3GB/PAE setting in
the boot.ini. No other applications are running on the server. The available
memory is showing 1.2GB.
The previous administrator has the server set with a fixed memory setting of
8120MB.
My question, is there a benefit that I am unaware of in having the server set
with fixed memory versus dynamic? Is not dynamic memory a better
configuration when SQL Server is not competing with other applications?
</pre>
</blockquote>
<pre wrap=""><!-->
</pre>
</blockquote>
</body>
</html>
--020809020907060301070206--|||Hello! Mike.
Why, He cannot use dynamic memory management? What's the relationship between
AWE enabled and memory options?
Thanks
Mike Hodgson wrote:
>I think you missed the bit where Robert said he has 8GB in his box and
>therefore must use AWE (and therefore, by implication, cannot use
>dynamic memory management).
>--
>*mike hodgson*
>http://sqlnerd.blogspot.com
>>Hello, Robert!
>[quoted text clipped - 36 lines]
>>
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200601/1|||This is a multi-part message in MIME format.
--010103060909000305060202
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
When using AWE memory SQL Server locks pages in memory. It will not
swap pages out to disk if another application makes a request for memory
and there is not enough available at the time (like it normally does
when it is using dynamic memory management). Using min & max server
memory is fine but once SQL marks the memory page as committed, it stays
that way until SQL Server shuts down (ie. it will not be released).
Dynamic memory management (at least in my book) also involves releasing
memory, when needed, to maintain a minimum free memory threshold (off
the top of my head I think the default threshold is 10MB).
Perhaps I misunderstood your post but I thought you were advocating
setting a max & min level and letting SQL Server allocate and release
memory as needed.
--
*mike hodgson*
http://sqlnerd.blogspot.com
Juliano H via SQLMonster.com wrote:
>Hello! Mike.
>Why, He cannot use dynamic memory management? What's the relationship between
>AWE enabled and memory options?
>
>Thanks
>Mike Hodgson wrote:
>
>>I think you missed the bit where Robert said he has 8GB in his box and
>>therefore must use AWE (and therefore, by implication, cannot use
>>dynamic memory management).
>>--
>>*mike hodgson*
>>http://sqlnerd.blogspot.com
>>
>>Hello, Robert!
>>
>>[quoted text clipped - 36 lines]
>>
>>
>
>
--010103060909000305060202
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>When using AWE memory SQL Server locks pages in memory. It will
not swap pages out to disk if another application makes a request for
memory and there is not enough available at the time (like it normally
does when it is using dynamic memory management). Using min & max
server memory is fine but once SQL marks the memory page as committed,
it stays that way until SQL Server shuts down (ie. it will not be
released). Dynamic memory management (at least in my book) also
involves releasing memory, when needed, to maintain a minimum free
memory threshold (off the top of my head I think the default threshold
is 10MB).<br>
<br>
Perhaps I misunderstood your post but I thought you were advocating
setting a max & min level and letting SQL Server allocate and
release memory as needed.<br>
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2"><a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
Juliano H via SQLMonster.com wrote:
<blockquote cite="mid5ae29f55fad3d@.uwe" type="cite">
<pre wrap="">Hello! Mike.
Why, He cannot use dynamic memory management? What's the relationship between
AWE enabled and memory options?
Thanks
Mike Hodgson wrote:
</pre>
<blockquote type="cite">
<pre wrap="">I think you missed the bit where Robert said he has 8GB in his box and
therefore must use AWE (and therefore, by implication, cannot use
dynamic memory management).
--
*mike hodgson*
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a>
</pre>
<blockquote type="cite">
<pre wrap="">Hello, Robert!
</pre>
</blockquote>
<pre wrap="">[quoted text clipped - 36 lines]
</pre>
<blockquote type="cite">
<pre wrap="">
</pre>
</blockquote>
</blockquote>
<pre wrap=""><!-->
</pre>
</blockquote>
</body>
</html>
--010103060909000305060202--
problems. The server has 8Gb memory with AWE enabled and 3GB/PAE setting in
the boot.ini. No other applications are running on the server. The available
memory is showing 1.2GB.
The previous administrator has the server set with a fixed memory setting of
8120MB.
My question, is there a benefit that I am unaware of in having the server set
with fixed memory versus dynamic? Is not dynamic memory a better
configuration when SQL Server is not competing with other applications?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200601/1If you are using AWE and you have 8GB you should set the MAX memory to no
more than 7GB since AWE is not dynamic in SQL2000.
--
Andrew J. Kelly SQL MVP
"Robert R via SQLMonster.com" <u3288@.uwe> wrote in message
news:5ad0d04f5b9b2@.uwe...
>I have inherited a SQL 2K server that is having some severe performance
> problems. The server has 8Gb memory with AWE enabled and 3GB/PAE setting
> in
> the boot.ini. No other applications are running on the server. The
> available
> memory is showing 1.2GB.
> The previous administrator has the server set with a fixed memory setting
> of
> 8120MB.
> My question, is there a benefit that I am unaware of in having the server
> set
> with fixed memory versus dynamic? Is not dynamic memory a better
> configuration when SQL Server is not competing with other applications?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200601/1|||OK. Thanks, for making me aware of that, but what about the question(s) I
posed:
"Is there a benefit that I am unaware of in having the server set
with fixed memory versus dynamic? Is not dynamic memory a better
configuration when SQL Server is not competing with other applications?"
Andrew J. Kelly wrote:
>If you are using AWE and you have 8GB you should set the MAX memory to no
>more than 7GB since AWE is not dynamic in SQL2000.
>>I have inherited a SQL 2K server that is having some severe performance
>> problems. The server has 8Gb memory with AWE enabled and 3GB/PAE setting
>[quoted text clipped - 11 lines]
>> with fixed memory versus dynamic? Is not dynamic memory a better
>> configuration when SQL Server is not competing with other applications?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200601/1|||This is a multi-part message in MIME format.
--010904070403070902020900
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Ordinarily, allowing SQL Server to dynamically manage the memory
available to it is the better (more efficient) option, however your
options are more limited with AWE memory. SQL Server 2000 does not swap
pages out of memory when using AWE memory (it locks pages in memory), so
you cannot use the dynamic memory management in SQL Server 2000 when you
allow SQL Server address space over 4GB - you must specify a max server
memory size (the pages in memory committed by SQL Server won't be
released until the SQL Server service shuts down).
SQL 2005 EE on Win 2003 takes advantage of changes to the underlying OS
and allows dynamic memory management with AWE, but with SQL 2000 (no
matter what OS) you're stuck with fixed AWE memory unfortunately.
So figure out how much memory is needed for the non-SQL stuff on the box
and allocate the rest to SQL Server. As Andrew suggested, a dedicated
SQL server with 8GB of physical RAM probably doesn't need more than
about 1GB for the OS et al. (monitoring agents, etc.), so you should be
OK allocating about 7GB to SQL Server.
--
*mike hodgson*
http://sqlnerd.blogspot.com
Robert R via SQLMonster.com wrote:
>I have inherited a SQL 2K server that is having some severe performance
>problems. The server has 8Gb memory with AWE enabled and 3GB/PAE setting in
>the boot.ini. No other applications are running on the server. The available
>memory is showing 1.2GB.
>The previous administrator has the server set with a fixed memory setting of
>8120MB.
>My question, is there a benefit that I am unaware of in having the server set
>with fixed memory versus dynamic? Is not dynamic memory a better
>configuration when SQL Server is not competing with other applications?
>
>
--010904070403070902020900
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>Ordinarily, allowing SQL Server to </tt><tt>dynamically </tt><tt>manage
the memory available to it is the better (more efficient) option,
however your options are more limited with AWE memory. SQL Server 2000
does not swap pages out of memory when using AWE memory (it locks pages
in memory), so you cannot use the dynamic memory management in SQL
Server 2000 when you allow SQL Server address space over 4GB - you must
specify a max server memory size (the pages in memory committed by SQL
Server won't be released until the SQL Server service shuts down).<br>
<br>
SQL 2005 EE on Win 2003 takes advantage of changes to the underlying OS
and allows dynamic memory management with AWE, but with SQL 2000 (no
matter what OS) you're stuck with fixed AWE memory unfortunately.<br>
<br>
So figure out how much memory is needed for the non-SQL stuff on the
box and allocate the rest to SQL Server. As Andrew suggested, a
dedicated SQL server with 8GB of physical RAM probably doesn't need
more than about 1GB for the OS et al. (monitoring agents, etc.), so you
should be OK allocating about 7GB to SQL Server.<br>
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2"><a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
Robert R via SQLMonster.com wrote:
<blockquote cite="mid5ad0d04f5b9b2@.uwe" type="cite">
<pre wrap="">I have inherited a SQL 2K server that is having some severe performance
problems. The server has 8Gb memory with AWE enabled and 3GB/PAE setting in
the boot.ini. No other applications are running on the server. The available
memory is showing 1.2GB.
The previous administrator has the server set with a fixed memory setting of
8120MB.
My question, is there a benefit that I am unaware of in having the server set
with fixed memory versus dynamic? Is not dynamic memory a better
configuration when SQL Server is not competing with other applications?
</pre>
</blockquote>
</body>
</html>
--010904070403070902020900--|||Hello Robert
I administer myself a windows 2000 / SQL 2000 server with 8 GB of memory
( this erver has superior performance )
if you use PAE icw AWE enabled you should first calculate how manny memory
your server needs in a typicall load ( i use 512 MB for my windows 2000
advanced server )
now fix the rest of the memory size in the AWE setting for SQL use
if you start your task manager you should see that the 7,5 gigabytes is
constantly claimed by SQL server
> My question, is there a benefit that I am unaware of in having the server
> set
> with fixed memory versus dynamic? Is not dynamic memory a better
> configuration when SQL Server is not competing with other applications?
In my opinion it is the other way around ,,,, however with PAE and AWE this
is simply not an option
regards
Michel Posseth [MCP]
"Robert R via SQLMonster.com" <u3288@.uwe> wrote in message
news:5ad0d04f5b9b2@.uwe...
>I have inherited a SQL 2K server that is having some severe performance
> problems. The server has 8Gb memory with AWE enabled and 3GB/PAE setting
> in
> the boot.ini. No other applications are running on the server. The
> available
> memory is showing 1.2GB.
> The previous administrator has the server set with a fixed memory setting
> of
> 8120MB.
> My question, is there a benefit that I am unaware of in having the server
> set
> with fixed memory versus dynamic? Is not dynamic memory a better
> configuration when SQL Server is not competing with other applications?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200601/1|||Hello, Robert!
To decide if you will use fixe the memory to SQL Server, you need to know
your environment. The SQL Server works better if it has memory enough to
process the information and transaction. If your SQL Server has any other
application installed you should not set fixed memory, because other process
must require more memory and the server will not have memory enough to
supress this request.
So, you should analyze it before and after that define if you will use fixed
memory or not.
I have problems too with hyper-memory allocation to SQL Server, or better, I
had define more memory that it needed.
I recomend for you, that you set a min memory and a maximum memory before,
monitor its performance and if you have many memory free, cache hit ratio
below 90% and other important counters to determine if SQL Server is using
memory efficiently.
If you need a help, please send me a message. Ok?
Take care, when you define fixed memory. You should monitor in System Monitor
(W2k ou W2k3) how many Pages/sec your server is paging because it. Ok?
Bye
Juliano Horta
Robert R wrote:
>I have inherited a SQL 2K server that is having some severe performance
>problems. The server has 8Gb memory with AWE enabled and 3GB/PAE setting in
>the boot.ini. No other applications are running on the server. The available
>memory is showing 1.2GB.
>The previous administrator has the server set with a fixed memory setting of
>8120MB.
>My question, is there a benefit that I am unaware of in having the server set
>with fixed memory versus dynamic? Is not dynamic memory a better
>configuration when SQL Server is not competing with other applications?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200601/1|||I thought that when I stated the memory was not dynamic it made this
irrelevant. It's rare that the Fixed memory option is used and I would
uncheck this but the memory in AWE is always static in SQL2000.
--
Andrew J. Kelly SQL MVP
"Robert R via SQLMonster.com" <u3288@.uwe> wrote in message
news:5ad1454f51cda@.uwe...
> OK. Thanks, for making me aware of that, but what about the question(s) I
> posed:
> "Is there a benefit that I am unaware of in having the server set
> with fixed memory versus dynamic? Is not dynamic memory a better
> configuration when SQL Server is not competing with other applications?"
>
> Andrew J. Kelly wrote:
>>If you are using AWE and you have 8GB you should set the MAX memory to no
>>more than 7GB since AWE is not dynamic in SQL2000.
>>I have inherited a SQL 2K server that is having some severe performance
>> problems. The server has 8Gb memory with AWE enabled and 3GB/PAE setting
>>[quoted text clipped - 11 lines]
>> with fixed memory versus dynamic? Is not dynamic memory a better
>> configuration when SQL Server is not competing with other applications?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200601/1|||This is a multi-part message in MIME format.
--020809020907060301070206
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
I think you missed the bit where Robert said he has 8GB in his box and
therefore must use AWE (and therefore, by implication, cannot use
dynamic memory management).
--
*mike hodgson*
http://sqlnerd.blogspot.com
Juliano H via SQLMonster.com wrote:
>Hello, Robert!
>To decide if you will use fixe the memory to SQL Server, you need to know
>your environment. The SQL Server works better if it has memory enough to
>process the information and transaction. If your SQL Server has any other
>application installed you should not set fixed memory, because other process
>must require more memory and the server will not have memory enough to
>supress this request.
>So, you should analyze it before and after that define if you will use fixed
>memory or not.
>I have problems too with hyper-memory allocation to SQL Server, or better, I
>had define more memory that it needed.
>I recomend for you, that you set a min memory and a maximum memory before,
>monitor its performance and if you have many memory free, cache hit ratio
>below 90% and other important counters to determine if SQL Server is using
>memory efficiently.
>If you need a help, please send me a message. Ok?
>Take care, when you define fixed memory. You should monitor in System Monitor
>(W2k ou W2k3) how many Pages/sec your server is paging because it. Ok?
>Bye
>Juliano Horta
>
>Robert R wrote:
>
>>I have inherited a SQL 2K server that is having some severe performance
>>problems. The server has 8Gb memory with AWE enabled and 3GB/PAE setting in
>>the boot.ini. No other applications are running on the server. The available
>>memory is showing 1.2GB.
>>The previous administrator has the server set with a fixed memory setting of
>>8120MB.
>>My question, is there a benefit that I am unaware of in having the server set
>>with fixed memory versus dynamic? Is not dynamic memory a better
>>configuration when SQL Server is not competing with other applications?
>>
>
>
--020809020907060301070206
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>I think you missed the bit where Robert said he has 8GB in his box
and therefore must use AWE (and therefore, by implication, cannot use
dynamic memory management).</tt><br>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2"><a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
Juliano H via SQLMonster.com wrote:
<blockquote cite="mid5ad45d0f6773c@.uwe" type="cite">
<pre wrap="">Hello, Robert!
To decide if you will use fixe the memory to SQL Server, you need to know
your environment. The SQL Server works better if it has memory enough to
process the information and transaction. If your SQL Server has any other
application installed you should not set fixed memory, because other process
must require more memory and the server will not have memory enough to
supress this request.
So, you should analyze it before and after that define if you will use fixed
memory or not.
I have problems too with hyper-memory allocation to SQL Server, or better, I
had define more memory that it needed.
I recomend for you, that you set a min memory and a maximum memory before,
monitor its performance and if you have many memory free, cache hit ratio
below 90% and other important counters to determine if SQL Server is using
memory efficiently.
If you need a help, please send me a message. Ok?
Take care, when you define fixed memory. You should monitor in System Monitor
(W2k ou W2k3) how many Pages/sec your server is paging because it. Ok?
Bye
Juliano Horta
Robert R wrote:
</pre>
<blockquote type="cite">
<pre wrap="">I have inherited a SQL 2K server that is having some severe performance
problems. The server has 8Gb memory with AWE enabled and 3GB/PAE setting in
the boot.ini. No other applications are running on the server. The available
memory is showing 1.2GB.
The previous administrator has the server set with a fixed memory setting of
8120MB.
My question, is there a benefit that I am unaware of in having the server set
with fixed memory versus dynamic? Is not dynamic memory a better
configuration when SQL Server is not competing with other applications?
</pre>
</blockquote>
<pre wrap=""><!-->
</pre>
</blockquote>
</body>
</html>
--020809020907060301070206--|||Hello! Mike.
Why, He cannot use dynamic memory management? What's the relationship between
AWE enabled and memory options?
Thanks
Mike Hodgson wrote:
>I think you missed the bit where Robert said he has 8GB in his box and
>therefore must use AWE (and therefore, by implication, cannot use
>dynamic memory management).
>--
>*mike hodgson*
>http://sqlnerd.blogspot.com
>>Hello, Robert!
>[quoted text clipped - 36 lines]
>>
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200601/1|||This is a multi-part message in MIME format.
--010103060909000305060202
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
When using AWE memory SQL Server locks pages in memory. It will not
swap pages out to disk if another application makes a request for memory
and there is not enough available at the time (like it normally does
when it is using dynamic memory management). Using min & max server
memory is fine but once SQL marks the memory page as committed, it stays
that way until SQL Server shuts down (ie. it will not be released).
Dynamic memory management (at least in my book) also involves releasing
memory, when needed, to maintain a minimum free memory threshold (off
the top of my head I think the default threshold is 10MB).
Perhaps I misunderstood your post but I thought you were advocating
setting a max & min level and letting SQL Server allocate and release
memory as needed.
--
*mike hodgson*
http://sqlnerd.blogspot.com
Juliano H via SQLMonster.com wrote:
>Hello! Mike.
>Why, He cannot use dynamic memory management? What's the relationship between
>AWE enabled and memory options?
>
>Thanks
>Mike Hodgson wrote:
>
>>I think you missed the bit where Robert said he has 8GB in his box and
>>therefore must use AWE (and therefore, by implication, cannot use
>>dynamic memory management).
>>--
>>*mike hodgson*
>>http://sqlnerd.blogspot.com
>>
>>Hello, Robert!
>>
>>[quoted text clipped - 36 lines]
>>
>>
>
>
--010103060909000305060202
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>When using AWE memory SQL Server locks pages in memory. It will
not swap pages out to disk if another application makes a request for
memory and there is not enough available at the time (like it normally
does when it is using dynamic memory management). Using min & max
server memory is fine but once SQL marks the memory page as committed,
it stays that way until SQL Server shuts down (ie. it will not be
released). Dynamic memory management (at least in my book) also
involves releasing memory, when needed, to maintain a minimum free
memory threshold (off the top of my head I think the default threshold
is 10MB).<br>
<br>
Perhaps I misunderstood your post but I thought you were advocating
setting a max & min level and letting SQL Server allocate and
release memory as needed.<br>
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2"><a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
Juliano H via SQLMonster.com wrote:
<blockquote cite="mid5ae29f55fad3d@.uwe" type="cite">
<pre wrap="">Hello! Mike.
Why, He cannot use dynamic memory management? What's the relationship between
AWE enabled and memory options?
Thanks
Mike Hodgson wrote:
</pre>
<blockquote type="cite">
<pre wrap="">I think you missed the bit where Robert said he has 8GB in his box and
therefore must use AWE (and therefore, by implication, cannot use
dynamic memory management).
--
*mike hodgson*
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a>
</pre>
<blockquote type="cite">
<pre wrap="">Hello, Robert!
</pre>
</blockquote>
<pre wrap="">[quoted text clipped - 36 lines]
</pre>
<blockquote type="cite">
<pre wrap="">
</pre>
</blockquote>
</blockquote>
<pre wrap=""><!-->
</pre>
</blockquote>
</body>
</html>
--010103060909000305060202--
Dynamic Memory Problem
Hello,
I have a SQL install that I only want to use 4098MB of memory. I originally
configured the server to dynamically use between 0 and 4096MB of RAM. This
was working fine. I then decided to experiment with a fixed amount to see
how the server would perform at peak memory usage, so I set the fixed amount
to 4096MB. After testing I switched the memory back to dynamic and the
original settings of 0 minimum and 4096 Maximum. Problem is, this SQL
install ALWAYS uses the full 4096MB now. I have tried restarting the
service, rebooting the server, but whenever the SQL service starts it
immediately eats up 4096MB of RAM.
Any ideas on how to fix this?
btw - server is running SP3a with all the latest critical hotfixes.
Thanks,
JBaileyI figured this out.
I ended up having to disable AWE in SQL, restart the SQL service, and then
reenable AWE
Everything is working normally now.
Wonder why it happened though
"JBailey" <abc@.123.com> wrote in message
news:OX6mN8BlDHA.2272@.tk2msftngp13.phx.gbl...
> Hello,
> I have a SQL install that I only want to use 4098MB of memory. I
originally
> configured the server to dynamically use between 0 and 4096MB of RAM. This
> was working fine. I then decided to experiment with a fixed amount to see
> how the server would perform at peak memory usage, so I set the fixed
amount
> to 4096MB. After testing I switched the memory back to dynamic and the
> original settings of 0 minimum and 4096 Maximum. Problem is, this SQL
> install ALWAYS uses the full 4096MB now. I have tried restarting the
> service, rebooting the server, but whenever the SQL service starts it
> immediately eats up 4096MB of RAM.
> Any ideas on how to fix this?
> btw - server is running SP3a with all the latest critical hotfixes.
> Thanks,
> JBailey
>|||Actually I was wrong. Rebooted the box and I'm still having the same issue.
:(
Any ideas?
"JBailey" <abc@.123.com> wrote in message
news:OX6mN8BlDHA.2272@.tk2msftngp13.phx.gbl...
> Hello,
> I have a SQL install that I only want to use 4098MB of memory. I
originally
> configured the server to dynamically use between 0 and 4096MB of RAM. This
> was working fine. I then decided to experiment with a fixed amount to see
> how the server would perform at peak memory usage, so I set the fixed
amount
> to 4096MB. After testing I switched the memory back to dynamic and the
> original settings of 0 minimum and 4096 Maximum. Problem is, this SQL
> install ALWAYS uses the full 4096MB now. I have tried restarting the
> service, rebooting the server, but whenever the SQL service starts it
> immediately eats up 4096MB of RAM.
> Any ideas on how to fix this?
> btw - server is running SP3a with all the latest critical hotfixes.
> Thanks,
> JBailey
>|||With AWE enabled, SQL server will take as much memory it can get -- if there
is a max, it goes to the max, else it goes to the max the system allows it.
So what you see is the standard behavior.
Quentin
"JBailey" <abc@.123.com> wrote in message
news:Ol40$QClDHA.2488@.TK2MSFTNGP12.phx.gbl...
> Actually I was wrong. Rebooted the box and I'm still having the same
issue.
> :(
> Any ideas?
>
> "JBailey" <abc@.123.com> wrote in message
> news:OX6mN8BlDHA.2272@.tk2msftngp13.phx.gbl...
> > Hello,
> >
> > I have a SQL install that I only want to use 4098MB of memory. I
> originally
> > configured the server to dynamically use between 0 and 4096MB of RAM.
This
> > was working fine. I then decided to experiment with a fixed amount to
see
> > how the server would perform at peak memory usage, so I set the fixed
> amount
> > to 4096MB. After testing I switched the memory back to dynamic and the
> > original settings of 0 minimum and 4096 Maximum. Problem is, this SQL
> > install ALWAYS uses the full 4096MB now. I have tried restarting the
> > service, rebooting the server, but whenever the SQL service starts it
> > immediately eats up 4096MB of RAM.
> >
> > Any ideas on how to fix this?
> >
> > btw - server is running SP3a with all the latest critical hotfixes.
> >
> > Thanks,
> >
> > JBailey
> >
> >
>|||When you use AWE it will fix the memory at what the Max Memory setting is.
That is how AWE works. You can not dynamically allocate memory with AWE set
on.
--
Andrew J. Kelly
SQL Server MVP
"JBailey" <abc@.123.com> wrote in message
news:OX6mN8BlDHA.2272@.tk2msftngp13.phx.gbl...
> Hello,
> I have a SQL install that I only want to use 4098MB of memory. I
originally
> configured the server to dynamically use between 0 and 4096MB of RAM. This
> was working fine. I then decided to experiment with a fixed amount to see
> how the server would perform at peak memory usage, so I set the fixed
amount
> to 4096MB. After testing I switched the memory back to dynamic and the
> original settings of 0 minimum and 4096 Maximum. Problem is, this SQL
> install ALWAYS uses the full 4096MB now. I have tried restarting the
> service, rebooting the server, but whenever the SQL service starts it
> immediately eats up 4096MB of RAM.
> Any ideas on how to fix this?
> btw - server is running SP3a with all the latest critical hotfixes.
> Thanks,
> JBailey
>|||Alright, I understand that, but I have a follow up.
I have three instances of SQL installed on a 2 node cluster.
One instance is set to use AWE and a max memory of 4096. This is the
instance that immediately takes up the 4GB of RAM
Two other instances are set to use AWE, and both are set to use max memory
of 2048. Neither of these instances use up the RAM immediately like the
first instance. Is this because neither of them are set to use more than 4GB
of RAM?
Thanks,
JBailey
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eQFsviClDHA.2244@.TK2MSFTNGP12.phx.gbl...
> When you use AWE it will fix the memory at what the Max Memory setting is.
> That is how AWE works. You can not dynamically allocate memory with AWE
set
> on.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "JBailey" <abc@.123.com> wrote in message
> news:OX6mN8BlDHA.2272@.tk2msftngp13.phx.gbl...
> > Hello,
> >
> > I have a SQL install that I only want to use 4098MB of memory. I
> originally
> > configured the server to dynamically use between 0 and 4096MB of RAM.
This
> > was working fine. I then decided to experiment with a fixed amount to
see
> > how the server would perform at peak memory usage, so I set the fixed
> amount
> > to 4096MB. After testing I switched the memory back to dynamic and the
> > original settings of 0 minimum and 4096 Maximum. Problem is, this SQL
> > install ALWAYS uses the full 4096MB now. I have tried restarting the
> > service, rebooting the server, but whenever the SQL service starts it
> > immediately eats up 4096MB of RAM.
> >
> > Any ideas on how to fix this?
> >
> > btw - server is running SP3a with all the latest critical hotfixes.
> >
> > Thanks,
> >
> > JBailey
> >
> >
>|||Sorry for the late reply. AWE is not used in the other 2 instances since
the memory can only be 2 GB. There are times when SQL Server just ignores
the AWE settings when they don't make sense and can not be used.
--
Andrew J. Kelly
SQL Server MVP
"JBailey" <abc@.123.com> wrote in message
news:%23Fhk%23zKlDHA.2512@.TK2MSFTNGP09.phx.gbl...
> Alright, I understand that, but I have a follow up.
> I have three instances of SQL installed on a 2 node cluster.
> One instance is set to use AWE and a max memory of 4096. This is the
> instance that immediately takes up the 4GB of RAM
> Two other instances are set to use AWE, and both are set to use max memory
> of 2048. Neither of these instances use up the RAM immediately like the
> first instance. Is this because neither of them are set to use more than
4GB
> of RAM?
> Thanks,
> JBailey
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eQFsviClDHA.2244@.TK2MSFTNGP12.phx.gbl...
> > When you use AWE it will fix the memory at what the Max Memory setting
is.
> > That is how AWE works. You can not dynamically allocate memory with AWE
> set
> > on.
> >
> > --
> >
> > Andrew J. Kelly
> > SQL Server MVP
> >
> >
> > "JBailey" <abc@.123.com> wrote in message
> > news:OX6mN8BlDHA.2272@.tk2msftngp13.phx.gbl...
> > > Hello,
> > >
> > > I have a SQL install that I only want to use 4098MB of memory. I
> > originally
> > > configured the server to dynamically use between 0 and 4096MB of RAM.
> This
> > > was working fine. I then decided to experiment with a fixed amount to
> see
> > > how the server would perform at peak memory usage, so I set the fixed
> > amount
> > > to 4096MB. After testing I switched the memory back to dynamic and the
> > > original settings of 0 minimum and 4096 Maximum. Problem is, this SQL
> > > install ALWAYS uses the full 4096MB now. I have tried restarting the
> > > service, rebooting the server, but whenever the SQL service starts it
> > > immediately eats up 4096MB of RAM.
> > >
> > > Any ideas on how to fix this?
> > >
> > > btw - server is running SP3a with all the latest critical hotfixes.
> > >
> > > Thanks,
> > >
> > > JBailey
> > >
> > >
> >
> >
>
I have a SQL install that I only want to use 4098MB of memory. I originally
configured the server to dynamically use between 0 and 4096MB of RAM. This
was working fine. I then decided to experiment with a fixed amount to see
how the server would perform at peak memory usage, so I set the fixed amount
to 4096MB. After testing I switched the memory back to dynamic and the
original settings of 0 minimum and 4096 Maximum. Problem is, this SQL
install ALWAYS uses the full 4096MB now. I have tried restarting the
service, rebooting the server, but whenever the SQL service starts it
immediately eats up 4096MB of RAM.
Any ideas on how to fix this?
btw - server is running SP3a with all the latest critical hotfixes.
Thanks,
JBaileyI figured this out.
I ended up having to disable AWE in SQL, restart the SQL service, and then
reenable AWE
Everything is working normally now.
Wonder why it happened though
"JBailey" <abc@.123.com> wrote in message
news:OX6mN8BlDHA.2272@.tk2msftngp13.phx.gbl...
> Hello,
> I have a SQL install that I only want to use 4098MB of memory. I
originally
> configured the server to dynamically use between 0 and 4096MB of RAM. This
> was working fine. I then decided to experiment with a fixed amount to see
> how the server would perform at peak memory usage, so I set the fixed
amount
> to 4096MB. After testing I switched the memory back to dynamic and the
> original settings of 0 minimum and 4096 Maximum. Problem is, this SQL
> install ALWAYS uses the full 4096MB now. I have tried restarting the
> service, rebooting the server, but whenever the SQL service starts it
> immediately eats up 4096MB of RAM.
> Any ideas on how to fix this?
> btw - server is running SP3a with all the latest critical hotfixes.
> Thanks,
> JBailey
>|||Actually I was wrong. Rebooted the box and I'm still having the same issue.
:(
Any ideas?
"JBailey" <abc@.123.com> wrote in message
news:OX6mN8BlDHA.2272@.tk2msftngp13.phx.gbl...
> Hello,
> I have a SQL install that I only want to use 4098MB of memory. I
originally
> configured the server to dynamically use between 0 and 4096MB of RAM. This
> was working fine. I then decided to experiment with a fixed amount to see
> how the server would perform at peak memory usage, so I set the fixed
amount
> to 4096MB. After testing I switched the memory back to dynamic and the
> original settings of 0 minimum and 4096 Maximum. Problem is, this SQL
> install ALWAYS uses the full 4096MB now. I have tried restarting the
> service, rebooting the server, but whenever the SQL service starts it
> immediately eats up 4096MB of RAM.
> Any ideas on how to fix this?
> btw - server is running SP3a with all the latest critical hotfixes.
> Thanks,
> JBailey
>|||With AWE enabled, SQL server will take as much memory it can get -- if there
is a max, it goes to the max, else it goes to the max the system allows it.
So what you see is the standard behavior.
Quentin
"JBailey" <abc@.123.com> wrote in message
news:Ol40$QClDHA.2488@.TK2MSFTNGP12.phx.gbl...
> Actually I was wrong. Rebooted the box and I'm still having the same
issue.
> :(
> Any ideas?
>
> "JBailey" <abc@.123.com> wrote in message
> news:OX6mN8BlDHA.2272@.tk2msftngp13.phx.gbl...
> > Hello,
> >
> > I have a SQL install that I only want to use 4098MB of memory. I
> originally
> > configured the server to dynamically use between 0 and 4096MB of RAM.
This
> > was working fine. I then decided to experiment with a fixed amount to
see
> > how the server would perform at peak memory usage, so I set the fixed
> amount
> > to 4096MB. After testing I switched the memory back to dynamic and the
> > original settings of 0 minimum and 4096 Maximum. Problem is, this SQL
> > install ALWAYS uses the full 4096MB now. I have tried restarting the
> > service, rebooting the server, but whenever the SQL service starts it
> > immediately eats up 4096MB of RAM.
> >
> > Any ideas on how to fix this?
> >
> > btw - server is running SP3a with all the latest critical hotfixes.
> >
> > Thanks,
> >
> > JBailey
> >
> >
>|||When you use AWE it will fix the memory at what the Max Memory setting is.
That is how AWE works. You can not dynamically allocate memory with AWE set
on.
--
Andrew J. Kelly
SQL Server MVP
"JBailey" <abc@.123.com> wrote in message
news:OX6mN8BlDHA.2272@.tk2msftngp13.phx.gbl...
> Hello,
> I have a SQL install that I only want to use 4098MB of memory. I
originally
> configured the server to dynamically use between 0 and 4096MB of RAM. This
> was working fine. I then decided to experiment with a fixed amount to see
> how the server would perform at peak memory usage, so I set the fixed
amount
> to 4096MB. After testing I switched the memory back to dynamic and the
> original settings of 0 minimum and 4096 Maximum. Problem is, this SQL
> install ALWAYS uses the full 4096MB now. I have tried restarting the
> service, rebooting the server, but whenever the SQL service starts it
> immediately eats up 4096MB of RAM.
> Any ideas on how to fix this?
> btw - server is running SP3a with all the latest critical hotfixes.
> Thanks,
> JBailey
>|||Alright, I understand that, but I have a follow up.
I have three instances of SQL installed on a 2 node cluster.
One instance is set to use AWE and a max memory of 4096. This is the
instance that immediately takes up the 4GB of RAM
Two other instances are set to use AWE, and both are set to use max memory
of 2048. Neither of these instances use up the RAM immediately like the
first instance. Is this because neither of them are set to use more than 4GB
of RAM?
Thanks,
JBailey
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eQFsviClDHA.2244@.TK2MSFTNGP12.phx.gbl...
> When you use AWE it will fix the memory at what the Max Memory setting is.
> That is how AWE works. You can not dynamically allocate memory with AWE
set
> on.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "JBailey" <abc@.123.com> wrote in message
> news:OX6mN8BlDHA.2272@.tk2msftngp13.phx.gbl...
> > Hello,
> >
> > I have a SQL install that I only want to use 4098MB of memory. I
> originally
> > configured the server to dynamically use between 0 and 4096MB of RAM.
This
> > was working fine. I then decided to experiment with a fixed amount to
see
> > how the server would perform at peak memory usage, so I set the fixed
> amount
> > to 4096MB. After testing I switched the memory back to dynamic and the
> > original settings of 0 minimum and 4096 Maximum. Problem is, this SQL
> > install ALWAYS uses the full 4096MB now. I have tried restarting the
> > service, rebooting the server, but whenever the SQL service starts it
> > immediately eats up 4096MB of RAM.
> >
> > Any ideas on how to fix this?
> >
> > btw - server is running SP3a with all the latest critical hotfixes.
> >
> > Thanks,
> >
> > JBailey
> >
> >
>|||Sorry for the late reply. AWE is not used in the other 2 instances since
the memory can only be 2 GB. There are times when SQL Server just ignores
the AWE settings when they don't make sense and can not be used.
--
Andrew J. Kelly
SQL Server MVP
"JBailey" <abc@.123.com> wrote in message
news:%23Fhk%23zKlDHA.2512@.TK2MSFTNGP09.phx.gbl...
> Alright, I understand that, but I have a follow up.
> I have three instances of SQL installed on a 2 node cluster.
> One instance is set to use AWE and a max memory of 4096. This is the
> instance that immediately takes up the 4GB of RAM
> Two other instances are set to use AWE, and both are set to use max memory
> of 2048. Neither of these instances use up the RAM immediately like the
> first instance. Is this because neither of them are set to use more than
4GB
> of RAM?
> Thanks,
> JBailey
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eQFsviClDHA.2244@.TK2MSFTNGP12.phx.gbl...
> > When you use AWE it will fix the memory at what the Max Memory setting
is.
> > That is how AWE works. You can not dynamically allocate memory with AWE
> set
> > on.
> >
> > --
> >
> > Andrew J. Kelly
> > SQL Server MVP
> >
> >
> > "JBailey" <abc@.123.com> wrote in message
> > news:OX6mN8BlDHA.2272@.tk2msftngp13.phx.gbl...
> > > Hello,
> > >
> > > I have a SQL install that I only want to use 4098MB of memory. I
> > originally
> > > configured the server to dynamically use between 0 and 4096MB of RAM.
> This
> > > was working fine. I then decided to experiment with a fixed amount to
> see
> > > how the server would perform at peak memory usage, so I set the fixed
> > amount
> > > to 4096MB. After testing I switched the memory back to dynamic and the
> > > original settings of 0 minimum and 4096 Maximum. Problem is, this SQL
> > > install ALWAYS uses the full 4096MB now. I have tried restarting the
> > > service, rebooting the server, but whenever the SQL service starts it
> > > immediately eats up 4096MB of RAM.
> > >
> > > Any ideas on how to fix this?
> > >
> > > btw - server is running SP3a with all the latest critical hotfixes.
> > >
> > > Thanks,
> > >
> > > JBailey
> > >
> > >
> >
> >
>
Subscribe to:
Posts (Atom)