I have SQL 2000 on a W2K advanced server. I want to use SQL server's
backup routine to backup the databases to a directory on a separate
computer. I have set up the SQL Client Agent on that other server hoping
that would somehow facilitate this, but there doesn't seem to be any way
to force SQL server to backup the file to a device that is not local to
the server itself.
I understand that one possible option would be to install a full version
of SQL 2000 on the other server and has the primary backup to that
secondary server. But that would mean 2 licenses of SQL Server. I
don't think we have 2 licenses, though I will check on that.
Surely there is some way to backup databases to an non-local device.
CurtisHowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/?id=555128
The same technique applies to manual backup jobs as well.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Curtis Vaughan" <curtis@.npc-usa.com> wrote in message
news:eiqgAsisEHA.1468@.TK2MSFTNGP12.phx.gbl...
> I have SQL 2000 on a W2K advanced server. I want to use SQL server's
> backup routine to backup the databases to a directory on a separate
> computer. I have set up the SQL Client Agent on that other server hoping
> that would somehow facilitate this, but there doesn't seem to be any way
> to force SQL server to backup the file to a device that is not local to
> the server itself.
> I understand that one possible option would be to install a full version
> of SQL 2000 on the other server and has the primary backup to that
> secondary server. But that would mean 2 licenses of SQL Server. I
> don't think we have 2 licenses, though I will check on that.
> Surely there is some way to backup databases to an non-local device.
> Curtis|||So, if I understand correctly the MSSQLSERVER service must be switched
from running under the LocalSystem account to running under a
domain-level account, such as, let's say, DataAdmin with a password. And
which account is not local to the server, but global to the domain. Right?
If that's true, what rights do I have to give the DataAdmin account?
Curtis
Geoff N. Hiten wrote:
> HowTo: Backup to UNC name using Database Maintenance Wizard
> http://support.microsoft.com/?id=555128
> The same technique applies to manual backup jobs as well.
>|||Curtis,
Per the KB article ...
2) The SQL Server service account must have FULL CONTROL rights to the file
system folder and to the share
Tunji
"Curtis Vaughan" <curtis@.npc-usa.com> wrote in message news:envLaWjsEHA.160@.
TK2MSFTNGP11.phx.gbl...
So, if I understand correctly the MSSQLSERVER service must be switched
from running under the LocalSystem account to running under a
domain-level account, such as, let's say, DataAdmin with a password. And
which account is not local to the server, but global to the domain. Right?
If that's true, what rights do I have to give the DataAdmin account?
Curtis
Geoff N. Hiten wrote:
> HowTo: Backup to UNC name using Database Maintenance Wizard
> http://support.microsoft.com/?id=555128
>
> The same technique applies to manual backup jobs as well.
>|||Everything works better if you give the domain-level account (DataAdmin in
your example) local administrator rights on the SQL server host computer.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Curtis Vaughan" <curtis@.npc-usa.com> wrote in message
news:envLaWjsEHA.160@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> So, if I understand correctly the MSSQLSERVER service must be switched
> from running under the LocalSystem account to running under a
> domain-level account, such as, let's say, DataAdmin with a password. And
> which account is not local to the server, but global to the domain. Right?
> If that's true, what rights do I have to give the DataAdmin account?
> Curtis
> Geoff N. Hiten wrote:
>
Showing posts with label advanced. Show all posts
Showing posts with label advanced. Show all posts
Sunday, March 25, 2012
Backing up Databases using SQLServer
I have SQL 2000 on a W2K advanced server. I want to use SQL server's
backup routine to backup the databases to a directory on a separate
computer. I have set up the SQL Client Agent on that other server hoping
that would somehow facilitate this, but there doesn't seem to be any way
to force SQL server to backup the file to a device that is not local to
the server itself.
I understand that one possible option would be to install a full version
of SQL 2000 on the other server and has the primary backup to that
secondary server. But that would mean 2 licenses of SQL Server. I
don't think we have 2 licenses, though I will check on that.
Surely there is some way to backup databases to an non-local device.
Curtis
HowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/?id=555128
The same technique applies to manual backup jobs as well.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Curtis Vaughan" <curtis@.npc-usa.com> wrote in message
news:eiqgAsisEHA.1468@.TK2MSFTNGP12.phx.gbl...
> I have SQL 2000 on a W2K advanced server. I want to use SQL server's
> backup routine to backup the databases to a directory on a separate
> computer. I have set up the SQL Client Agent on that other server hoping
> that would somehow facilitate this, but there doesn't seem to be any way
> to force SQL server to backup the file to a device that is not local to
> the server itself.
> I understand that one possible option would be to install a full version
> of SQL 2000 on the other server and has the primary backup to that
> secondary server. But that would mean 2 licenses of SQL Server. I
> don't think we have 2 licenses, though I will check on that.
> Surely there is some way to backup databases to an non-local device.
> Curtis
|||So, if I understand correctly the MSSQLSERVER service must be switched
from running under the LocalSystem account to running under a
domain-level account, such as, let's say, DataAdmin with a password. And
which account is not local to the server, but global to the domain. Right?
If that's true, what rights do I have to give the DataAdmin account?
Curtis
Geoff N. Hiten wrote:
> HowTo: Backup to UNC name using Database Maintenance Wizard
> http://support.microsoft.com/?id=555128
> The same technique applies to manual backup jobs as well.
>
|||Curtis,
Per the KB article ...
2) The SQL Server service account must have FULL CONTROL rights to the file system folder and to the share
Tunji
"Curtis Vaughan" <curtis@.npc-usa.com> wrote in message news:envLaWjsEHA.160@.TK2MSFTNGP11.phx.gbl...
So, if I understand correctly the MSSQLSERVER service must be switched
from running under the LocalSystem account to running under a
domain-level account, such as, let's say, DataAdmin with a password. And
which account is not local to the server, but global to the domain. Right?
If that's true, what rights do I have to give the DataAdmin account?
Curtis
Geoff N. Hiten wrote:
> HowTo: Backup to UNC name using Database Maintenance Wizard
> http://support.microsoft.com/?id=555128
> The same technique applies to manual backup jobs as well.
>
|||Everything works better if you give the domain-level account (DataAdmin in
your example) local administrator rights on the SQL server host computer.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Curtis Vaughan" <curtis@.npc-usa.com> wrote in message
news:envLaWjsEHA.160@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> So, if I understand correctly the MSSQLSERVER service must be switched
> from running under the LocalSystem account to running under a
> domain-level account, such as, let's say, DataAdmin with a password. And
> which account is not local to the server, but global to the domain. Right?
> If that's true, what rights do I have to give the DataAdmin account?
> Curtis
> Geoff N. Hiten wrote:
backup routine to backup the databases to a directory on a separate
computer. I have set up the SQL Client Agent on that other server hoping
that would somehow facilitate this, but there doesn't seem to be any way
to force SQL server to backup the file to a device that is not local to
the server itself.
I understand that one possible option would be to install a full version
of SQL 2000 on the other server and has the primary backup to that
secondary server. But that would mean 2 licenses of SQL Server. I
don't think we have 2 licenses, though I will check on that.
Surely there is some way to backup databases to an non-local device.
Curtis
HowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/?id=555128
The same technique applies to manual backup jobs as well.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Curtis Vaughan" <curtis@.npc-usa.com> wrote in message
news:eiqgAsisEHA.1468@.TK2MSFTNGP12.phx.gbl...
> I have SQL 2000 on a W2K advanced server. I want to use SQL server's
> backup routine to backup the databases to a directory on a separate
> computer. I have set up the SQL Client Agent on that other server hoping
> that would somehow facilitate this, but there doesn't seem to be any way
> to force SQL server to backup the file to a device that is not local to
> the server itself.
> I understand that one possible option would be to install a full version
> of SQL 2000 on the other server and has the primary backup to that
> secondary server. But that would mean 2 licenses of SQL Server. I
> don't think we have 2 licenses, though I will check on that.
> Surely there is some way to backup databases to an non-local device.
> Curtis
|||So, if I understand correctly the MSSQLSERVER service must be switched
from running under the LocalSystem account to running under a
domain-level account, such as, let's say, DataAdmin with a password. And
which account is not local to the server, but global to the domain. Right?
If that's true, what rights do I have to give the DataAdmin account?
Curtis
Geoff N. Hiten wrote:
> HowTo: Backup to UNC name using Database Maintenance Wizard
> http://support.microsoft.com/?id=555128
> The same technique applies to manual backup jobs as well.
>
|||Curtis,
Per the KB article ...
2) The SQL Server service account must have FULL CONTROL rights to the file system folder and to the share
Tunji
"Curtis Vaughan" <curtis@.npc-usa.com> wrote in message news:envLaWjsEHA.160@.TK2MSFTNGP11.phx.gbl...
So, if I understand correctly the MSSQLSERVER service must be switched
from running under the LocalSystem account to running under a
domain-level account, such as, let's say, DataAdmin with a password. And
which account is not local to the server, but global to the domain. Right?
If that's true, what rights do I have to give the DataAdmin account?
Curtis
Geoff N. Hiten wrote:
> HowTo: Backup to UNC name using Database Maintenance Wizard
> http://support.microsoft.com/?id=555128
> The same technique applies to manual backup jobs as well.
>
|||Everything works better if you give the domain-level account (DataAdmin in
your example) local administrator rights on the SQL server host computer.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Curtis Vaughan" <curtis@.npc-usa.com> wrote in message
news:envLaWjsEHA.160@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> So, if I understand correctly the MSSQLSERVER service must be switched
> from running under the LocalSystem account to running under a
> domain-level account, such as, let's say, DataAdmin with a password. And
> which account is not local to the server, but global to the domain. Right?
> If that's true, what rights do I have to give the DataAdmin account?
> Curtis
> Geoff N. Hiten wrote:
Backing up Databases using SQLServer
I have SQL 2000 on a W2K advanced server. I want to use SQL server's
backup routine to backup the databases to a directory on a separate
computer. I have set up the SQL Client Agent on that other server hoping
that would somehow facilitate this, but there doesn't seem to be any way
to force SQL server to backup the file to a device that is not local to
the server itself.
I understand that one possible option would be to install a full version
of SQL 2000 on the other server and has the primary backup to that
secondary server. But that would mean 2 licenses of SQL Server. I
don't think we have 2 licenses, though I will check on that.
Surely there is some way to backup databases to an non-local device.
CurtisHowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/?id=555128
The same technique applies to manual backup jobs as well.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Curtis Vaughan" <curtis@.npc-usa.com> wrote in message
news:eiqgAsisEHA.1468@.TK2MSFTNGP12.phx.gbl...
> I have SQL 2000 on a W2K advanced server. I want to use SQL server's
> backup routine to backup the databases to a directory on a separate
> computer. I have set up the SQL Client Agent on that other server hoping
> that would somehow facilitate this, but there doesn't seem to be any way
> to force SQL server to backup the file to a device that is not local to
> the server itself.
> I understand that one possible option would be to install a full version
> of SQL 2000 on the other server and has the primary backup to that
> secondary server. But that would mean 2 licenses of SQL Server. I
> don't think we have 2 licenses, though I will check on that.
> Surely there is some way to backup databases to an non-local device.
> Curtis|||So, if I understand correctly the MSSQLSERVER service must be switched
from running under the LocalSystem account to running under a
domain-level account, such as, let's say, DataAdmin with a password. And
which account is not local to the server, but global to the domain. Right?
If that's true, what rights do I have to give the DataAdmin account?
Curtis
Geoff N. Hiten wrote:
> HowTo: Backup to UNC name using Database Maintenance Wizard
> http://support.microsoft.com/?id=555128
> The same technique applies to manual backup jobs as well.
>|||This is a multi-part message in MIME format.
--=_NextPart_000_01D8_01C4B22C.EC0EE200
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Curtis,
Per the KB article ... 2) The SQL Server service account must have FULL CONTROL rights to the =file system folder and to the share
Tunji
"Curtis Vaughan" <curtis@.npc-usa.com> wrote in message =news:envLaWjsEHA.160@.TK2MSFTNGP11.phx.gbl...
So, if I understand correctly the MSSQLSERVER service must be switched =
from running under the LocalSystem account to running under a domain-level account, such as, let's say, DataAdmin with a password. =And which account is not local to the server, but global to the domain. =Right?
If that's true, what rights do I have to give the DataAdmin account?
Curtis
Geoff N. Hiten wrote:
> HowTo: Backup to UNC name using Database Maintenance Wizard
> http://support.microsoft.com/?id=3D555128
> > The same technique applies to manual backup jobs as well.
>
--=_NextPart_000_01D8_01C4B22C.EC0EE200
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Curtis,
Per the KB article ...
2) The SQL Server service account must have FULL CONTROL =rights to the file system folder and to the share
Tunji
"Curtis Vaughan" wrote in =message news:envLaWjsEHA.160@.T=K2MSFTNGP11.phx.gbl...So, if I understand correctly the MSSQLSERVER service must be switched =from running under the LocalSystem account to running under a =domain-level account, such as, let's say, DataAdmin with a password. And which =account is not local to the server, but global to the domain. Right?If =that's true, what rights do I have to give the DataAdmin account?CurtisGeoff N. Hiten wrote:> HowTo: =Backup to UNC name using Database Maintenance Wizard>http://support.microsoft.com/?id=3D555128">http://support.microso=ft.com/?id=3D555128> > The same technique applies to manual backup jobs as well.>
--=_NextPart_000_01D8_01C4B22C.EC0EE200--|||Everything works better if you give the domain-level account (DataAdmin in
your example) local administrator rights on the SQL server host computer.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Curtis Vaughan" <curtis@.npc-usa.com> wrote in message
news:envLaWjsEHA.160@.TK2MSFTNGP11.phx.gbl...
> So, if I understand correctly the MSSQLSERVER service must be switched
> from running under the LocalSystem account to running under a
> domain-level account, such as, let's say, DataAdmin with a password. And
> which account is not local to the server, but global to the domain. Right?
> If that's true, what rights do I have to give the DataAdmin account?
> Curtis
> Geoff N. Hiten wrote:
> > HowTo: Backup to UNC name using Database Maintenance Wizard
> > http://support.microsoft.com/?id=555128
> >
> > The same technique applies to manual backup jobs as well.
> >|||Yeh! It's working!!!
Thanks everyone!!!
Geoff N. Hiten wrote:
> Everything works better if you give the domain-level account (DataAdmin in
> your example) local administrator rights on the SQL server host computer.
>sql
backup routine to backup the databases to a directory on a separate
computer. I have set up the SQL Client Agent on that other server hoping
that would somehow facilitate this, but there doesn't seem to be any way
to force SQL server to backup the file to a device that is not local to
the server itself.
I understand that one possible option would be to install a full version
of SQL 2000 on the other server and has the primary backup to that
secondary server. But that would mean 2 licenses of SQL Server. I
don't think we have 2 licenses, though I will check on that.
Surely there is some way to backup databases to an non-local device.
CurtisHowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/?id=555128
The same technique applies to manual backup jobs as well.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Curtis Vaughan" <curtis@.npc-usa.com> wrote in message
news:eiqgAsisEHA.1468@.TK2MSFTNGP12.phx.gbl...
> I have SQL 2000 on a W2K advanced server. I want to use SQL server's
> backup routine to backup the databases to a directory on a separate
> computer. I have set up the SQL Client Agent on that other server hoping
> that would somehow facilitate this, but there doesn't seem to be any way
> to force SQL server to backup the file to a device that is not local to
> the server itself.
> I understand that one possible option would be to install a full version
> of SQL 2000 on the other server and has the primary backup to that
> secondary server. But that would mean 2 licenses of SQL Server. I
> don't think we have 2 licenses, though I will check on that.
> Surely there is some way to backup databases to an non-local device.
> Curtis|||So, if I understand correctly the MSSQLSERVER service must be switched
from running under the LocalSystem account to running under a
domain-level account, such as, let's say, DataAdmin with a password. And
which account is not local to the server, but global to the domain. Right?
If that's true, what rights do I have to give the DataAdmin account?
Curtis
Geoff N. Hiten wrote:
> HowTo: Backup to UNC name using Database Maintenance Wizard
> http://support.microsoft.com/?id=555128
> The same technique applies to manual backup jobs as well.
>|||This is a multi-part message in MIME format.
--=_NextPart_000_01D8_01C4B22C.EC0EE200
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Curtis,
Per the KB article ... 2) The SQL Server service account must have FULL CONTROL rights to the =file system folder and to the share
Tunji
"Curtis Vaughan" <curtis@.npc-usa.com> wrote in message =news:envLaWjsEHA.160@.TK2MSFTNGP11.phx.gbl...
So, if I understand correctly the MSSQLSERVER service must be switched =
from running under the LocalSystem account to running under a domain-level account, such as, let's say, DataAdmin with a password. =And which account is not local to the server, but global to the domain. =Right?
If that's true, what rights do I have to give the DataAdmin account?
Curtis
Geoff N. Hiten wrote:
> HowTo: Backup to UNC name using Database Maintenance Wizard
> http://support.microsoft.com/?id=3D555128
> > The same technique applies to manual backup jobs as well.
>
--=_NextPart_000_01D8_01C4B22C.EC0EE200
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Curtis,
Per the KB article ...
2) The SQL Server service account must have FULL CONTROL =rights to the file system folder and to the share
Tunji
"Curtis Vaughan"
--=_NextPart_000_01D8_01C4B22C.EC0EE200--|||Everything works better if you give the domain-level account (DataAdmin in
your example) local administrator rights on the SQL server host computer.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Curtis Vaughan" <curtis@.npc-usa.com> wrote in message
news:envLaWjsEHA.160@.TK2MSFTNGP11.phx.gbl...
> So, if I understand correctly the MSSQLSERVER service must be switched
> from running under the LocalSystem account to running under a
> domain-level account, such as, let's say, DataAdmin with a password. And
> which account is not local to the server, but global to the domain. Right?
> If that's true, what rights do I have to give the DataAdmin account?
> Curtis
> Geoff N. Hiten wrote:
> > HowTo: Backup to UNC name using Database Maintenance Wizard
> > http://support.microsoft.com/?id=555128
> >
> > The same technique applies to manual backup jobs as well.
> >|||Yeh! It's working!!!
Thanks everyone!!!
Geoff N. Hiten wrote:
> Everything works better if you give the domain-level account (DataAdmin in
> your example) local administrator rights on the SQL server host computer.
>sql
Thursday, March 8, 2012
AWE questions
We are looking to take advantage of AWE in a single instance of SQL Server
2000 Enterprise running on a Windows 2000 Advanced Server. The cluster will
have 8GB of physical memory. I would like Sql Server to use 7GB and leave
1 GB for the O/S. If I understand the documentation correctly we need to do
the following:
1. Add "/pae /3gb /awe" to the boot.ini (I believe "/pae" is
necessary and the others are optional)
2. Make sure the sql server startup account is either an administrator
or has the Windows 2000 lock pages in memory privilege.
3. In query analyzer:
sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
GO
sp_configure 'max server memory', 7168
RECONFIGURE
GO
4. Reboot Server
MarkYou don't need the /awe switch but other than you are right :-)
Also, note this KB about AWE + cluster failover Pending Timeout
http://support.microsoft.com/default.aspx?scid=kb;en-us;329914
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Mark Sullivan" <msullivan@.getconnected.com> wrote in message
news:eDBCz8RuDHA.2360@.TK2MSFTNGP09.phx.gbl...
We are looking to take advantage of AWE in a single instance of SQL Server
2000 Enterprise running on a Windows 2000 Advanced Server. The cluster will
have 8GB of physical memory. I would like Sql Server to use 7GB and leave
1 GB for the O/S. If I understand the documentation correctly we need to do
the following:
1. Add "/pae /3gb /awe" to the boot.ini (I believe "/pae" is
necessary and the others are optional)
2. Make sure the sql server startup account is either an administrator
or has the Windows 2000 lock pages in memory privilege.
3. In query analyzer:
sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
GO
sp_configure 'max server memory', 7168
RECONFIGURE
GO
4. Reboot Server
Mark|||Thanks for the reply. Has AWE been a positive experience?
Ken
2000 Enterprise running on a Windows 2000 Advanced Server. The cluster will
have 8GB of physical memory. I would like Sql Server to use 7GB and leave
1 GB for the O/S. If I understand the documentation correctly we need to do
the following:
1. Add "/pae /3gb /awe" to the boot.ini (I believe "/pae" is
necessary and the others are optional)
2. Make sure the sql server startup account is either an administrator
or has the Windows 2000 lock pages in memory privilege.
3. In query analyzer:
sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
GO
sp_configure 'max server memory', 7168
RECONFIGURE
GO
4. Reboot Server
MarkYou don't need the /awe switch but other than you are right :-)
Also, note this KB about AWE + cluster failover Pending Timeout
http://support.microsoft.com/default.aspx?scid=kb;en-us;329914
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Mark Sullivan" <msullivan@.getconnected.com> wrote in message
news:eDBCz8RuDHA.2360@.TK2MSFTNGP09.phx.gbl...
We are looking to take advantage of AWE in a single instance of SQL Server
2000 Enterprise running on a Windows 2000 Advanced Server. The cluster will
have 8GB of physical memory. I would like Sql Server to use 7GB and leave
1 GB for the O/S. If I understand the documentation correctly we need to do
the following:
1. Add "/pae /3gb /awe" to the boot.ini (I believe "/pae" is
necessary and the others are optional)
2. Make sure the sql server startup account is either an administrator
or has the Windows 2000 lock pages in memory privilege.
3. In query analyzer:
sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
GO
sp_configure 'max server memory', 7168
RECONFIGURE
GO
4. Reboot Server
Mark|||Thanks for the reply. Has AWE been a positive experience?
Ken
awe question
We are going to turn on AWE on our production environment currently
its 4 processor box with 12GB of RAM and windows advanced server 2000
loaded on it. Now my question is do i need to use /PAE and /3GB
switches in our boot.ini in order to enable 6gigs to our sql server
or /PAE will okay?kulkarni.ninad@.gmail.com wrote:
> We are going to turn on AWE on our production environment currently
> its 4 processor box with 12GB of RAM and windows advanced server 2000
> loaded on it. Now my question is do i need to use /PAE and /3GB
> switches in our boot.ini in order to enable 6gigs to our sql server
> or /PAE will okay?
>
With 6Gb of RAM, I would use the /3GB switch as well. That would
effectively allow SQL to use as much as 5GB. Without the /3Gb switch
you are pretty well limited to 4Gb.|||That is not really true. /3GB allows SQL Server (or any properly enabled
application) to use up to 3GB of the 4GB of directly addressable memory but
limits the OS to 1GB of that same space. AWE and PAE are to allow the OS and
the App to see memory above 4GB and is totally independent of the /3GB
switch. The decision to use /3GB or not is dependent on how much of that
directly addressable memory you need to use. The only part of SQL Server
that can use memory above 2GB (or 3GB with the /3GB) is the data buffer
cache. I would question why he wants to use only 6GB when he has 12
available. I suspect he may be in a cluster and if that is true and it is
Active-Active then 6GB may be too much since AWE memory is fixed and can not
be dynamically adjusted.
Andrew J. Kelly SQL MVP
"Don Peterson" <sgtp.usmc@.gmail.com> wrote in message
news:uEmOq6LbHHA.5044@.TK2MSFTNGP05.phx.gbl...
> kulkarni.ninad@.gmail.com wrote:
> With 6Gb of RAM, I would use the /3GB switch as well. That would
> effectively allow SQL to use as much as 5GB. Without the /3Gb switch you
> are pretty well limited to 4Gb.|||Andrew J. Kelly wrote:
> That is not really true. /3GB allows SQL Server (or any properly enabled
> application) to use up to 3GB of the 4GB of directly addressable memory bu
t
> limits the OS to 1GB of that same space. AWE and PAE are to allow the OS a
nd
> the App to see memory above 4GB and is totally independent of the /3GB
> switch. The decision to use /3GB or not is dependent on how much of that
> directly addressable memory you need to use. The only part of SQL Server
> that can use memory above 2GB (or 3GB with the /3GB) is the data buffer
> cache. I would question why he wants to use only 6GB when he has 12
> available. I suspect he may be in a cluster and if that is true and it is
> Active-Active then 6GB may be too much since AWE memory is fixed and can n
ot
> be dynamically adjusted.
>
My thinking was that there was only 6Gb and if you didn't use the /3Gb
switch, then the OS would effectively take the full 2Gb available to it,
leaving 4Gb for applications such as SQL. While using the /3Gb switch
would limit the OS to 1Gb, thereby allowing SQL to use a full 5Gb.
However, now I see that there is actually 12Gb...
With 12Gb you don't need the /3Gb switch in order to allow SQL to see
6Gb (actually it could use 10Gb), however, I have to wonder why you want
to limit SQL to 6Gb when you have 12?
I think my recommendation still stands though, because if I recall, it's
not until you have over 16Gb of RAM that the OS has to use that extra Gb
of space to maintain the Address Extensions.
So, using the /3Gb switch would allow SQL to use up to 11Gb instead of
10Gb without it. Although I'd keep a close eye on things to make sure
that the OS is not having to do a bunch of swapping. In certain
circumstances the OS might be able to make better use of that Gb than SQL.|||> I think my recommendation still stands though, because if I recall, it's
> not until you have over 16Gb of RAM that the OS has to use that extra Gb
> of space to maintain the Address Extensions.
I believe that Unisys has recommendations to not use /3GB if you have > 12GB
in the machine.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Don Peterson" <sgtp.usmc@.gmail.com> wrote in message news:uhqohQYbHHA.588@.TK2MSFTNGP06.phx.
gbl...
> Andrew J. Kelly wrote:
> My thinking was that there was only 6Gb and if you didn't use the /3Gb
> switch, then the OS would effectively take the full 2Gb available to it,
> leaving 4Gb for applications such as SQL. While using the /3Gb switch
> would limit the OS to 1Gb, thereby allowing SQL to use a full 5Gb.
> However, now I see that there is actually 12Gb...
> With 12Gb you don't need the /3Gb switch in order to allow SQL to see
> 6Gb (actually it could use 10Gb), however, I have to wonder why you want
> to limit SQL to 6Gb when you have 12?
> I think my recommendation still stands though, because if I recall, it's
> not until you have over 16Gb of RAM that the OS has to use that extra Gb
> of space to maintain the Address Extensions.
> So, using the /3Gb switch would allow SQL to use up to 11Gb instead of
> 10Gb without it. Although I'd keep a close eye on things to make sure
> that the OS is not having to do a bunch of swapping. In certain
> circumstances the OS might be able to make better use of that Gb than SQL.
its 4 processor box with 12GB of RAM and windows advanced server 2000
loaded on it. Now my question is do i need to use /PAE and /3GB
switches in our boot.ini in order to enable 6gigs to our sql server
or /PAE will okay?kulkarni.ninad@.gmail.com wrote:
> We are going to turn on AWE on our production environment currently
> its 4 processor box with 12GB of RAM and windows advanced server 2000
> loaded on it. Now my question is do i need to use /PAE and /3GB
> switches in our boot.ini in order to enable 6gigs to our sql server
> or /PAE will okay?
>
With 6Gb of RAM, I would use the /3GB switch as well. That would
effectively allow SQL to use as much as 5GB. Without the /3Gb switch
you are pretty well limited to 4Gb.|||That is not really true. /3GB allows SQL Server (or any properly enabled
application) to use up to 3GB of the 4GB of directly addressable memory but
limits the OS to 1GB of that same space. AWE and PAE are to allow the OS and
the App to see memory above 4GB and is totally independent of the /3GB
switch. The decision to use /3GB or not is dependent on how much of that
directly addressable memory you need to use. The only part of SQL Server
that can use memory above 2GB (or 3GB with the /3GB) is the data buffer
cache. I would question why he wants to use only 6GB when he has 12
available. I suspect he may be in a cluster and if that is true and it is
Active-Active then 6GB may be too much since AWE memory is fixed and can not
be dynamically adjusted.
Andrew J. Kelly SQL MVP
"Don Peterson" <sgtp.usmc@.gmail.com> wrote in message
news:uEmOq6LbHHA.5044@.TK2MSFTNGP05.phx.gbl...
> kulkarni.ninad@.gmail.com wrote:
> With 6Gb of RAM, I would use the /3GB switch as well. That would
> effectively allow SQL to use as much as 5GB. Without the /3Gb switch you
> are pretty well limited to 4Gb.|||Andrew J. Kelly wrote:
> That is not really true. /3GB allows SQL Server (or any properly enabled
> application) to use up to 3GB of the 4GB of directly addressable memory bu
t
> limits the OS to 1GB of that same space. AWE and PAE are to allow the OS a
nd
> the App to see memory above 4GB and is totally independent of the /3GB
> switch. The decision to use /3GB or not is dependent on how much of that
> directly addressable memory you need to use. The only part of SQL Server
> that can use memory above 2GB (or 3GB with the /3GB) is the data buffer
> cache. I would question why he wants to use only 6GB when he has 12
> available. I suspect he may be in a cluster and if that is true and it is
> Active-Active then 6GB may be too much since AWE memory is fixed and can n
ot
> be dynamically adjusted.
>
My thinking was that there was only 6Gb and if you didn't use the /3Gb
switch, then the OS would effectively take the full 2Gb available to it,
leaving 4Gb for applications such as SQL. While using the /3Gb switch
would limit the OS to 1Gb, thereby allowing SQL to use a full 5Gb.
However, now I see that there is actually 12Gb...
With 12Gb you don't need the /3Gb switch in order to allow SQL to see
6Gb (actually it could use 10Gb), however, I have to wonder why you want
to limit SQL to 6Gb when you have 12?
I think my recommendation still stands though, because if I recall, it's
not until you have over 16Gb of RAM that the OS has to use that extra Gb
of space to maintain the Address Extensions.
So, using the /3Gb switch would allow SQL to use up to 11Gb instead of
10Gb without it. Although I'd keep a close eye on things to make sure
that the OS is not having to do a bunch of swapping. In certain
circumstances the OS might be able to make better use of that Gb than SQL.|||> I think my recommendation still stands though, because if I recall, it's
> not until you have over 16Gb of RAM that the OS has to use that extra Gb
> of space to maintain the Address Extensions.
I believe that Unisys has recommendations to not use /3GB if you have > 12GB
in the machine.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Don Peterson" <sgtp.usmc@.gmail.com> wrote in message news:uhqohQYbHHA.588@.TK2MSFTNGP06.phx.
gbl...
> Andrew J. Kelly wrote:
> My thinking was that there was only 6Gb and if you didn't use the /3Gb
> switch, then the OS would effectively take the full 2Gb available to it,
> leaving 4Gb for applications such as SQL. While using the /3Gb switch
> would limit the OS to 1Gb, thereby allowing SQL to use a full 5Gb.
> However, now I see that there is actually 12Gb...
> With 12Gb you don't need the /3Gb switch in order to allow SQL to see
> 6Gb (actually it could use 10Gb), however, I have to wonder why you want
> to limit SQL to 6Gb when you have 12?
> I think my recommendation still stands though, because if I recall, it's
> not until you have over 16Gb of RAM that the OS has to use that extra Gb
> of space to maintain the Address Extensions.
> So, using the /3Gb switch would allow SQL to use up to 11Gb instead of
> 10Gb without it. Although I'd keep a close eye on things to make sure
> that the OS is not having to do a bunch of swapping. In certain
> circumstances the OS might be able to make better use of that Gb than SQL.
awe question
We are going to turn on AWE on our production environment currently
its 4 processor box with 12GB of RAM and windows advanced server 2000
loaded on it. Now my question is do i need to use /PAE and /3GB
switches in our boot.ini in order to enable 6gigs to our sql server
or /PAE will okay?
kulkarni.ninad@.gmail.com wrote:
> We are going to turn on AWE on our production environment currently
> its 4 processor box with 12GB of RAM and windows advanced server 2000
> loaded on it. Now my question is do i need to use /PAE and /3GB
> switches in our boot.ini in order to enable 6gigs to our sql server
> or /PAE will okay?
>
With 6Gb of RAM, I would use the /3GB switch as well. That would
effectively allow SQL to use as much as 5GB. Without the /3Gb switch
you are pretty well limited to 4Gb.
|||That is not really true. /3GB allows SQL Server (or any properly enabled
application) to use up to 3GB of the 4GB of directly addressable memory but
limits the OS to 1GB of that same space. AWE and PAE are to allow the OS and
the App to see memory above 4GB and is totally independent of the /3GB
switch. The decision to use /3GB or not is dependent on how much of that
directly addressable memory you need to use. The only part of SQL Server
that can use memory above 2GB (or 3GB with the /3GB) is the data buffer
cache. I would question why he wants to use only 6GB when he has 12
available. I suspect he may be in a cluster and if that is true and it is
Active-Active then 6GB may be too much since AWE memory is fixed and can not
be dynamically adjusted.
Andrew J. Kelly SQL MVP
"Don Peterson" <sgtp.usmc@.gmail.com> wrote in message
news:uEmOq6LbHHA.5044@.TK2MSFTNGP05.phx.gbl...
> kulkarni.ninad@.gmail.com wrote:
> With 6Gb of RAM, I would use the /3GB switch as well. That would
> effectively allow SQL to use as much as 5GB. Without the /3Gb switch you
> are pretty well limited to 4Gb.
|||Andrew J. Kelly wrote:
> That is not really true. /3GB allows SQL Server (or any properly enabled
> application) to use up to 3GB of the 4GB of directly addressable memory but
> limits the OS to 1GB of that same space. AWE and PAE are to allow the OS and
> the App to see memory above 4GB and is totally independent of the /3GB
> switch. The decision to use /3GB or not is dependent on how much of that
> directly addressable memory you need to use. The only part of SQL Server
> that can use memory above 2GB (or 3GB with the /3GB) is the data buffer
> cache. I would question why he wants to use only 6GB when he has 12
> available. I suspect he may be in a cluster and if that is true and it is
> Active-Active then 6GB may be too much since AWE memory is fixed and can not
> be dynamically adjusted.
>
My thinking was that there was only 6Gb and if you didn't use the /3Gb
switch, then the OS would effectively take the full 2Gb available to it,
leaving 4Gb for applications such as SQL. While using the /3Gb switch
would limit the OS to 1Gb, thereby allowing SQL to use a full 5Gb.
However, now I see that there is actually 12Gb...
With 12Gb you don't need the /3Gb switch in order to allow SQL to see
6Gb (actually it could use 10Gb), however, I have to wonder why you want
to limit SQL to 6Gb when you have 12?
I think my recommendation still stands though, because if I recall, it's
not until you have over 16Gb of RAM that the OS has to use that extra Gb
of space to maintain the Address Extensions.
So, using the /3Gb switch would allow SQL to use up to 11Gb instead of
10Gb without it. Although I'd keep a close eye on things to make sure
that the OS is not having to do a bunch of swapping. In certain
circumstances the OS might be able to make better use of that Gb than SQL.
its 4 processor box with 12GB of RAM and windows advanced server 2000
loaded on it. Now my question is do i need to use /PAE and /3GB
switches in our boot.ini in order to enable 6gigs to our sql server
or /PAE will okay?
kulkarni.ninad@.gmail.com wrote:
> We are going to turn on AWE on our production environment currently
> its 4 processor box with 12GB of RAM and windows advanced server 2000
> loaded on it. Now my question is do i need to use /PAE and /3GB
> switches in our boot.ini in order to enable 6gigs to our sql server
> or /PAE will okay?
>
With 6Gb of RAM, I would use the /3GB switch as well. That would
effectively allow SQL to use as much as 5GB. Without the /3Gb switch
you are pretty well limited to 4Gb.
|||That is not really true. /3GB allows SQL Server (or any properly enabled
application) to use up to 3GB of the 4GB of directly addressable memory but
limits the OS to 1GB of that same space. AWE and PAE are to allow the OS and
the App to see memory above 4GB and is totally independent of the /3GB
switch. The decision to use /3GB or not is dependent on how much of that
directly addressable memory you need to use. The only part of SQL Server
that can use memory above 2GB (or 3GB with the /3GB) is the data buffer
cache. I would question why he wants to use only 6GB when he has 12
available. I suspect he may be in a cluster and if that is true and it is
Active-Active then 6GB may be too much since AWE memory is fixed and can not
be dynamically adjusted.
Andrew J. Kelly SQL MVP
"Don Peterson" <sgtp.usmc@.gmail.com> wrote in message
news:uEmOq6LbHHA.5044@.TK2MSFTNGP05.phx.gbl...
> kulkarni.ninad@.gmail.com wrote:
> With 6Gb of RAM, I would use the /3GB switch as well. That would
> effectively allow SQL to use as much as 5GB. Without the /3Gb switch you
> are pretty well limited to 4Gb.
|||Andrew J. Kelly wrote:
> That is not really true. /3GB allows SQL Server (or any properly enabled
> application) to use up to 3GB of the 4GB of directly addressable memory but
> limits the OS to 1GB of that same space. AWE and PAE are to allow the OS and
> the App to see memory above 4GB and is totally independent of the /3GB
> switch. The decision to use /3GB or not is dependent on how much of that
> directly addressable memory you need to use. The only part of SQL Server
> that can use memory above 2GB (or 3GB with the /3GB) is the data buffer
> cache. I would question why he wants to use only 6GB when he has 12
> available. I suspect he may be in a cluster and if that is true and it is
> Active-Active then 6GB may be too much since AWE memory is fixed and can not
> be dynamically adjusted.
>
My thinking was that there was only 6Gb and if you didn't use the /3Gb
switch, then the OS would effectively take the full 2Gb available to it,
leaving 4Gb for applications such as SQL. While using the /3Gb switch
would limit the OS to 1Gb, thereby allowing SQL to use a full 5Gb.
However, now I see that there is actually 12Gb...
With 12Gb you don't need the /3Gb switch in order to allow SQL to see
6Gb (actually it could use 10Gb), however, I have to wonder why you want
to limit SQL to 6Gb when you have 12?
I think my recommendation still stands though, because if I recall, it's
not until you have over 16Gb of RAM that the OS has to use that extra Gb
of space to maintain the Address Extensions.
So, using the /3Gb switch would allow SQL to use up to 11Gb instead of
10Gb without it. Although I'd keep a close eye on things to make sure
that the OS is not having to do a bunch of swapping. In certain
circumstances the OS might be able to make better use of that Gb than SQL.
awe question
We are going to turn on AWE on our production environment currently
its 4 processor box with 12GB of RAM and windows advanced server 2000
loaded on it. Now my question is do i need to use /PAE and /3GB
switches in our boot.ini in order to enable 6gigs to our sql server
or /PAE will okay?kulkarni.ninad@.gmail.com wrote:
> We are going to turn on AWE on our production environment currently
> its 4 processor box with 12GB of RAM and windows advanced server 2000
> loaded on it. Now my question is do i need to use /PAE and /3GB
> switches in our boot.ini in order to enable 6gigs to our sql server
> or /PAE will okay?
>
With 6Gb of RAM, I would use the /3GB switch as well. That would
effectively allow SQL to use as much as 5GB. Without the /3Gb switch
you are pretty well limited to 4Gb.|||That is not really true. /3GB allows SQL Server (or any properly enabled
application) to use up to 3GB of the 4GB of directly addressable memory but
limits the OS to 1GB of that same space. AWE and PAE are to allow the OS and
the App to see memory above 4GB and is totally independent of the /3GB
switch. The decision to use /3GB or not is dependent on how much of that
directly addressable memory you need to use. The only part of SQL Server
that can use memory above 2GB (or 3GB with the /3GB) is the data buffer
cache. I would question why he wants to use only 6GB when he has 12
available. I suspect he may be in a cluster and if that is true and it is
Active-Active then 6GB may be too much since AWE memory is fixed and can not
be dynamically adjusted.
--
Andrew J. Kelly SQL MVP
"Don Peterson" <sgtp.usmc@.gmail.com> wrote in message
news:uEmOq6LbHHA.5044@.TK2MSFTNGP05.phx.gbl...
> kulkarni.ninad@.gmail.com wrote:
>> We are going to turn on AWE on our production environment currently
>> its 4 processor box with 12GB of RAM and windows advanced server 2000
>> loaded on it. Now my question is do i need to use /PAE and /3GB
>> switches in our boot.ini in order to enable 6gigs to our sql server
>> or /PAE will okay?
> With 6Gb of RAM, I would use the /3GB switch as well. That would
> effectively allow SQL to use as much as 5GB. Without the /3Gb switch you
> are pretty well limited to 4Gb.|||Andrew J. Kelly wrote:
> That is not really true. /3GB allows SQL Server (or any properly enabled
> application) to use up to 3GB of the 4GB of directly addressable memory but
> limits the OS to 1GB of that same space. AWE and PAE are to allow the OS and
> the App to see memory above 4GB and is totally independent of the /3GB
> switch. The decision to use /3GB or not is dependent on how much of that
> directly addressable memory you need to use. The only part of SQL Server
> that can use memory above 2GB (or 3GB with the /3GB) is the data buffer
> cache. I would question why he wants to use only 6GB when he has 12
> available. I suspect he may be in a cluster and if that is true and it is
> Active-Active then 6GB may be too much since AWE memory is fixed and can not
> be dynamically adjusted.
>
My thinking was that there was only 6Gb and if you didn't use the /3Gb
switch, then the OS would effectively take the full 2Gb available to it,
leaving 4Gb for applications such as SQL. While using the /3Gb switch
would limit the OS to 1Gb, thereby allowing SQL to use a full 5Gb.
However, now I see that there is actually 12Gb...
With 12Gb you don't need the /3Gb switch in order to allow SQL to see
6Gb (actually it could use 10Gb), however, I have to wonder why you want
to limit SQL to 6Gb when you have 12?
I think my recommendation still stands though, because if I recall, it's
not until you have over 16Gb of RAM that the OS has to use that extra Gb
of space to maintain the Address Extensions.
So, using the /3Gb switch would allow SQL to use up to 11Gb instead of
10Gb without it. Although I'd keep a close eye on things to make sure
that the OS is not having to do a bunch of swapping. In certain
circumstances the OS might be able to make better use of that Gb than SQL.|||> I think my recommendation still stands though, because if I recall, it's
> not until you have over 16Gb of RAM that the OS has to use that extra Gb
> of space to maintain the Address Extensions.
I believe that Unisys has recommendations to not use /3GB if you have > 12GB in the machine.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Don Peterson" <sgtp.usmc@.gmail.com> wrote in message news:uhqohQYbHHA.588@.TK2MSFTNGP06.phx.gbl...
> Andrew J. Kelly wrote:
>> That is not really true. /3GB allows SQL Server (or any properly enabled
>> application) to use up to 3GB of the 4GB of directly addressable memory but
>> limits the OS to 1GB of that same space. AWE and PAE are to allow the OS and
>> the App to see memory above 4GB and is totally independent of the /3GB
>> switch. The decision to use /3GB or not is dependent on how much of that
>> directly addressable memory you need to use. The only part of SQL Server
>> that can use memory above 2GB (or 3GB with the /3GB) is the data buffer
>> cache. I would question why he wants to use only 6GB when he has 12
>> available. I suspect he may be in a cluster and if that is true and it is
>> Active-Active then 6GB may be too much since AWE memory is fixed and can not
>> be dynamically adjusted.
> My thinking was that there was only 6Gb and if you didn't use the /3Gb
> switch, then the OS would effectively take the full 2Gb available to it,
> leaving 4Gb for applications such as SQL. While using the /3Gb switch
> would limit the OS to 1Gb, thereby allowing SQL to use a full 5Gb.
> However, now I see that there is actually 12Gb...
> With 12Gb you don't need the /3Gb switch in order to allow SQL to see
> 6Gb (actually it could use 10Gb), however, I have to wonder why you want
> to limit SQL to 6Gb when you have 12?
> I think my recommendation still stands though, because if I recall, it's
> not until you have over 16Gb of RAM that the OS has to use that extra Gb
> of space to maintain the Address Extensions.
> So, using the /3Gb switch would allow SQL to use up to 11Gb instead of
> 10Gb without it. Although I'd keep a close eye on things to make sure
> that the OS is not having to do a bunch of swapping. In certain
> circumstances the OS might be able to make better use of that Gb than SQL.
its 4 processor box with 12GB of RAM and windows advanced server 2000
loaded on it. Now my question is do i need to use /PAE and /3GB
switches in our boot.ini in order to enable 6gigs to our sql server
or /PAE will okay?kulkarni.ninad@.gmail.com wrote:
> We are going to turn on AWE on our production environment currently
> its 4 processor box with 12GB of RAM and windows advanced server 2000
> loaded on it. Now my question is do i need to use /PAE and /3GB
> switches in our boot.ini in order to enable 6gigs to our sql server
> or /PAE will okay?
>
With 6Gb of RAM, I would use the /3GB switch as well. That would
effectively allow SQL to use as much as 5GB. Without the /3Gb switch
you are pretty well limited to 4Gb.|||That is not really true. /3GB allows SQL Server (or any properly enabled
application) to use up to 3GB of the 4GB of directly addressable memory but
limits the OS to 1GB of that same space. AWE and PAE are to allow the OS and
the App to see memory above 4GB and is totally independent of the /3GB
switch. The decision to use /3GB or not is dependent on how much of that
directly addressable memory you need to use. The only part of SQL Server
that can use memory above 2GB (or 3GB with the /3GB) is the data buffer
cache. I would question why he wants to use only 6GB when he has 12
available. I suspect he may be in a cluster and if that is true and it is
Active-Active then 6GB may be too much since AWE memory is fixed and can not
be dynamically adjusted.
--
Andrew J. Kelly SQL MVP
"Don Peterson" <sgtp.usmc@.gmail.com> wrote in message
news:uEmOq6LbHHA.5044@.TK2MSFTNGP05.phx.gbl...
> kulkarni.ninad@.gmail.com wrote:
>> We are going to turn on AWE on our production environment currently
>> its 4 processor box with 12GB of RAM and windows advanced server 2000
>> loaded on it. Now my question is do i need to use /PAE and /3GB
>> switches in our boot.ini in order to enable 6gigs to our sql server
>> or /PAE will okay?
> With 6Gb of RAM, I would use the /3GB switch as well. That would
> effectively allow SQL to use as much as 5GB. Without the /3Gb switch you
> are pretty well limited to 4Gb.|||Andrew J. Kelly wrote:
> That is not really true. /3GB allows SQL Server (or any properly enabled
> application) to use up to 3GB of the 4GB of directly addressable memory but
> limits the OS to 1GB of that same space. AWE and PAE are to allow the OS and
> the App to see memory above 4GB and is totally independent of the /3GB
> switch. The decision to use /3GB or not is dependent on how much of that
> directly addressable memory you need to use. The only part of SQL Server
> that can use memory above 2GB (or 3GB with the /3GB) is the data buffer
> cache. I would question why he wants to use only 6GB when he has 12
> available. I suspect he may be in a cluster and if that is true and it is
> Active-Active then 6GB may be too much since AWE memory is fixed and can not
> be dynamically adjusted.
>
My thinking was that there was only 6Gb and if you didn't use the /3Gb
switch, then the OS would effectively take the full 2Gb available to it,
leaving 4Gb for applications such as SQL. While using the /3Gb switch
would limit the OS to 1Gb, thereby allowing SQL to use a full 5Gb.
However, now I see that there is actually 12Gb...
With 12Gb you don't need the /3Gb switch in order to allow SQL to see
6Gb (actually it could use 10Gb), however, I have to wonder why you want
to limit SQL to 6Gb when you have 12?
I think my recommendation still stands though, because if I recall, it's
not until you have over 16Gb of RAM that the OS has to use that extra Gb
of space to maintain the Address Extensions.
So, using the /3Gb switch would allow SQL to use up to 11Gb instead of
10Gb without it. Although I'd keep a close eye on things to make sure
that the OS is not having to do a bunch of swapping. In certain
circumstances the OS might be able to make better use of that Gb than SQL.|||> I think my recommendation still stands though, because if I recall, it's
> not until you have over 16Gb of RAM that the OS has to use that extra Gb
> of space to maintain the Address Extensions.
I believe that Unisys has recommendations to not use /3GB if you have > 12GB in the machine.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Don Peterson" <sgtp.usmc@.gmail.com> wrote in message news:uhqohQYbHHA.588@.TK2MSFTNGP06.phx.gbl...
> Andrew J. Kelly wrote:
>> That is not really true. /3GB allows SQL Server (or any properly enabled
>> application) to use up to 3GB of the 4GB of directly addressable memory but
>> limits the OS to 1GB of that same space. AWE and PAE are to allow the OS and
>> the App to see memory above 4GB and is totally independent of the /3GB
>> switch. The decision to use /3GB or not is dependent on how much of that
>> directly addressable memory you need to use. The only part of SQL Server
>> that can use memory above 2GB (or 3GB with the /3GB) is the data buffer
>> cache. I would question why he wants to use only 6GB when he has 12
>> available. I suspect he may be in a cluster and if that is true and it is
>> Active-Active then 6GB may be too much since AWE memory is fixed and can not
>> be dynamically adjusted.
> My thinking was that there was only 6Gb and if you didn't use the /3Gb
> switch, then the OS would effectively take the full 2Gb available to it,
> leaving 4Gb for applications such as SQL. While using the /3Gb switch
> would limit the OS to 1Gb, thereby allowing SQL to use a full 5Gb.
> However, now I see that there is actually 12Gb...
> With 12Gb you don't need the /3Gb switch in order to allow SQL to see
> 6Gb (actually it could use 10Gb), however, I have to wonder why you want
> to limit SQL to 6Gb when you have 12?
> I think my recommendation still stands though, because if I recall, it's
> not until you have over 16Gb of RAM that the OS has to use that extra Gb
> of space to maintain the Address Extensions.
> So, using the /3Gb switch would allow SQL to use up to 11Gb instead of
> 10Gb without it. Although I'd keep a close eye on things to make sure
> that the OS is not having to do a bunch of swapping. In certain
> circumstances the OS might be able to make better use of that Gb than SQL.
AWE on 4GB multi-instance box?
Hi
We have a SQL server 2000 cluster, built on Windows 2000 Advanced server.
We are using an Active / Passive configuration which has 4 instances of SQL
Enterprise, and 1 Instance of IIS.
Both boxes are identical, and clustering works just fine.
However, in order to use the 4Gb in each box, should we enable the /3Gb
switch in Boot.Ini?
How then do we configure each instance of SQL Server? I believe that once
the /3Gb switch is enabled, that SQL will grab all the memory for itself,
leaving only 128Mb for the OS. We would probably need to specify a maximum
memory usage requirement, per instance, if that was to be the case.
There seems to be 2 options to me. Can anyone offer advice on whether we
should take:
1). Disable the AWE, via the removal of the /3Gb switch, and let SQL Server
manage its own memory
2). Enable AWE, via the use of the /3Gb switch, but then specify the maximum
memory each instance of SQL Server can use.
Does anyone have a view?
Thanks
Paul Aspinall
MCSD, MCDBA
Paul,
The /3GB switch does not enable or disable AWE. That is done via the AWE
option of sp_configure. If you have 4 actual instances of SQL Server plus
IIS on a single box with only 4GB of memory you certainly do not want to use
/3GB. You must share that 4GB with all 5 instances plus the OS and do not
want anything to swap to the swap file. Is there a particular reason to
have 4 separate instances vs. one with all the db's in it? It's hard to say
what the proper memory config should be given I know nothing about any of
your instances or how they are used. You might simply want to stay with
dynamic memory config on all 4 with no /3GB. AWE is only really useful with
more than 4GB anyway. It's IIS that I would be most worried about since it
doesn't play as well with others as SQL Server does.
Andrew J. Kelly SQL MVP
"Paul Aspinall" <paul@.aspy.co.uk> wrote in message
news:oG4Be.28164$O22.20831@.fe1.news.blueyonder.co. uk...
> Hi
> We have a SQL server 2000 cluster, built on Windows 2000 Advanced server.
> We are using an Active / Passive configuration which has 4 instances of
> SQL Enterprise, and 1 Instance of IIS.
> Both boxes are identical, and clustering works just fine.
> However, in order to use the 4Gb in each box, should we enable the /3Gb
> switch in Boot.Ini?
> How then do we configure each instance of SQL Server? I believe that once
> the /3Gb switch is enabled, that SQL will grab all the memory for itself,
> leaving only 128Mb for the OS. We would probably need to specify a maximum
> memory usage requirement, per instance, if that was to be the case.
> There seems to be 2 options to me. Can anyone offer advice on whether we
> should take:
> 1). Disable the AWE, via the removal of the /3Gb switch, and let SQL
> Server manage its own memory
> 2). Enable AWE, via the use of the /3Gb switch, but then specify the
> maximum memory each instance of SQL Server can use.
>
> Does anyone have a view?
> Thanks
>
> Paul Aspinall
> MCSD, MCDBA
>
We have a SQL server 2000 cluster, built on Windows 2000 Advanced server.
We are using an Active / Passive configuration which has 4 instances of SQL
Enterprise, and 1 Instance of IIS.
Both boxes are identical, and clustering works just fine.
However, in order to use the 4Gb in each box, should we enable the /3Gb
switch in Boot.Ini?
How then do we configure each instance of SQL Server? I believe that once
the /3Gb switch is enabled, that SQL will grab all the memory for itself,
leaving only 128Mb for the OS. We would probably need to specify a maximum
memory usage requirement, per instance, if that was to be the case.
There seems to be 2 options to me. Can anyone offer advice on whether we
should take:
1). Disable the AWE, via the removal of the /3Gb switch, and let SQL Server
manage its own memory
2). Enable AWE, via the use of the /3Gb switch, but then specify the maximum
memory each instance of SQL Server can use.
Does anyone have a view?
Thanks
Paul Aspinall
MCSD, MCDBA
Paul,
The /3GB switch does not enable or disable AWE. That is done via the AWE
option of sp_configure. If you have 4 actual instances of SQL Server plus
IIS on a single box with only 4GB of memory you certainly do not want to use
/3GB. You must share that 4GB with all 5 instances plus the OS and do not
want anything to swap to the swap file. Is there a particular reason to
have 4 separate instances vs. one with all the db's in it? It's hard to say
what the proper memory config should be given I know nothing about any of
your instances or how they are used. You might simply want to stay with
dynamic memory config on all 4 with no /3GB. AWE is only really useful with
more than 4GB anyway. It's IIS that I would be most worried about since it
doesn't play as well with others as SQL Server does.
Andrew J. Kelly SQL MVP
"Paul Aspinall" <paul@.aspy.co.uk> wrote in message
news:oG4Be.28164$O22.20831@.fe1.news.blueyonder.co. uk...
> Hi
> We have a SQL server 2000 cluster, built on Windows 2000 Advanced server.
> We are using an Active / Passive configuration which has 4 instances of
> SQL Enterprise, and 1 Instance of IIS.
> Both boxes are identical, and clustering works just fine.
> However, in order to use the 4Gb in each box, should we enable the /3Gb
> switch in Boot.Ini?
> How then do we configure each instance of SQL Server? I believe that once
> the /3Gb switch is enabled, that SQL will grab all the memory for itself,
> leaving only 128Mb for the OS. We would probably need to specify a maximum
> memory usage requirement, per instance, if that was to be the case.
> There seems to be 2 options to me. Can anyone offer advice on whether we
> should take:
> 1). Disable the AWE, via the removal of the /3Gb switch, and let SQL
> Server manage its own memory
> 2). Enable AWE, via the use of the /3Gb switch, but then specify the
> maximum memory each instance of SQL Server can use.
>
> Does anyone have a view?
> Thanks
>
> Paul Aspinall
> MCSD, MCDBA
>
Wednesday, March 7, 2012
AWE Enabled + Security Settings set but still no memory increase?
Hi,
If anyone has come across a similar issue, any help would
be appreciated . We have a WIN2k3 Server (not advanced)
running SQL 2k Enterprise. We have installed an
additional 2gb worth of memory ramping it up to 4gb. We
have set up AWE enabled settings on SQL 2k and adjusted
the local security settings. Problem is that we never
get above 2gb on our memory usage? Can someone explain
why this is happening and maybe highlight something that
I have missed? The reason for installing the additional
memory was to reduce dts loads and sql qry times that hit
2gb memory capacity on a daily basis?!?! Why doesnt sql
use the additional 2gb?!!
Thanks in advance
PaulI presume you have increased the maximum amount of memory SQL Server can use
(Enterprise manages, right click server, properties, memory)? Does it
recognise there is now 4gb in the machine?
"Paul" <ptel4d@.hotmail.com> wrote in message
news:073701c351bd$5eee7320$a601280a@.phx.gbl...
> Hi,
> If anyone has come across a similar issue, any help would
> be appreciated . We have a WIN2k3 Server (not advanced)
> running SQL 2k Enterprise. We have installed an
> additional 2gb worth of memory ramping it up to 4gb. We
> have set up AWE enabled settings on SQL 2k and adjusted
> the local security settings. Problem is that we never
> get above 2gb on our memory usage? Can someone explain
> why this is happening and maybe highlight something that
> I have missed? The reason for installing the additional
> memory was to reduce dts loads and sql qry times that hit
> 2gb memory capacity on a daily basis?!?! Why doesnt sql
> use the additional 2gb?!!
> Thanks in advance
> Paul
If anyone has come across a similar issue, any help would
be appreciated . We have a WIN2k3 Server (not advanced)
running SQL 2k Enterprise. We have installed an
additional 2gb worth of memory ramping it up to 4gb. We
have set up AWE enabled settings on SQL 2k and adjusted
the local security settings. Problem is that we never
get above 2gb on our memory usage? Can someone explain
why this is happening and maybe highlight something that
I have missed? The reason for installing the additional
memory was to reduce dts loads and sql qry times that hit
2gb memory capacity on a daily basis?!?! Why doesnt sql
use the additional 2gb?!!
Thanks in advance
PaulI presume you have increased the maximum amount of memory SQL Server can use
(Enterprise manages, right click server, properties, memory)? Does it
recognise there is now 4gb in the machine?
"Paul" <ptel4d@.hotmail.com> wrote in message
news:073701c351bd$5eee7320$a601280a@.phx.gbl...
> Hi,
> If anyone has come across a similar issue, any help would
> be appreciated . We have a WIN2k3 Server (not advanced)
> running SQL 2k Enterprise. We have installed an
> additional 2gb worth of memory ramping it up to 4gb. We
> have set up AWE enabled settings on SQL 2k and adjusted
> the local security settings. Problem is that we never
> get above 2gb on our memory usage? Can someone explain
> why this is happening and maybe highlight something that
> I have missed? The reason for installing the additional
> memory was to reduce dts loads and sql qry times that hit
> 2gb memory capacity on a daily basis?!?! Why doesnt sql
> use the additional 2gb?!!
> Thanks in advance
> Paul
AWE and SQL Cluster
Hi, I have the following questions regarding AWE on SQL 2000 with Windows
2000 Advanced Server and on a cluster (both nodes have an active SQL Server
instance running). The SA has upgraded the memory of both nodes to 8G each.
And I am tempted to use AWE to boost my SQL Server performance. The server
is dedicated to running SQL Server.
After AWE is enabled, can I configure my SQL Server max memory to something
like 7G?
Concern is, while it may run OK during normal operating mode, what will
happen in case both SQL Servers are moved to one node?
Thanks very much in advance.
Sean
I would definately suggest using the AWE enabled option set to true.
especially if each node is dedicated to sql server. you will have to
restart the sql services to have the changes take place. check the
BOL: awe enabled option and the section managing awe memory.
Sean T Shen wrote:
> Hi, I have the following questions regarding AWE on SQL 2000 with Windows
> 2000 Advanced Server and on a cluster (both nodes have an active SQL Server
> instance running). The SA has upgraded the memory of both nodes to 8G each.
> And I am tempted to use AWE to boost my SQL Server performance. The server
> is dedicated to running SQL Server.
> After AWE is enabled, can I configure my SQL Server max memory to something
> like 7G?
> Concern is, while it may run OK during normal operating mode, what will
> happen in case both SQL Servers are moved to one node?
> Thanks very much in advance.
> Sean
|||As I understand your situation, by use AWE and setting max memory to 7 GB
for the active virtual server, there will be no memory available in case of
a 'fail-over' (With AWE, the OS requires 1 GB).
I believe that you need to set max memory to an amount that leaves adequate
memory for the other virtual server in case of fail-over.
From:
http://www.microsoft.com/technet/pro...y/hasog01.mspx
Allocating Memory
If you are running multiple instances of SQL Server on separate nodes in the
cluster, you must determine the total amount of physical memory required for
each server and determine the appropriate allocation of memory for each SQL
Server instance. Performance of SQL Server is directly related to the amount
of physical memory allocated to it by the operating system. SQL Server
provides two modes for memory management: dynamic and fixed. Dynamic memory
allocation lets SQL acquire and release memory from the operating system as
needed. Dynamic memory allows you to set a maximum amount of physical memory
that the operating system can allocate to SQL Server. This allows the
remaining memory to be reserved for other purposes, such as the operating
system and other SQL Server instances. Fixed memory allows you to set a
specific amount of physical memory that SQL Server can use. This amount
neither grows nor shrinks.
Configure each server with the same amount of physical memory, and then
allocate memory to each SQL Server instance to ensure that sufficient memory
is available to support the failover of a SQL Server instance from another
node. If a server does not use a lot of memory compared to the available
memory on the server, specify dynamic memory and set the maximum amount of
memory that the operating system can allocate. By doing so, you leave
sufficient memory for the failover to occur quickly and successfully. If an
instance uses all of the available physical memory, failover will occur more
slowly and can fail if memory is not released quickly enough.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"GlennThomas5" <glennthomas5@.gmail.com> wrote in message
news:1158944385.401752.80530@.e3g2000cwe.googlegrou ps.com...
>I would definately suggest using the AWE enabled option set to true.
> especially if each node is dedicated to sql server. you will have to
> restart the sql services to have the changes take place. check the
> BOL: awe enabled option and the section managing awe memory.
> Sean T Shen wrote:
>
|||Let me add to that, as I understand it, that if you use 4 GB or less for a
SQL Server instance, by enabling AWE, you will experience a net loss.
See:
"AWE is a set of memory management extensions to the Microsoft Win32? API
that allows applications to address memory beyond 4 GB."
Memory -AWE, Not usable < 4GB
http://download.microsoft.com/downlo...dv64BitEnv.doc
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:usYDLvm3GHA.5032@.TK2MSFTNGP04.phx.gbl...
> As I understand your situation, by use AWE and setting max memory to 7 GB
> for the active virtual server, there will be no memory available in case
> of a 'fail-over' (With AWE, the OS requires 1 GB).
> I believe that you need to set max memory to an amount that leaves
> adequate memory for the other virtual server in case of fail-over.
> From:
> http://www.microsoft.com/technet/pro...y/hasog01.mspx
> Allocating Memory
> If you are running multiple instances of SQL Server on separate nodes in
> the cluster, you must determine the total amount of physical memory
> required for each server and determine the appropriate allocation of
> memory for each SQL Server instance. Performance of SQL Server is directly
> related to the amount of physical memory allocated to it by the operating
> system. SQL Server provides two modes for memory management: dynamic and
> fixed. Dynamic memory allocation lets SQL acquire and release memory from
> the operating system as needed. Dynamic memory allows you to set a maximum
> amount of physical memory that the operating system can allocate to SQL
> Server. This allows the remaining memory to be reserved for other
> purposes, such as the operating system and other SQL Server instances.
> Fixed memory allows you to set a specific amount of physical memory that
> SQL Server can use. This amount neither grows nor shrinks.
> Configure each server with the same amount of physical memory, and then
> allocate memory to each SQL Server instance to ensure that sufficient
> memory is available to support the failover of a SQL Server instance from
> another node. If a server does not use a lot of memory compared to the
> available memory on the server, specify dynamic memory and set the maximum
> amount of memory that the operating system can allocate. By doing so, you
> leave sufficient memory for the failover to occur quickly and
> successfully. If an instance uses all of the available physical memory,
> failover will occur more slowly and can fail if memory is not released
> quickly enough.
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
>
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "GlennThomas5" <glennthomas5@.gmail.com> wrote in message
> news:1158944385.401752.80530@.e3g2000cwe.googlegrou ps.com...
>
|||I think your right about the 1GB for the OS on clustered services...he
definately needs to factor in outside things such as the clustered
services, other applications, etc.
Arnie Rowland wrote:[vbcol=seagreen]
> As I understand your situation, by use AWE and setting max memory to 7 GB
> for the active virtual server, there will be no memory available in case of
> a 'fail-over' (With AWE, the OS requires 1 GB).
> I believe that you need to set max memory to an amount that leaves adequate
> memory for the other virtual server in case of fail-over.
> From:
> http://www.microsoft.com/technet/pro...y/hasog01.mspx
> Allocating Memory
> If you are running multiple instances of SQL Server on separate nodes in the
> cluster, you must determine the total amount of physical memory required for
> each server and determine the appropriate allocation of memory for each SQL
> Server instance. Performance of SQL Server is directly related to the amount
> of physical memory allocated to it by the operating system. SQL Server
> provides two modes for memory management: dynamic and fixed. Dynamic memory
> allocation lets SQL acquire and release memory from the operating system as
> needed. Dynamic memory allows you to set a maximum amount of physical memory
> that the operating system can allocate to SQL Server. This allows the
> remaining memory to be reserved for other purposes, such as the operating
> system and other SQL Server instances. Fixed memory allows you to set a
> specific amount of physical memory that SQL Server can use. This amount
> neither grows nor shrinks.
> Configure each server with the same amount of physical memory, and then
> allocate memory to each SQL Server instance to ensure that sufficient memory
> is available to support the failover of a SQL Server instance from another
> node. If a server does not use a lot of memory compared to the available
> memory on the server, specify dynamic memory and set the maximum amount of
> memory that the operating system can allocate. By doing so, you leave
> sufficient memory for the failover to occur quickly and successfully. If an
> instance uses all of the available physical memory, failover will occur more
> slowly and can fail if memory is not released quickly enough.
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
>
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "GlennThomas5" <glennthomas5@.gmail.com> wrote in message
> news:1158944385.401752.80530@.e3g2000cwe.googlegrou ps.com...
|||I am facing a dilemma:
For each node, the total memory is 8G.
If I configure each SQL instance as having 3.5G, then when both of the SQL
instances running on the same node (the failover situation), I would have
3.5x2=7G total, dedicated, statically, to SQL Servers, leaving 1G for OS.
But, most of the time, I am running each SQL on each node, thus I am wasting
all this memory (8-3.5=4.5G for each node).
In this case, looks like I am better off not upgrading the physical memory.
If I just configure each node with 4G of memory each, and have SQL Server
dynamically eat up its uage (with a max server memory set to 3.5), then I
still will get 3.5G for each SQL Server. And when both instances fails over
to one side, SQL and OS will dynamically negotiate their memory needs.
Plus, I have one other cluster with AWE set. they each have 8G. So here is
what I did
Boot.ini --> /3GB /PAE --> reboot
SQL
sp_configure 'awe', 1
reconfigure
sp_configure 'max server memory', 3584 /* 1024x3.5 */
reconfigure
[I was worry about this failover situation, that is why I only set this to
3.5G to see what happens]
You would expect that after this and everything rebooted, I should see my
SQL Server statically grabs all the memory up to 3.5G. But it did not!!!!
Contrary to what I have read in articles.
Now my question is
1. In AWE, does or does not SQL Server use static memory allocation?
2. Will SQL Server ever take advantage of AWE, even if I counter it to use 6
or 7G?
3. Do I need to set anything else in sp_configure other than what I have
already done?
Thanks for the answer already provided, and additional thanks in advance for
any further info.
Sean
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:%238CVjzm3GHA.600@.TK2MSFTNGP05.phx.gbl...
> Let me add to that, as I understand it, that if you use 4 GB or less for a
> SQL Server instance, by enabling AWE, you will experience a net loss.
> See:
> "AWE is a set of memory management extensions to the Microsoft Win32? API
> that allows applications to address memory beyond 4 GB."
> Memory -AWE, Not usable < 4GB
> http://download.microsoft.com/downlo...dv64BitEnv.doc
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:usYDLvm3GHA.5032@.TK2MSFTNGP04.phx.gbl...
>
|||The bigger question is, can I even set anything up beyond 3.5G per each SQL
instance? If I do, will I not be able to move both SQL node resources to
one node (thus defeats the purpose of cluster HA protection)?
"GlennThomas5" <glennthomas5@.gmail.com> wrote in message
news:1158948261.902182.108570@.d34g2000cwd.googlegr oups.com...
>I think your right about the 1GB for the OS on clustered services...he
> definately needs to factor in outside things such as the clustered
> services, other applications, etc.
> Arnie Rowland wrote:
>
|||Actually, you are not 'wasting' any memory -you are 'buying' high
availability (fail-over) insurance -and the cost that insurance is reflected
in the price of licenses, increased hardware costs, memory, etc.
You have a couple of options. (these comments are NOT germane for 64 bit
Windows and 64 bit SQL Server.)
1. Set memory statically at 3.5 GB per node.
2. Set memory for SQL to dynamically acquire, with a min size of 3.5 GB and
a max size of, perhaps 6 GB. That will leave 1 GB for the failed over
virtual server to grab a starting point and then to negotiate with the OS
for more memory from the active virtual server.
Your other questions:
Don't use AWE unless a virtual server will have more than 4 GB -so option 1
above does not use AWE.
SQL Server will use memory about 2 GB for data cache only. Procedure cache,
and all other SQL memory needs come out of 2 GB
SQL Server does not immediately 'acquire' the max memory, it will increase
its demand from the OS as need requires.
SQL Server will use AWE memory above 4 GB for data cache only.
I hope this, and the articles I posted earlier, help.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Sean T Shen" <seantshen@.optonline.net> wrote in message
news:OU61lUn3GHA.1304@.TK2MSFTNGP05.phx.gbl...
>I am facing a dilemma:
> For each node, the total memory is 8G.
> If I configure each SQL instance as having 3.5G, then when both of the SQL
> instances running on the same node (the failover situation), I would have
> 3.5x2=7G total, dedicated, statically, to SQL Servers, leaving 1G for OS.
> But, most of the time, I am running each SQL on each node, thus I am
> wasting all this memory (8-3.5=4.5G for each node).
> In this case, looks like I am better off not upgrading the physical
> memory. If I just configure each node with 4G of memory each, and have SQL
> Server dynamically eat up its uage (with a max server memory set to 3.5),
> then I still will get 3.5G for each SQL Server. And when both instances
> fails over to one side, SQL and OS will dynamically negotiate their memory
> needs.
> Plus, I have one other cluster with AWE set. they each have 8G. So here
> is what I did
> Boot.ini --> /3GB /PAE --> reboot
> SQL
> sp_configure 'awe', 1
> reconfigure
> sp_configure 'max server memory', 3584 /* 1024x3.5 */
> reconfigure
> [I was worry about this failover situation, that is why I only set this to
> 3.5G to see what happens]
> You would expect that after this and everything rebooted, I should see my
> SQL Server statically grabs all the memory up to 3.5G. But it did not!!!!
> Contrary to what I have read in articles.
> Now my question is
> 1. In AWE, does or does not SQL Server use static memory allocation?
> 2. Will SQL Server ever take advantage of AWE, even if I counter it to use
> 6 or 7G?
> 3. Do I need to set anything else in sp_configure other than what I have
> already done?
> Thanks for the answer already provided, and additional thanks in advance
> for any further info.
> Sean
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:%238CVjzm3GHA.600@.TK2MSFTNGP05.phx.gbl...
>
|||That should have read 'above'.
> SQL Server will use memory above 2 GB for data cache only.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:%23Ttdlyn3GHA.476@.TK2MSFTNGP06.phx.gbl...
> Actually, you are not 'wasting' any memory -you are 'buying' high
> availability (fail-over) insurance -and the cost that insurance is
> reflected in the price of licenses, increased hardware costs, memory, etc.
> You have a couple of options. (these comments are NOT germane for 64 bit
> Windows and 64 bit SQL Server.)
> 1. Set memory statically at 3.5 GB per node.
> 2. Set memory for SQL to dynamically acquire, with a min size of 3.5 GB
> and a max size of, perhaps 6 GB. That will leave 1 GB for the failed over
> virtual server to grab a starting point and then to negotiate with the OS
> for more memory from the active virtual server.
> Your other questions:
> Don't use AWE unless a virtual server will have more than 4 GB -so option
> 1 above does not use AWE.
> SQL Server will use memory about 2 GB for data cache only. Procedure
> cache, and all other SQL memory needs come out of 2 GB
> SQL Server does not immediately 'acquire' the max memory, it will increase
> its demand from the OS as need requires.
> SQL Server will use AWE memory above 4 GB for data cache only.
> I hope this, and the articles I posted earlier, help.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Sean T Shen" <seantshen@.optonline.net> wrote in message
> news:OU61lUn3GHA.1304@.TK2MSFTNGP05.phx.gbl...
>
|||Not sure if I have successfully posted my reply.
Trying it again
Well, this is what I quote from a Microsoft article...do not have it handy
right now...but this is what it says
One caution about using the "awe enabled" setting is that after turning it
on, SQL Server no longer dynamically manages memory. Instead, it takes all
of the available RAM (except about 128MB which is left for the operating
system). If you want to prevent SQL Server from taking all of the RAM, you
must set the "max server memory" option (described in more detail later in
this article) to a figure that limits SQL Server to the amount or RAM you
specify.
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:%23Ttdlyn3GHA.476@.TK2MSFTNGP06.phx.gbl...
> Actually, you are not 'wasting' any memory -you are 'buying' high
> availability (fail-over) insurance -and the cost that insurance is
> reflected in the price of licenses, increased hardware costs, memory, etc.
> You have a couple of options. (these comments are NOT germane for 64 bit
> Windows and 64 bit SQL Server.)
> 1. Set memory statically at 3.5 GB per node.
> 2. Set memory for SQL to dynamically acquire, with a min size of 3.5 GB
> and a max size of, perhaps 6 GB. That will leave 1 GB for the failed over
> virtual server to grab a starting point and then to negotiate with the OS
> for more memory from the active virtual server.
> Your other questions:
> Don't use AWE unless a virtual server will have more than 4 GB -so option
> 1 above does not use AWE.
> SQL Server will use memory about 2 GB for data cache only. Procedure
> cache, and all other SQL memory needs come out of 2 GB
> SQL Server does not immediately 'acquire' the max memory, it will increase
> its demand from the OS as need requires.
> SQL Server will use AWE memory above 4 GB for data cache only.
> I hope this, and the articles I posted earlier, help.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Sean T Shen" <seantshen@.optonline.net> wrote in message
> news:OU61lUn3GHA.1304@.TK2MSFTNGP05.phx.gbl...
>
2000 Advanced Server and on a cluster (both nodes have an active SQL Server
instance running). The SA has upgraded the memory of both nodes to 8G each.
And I am tempted to use AWE to boost my SQL Server performance. The server
is dedicated to running SQL Server.
After AWE is enabled, can I configure my SQL Server max memory to something
like 7G?
Concern is, while it may run OK during normal operating mode, what will
happen in case both SQL Servers are moved to one node?
Thanks very much in advance.
Sean
I would definately suggest using the AWE enabled option set to true.
especially if each node is dedicated to sql server. you will have to
restart the sql services to have the changes take place. check the
BOL: awe enabled option and the section managing awe memory.
Sean T Shen wrote:
> Hi, I have the following questions regarding AWE on SQL 2000 with Windows
> 2000 Advanced Server and on a cluster (both nodes have an active SQL Server
> instance running). The SA has upgraded the memory of both nodes to 8G each.
> And I am tempted to use AWE to boost my SQL Server performance. The server
> is dedicated to running SQL Server.
> After AWE is enabled, can I configure my SQL Server max memory to something
> like 7G?
> Concern is, while it may run OK during normal operating mode, what will
> happen in case both SQL Servers are moved to one node?
> Thanks very much in advance.
> Sean
|||As I understand your situation, by use AWE and setting max memory to 7 GB
for the active virtual server, there will be no memory available in case of
a 'fail-over' (With AWE, the OS requires 1 GB).
I believe that you need to set max memory to an amount that leaves adequate
memory for the other virtual server in case of fail-over.
From:
http://www.microsoft.com/technet/pro...y/hasog01.mspx
Allocating Memory
If you are running multiple instances of SQL Server on separate nodes in the
cluster, you must determine the total amount of physical memory required for
each server and determine the appropriate allocation of memory for each SQL
Server instance. Performance of SQL Server is directly related to the amount
of physical memory allocated to it by the operating system. SQL Server
provides two modes for memory management: dynamic and fixed. Dynamic memory
allocation lets SQL acquire and release memory from the operating system as
needed. Dynamic memory allows you to set a maximum amount of physical memory
that the operating system can allocate to SQL Server. This allows the
remaining memory to be reserved for other purposes, such as the operating
system and other SQL Server instances. Fixed memory allows you to set a
specific amount of physical memory that SQL Server can use. This amount
neither grows nor shrinks.
Configure each server with the same amount of physical memory, and then
allocate memory to each SQL Server instance to ensure that sufficient memory
is available to support the failover of a SQL Server instance from another
node. If a server does not use a lot of memory compared to the available
memory on the server, specify dynamic memory and set the maximum amount of
memory that the operating system can allocate. By doing so, you leave
sufficient memory for the failover to occur quickly and successfully. If an
instance uses all of the available physical memory, failover will occur more
slowly and can fail if memory is not released quickly enough.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"GlennThomas5" <glennthomas5@.gmail.com> wrote in message
news:1158944385.401752.80530@.e3g2000cwe.googlegrou ps.com...
>I would definately suggest using the AWE enabled option set to true.
> especially if each node is dedicated to sql server. you will have to
> restart the sql services to have the changes take place. check the
> BOL: awe enabled option and the section managing awe memory.
> Sean T Shen wrote:
>
|||Let me add to that, as I understand it, that if you use 4 GB or less for a
SQL Server instance, by enabling AWE, you will experience a net loss.
See:
"AWE is a set of memory management extensions to the Microsoft Win32? API
that allows applications to address memory beyond 4 GB."
Memory -AWE, Not usable < 4GB
http://download.microsoft.com/downlo...dv64BitEnv.doc
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:usYDLvm3GHA.5032@.TK2MSFTNGP04.phx.gbl...
> As I understand your situation, by use AWE and setting max memory to 7 GB
> for the active virtual server, there will be no memory available in case
> of a 'fail-over' (With AWE, the OS requires 1 GB).
> I believe that you need to set max memory to an amount that leaves
> adequate memory for the other virtual server in case of fail-over.
> From:
> http://www.microsoft.com/technet/pro...y/hasog01.mspx
> Allocating Memory
> If you are running multiple instances of SQL Server on separate nodes in
> the cluster, you must determine the total amount of physical memory
> required for each server and determine the appropriate allocation of
> memory for each SQL Server instance. Performance of SQL Server is directly
> related to the amount of physical memory allocated to it by the operating
> system. SQL Server provides two modes for memory management: dynamic and
> fixed. Dynamic memory allocation lets SQL acquire and release memory from
> the operating system as needed. Dynamic memory allows you to set a maximum
> amount of physical memory that the operating system can allocate to SQL
> Server. This allows the remaining memory to be reserved for other
> purposes, such as the operating system and other SQL Server instances.
> Fixed memory allows you to set a specific amount of physical memory that
> SQL Server can use. This amount neither grows nor shrinks.
> Configure each server with the same amount of physical memory, and then
> allocate memory to each SQL Server instance to ensure that sufficient
> memory is available to support the failover of a SQL Server instance from
> another node. If a server does not use a lot of memory compared to the
> available memory on the server, specify dynamic memory and set the maximum
> amount of memory that the operating system can allocate. By doing so, you
> leave sufficient memory for the failover to occur quickly and
> successfully. If an instance uses all of the available physical memory,
> failover will occur more slowly and can fail if memory is not released
> quickly enough.
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
>
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "GlennThomas5" <glennthomas5@.gmail.com> wrote in message
> news:1158944385.401752.80530@.e3g2000cwe.googlegrou ps.com...
>
|||I think your right about the 1GB for the OS on clustered services...he
definately needs to factor in outside things such as the clustered
services, other applications, etc.
Arnie Rowland wrote:[vbcol=seagreen]
> As I understand your situation, by use AWE and setting max memory to 7 GB
> for the active virtual server, there will be no memory available in case of
> a 'fail-over' (With AWE, the OS requires 1 GB).
> I believe that you need to set max memory to an amount that leaves adequate
> memory for the other virtual server in case of fail-over.
> From:
> http://www.microsoft.com/technet/pro...y/hasog01.mspx
> Allocating Memory
> If you are running multiple instances of SQL Server on separate nodes in the
> cluster, you must determine the total amount of physical memory required for
> each server and determine the appropriate allocation of memory for each SQL
> Server instance. Performance of SQL Server is directly related to the amount
> of physical memory allocated to it by the operating system. SQL Server
> provides two modes for memory management: dynamic and fixed. Dynamic memory
> allocation lets SQL acquire and release memory from the operating system as
> needed. Dynamic memory allows you to set a maximum amount of physical memory
> that the operating system can allocate to SQL Server. This allows the
> remaining memory to be reserved for other purposes, such as the operating
> system and other SQL Server instances. Fixed memory allows you to set a
> specific amount of physical memory that SQL Server can use. This amount
> neither grows nor shrinks.
> Configure each server with the same amount of physical memory, and then
> allocate memory to each SQL Server instance to ensure that sufficient memory
> is available to support the failover of a SQL Server instance from another
> node. If a server does not use a lot of memory compared to the available
> memory on the server, specify dynamic memory and set the maximum amount of
> memory that the operating system can allocate. By doing so, you leave
> sufficient memory for the failover to occur quickly and successfully. If an
> instance uses all of the available physical memory, failover will occur more
> slowly and can fail if memory is not released quickly enough.
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
>
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "GlennThomas5" <glennthomas5@.gmail.com> wrote in message
> news:1158944385.401752.80530@.e3g2000cwe.googlegrou ps.com...
|||I am facing a dilemma:
For each node, the total memory is 8G.
If I configure each SQL instance as having 3.5G, then when both of the SQL
instances running on the same node (the failover situation), I would have
3.5x2=7G total, dedicated, statically, to SQL Servers, leaving 1G for OS.
But, most of the time, I am running each SQL on each node, thus I am wasting
all this memory (8-3.5=4.5G for each node).
In this case, looks like I am better off not upgrading the physical memory.
If I just configure each node with 4G of memory each, and have SQL Server
dynamically eat up its uage (with a max server memory set to 3.5), then I
still will get 3.5G for each SQL Server. And when both instances fails over
to one side, SQL and OS will dynamically negotiate their memory needs.
Plus, I have one other cluster with AWE set. they each have 8G. So here is
what I did
Boot.ini --> /3GB /PAE --> reboot
SQL
sp_configure 'awe', 1
reconfigure
sp_configure 'max server memory', 3584 /* 1024x3.5 */
reconfigure
[I was worry about this failover situation, that is why I only set this to
3.5G to see what happens]
You would expect that after this and everything rebooted, I should see my
SQL Server statically grabs all the memory up to 3.5G. But it did not!!!!
Contrary to what I have read in articles.
Now my question is
1. In AWE, does or does not SQL Server use static memory allocation?
2. Will SQL Server ever take advantage of AWE, even if I counter it to use 6
or 7G?
3. Do I need to set anything else in sp_configure other than what I have
already done?
Thanks for the answer already provided, and additional thanks in advance for
any further info.
Sean
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:%238CVjzm3GHA.600@.TK2MSFTNGP05.phx.gbl...
> Let me add to that, as I understand it, that if you use 4 GB or less for a
> SQL Server instance, by enabling AWE, you will experience a net loss.
> See:
> "AWE is a set of memory management extensions to the Microsoft Win32? API
> that allows applications to address memory beyond 4 GB."
> Memory -AWE, Not usable < 4GB
> http://download.microsoft.com/downlo...dv64BitEnv.doc
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:usYDLvm3GHA.5032@.TK2MSFTNGP04.phx.gbl...
>
|||The bigger question is, can I even set anything up beyond 3.5G per each SQL
instance? If I do, will I not be able to move both SQL node resources to
one node (thus defeats the purpose of cluster HA protection)?
"GlennThomas5" <glennthomas5@.gmail.com> wrote in message
news:1158948261.902182.108570@.d34g2000cwd.googlegr oups.com...
>I think your right about the 1GB for the OS on clustered services...he
> definately needs to factor in outside things such as the clustered
> services, other applications, etc.
> Arnie Rowland wrote:
>
|||Actually, you are not 'wasting' any memory -you are 'buying' high
availability (fail-over) insurance -and the cost that insurance is reflected
in the price of licenses, increased hardware costs, memory, etc.
You have a couple of options. (these comments are NOT germane for 64 bit
Windows and 64 bit SQL Server.)
1. Set memory statically at 3.5 GB per node.
2. Set memory for SQL to dynamically acquire, with a min size of 3.5 GB and
a max size of, perhaps 6 GB. That will leave 1 GB for the failed over
virtual server to grab a starting point and then to negotiate with the OS
for more memory from the active virtual server.
Your other questions:
Don't use AWE unless a virtual server will have more than 4 GB -so option 1
above does not use AWE.
SQL Server will use memory about 2 GB for data cache only. Procedure cache,
and all other SQL memory needs come out of 2 GB
SQL Server does not immediately 'acquire' the max memory, it will increase
its demand from the OS as need requires.
SQL Server will use AWE memory above 4 GB for data cache only.
I hope this, and the articles I posted earlier, help.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Sean T Shen" <seantshen@.optonline.net> wrote in message
news:OU61lUn3GHA.1304@.TK2MSFTNGP05.phx.gbl...
>I am facing a dilemma:
> For each node, the total memory is 8G.
> If I configure each SQL instance as having 3.5G, then when both of the SQL
> instances running on the same node (the failover situation), I would have
> 3.5x2=7G total, dedicated, statically, to SQL Servers, leaving 1G for OS.
> But, most of the time, I am running each SQL on each node, thus I am
> wasting all this memory (8-3.5=4.5G for each node).
> In this case, looks like I am better off not upgrading the physical
> memory. If I just configure each node with 4G of memory each, and have SQL
> Server dynamically eat up its uage (with a max server memory set to 3.5),
> then I still will get 3.5G for each SQL Server. And when both instances
> fails over to one side, SQL and OS will dynamically negotiate their memory
> needs.
> Plus, I have one other cluster with AWE set. they each have 8G. So here
> is what I did
> Boot.ini --> /3GB /PAE --> reboot
> SQL
> sp_configure 'awe', 1
> reconfigure
> sp_configure 'max server memory', 3584 /* 1024x3.5 */
> reconfigure
> [I was worry about this failover situation, that is why I only set this to
> 3.5G to see what happens]
> You would expect that after this and everything rebooted, I should see my
> SQL Server statically grabs all the memory up to 3.5G. But it did not!!!!
> Contrary to what I have read in articles.
> Now my question is
> 1. In AWE, does or does not SQL Server use static memory allocation?
> 2. Will SQL Server ever take advantage of AWE, even if I counter it to use
> 6 or 7G?
> 3. Do I need to set anything else in sp_configure other than what I have
> already done?
> Thanks for the answer already provided, and additional thanks in advance
> for any further info.
> Sean
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:%238CVjzm3GHA.600@.TK2MSFTNGP05.phx.gbl...
>
|||That should have read 'above'.
> SQL Server will use memory above 2 GB for data cache only.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:%23Ttdlyn3GHA.476@.TK2MSFTNGP06.phx.gbl...
> Actually, you are not 'wasting' any memory -you are 'buying' high
> availability (fail-over) insurance -and the cost that insurance is
> reflected in the price of licenses, increased hardware costs, memory, etc.
> You have a couple of options. (these comments are NOT germane for 64 bit
> Windows and 64 bit SQL Server.)
> 1. Set memory statically at 3.5 GB per node.
> 2. Set memory for SQL to dynamically acquire, with a min size of 3.5 GB
> and a max size of, perhaps 6 GB. That will leave 1 GB for the failed over
> virtual server to grab a starting point and then to negotiate with the OS
> for more memory from the active virtual server.
> Your other questions:
> Don't use AWE unless a virtual server will have more than 4 GB -so option
> 1 above does not use AWE.
> SQL Server will use memory about 2 GB for data cache only. Procedure
> cache, and all other SQL memory needs come out of 2 GB
> SQL Server does not immediately 'acquire' the max memory, it will increase
> its demand from the OS as need requires.
> SQL Server will use AWE memory above 4 GB for data cache only.
> I hope this, and the articles I posted earlier, help.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Sean T Shen" <seantshen@.optonline.net> wrote in message
> news:OU61lUn3GHA.1304@.TK2MSFTNGP05.phx.gbl...
>
|||Not sure if I have successfully posted my reply.
Trying it again
Well, this is what I quote from a Microsoft article...do not have it handy
right now...but this is what it says
One caution about using the "awe enabled" setting is that after turning it
on, SQL Server no longer dynamically manages memory. Instead, it takes all
of the available RAM (except about 128MB which is left for the operating
system). If you want to prevent SQL Server from taking all of the RAM, you
must set the "max server memory" option (described in more detail later in
this article) to a figure that limits SQL Server to the amount or RAM you
specify.
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:%23Ttdlyn3GHA.476@.TK2MSFTNGP06.phx.gbl...
> Actually, you are not 'wasting' any memory -you are 'buying' high
> availability (fail-over) insurance -and the cost that insurance is
> reflected in the price of licenses, increased hardware costs, memory, etc.
> You have a couple of options. (these comments are NOT germane for 64 bit
> Windows and 64 bit SQL Server.)
> 1. Set memory statically at 3.5 GB per node.
> 2. Set memory for SQL to dynamically acquire, with a min size of 3.5 GB
> and a max size of, perhaps 6 GB. That will leave 1 GB for the failed over
> virtual server to grab a starting point and then to negotiate with the OS
> for more memory from the active virtual server.
> Your other questions:
> Don't use AWE unless a virtual server will have more than 4 GB -so option
> 1 above does not use AWE.
> SQL Server will use memory about 2 GB for data cache only. Procedure
> cache, and all other SQL memory needs come out of 2 GB
> SQL Server does not immediately 'acquire' the max memory, it will increase
> its demand from the OS as need requires.
> SQL Server will use AWE memory above 4 GB for data cache only.
> I hope this, and the articles I posted earlier, help.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Sean T Shen" <seantshen@.optonline.net> wrote in message
> news:OU61lUn3GHA.1304@.TK2MSFTNGP05.phx.gbl...
>
AWE and SQL Cluster
Hi, I have the following questions regarding AWE on SQL 2000 with Windows
2000 Advanced Server and on a cluster (both nodes have an active SQL Server
instance running). The SA has upgraded the memory of both nodes to 8G each.
And I am tempted to use AWE to boost my SQL Server performance. The server
is dedicated to running SQL Server.
After AWE is enabled, can I configure my SQL Server max memory to something
like 7G?
Concern is, while it may run OK during normal operating mode, what will
happen in case both SQL Servers are moved to one node?
Thanks very much in advance.
SeanI would definately suggest using the AWE enabled option set to true.
especially if each node is dedicated to sql server. you will have to
restart the sql services to have the changes take place. check the
BOL: awe enabled option and the section managing awe memory.
Sean T Shen wrote:
> Hi, I have the following questions regarding AWE on SQL 2000 with Windows
> 2000 Advanced Server and on a cluster (both nodes have an active SQL Server
> instance running). The SA has upgraded the memory of both nodes to 8G each.
> And I am tempted to use AWE to boost my SQL Server performance. The server
> is dedicated to running SQL Server.
> After AWE is enabled, can I configure my SQL Server max memory to something
> like 7G?
> Concern is, while it may run OK during normal operating mode, what will
> happen in case both SQL Servers are moved to one node?
> Thanks very much in advance.
> Sean|||As I understand your situation, by use AWE and setting max memory to 7 GB
for the active virtual server, there will be no memory available in case of
a 'fail-over' (With AWE, the OS requires 1 GB).
I believe that you need to set max memory to an amount that leaves adequate
memory for the other virtual server in case of fail-over.
From:
http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/hasog01.mspx
Allocating Memory
If you are running multiple instances of SQL Server on separate nodes in the
cluster, you must determine the total amount of physical memory required for
each server and determine the appropriate allocation of memory for each SQL
Server instance. Performance of SQL Server is directly related to the amount
of physical memory allocated to it by the operating system. SQL Server
provides two modes for memory management: dynamic and fixed. Dynamic memory
allocation lets SQL acquire and release memory from the operating system as
needed. Dynamic memory allows you to set a maximum amount of physical memory
that the operating system can allocate to SQL Server. This allows the
remaining memory to be reserved for other purposes, such as the operating
system and other SQL Server instances. Fixed memory allows you to set a
specific amount of physical memory that SQL Server can use. This amount
neither grows nor shrinks.
Configure each server with the same amount of physical memory, and then
allocate memory to each SQL Server instance to ensure that sufficient memory
is available to support the failover of a SQL Server instance from another
node. If a server does not use a lot of memory compared to the available
memory on the server, specify dynamic memory and set the maximum amount of
memory that the operating system can allocate. By doing so, you leave
sufficient memory for the failover to occur quickly and successfully. If an
instance uses all of the available physical memory, failover will occur more
slowly and can fail if memory is not released quickly enough.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"GlennThomas5" <glennthomas5@.gmail.com> wrote in message
news:1158944385.401752.80530@.e3g2000cwe.googlegroups.com...
>I would definately suggest using the AWE enabled option set to true.
> especially if each node is dedicated to sql server. you will have to
> restart the sql services to have the changes take place. check the
> BOL: awe enabled option and the section managing awe memory.
> Sean T Shen wrote:
>> Hi, I have the following questions regarding AWE on SQL 2000 with Windows
>> 2000 Advanced Server and on a cluster (both nodes have an active SQL
>> Server
>> instance running). The SA has upgraded the memory of both nodes to 8G
>> each.
>> And I am tempted to use AWE to boost my SQL Server performance. The
>> server
>> is dedicated to running SQL Server.
>> After AWE is enabled, can I configure my SQL Server max memory to
>> something
>> like 7G?
>> Concern is, while it may run OK during normal operating mode, what
>> will
>> happen in case both SQL Servers are moved to one node?
>> Thanks very much in advance.
>> Sean
>|||Let me add to that, as I understand it, that if you use 4 GB or less for a
SQL Server instance, by enabling AWE, you will experience a net loss.
See:
"AWE is a set of memory management extensions to the Microsoft Win32? API
that allows applications to address memory beyond 4 GB."
Memory -AWE, Not usable < 4GB
http://download.microsoft.com/download/9/c/c/9cc42e30-538b-4451-8fdb-7134a004f94c/Adv64BitEnv.doc
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:usYDLvm3GHA.5032@.TK2MSFTNGP04.phx.gbl...
> As I understand your situation, by use AWE and setting max memory to 7 GB
> for the active virtual server, there will be no memory available in case
> of a 'fail-over' (With AWE, the OS requires 1 GB).
> I believe that you need to set max memory to an amount that leaves
> adequate memory for the other virtual server in case of fail-over.
> From:
> http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/hasog01.mspx
> Allocating Memory
> If you are running multiple instances of SQL Server on separate nodes in
> the cluster, you must determine the total amount of physical memory
> required for each server and determine the appropriate allocation of
> memory for each SQL Server instance. Performance of SQL Server is directly
> related to the amount of physical memory allocated to it by the operating
> system. SQL Server provides two modes for memory management: dynamic and
> fixed. Dynamic memory allocation lets SQL acquire and release memory from
> the operating system as needed. Dynamic memory allows you to set a maximum
> amount of physical memory that the operating system can allocate to SQL
> Server. This allows the remaining memory to be reserved for other
> purposes, such as the operating system and other SQL Server instances.
> Fixed memory allows you to set a specific amount of physical memory that
> SQL Server can use. This amount neither grows nor shrinks.
> Configure each server with the same amount of physical memory, and then
> allocate memory to each SQL Server instance to ensure that sufficient
> memory is available to support the failover of a SQL Server instance from
> another node. If a server does not use a lot of memory compared to the
> available memory on the server, specify dynamic memory and set the maximum
> amount of memory that the operating system can allocate. By doing so, you
> leave sufficient memory for the failover to occur quickly and
> successfully. If an instance uses all of the available physical memory,
> failover will occur more slowly and can fail if memory is not released
> quickly enough.
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
>
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "GlennThomas5" <glennthomas5@.gmail.com> wrote in message
> news:1158944385.401752.80530@.e3g2000cwe.googlegroups.com...
>>I would definately suggest using the AWE enabled option set to true.
>> especially if each node is dedicated to sql server. you will have to
>> restart the sql services to have the changes take place. check the
>> BOL: awe enabled option and the section managing awe memory.
>> Sean T Shen wrote:
>> Hi, I have the following questions regarding AWE on SQL 2000 with
>> Windows
>> 2000 Advanced Server and on a cluster (both nodes have an active SQL
>> Server
>> instance running). The SA has upgraded the memory of both nodes to 8G
>> each.
>> And I am tempted to use AWE to boost my SQL Server performance. The
>> server
>> is dedicated to running SQL Server.
>> After AWE is enabled, can I configure my SQL Server max memory to
>> something
>> like 7G?
>> Concern is, while it may run OK during normal operating mode, what
>> will
>> happen in case both SQL Servers are moved to one node?
>> Thanks very much in advance.
>> Sean
>|||I think your right about the 1GB for the OS on clustered services...he
definately needs to factor in outside things such as the clustered
services, other applications, etc.
Arnie Rowland wrote:
> As I understand your situation, by use AWE and setting max memory to 7 GB
> for the active virtual server, there will be no memory available in case of
> a 'fail-over' (With AWE, the OS requires 1 GB).
> I believe that you need to set max memory to an amount that leaves adequate
> memory for the other virtual server in case of fail-over.
> From:
> http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/hasog01.mspx
> Allocating Memory
> If you are running multiple instances of SQL Server on separate nodes in the
> cluster, you must determine the total amount of physical memory required for
> each server and determine the appropriate allocation of memory for each SQL
> Server instance. Performance of SQL Server is directly related to the amount
> of physical memory allocated to it by the operating system. SQL Server
> provides two modes for memory management: dynamic and fixed. Dynamic memory
> allocation lets SQL acquire and release memory from the operating system as
> needed. Dynamic memory allows you to set a maximum amount of physical memory
> that the operating system can allocate to SQL Server. This allows the
> remaining memory to be reserved for other purposes, such as the operating
> system and other SQL Server instances. Fixed memory allows you to set a
> specific amount of physical memory that SQL Server can use. This amount
> neither grows nor shrinks.
> Configure each server with the same amount of physical memory, and then
> allocate memory to each SQL Server instance to ensure that sufficient memory
> is available to support the failover of a SQL Server instance from another
> node. If a server does not use a lot of memory compared to the available
> memory on the server, specify dynamic memory and set the maximum amount of
> memory that the operating system can allocate. By doing so, you leave
> sufficient memory for the failover to occur quickly and successfully. If an
> instance uses all of the available physical memory, failover will occur more
> slowly and can fail if memory is not released quickly enough.
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
>
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "GlennThomas5" <glennthomas5@.gmail.com> wrote in message
> news:1158944385.401752.80530@.e3g2000cwe.googlegroups.com...
> >I would definately suggest using the AWE enabled option set to true.
> > especially if each node is dedicated to sql server. you will have to
> > restart the sql services to have the changes take place. check the
> > BOL: awe enabled option and the section managing awe memory.
> >
> > Sean T Shen wrote:
> >> Hi, I have the following questions regarding AWE on SQL 2000 with Windows
> >> 2000 Advanced Server and on a cluster (both nodes have an active SQL
> >> Server
> >> instance running). The SA has upgraded the memory of both nodes to 8G
> >> each.
> >> And I am tempted to use AWE to boost my SQL Server performance. The
> >> server
> >> is dedicated to running SQL Server.
> >>
> >> After AWE is enabled, can I configure my SQL Server max memory to
> >> something
> >> like 7G?
> >> Concern is, while it may run OK during normal operating mode, what
> >> will
> >> happen in case both SQL Servers are moved to one node?
> >>
> >> Thanks very much in advance.
> >> Sean
> >|||I am facing a dilemma:
For each node, the total memory is 8G.
If I configure each SQL instance as having 3.5G, then when both of the SQL
instances running on the same node (the failover situation), I would have
3.5x2=7G total, dedicated, statically, to SQL Servers, leaving 1G for OS.
But, most of the time, I am running each SQL on each node, thus I am wasting
all this memory (8-3.5=4.5G for each node).
In this case, looks like I am better off not upgrading the physical memory.
If I just configure each node with 4G of memory each, and have SQL Server
dynamically eat up its uage (with a max server memory set to 3.5), then I
still will get 3.5G for each SQL Server. And when both instances fails over
to one side, SQL and OS will dynamically negotiate their memory needs.
Plus, I have one other cluster with AWE set. they each have 8G. So here is
what I did
Boot.ini --> /3GB /PAE --> reboot
SQL
sp_configure 'awe', 1
reconfigure
sp_configure 'max server memory', 3584 /* 1024x3.5 */
reconfigure
[I was worry about this failover situation, that is why I only set this to
3.5G to see what happens]
You would expect that after this and everything rebooted, I should see my
SQL Server statically grabs all the memory up to 3.5G. But it did not!!!!
Contrary to what I have read in articles.
Now my question is
1. In AWE, does or does not SQL Server use static memory allocation?
2. Will SQL Server ever take advantage of AWE, even if I counter it to use 6
or 7G?
3. Do I need to set anything else in sp_configure other than what I have
already done?
Thanks for the answer already provided, and additional thanks in advance for
any further info.
Sean
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:%238CVjzm3GHA.600@.TK2MSFTNGP05.phx.gbl...
> Let me add to that, as I understand it, that if you use 4 GB or less for a
> SQL Server instance, by enabling AWE, you will experience a net loss.
> See:
> "AWE is a set of memory management extensions to the Microsoft Win32? API
> that allows applications to address memory beyond 4 GB."
> Memory -AWE, Not usable < 4GB
> http://download.microsoft.com/download/9/c/c/9cc42e30-538b-4451-8fdb-7134a004f94c/Adv64BitEnv.doc
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:usYDLvm3GHA.5032@.TK2MSFTNGP04.phx.gbl...
>> As I understand your situation, by use AWE and setting max memory to 7 GB
>> for the active virtual server, there will be no memory available in case
>> of a 'fail-over' (With AWE, the OS requires 1 GB).
>> I believe that you need to set max memory to an amount that leaves
>> adequate memory for the other virtual server in case of fail-over.
>> From:
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/hasog01.mspx
>> Allocating Memory
>> If you are running multiple instances of SQL Server on separate nodes in
>> the cluster, you must determine the total amount of physical memory
>> required for each server and determine the appropriate allocation of
>> memory for each SQL Server instance. Performance of SQL Server is
>> directly related to the amount of physical memory allocated to it by the
>> operating system. SQL Server provides two modes for memory management:
>> dynamic and fixed. Dynamic memory allocation lets SQL acquire and release
>> memory from the operating system as needed. Dynamic memory allows you to
>> set a maximum amount of physical memory that the operating system can
>> allocate to SQL Server. This allows the remaining memory to be reserved
>> for other purposes, such as the operating system and other SQL Server
>> instances. Fixed memory allows you to set a specific amount of physical
>> memory that SQL Server can use. This amount neither grows nor shrinks.
>> Configure each server with the same amount of physical memory, and then
>> allocate memory to each SQL Server instance to ensure that sufficient
>> memory is available to support the failover of a SQL Server instance from
>> another node. If a server does not use a lot of memory compared to the
>> available memory on the server, specify dynamic memory and set the
>> maximum amount of memory that the operating system can allocate. By doing
>> so, you leave sufficient memory for the failover to occur quickly and
>> successfully. If an instance uses all of the available physical memory,
>> failover will occur more slowly and can fail if memory is not released
>> quickly enough.
>>
>> --
>> Arnie Rowland, Ph.D.
>> Westwood Consulting, Inc
>>
>> Most good judgment comes from experience.
>> Most experience comes from bad judgment.
>> - Anonymous
>>
>> "GlennThomas5" <glennthomas5@.gmail.com> wrote in message
>> news:1158944385.401752.80530@.e3g2000cwe.googlegroups.com...
>>I would definately suggest using the AWE enabled option set to true.
>> especially if each node is dedicated to sql server. you will have to
>> restart the sql services to have the changes take place. check the
>> BOL: awe enabled option and the section managing awe memory.
>> Sean T Shen wrote:
>> Hi, I have the following questions regarding AWE on SQL 2000 with
>> Windows
>> 2000 Advanced Server and on a cluster (both nodes have an active SQL
>> Server
>> instance running). The SA has upgraded the memory of both nodes to 8G
>> each.
>> And I am tempted to use AWE to boost my SQL Server performance. The
>> server
>> is dedicated to running SQL Server.
>> After AWE is enabled, can I configure my SQL Server max memory to
>> something
>> like 7G?
>> Concern is, while it may run OK during normal operating mode, what
>> will
>> happen in case both SQL Servers are moved to one node?
>> Thanks very much in advance.
>> Sean
>>
>|||The bigger question is, can I even set anything up beyond 3.5G per each SQL
instance? If I do, will I not be able to move both SQL node resources to
one node (thus defeats the purpose of cluster HA protection)?
"GlennThomas5" <glennthomas5@.gmail.com> wrote in message
news:1158948261.902182.108570@.d34g2000cwd.googlegroups.com...
>I think your right about the 1GB for the OS on clustered services...he
> definately needs to factor in outside things such as the clustered
> services, other applications, etc.
> Arnie Rowland wrote:
>> As I understand your situation, by use AWE and setting max memory to 7 GB
>> for the active virtual server, there will be no memory available in case
>> of
>> a 'fail-over' (With AWE, the OS requires 1 GB).
>> I believe that you need to set max memory to an amount that leaves
>> adequate
>> memory for the other virtual server in case of fail-over.
>> From:
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/hasog01.mspx
>> Allocating Memory
>> If you are running multiple instances of SQL Server on separate nodes in
>> the
>> cluster, you must determine the total amount of physical memory required
>> for
>> each server and determine the appropriate allocation of memory for each
>> SQL
>> Server instance. Performance of SQL Server is directly related to the
>> amount
>> of physical memory allocated to it by the operating system. SQL Server
>> provides two modes for memory management: dynamic and fixed. Dynamic
>> memory
>> allocation lets SQL acquire and release memory from the operating system
>> as
>> needed. Dynamic memory allows you to set a maximum amount of physical
>> memory
>> that the operating system can allocate to SQL Server. This allows the
>> remaining memory to be reserved for other purposes, such as the operating
>> system and other SQL Server instances. Fixed memory allows you to set a
>> specific amount of physical memory that SQL Server can use. This amount
>> neither grows nor shrinks.
>> Configure each server with the same amount of physical memory, and then
>> allocate memory to each SQL Server instance to ensure that sufficient
>> memory
>> is available to support the failover of a SQL Server instance from
>> another
>> node. If a server does not use a lot of memory compared to the available
>> memory on the server, specify dynamic memory and set the maximum amount
>> of
>> memory that the operating system can allocate. By doing so, you leave
>> sufficient memory for the failover to occur quickly and successfully. If
>> an
>> instance uses all of the available physical memory, failover will occur
>> more
>> slowly and can fail if memory is not released quickly enough.
>>
>> --
>> Arnie Rowland, Ph.D.
>> Westwood Consulting, Inc
>>
>> Most good judgment comes from experience.
>> Most experience comes from bad judgment.
>> - Anonymous
>>
>> "GlennThomas5" <glennthomas5@.gmail.com> wrote in message
>> news:1158944385.401752.80530@.e3g2000cwe.googlegroups.com...
>> >I would definately suggest using the AWE enabled option set to true.
>> > especially if each node is dedicated to sql server. you will have to
>> > restart the sql services to have the changes take place. check the
>> > BOL: awe enabled option and the section managing awe memory.
>> >
>> > Sean T Shen wrote:
>> >> Hi, I have the following questions regarding AWE on SQL 2000 with
>> >> Windows
>> >> 2000 Advanced Server and on a cluster (both nodes have an active SQL
>> >> Server
>> >> instance running). The SA has upgraded the memory of both nodes to 8G
>> >> each.
>> >> And I am tempted to use AWE to boost my SQL Server performance. The
>> >> server
>> >> is dedicated to running SQL Server.
>> >>
>> >> After AWE is enabled, can I configure my SQL Server max memory to
>> >> something
>> >> like 7G?
>> >> Concern is, while it may run OK during normal operating mode, what
>> >> will
>> >> happen in case both SQL Servers are moved to one node?
>> >>
>> >> Thanks very much in advance.
>> >> Sean
>> >
>|||Actually, you are not 'wasting' any memory -you are 'buying' high
availability (fail-over) insurance -and the cost that insurance is reflected
in the price of licenses, increased hardware costs, memory, etc.
You have a couple of options. (these comments are NOT germane for 64 bit
Windows and 64 bit SQL Server.)
1. Set memory statically at 3.5 GB per node.
2. Set memory for SQL to dynamically acquire, with a min size of 3.5 GB and
a max size of, perhaps 6 GB. That will leave 1 GB for the failed over
virtual server to grab a starting point and then to negotiate with the OS
for more memory from the active virtual server.
Your other questions:
Don't use AWE unless a virtual server will have more than 4 GB -so option 1
above does not use AWE.
SQL Server will use memory about 2 GB for data cache only. Procedure cache,
and all other SQL memory needs come out of 2 GB
SQL Server does not immediately 'acquire' the max memory, it will increase
its demand from the OS as need requires.
SQL Server will use AWE memory above 4 GB for data cache only.
I hope this, and the articles I posted earlier, help.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Sean T Shen" <seantshen@.optonline.net> wrote in message
news:OU61lUn3GHA.1304@.TK2MSFTNGP05.phx.gbl...
>I am facing a dilemma:
> For each node, the total memory is 8G.
> If I configure each SQL instance as having 3.5G, then when both of the SQL
> instances running on the same node (the failover situation), I would have
> 3.5x2=7G total, dedicated, statically, to SQL Servers, leaving 1G for OS.
> But, most of the time, I am running each SQL on each node, thus I am
> wasting all this memory (8-3.5=4.5G for each node).
> In this case, looks like I am better off not upgrading the physical
> memory. If I just configure each node with 4G of memory each, and have SQL
> Server dynamically eat up its uage (with a max server memory set to 3.5),
> then I still will get 3.5G for each SQL Server. And when both instances
> fails over to one side, SQL and OS will dynamically negotiate their memory
> needs.
> Plus, I have one other cluster with AWE set. they each have 8G. So here
> is what I did
> Boot.ini --> /3GB /PAE --> reboot
> SQL
> sp_configure 'awe', 1
> reconfigure
> sp_configure 'max server memory', 3584 /* 1024x3.5 */
> reconfigure
> [I was worry about this failover situation, that is why I only set this to
> 3.5G to see what happens]
> You would expect that after this and everything rebooted, I should see my
> SQL Server statically grabs all the memory up to 3.5G. But it did not!!!!
> Contrary to what I have read in articles.
> Now my question is
> 1. In AWE, does or does not SQL Server use static memory allocation?
> 2. Will SQL Server ever take advantage of AWE, even if I counter it to use
> 6 or 7G?
> 3. Do I need to set anything else in sp_configure other than what I have
> already done?
> Thanks for the answer already provided, and additional thanks in advance
> for any further info.
> Sean
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:%238CVjzm3GHA.600@.TK2MSFTNGP05.phx.gbl...
>> Let me add to that, as I understand it, that if you use 4 GB or less for
>> a SQL Server instance, by enabling AWE, you will experience a net loss.
>> See:
>> "AWE is a set of memory management extensions to the Microsoft Win32? API
>> that allows applications to address memory beyond 4 GB."
>> Memory -AWE, Not usable < 4GB
>> http://download.microsoft.com/download/9/c/c/9cc42e30-538b-4451-8fdb-7134a004f94c/Adv64BitEnv.doc
>>
>> --
>> Arnie Rowland, Ph.D.
>> Westwood Consulting, Inc
>> Most good judgment comes from experience.
>> Most experience comes from bad judgment.
>> - Anonymous
>>
>> "Arnie Rowland" <arnie@.1568.com> wrote in message
>> news:usYDLvm3GHA.5032@.TK2MSFTNGP04.phx.gbl...
>> As I understand your situation, by use AWE and setting max memory to 7
>> GB for the active virtual server, there will be no memory available in
>> case of a 'fail-over' (With AWE, the OS requires 1 GB).
>> I believe that you need to set max memory to an amount that leaves
>> adequate memory for the other virtual server in case of fail-over.
>> From:
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/hasog01.mspx
>> Allocating Memory
>> If you are running multiple instances of SQL Server on separate nodes in
>> the cluster, you must determine the total amount of physical memory
>> required for each server and determine the appropriate allocation of
>> memory for each SQL Server instance. Performance of SQL Server is
>> directly related to the amount of physical memory allocated to it by the
>> operating system. SQL Server provides two modes for memory management:
>> dynamic and fixed. Dynamic memory allocation lets SQL acquire and
>> release memory from the operating system as needed. Dynamic memory
>> allows you to set a maximum amount of physical memory that the operating
>> system can allocate to SQL Server. This allows the remaining memory to
>> be reserved for other purposes, such as the operating system and other
>> SQL Server instances. Fixed memory allows you to set a specific amount
>> of physical memory that SQL Server can use. This amount neither grows
>> nor shrinks.
>> Configure each server with the same amount of physical memory, and then
>> allocate memory to each SQL Server instance to ensure that sufficient
>> memory is available to support the failover of a SQL Server instance
>> from another node. If a server does not use a lot of memory compared to
>> the available memory on the server, specify dynamic memory and set the
>> maximum amount of memory that the operating system can allocate. By
>> doing so, you leave sufficient memory for the failover to occur quickly
>> and successfully. If an instance uses all of the available physical
>> memory, failover will occur more slowly and can fail if memory is not
>> released quickly enough.
>>
>> --
>> Arnie Rowland, Ph.D.
>> Westwood Consulting, Inc
>>
>> Most good judgment comes from experience.
>> Most experience comes from bad judgment.
>> - Anonymous
>>
>> "GlennThomas5" <glennthomas5@.gmail.com> wrote in message
>> news:1158944385.401752.80530@.e3g2000cwe.googlegroups.com...
>>I would definately suggest using the AWE enabled option set to true.
>> especially if each node is dedicated to sql server. you will have to
>> restart the sql services to have the changes take place. check the
>> BOL: awe enabled option and the section managing awe memory.
>> Sean T Shen wrote:
>> Hi, I have the following questions regarding AWE on SQL 2000 with
>> Windows
>> 2000 Advanced Server and on a cluster (both nodes have an active SQL
>> Server
>> instance running). The SA has upgraded the memory of both nodes to 8G
>> each.
>> And I am tempted to use AWE to boost my SQL Server performance. The
>> server
>> is dedicated to running SQL Server.
>> After AWE is enabled, can I configure my SQL Server max memory to
>> something
>> like 7G?
>> Concern is, while it may run OK during normal operating mode, what
>> will
>> happen in case both SQL Servers are moved to one node?
>> Thanks very much in advance.
>> Sean
>>
>>
>|||That should have read 'above'.
> SQL Server will use memory above 2 GB for data cache only.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:%23Ttdlyn3GHA.476@.TK2MSFTNGP06.phx.gbl...
> Actually, you are not 'wasting' any memory -you are 'buying' high
> availability (fail-over) insurance -and the cost that insurance is
> reflected in the price of licenses, increased hardware costs, memory, etc.
> You have a couple of options. (these comments are NOT germane for 64 bit
> Windows and 64 bit SQL Server.)
> 1. Set memory statically at 3.5 GB per node.
> 2. Set memory for SQL to dynamically acquire, with a min size of 3.5 GB
> and a max size of, perhaps 6 GB. That will leave 1 GB for the failed over
> virtual server to grab a starting point and then to negotiate with the OS
> for more memory from the active virtual server.
> Your other questions:
> Don't use AWE unless a virtual server will have more than 4 GB -so option
> 1 above does not use AWE.
> SQL Server will use memory about 2 GB for data cache only. Procedure
> cache, and all other SQL memory needs come out of 2 GB
> SQL Server does not immediately 'acquire' the max memory, it will increase
> its demand from the OS as need requires.
> SQL Server will use AWE memory above 4 GB for data cache only.
> I hope this, and the articles I posted earlier, help.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Sean T Shen" <seantshen@.optonline.net> wrote in message
> news:OU61lUn3GHA.1304@.TK2MSFTNGP05.phx.gbl...
>>I am facing a dilemma:
>> For each node, the total memory is 8G.
>> If I configure each SQL instance as having 3.5G, then when both of the
>> SQL instances running on the same node (the failover situation), I would
>> have 3.5x2=7G total, dedicated, statically, to SQL Servers, leaving 1G
>> for OS.
>> But, most of the time, I am running each SQL on each node, thus I am
>> wasting all this memory (8-3.5=4.5G for each node).
>> In this case, looks like I am better off not upgrading the physical
>> memory. If I just configure each node with 4G of memory each, and have
>> SQL Server dynamically eat up its uage (with a max server memory set to
>> 3.5), then I still will get 3.5G for each SQL Server. And when both
>> instances fails over to one side, SQL and OS will dynamically negotiate
>> their memory needs.
>> Plus, I have one other cluster with AWE set. they each have 8G. So here
>> is what I did
>> Boot.ini --> /3GB /PAE --> reboot
>> SQL
>> sp_configure 'awe', 1
>> reconfigure
>> sp_configure 'max server memory', 3584 /* 1024x3.5 */
>> reconfigure
>> [I was worry about this failover situation, that is why I only set this
>> to 3.5G to see what happens]
>> You would expect that after this and everything rebooted, I should see my
>> SQL Server statically grabs all the memory up to 3.5G. But it did
>> not!!!! Contrary to what I have read in articles.
>> Now my question is
>> 1. In AWE, does or does not SQL Server use static memory allocation?
>> 2. Will SQL Server ever take advantage of AWE, even if I counter it to
>> use 6 or 7G?
>> 3. Do I need to set anything else in sp_configure other than what I have
>> already done?
>> Thanks for the answer already provided, and additional thanks in advance
>> for any further info.
>> Sean
>> "Arnie Rowland" <arnie@.1568.com> wrote in message
>> news:%238CVjzm3GHA.600@.TK2MSFTNGP05.phx.gbl...
>> Let me add to that, as I understand it, that if you use 4 GB or less for
>> a SQL Server instance, by enabling AWE, you will experience a net loss.
>> See:
>> "AWE is a set of memory management extensions to the Microsoft Win32?
>> API that allows applications to address memory beyond 4 GB."
>> Memory -AWE, Not usable < 4GB
>> http://download.microsoft.com/download/9/c/c/9cc42e30-538b-4451-8fdb-7134a004f94c/Adv64BitEnv.doc
>>
>> --
>> Arnie Rowland, Ph.D.
>> Westwood Consulting, Inc
>> Most good judgment comes from experience.
>> Most experience comes from bad judgment.
>> - Anonymous
>>
>> "Arnie Rowland" <arnie@.1568.com> wrote in message
>> news:usYDLvm3GHA.5032@.TK2MSFTNGP04.phx.gbl...
>> As I understand your situation, by use AWE and setting max memory to 7
>> GB for the active virtual server, there will be no memory available in
>> case of a 'fail-over' (With AWE, the OS requires 1 GB).
>> I believe that you need to set max memory to an amount that leaves
>> adequate memory for the other virtual server in case of fail-over.
>> From:
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/hasog01.mspx
>> Allocating Memory
>> If you are running multiple instances of SQL Server on separate nodes
>> in the cluster, you must determine the total amount of physical memory
>> required for each server and determine the appropriate allocation of
>> memory for each SQL Server instance. Performance of SQL Server is
>> directly related to the amount of physical memory allocated to it by
>> the operating system. SQL Server provides two modes for memory
>> management: dynamic and fixed. Dynamic memory allocation lets SQL
>> acquire and release memory from the operating system as needed. Dynamic
>> memory allows you to set a maximum amount of physical memory that the
>> operating system can allocate to SQL Server. This allows the remaining
>> memory to be reserved for other purposes, such as the operating system
>> and other SQL Server instances. Fixed memory allows you to set a
>> specific amount of physical memory that SQL Server can use. This amount
>> neither grows nor shrinks.
>> Configure each server with the same amount of physical memory, and then
>> allocate memory to each SQL Server instance to ensure that sufficient
>> memory is available to support the failover of a SQL Server instance
>> from another node. If a server does not use a lot of memory compared to
>> the available memory on the server, specify dynamic memory and set the
>> maximum amount of memory that the operating system can allocate. By
>> doing so, you leave sufficient memory for the failover to occur quickly
>> and successfully. If an instance uses all of the available physical
>> memory, failover will occur more slowly and can fail if memory is not
>> released quickly enough.
>>
>> --
>> Arnie Rowland, Ph.D.
>> Westwood Consulting, Inc
>>
>> Most good judgment comes from experience.
>> Most experience comes from bad judgment.
>> - Anonymous
>>
>> "GlennThomas5" <glennthomas5@.gmail.com> wrote in message
>> news:1158944385.401752.80530@.e3g2000cwe.googlegroups.com...
>>I would definately suggest using the AWE enabled option set to true.
>> especially if each node is dedicated to sql server. you will have to
>> restart the sql services to have the changes take place. check the
>> BOL: awe enabled option and the section managing awe memory.
>> Sean T Shen wrote:
>> Hi, I have the following questions regarding AWE on SQL 2000 with
>> Windows
>> 2000 Advanced Server and on a cluster (both nodes have an active SQL
>> Server
>> instance running). The SA has upgraded the memory of both nodes to
>> 8G each.
>> And I am tempted to use AWE to boost my SQL Server performance. The
>> server
>> is dedicated to running SQL Server.
>> After AWE is enabled, can I configure my SQL Server max memory to
>> something
>> like 7G?
>> Concern is, while it may run OK during normal operating mode,
>> what will
>> happen in case both SQL Servers are moved to one node?
>> Thanks very much in advance.
>> Sean
>>
>>
>>
>|||Not sure if I have successfully posted my reply.
Trying it again
Well, this is what I quote from a Microsoft article...do not have it handy
right now...but this is what it says
--
One caution about using the "awe enabled" setting is that after turning it
on, SQL Server no longer dynamically manages memory. Instead, it takes all
of the available RAM (except about 128MB which is left for the operating
system). If you want to prevent SQL Server from taking all of the RAM, you
must set the "max server memory" option (described in more detail later in
this article) to a figure that limits SQL Server to the amount or RAM you
specify.
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:%23Ttdlyn3GHA.476@.TK2MSFTNGP06.phx.gbl...
> Actually, you are not 'wasting' any memory -you are 'buying' high
> availability (fail-over) insurance -and the cost that insurance is
> reflected in the price of licenses, increased hardware costs, memory, etc.
> You have a couple of options. (these comments are NOT germane for 64 bit
> Windows and 64 bit SQL Server.)
> 1. Set memory statically at 3.5 GB per node.
> 2. Set memory for SQL to dynamically acquire, with a min size of 3.5 GB
> and a max size of, perhaps 6 GB. That will leave 1 GB for the failed over
> virtual server to grab a starting point and then to negotiate with the OS
> for more memory from the active virtual server.
> Your other questions:
> Don't use AWE unless a virtual server will have more than 4 GB -so option
> 1 above does not use AWE.
> SQL Server will use memory about 2 GB for data cache only. Procedure
> cache, and all other SQL memory needs come out of 2 GB
> SQL Server does not immediately 'acquire' the max memory, it will increase
> its demand from the OS as need requires.
> SQL Server will use AWE memory above 4 GB for data cache only.
> I hope this, and the articles I posted earlier, help.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Sean T Shen" <seantshen@.optonline.net> wrote in message
> news:OU61lUn3GHA.1304@.TK2MSFTNGP05.phx.gbl...
>>I am facing a dilemma:
>> For each node, the total memory is 8G.
>> If I configure each SQL instance as having 3.5G, then when both of the
>> SQL instances running on the same node (the failover situation), I would
>> have 3.5x2=7G total, dedicated, statically, to SQL Servers, leaving 1G
>> for OS.
>> But, most of the time, I am running each SQL on each node, thus I am
>> wasting all this memory (8-3.5=4.5G for each node).
>> In this case, looks like I am better off not upgrading the physical
>> memory. If I just configure each node with 4G of memory each, and have
>> SQL Server dynamically eat up its uage (with a max server memory set to
>> 3.5), then I still will get 3.5G for each SQL Server. And when both
>> instances fails over to one side, SQL and OS will dynamically negotiate
>> their memory needs.
>> Plus, I have one other cluster with AWE set. they each have 8G. So here
>> is what I did
>> Boot.ini --> /3GB /PAE --> reboot
>> SQL
>> sp_configure 'awe', 1
>> reconfigure
>> sp_configure 'max server memory', 3584 /* 1024x3.5 */
>> reconfigure
>> [I was worry about this failover situation, that is why I only set this
>> to 3.5G to see what happens]
>> You would expect that after this and everything rebooted, I should see my
>> SQL Server statically grabs all the memory up to 3.5G. But it did
>> not!!!! Contrary to what I have read in articles.
>> Now my question is
>> 1. In AWE, does or does not SQL Server use static memory allocation?
>> 2. Will SQL Server ever take advantage of AWE, even if I counter it to
>> use 6 or 7G?
>> 3. Do I need to set anything else in sp_configure other than what I have
>> already done?
>> Thanks for the answer already provided, and additional thanks in advance
>> for any further info.
>> Sean
>> "Arnie Rowland" <arnie@.1568.com> wrote in message
>> news:%238CVjzm3GHA.600@.TK2MSFTNGP05.phx.gbl...
>> Let me add to that, as I understand it, that if you use 4 GB or less for
>> a SQL Server instance, by enabling AWE, you will experience a net loss.
>> See:
>> "AWE is a set of memory management extensions to the Microsoft Win32?
>> API that allows applications to address memory beyond 4 GB."
>> Memory -AWE, Not usable < 4GB
>> http://download.microsoft.com/download/9/c/c/9cc42e30-538b-4451-8fdb-7134a004f94c/Adv64BitEnv.doc
>>
>> --
>> Arnie Rowland, Ph.D.
>> Westwood Consulting, Inc
>> Most good judgment comes from experience.
>> Most experience comes from bad judgment.
>> - Anonymous
>>
>> "Arnie Rowland" <arnie@.1568.com> wrote in message
>> news:usYDLvm3GHA.5032@.TK2MSFTNGP04.phx.gbl...
>> As I understand your situation, by use AWE and setting max memory to 7
>> GB for the active virtual server, there will be no memory available in
>> case of a 'fail-over' (With AWE, the OS requires 1 GB).
>> I believe that you need to set max memory to an amount that leaves
>> adequate memory for the other virtual server in case of fail-over.
>> From:
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/hasog01.mspx
>> Allocating Memory
>> If you are running multiple instances of SQL Server on separate nodes
>> in the cluster, you must determine the total amount of physical memory
>> required for each server and determine the appropriate allocation of
>> memory for each SQL Server instance. Performance of SQL Server is
>> directly related to the amount of physical memory allocated to it by
>> the operating system. SQL Server provides two modes for memory
>> management: dynamic and fixed. Dynamic memory allocation lets SQL
>> acquire and release memory from the operating system as needed. Dynamic
>> memory allows you to set a maximum amount of physical memory that the
>> operating system can allocate to SQL Server. This allows the remaining
>> memory to be reserved for other purposes, such as the operating system
>> and other SQL Server instances. Fixed memory allows you to set a
>> specific amount of physical memory that SQL Server can use. This amount
>> neither grows nor shrinks.
>> Configure each server with the same amount of physical memory, and then
>> allocate memory to each SQL Server instance to ensure that sufficient
>> memory is available to support the failover of a SQL Server instance
>> from another node. If a server does not use a lot of memory compared to
>> the available memory on the server, specify dynamic memory and set the
>> maximum amount of memory that the operating system can allocate. By
>> doing so, you leave sufficient memory for the failover to occur quickly
>> and successfully. If an instance uses all of the available physical
>> memory, failover will occur more slowly and can fail if memory is not
>> released quickly enough.
>>
>> --
>> Arnie Rowland, Ph.D.
>> Westwood Consulting, Inc
>>
>> Most good judgment comes from experience.
>> Most experience comes from bad judgment.
>> - Anonymous
>>
>> "GlennThomas5" <glennthomas5@.gmail.com> wrote in message
>> news:1158944385.401752.80530@.e3g2000cwe.googlegroups.com...
>>I would definately suggest using the AWE enabled option set to true.
>> especially if each node is dedicated to sql server. you will have to
>> restart the sql services to have the changes take place. check the
>> BOL: awe enabled option and the section managing awe memory.
>> Sean T Shen wrote:
>> Hi, I have the following questions regarding AWE on SQL 2000 with
>> Windows
>> 2000 Advanced Server and on a cluster (both nodes have an active SQL
>> Server
>> instance running). The SA has upgraded the memory of both nodes to
>> 8G each.
>> And I am tempted to use AWE to boost my SQL Server performance. The
>> server
>> is dedicated to running SQL Server.
>> After AWE is enabled, can I configure my SQL Server max memory to
>> something
>> like 7G?
>> Concern is, while it may run OK during normal operating mode,
>> what will
>> happen in case both SQL Servers are moved to one node?
>> Thanks very much in advance.
>> Sean
>>
>>
>>
>
2000 Advanced Server and on a cluster (both nodes have an active SQL Server
instance running). The SA has upgraded the memory of both nodes to 8G each.
And I am tempted to use AWE to boost my SQL Server performance. The server
is dedicated to running SQL Server.
After AWE is enabled, can I configure my SQL Server max memory to something
like 7G?
Concern is, while it may run OK during normal operating mode, what will
happen in case both SQL Servers are moved to one node?
Thanks very much in advance.
SeanI would definately suggest using the AWE enabled option set to true.
especially if each node is dedicated to sql server. you will have to
restart the sql services to have the changes take place. check the
BOL: awe enabled option and the section managing awe memory.
Sean T Shen wrote:
> Hi, I have the following questions regarding AWE on SQL 2000 with Windows
> 2000 Advanced Server and on a cluster (both nodes have an active SQL Server
> instance running). The SA has upgraded the memory of both nodes to 8G each.
> And I am tempted to use AWE to boost my SQL Server performance. The server
> is dedicated to running SQL Server.
> After AWE is enabled, can I configure my SQL Server max memory to something
> like 7G?
> Concern is, while it may run OK during normal operating mode, what will
> happen in case both SQL Servers are moved to one node?
> Thanks very much in advance.
> Sean|||As I understand your situation, by use AWE and setting max memory to 7 GB
for the active virtual server, there will be no memory available in case of
a 'fail-over' (With AWE, the OS requires 1 GB).
I believe that you need to set max memory to an amount that leaves adequate
memory for the other virtual server in case of fail-over.
From:
http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/hasog01.mspx
Allocating Memory
If you are running multiple instances of SQL Server on separate nodes in the
cluster, you must determine the total amount of physical memory required for
each server and determine the appropriate allocation of memory for each SQL
Server instance. Performance of SQL Server is directly related to the amount
of physical memory allocated to it by the operating system. SQL Server
provides two modes for memory management: dynamic and fixed. Dynamic memory
allocation lets SQL acquire and release memory from the operating system as
needed. Dynamic memory allows you to set a maximum amount of physical memory
that the operating system can allocate to SQL Server. This allows the
remaining memory to be reserved for other purposes, such as the operating
system and other SQL Server instances. Fixed memory allows you to set a
specific amount of physical memory that SQL Server can use. This amount
neither grows nor shrinks.
Configure each server with the same amount of physical memory, and then
allocate memory to each SQL Server instance to ensure that sufficient memory
is available to support the failover of a SQL Server instance from another
node. If a server does not use a lot of memory compared to the available
memory on the server, specify dynamic memory and set the maximum amount of
memory that the operating system can allocate. By doing so, you leave
sufficient memory for the failover to occur quickly and successfully. If an
instance uses all of the available physical memory, failover will occur more
slowly and can fail if memory is not released quickly enough.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"GlennThomas5" <glennthomas5@.gmail.com> wrote in message
news:1158944385.401752.80530@.e3g2000cwe.googlegroups.com...
>I would definately suggest using the AWE enabled option set to true.
> especially if each node is dedicated to sql server. you will have to
> restart the sql services to have the changes take place. check the
> BOL: awe enabled option and the section managing awe memory.
> Sean T Shen wrote:
>> Hi, I have the following questions regarding AWE on SQL 2000 with Windows
>> 2000 Advanced Server and on a cluster (both nodes have an active SQL
>> Server
>> instance running). The SA has upgraded the memory of both nodes to 8G
>> each.
>> And I am tempted to use AWE to boost my SQL Server performance. The
>> server
>> is dedicated to running SQL Server.
>> After AWE is enabled, can I configure my SQL Server max memory to
>> something
>> like 7G?
>> Concern is, while it may run OK during normal operating mode, what
>> will
>> happen in case both SQL Servers are moved to one node?
>> Thanks very much in advance.
>> Sean
>|||Let me add to that, as I understand it, that if you use 4 GB or less for a
SQL Server instance, by enabling AWE, you will experience a net loss.
See:
"AWE is a set of memory management extensions to the Microsoft Win32? API
that allows applications to address memory beyond 4 GB."
Memory -AWE, Not usable < 4GB
http://download.microsoft.com/download/9/c/c/9cc42e30-538b-4451-8fdb-7134a004f94c/Adv64BitEnv.doc
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:usYDLvm3GHA.5032@.TK2MSFTNGP04.phx.gbl...
> As I understand your situation, by use AWE and setting max memory to 7 GB
> for the active virtual server, there will be no memory available in case
> of a 'fail-over' (With AWE, the OS requires 1 GB).
> I believe that you need to set max memory to an amount that leaves
> adequate memory for the other virtual server in case of fail-over.
> From:
> http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/hasog01.mspx
> Allocating Memory
> If you are running multiple instances of SQL Server on separate nodes in
> the cluster, you must determine the total amount of physical memory
> required for each server and determine the appropriate allocation of
> memory for each SQL Server instance. Performance of SQL Server is directly
> related to the amount of physical memory allocated to it by the operating
> system. SQL Server provides two modes for memory management: dynamic and
> fixed. Dynamic memory allocation lets SQL acquire and release memory from
> the operating system as needed. Dynamic memory allows you to set a maximum
> amount of physical memory that the operating system can allocate to SQL
> Server. This allows the remaining memory to be reserved for other
> purposes, such as the operating system and other SQL Server instances.
> Fixed memory allows you to set a specific amount of physical memory that
> SQL Server can use. This amount neither grows nor shrinks.
> Configure each server with the same amount of physical memory, and then
> allocate memory to each SQL Server instance to ensure that sufficient
> memory is available to support the failover of a SQL Server instance from
> another node. If a server does not use a lot of memory compared to the
> available memory on the server, specify dynamic memory and set the maximum
> amount of memory that the operating system can allocate. By doing so, you
> leave sufficient memory for the failover to occur quickly and
> successfully. If an instance uses all of the available physical memory,
> failover will occur more slowly and can fail if memory is not released
> quickly enough.
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
>
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "GlennThomas5" <glennthomas5@.gmail.com> wrote in message
> news:1158944385.401752.80530@.e3g2000cwe.googlegroups.com...
>>I would definately suggest using the AWE enabled option set to true.
>> especially if each node is dedicated to sql server. you will have to
>> restart the sql services to have the changes take place. check the
>> BOL: awe enabled option and the section managing awe memory.
>> Sean T Shen wrote:
>> Hi, I have the following questions regarding AWE on SQL 2000 with
>> Windows
>> 2000 Advanced Server and on a cluster (both nodes have an active SQL
>> Server
>> instance running). The SA has upgraded the memory of both nodes to 8G
>> each.
>> And I am tempted to use AWE to boost my SQL Server performance. The
>> server
>> is dedicated to running SQL Server.
>> After AWE is enabled, can I configure my SQL Server max memory to
>> something
>> like 7G?
>> Concern is, while it may run OK during normal operating mode, what
>> will
>> happen in case both SQL Servers are moved to one node?
>> Thanks very much in advance.
>> Sean
>|||I think your right about the 1GB for the OS on clustered services...he
definately needs to factor in outside things such as the clustered
services, other applications, etc.
Arnie Rowland wrote:
> As I understand your situation, by use AWE and setting max memory to 7 GB
> for the active virtual server, there will be no memory available in case of
> a 'fail-over' (With AWE, the OS requires 1 GB).
> I believe that you need to set max memory to an amount that leaves adequate
> memory for the other virtual server in case of fail-over.
> From:
> http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/hasog01.mspx
> Allocating Memory
> If you are running multiple instances of SQL Server on separate nodes in the
> cluster, you must determine the total amount of physical memory required for
> each server and determine the appropriate allocation of memory for each SQL
> Server instance. Performance of SQL Server is directly related to the amount
> of physical memory allocated to it by the operating system. SQL Server
> provides two modes for memory management: dynamic and fixed. Dynamic memory
> allocation lets SQL acquire and release memory from the operating system as
> needed. Dynamic memory allows you to set a maximum amount of physical memory
> that the operating system can allocate to SQL Server. This allows the
> remaining memory to be reserved for other purposes, such as the operating
> system and other SQL Server instances. Fixed memory allows you to set a
> specific amount of physical memory that SQL Server can use. This amount
> neither grows nor shrinks.
> Configure each server with the same amount of physical memory, and then
> allocate memory to each SQL Server instance to ensure that sufficient memory
> is available to support the failover of a SQL Server instance from another
> node. If a server does not use a lot of memory compared to the available
> memory on the server, specify dynamic memory and set the maximum amount of
> memory that the operating system can allocate. By doing so, you leave
> sufficient memory for the failover to occur quickly and successfully. If an
> instance uses all of the available physical memory, failover will occur more
> slowly and can fail if memory is not released quickly enough.
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
>
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "GlennThomas5" <glennthomas5@.gmail.com> wrote in message
> news:1158944385.401752.80530@.e3g2000cwe.googlegroups.com...
> >I would definately suggest using the AWE enabled option set to true.
> > especially if each node is dedicated to sql server. you will have to
> > restart the sql services to have the changes take place. check the
> > BOL: awe enabled option and the section managing awe memory.
> >
> > Sean T Shen wrote:
> >> Hi, I have the following questions regarding AWE on SQL 2000 with Windows
> >> 2000 Advanced Server and on a cluster (both nodes have an active SQL
> >> Server
> >> instance running). The SA has upgraded the memory of both nodes to 8G
> >> each.
> >> And I am tempted to use AWE to boost my SQL Server performance. The
> >> server
> >> is dedicated to running SQL Server.
> >>
> >> After AWE is enabled, can I configure my SQL Server max memory to
> >> something
> >> like 7G?
> >> Concern is, while it may run OK during normal operating mode, what
> >> will
> >> happen in case both SQL Servers are moved to one node?
> >>
> >> Thanks very much in advance.
> >> Sean
> >|||I am facing a dilemma:
For each node, the total memory is 8G.
If I configure each SQL instance as having 3.5G, then when both of the SQL
instances running on the same node (the failover situation), I would have
3.5x2=7G total, dedicated, statically, to SQL Servers, leaving 1G for OS.
But, most of the time, I am running each SQL on each node, thus I am wasting
all this memory (8-3.5=4.5G for each node).
In this case, looks like I am better off not upgrading the physical memory.
If I just configure each node with 4G of memory each, and have SQL Server
dynamically eat up its uage (with a max server memory set to 3.5), then I
still will get 3.5G for each SQL Server. And when both instances fails over
to one side, SQL and OS will dynamically negotiate their memory needs.
Plus, I have one other cluster with AWE set. they each have 8G. So here is
what I did
Boot.ini --> /3GB /PAE --> reboot
SQL
sp_configure 'awe', 1
reconfigure
sp_configure 'max server memory', 3584 /* 1024x3.5 */
reconfigure
[I was worry about this failover situation, that is why I only set this to
3.5G to see what happens]
You would expect that after this and everything rebooted, I should see my
SQL Server statically grabs all the memory up to 3.5G. But it did not!!!!
Contrary to what I have read in articles.
Now my question is
1. In AWE, does or does not SQL Server use static memory allocation?
2. Will SQL Server ever take advantage of AWE, even if I counter it to use 6
or 7G?
3. Do I need to set anything else in sp_configure other than what I have
already done?
Thanks for the answer already provided, and additional thanks in advance for
any further info.
Sean
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:%238CVjzm3GHA.600@.TK2MSFTNGP05.phx.gbl...
> Let me add to that, as I understand it, that if you use 4 GB or less for a
> SQL Server instance, by enabling AWE, you will experience a net loss.
> See:
> "AWE is a set of memory management extensions to the Microsoft Win32? API
> that allows applications to address memory beyond 4 GB."
> Memory -AWE, Not usable < 4GB
> http://download.microsoft.com/download/9/c/c/9cc42e30-538b-4451-8fdb-7134a004f94c/Adv64BitEnv.doc
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:usYDLvm3GHA.5032@.TK2MSFTNGP04.phx.gbl...
>> As I understand your situation, by use AWE and setting max memory to 7 GB
>> for the active virtual server, there will be no memory available in case
>> of a 'fail-over' (With AWE, the OS requires 1 GB).
>> I believe that you need to set max memory to an amount that leaves
>> adequate memory for the other virtual server in case of fail-over.
>> From:
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/hasog01.mspx
>> Allocating Memory
>> If you are running multiple instances of SQL Server on separate nodes in
>> the cluster, you must determine the total amount of physical memory
>> required for each server and determine the appropriate allocation of
>> memory for each SQL Server instance. Performance of SQL Server is
>> directly related to the amount of physical memory allocated to it by the
>> operating system. SQL Server provides two modes for memory management:
>> dynamic and fixed. Dynamic memory allocation lets SQL acquire and release
>> memory from the operating system as needed. Dynamic memory allows you to
>> set a maximum amount of physical memory that the operating system can
>> allocate to SQL Server. This allows the remaining memory to be reserved
>> for other purposes, such as the operating system and other SQL Server
>> instances. Fixed memory allows you to set a specific amount of physical
>> memory that SQL Server can use. This amount neither grows nor shrinks.
>> Configure each server with the same amount of physical memory, and then
>> allocate memory to each SQL Server instance to ensure that sufficient
>> memory is available to support the failover of a SQL Server instance from
>> another node. If a server does not use a lot of memory compared to the
>> available memory on the server, specify dynamic memory and set the
>> maximum amount of memory that the operating system can allocate. By doing
>> so, you leave sufficient memory for the failover to occur quickly and
>> successfully. If an instance uses all of the available physical memory,
>> failover will occur more slowly and can fail if memory is not released
>> quickly enough.
>>
>> --
>> Arnie Rowland, Ph.D.
>> Westwood Consulting, Inc
>>
>> Most good judgment comes from experience.
>> Most experience comes from bad judgment.
>> - Anonymous
>>
>> "GlennThomas5" <glennthomas5@.gmail.com> wrote in message
>> news:1158944385.401752.80530@.e3g2000cwe.googlegroups.com...
>>I would definately suggest using the AWE enabled option set to true.
>> especially if each node is dedicated to sql server. you will have to
>> restart the sql services to have the changes take place. check the
>> BOL: awe enabled option and the section managing awe memory.
>> Sean T Shen wrote:
>> Hi, I have the following questions regarding AWE on SQL 2000 with
>> Windows
>> 2000 Advanced Server and on a cluster (both nodes have an active SQL
>> Server
>> instance running). The SA has upgraded the memory of both nodes to 8G
>> each.
>> And I am tempted to use AWE to boost my SQL Server performance. The
>> server
>> is dedicated to running SQL Server.
>> After AWE is enabled, can I configure my SQL Server max memory to
>> something
>> like 7G?
>> Concern is, while it may run OK during normal operating mode, what
>> will
>> happen in case both SQL Servers are moved to one node?
>> Thanks very much in advance.
>> Sean
>>
>|||The bigger question is, can I even set anything up beyond 3.5G per each SQL
instance? If I do, will I not be able to move both SQL node resources to
one node (thus defeats the purpose of cluster HA protection)?
"GlennThomas5" <glennthomas5@.gmail.com> wrote in message
news:1158948261.902182.108570@.d34g2000cwd.googlegroups.com...
>I think your right about the 1GB for the OS on clustered services...he
> definately needs to factor in outside things such as the clustered
> services, other applications, etc.
> Arnie Rowland wrote:
>> As I understand your situation, by use AWE and setting max memory to 7 GB
>> for the active virtual server, there will be no memory available in case
>> of
>> a 'fail-over' (With AWE, the OS requires 1 GB).
>> I believe that you need to set max memory to an amount that leaves
>> adequate
>> memory for the other virtual server in case of fail-over.
>> From:
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/hasog01.mspx
>> Allocating Memory
>> If you are running multiple instances of SQL Server on separate nodes in
>> the
>> cluster, you must determine the total amount of physical memory required
>> for
>> each server and determine the appropriate allocation of memory for each
>> SQL
>> Server instance. Performance of SQL Server is directly related to the
>> amount
>> of physical memory allocated to it by the operating system. SQL Server
>> provides two modes for memory management: dynamic and fixed. Dynamic
>> memory
>> allocation lets SQL acquire and release memory from the operating system
>> as
>> needed. Dynamic memory allows you to set a maximum amount of physical
>> memory
>> that the operating system can allocate to SQL Server. This allows the
>> remaining memory to be reserved for other purposes, such as the operating
>> system and other SQL Server instances. Fixed memory allows you to set a
>> specific amount of physical memory that SQL Server can use. This amount
>> neither grows nor shrinks.
>> Configure each server with the same amount of physical memory, and then
>> allocate memory to each SQL Server instance to ensure that sufficient
>> memory
>> is available to support the failover of a SQL Server instance from
>> another
>> node. If a server does not use a lot of memory compared to the available
>> memory on the server, specify dynamic memory and set the maximum amount
>> of
>> memory that the operating system can allocate. By doing so, you leave
>> sufficient memory for the failover to occur quickly and successfully. If
>> an
>> instance uses all of the available physical memory, failover will occur
>> more
>> slowly and can fail if memory is not released quickly enough.
>>
>> --
>> Arnie Rowland, Ph.D.
>> Westwood Consulting, Inc
>>
>> Most good judgment comes from experience.
>> Most experience comes from bad judgment.
>> - Anonymous
>>
>> "GlennThomas5" <glennthomas5@.gmail.com> wrote in message
>> news:1158944385.401752.80530@.e3g2000cwe.googlegroups.com...
>> >I would definately suggest using the AWE enabled option set to true.
>> > especially if each node is dedicated to sql server. you will have to
>> > restart the sql services to have the changes take place. check the
>> > BOL: awe enabled option and the section managing awe memory.
>> >
>> > Sean T Shen wrote:
>> >> Hi, I have the following questions regarding AWE on SQL 2000 with
>> >> Windows
>> >> 2000 Advanced Server and on a cluster (both nodes have an active SQL
>> >> Server
>> >> instance running). The SA has upgraded the memory of both nodes to 8G
>> >> each.
>> >> And I am tempted to use AWE to boost my SQL Server performance. The
>> >> server
>> >> is dedicated to running SQL Server.
>> >>
>> >> After AWE is enabled, can I configure my SQL Server max memory to
>> >> something
>> >> like 7G?
>> >> Concern is, while it may run OK during normal operating mode, what
>> >> will
>> >> happen in case both SQL Servers are moved to one node?
>> >>
>> >> Thanks very much in advance.
>> >> Sean
>> >
>|||Actually, you are not 'wasting' any memory -you are 'buying' high
availability (fail-over) insurance -and the cost that insurance is reflected
in the price of licenses, increased hardware costs, memory, etc.
You have a couple of options. (these comments are NOT germane for 64 bit
Windows and 64 bit SQL Server.)
1. Set memory statically at 3.5 GB per node.
2. Set memory for SQL to dynamically acquire, with a min size of 3.5 GB and
a max size of, perhaps 6 GB. That will leave 1 GB for the failed over
virtual server to grab a starting point and then to negotiate with the OS
for more memory from the active virtual server.
Your other questions:
Don't use AWE unless a virtual server will have more than 4 GB -so option 1
above does not use AWE.
SQL Server will use memory about 2 GB for data cache only. Procedure cache,
and all other SQL memory needs come out of 2 GB
SQL Server does not immediately 'acquire' the max memory, it will increase
its demand from the OS as need requires.
SQL Server will use AWE memory above 4 GB for data cache only.
I hope this, and the articles I posted earlier, help.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Sean T Shen" <seantshen@.optonline.net> wrote in message
news:OU61lUn3GHA.1304@.TK2MSFTNGP05.phx.gbl...
>I am facing a dilemma:
> For each node, the total memory is 8G.
> If I configure each SQL instance as having 3.5G, then when both of the SQL
> instances running on the same node (the failover situation), I would have
> 3.5x2=7G total, dedicated, statically, to SQL Servers, leaving 1G for OS.
> But, most of the time, I am running each SQL on each node, thus I am
> wasting all this memory (8-3.5=4.5G for each node).
> In this case, looks like I am better off not upgrading the physical
> memory. If I just configure each node with 4G of memory each, and have SQL
> Server dynamically eat up its uage (with a max server memory set to 3.5),
> then I still will get 3.5G for each SQL Server. And when both instances
> fails over to one side, SQL and OS will dynamically negotiate their memory
> needs.
> Plus, I have one other cluster with AWE set. they each have 8G. So here
> is what I did
> Boot.ini --> /3GB /PAE --> reboot
> SQL
> sp_configure 'awe', 1
> reconfigure
> sp_configure 'max server memory', 3584 /* 1024x3.5 */
> reconfigure
> [I was worry about this failover situation, that is why I only set this to
> 3.5G to see what happens]
> You would expect that after this and everything rebooted, I should see my
> SQL Server statically grabs all the memory up to 3.5G. But it did not!!!!
> Contrary to what I have read in articles.
> Now my question is
> 1. In AWE, does or does not SQL Server use static memory allocation?
> 2. Will SQL Server ever take advantage of AWE, even if I counter it to use
> 6 or 7G?
> 3. Do I need to set anything else in sp_configure other than what I have
> already done?
> Thanks for the answer already provided, and additional thanks in advance
> for any further info.
> Sean
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:%238CVjzm3GHA.600@.TK2MSFTNGP05.phx.gbl...
>> Let me add to that, as I understand it, that if you use 4 GB or less for
>> a SQL Server instance, by enabling AWE, you will experience a net loss.
>> See:
>> "AWE is a set of memory management extensions to the Microsoft Win32? API
>> that allows applications to address memory beyond 4 GB."
>> Memory -AWE, Not usable < 4GB
>> http://download.microsoft.com/download/9/c/c/9cc42e30-538b-4451-8fdb-7134a004f94c/Adv64BitEnv.doc
>>
>> --
>> Arnie Rowland, Ph.D.
>> Westwood Consulting, Inc
>> Most good judgment comes from experience.
>> Most experience comes from bad judgment.
>> - Anonymous
>>
>> "Arnie Rowland" <arnie@.1568.com> wrote in message
>> news:usYDLvm3GHA.5032@.TK2MSFTNGP04.phx.gbl...
>> As I understand your situation, by use AWE and setting max memory to 7
>> GB for the active virtual server, there will be no memory available in
>> case of a 'fail-over' (With AWE, the OS requires 1 GB).
>> I believe that you need to set max memory to an amount that leaves
>> adequate memory for the other virtual server in case of fail-over.
>> From:
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/hasog01.mspx
>> Allocating Memory
>> If you are running multiple instances of SQL Server on separate nodes in
>> the cluster, you must determine the total amount of physical memory
>> required for each server and determine the appropriate allocation of
>> memory for each SQL Server instance. Performance of SQL Server is
>> directly related to the amount of physical memory allocated to it by the
>> operating system. SQL Server provides two modes for memory management:
>> dynamic and fixed. Dynamic memory allocation lets SQL acquire and
>> release memory from the operating system as needed. Dynamic memory
>> allows you to set a maximum amount of physical memory that the operating
>> system can allocate to SQL Server. This allows the remaining memory to
>> be reserved for other purposes, such as the operating system and other
>> SQL Server instances. Fixed memory allows you to set a specific amount
>> of physical memory that SQL Server can use. This amount neither grows
>> nor shrinks.
>> Configure each server with the same amount of physical memory, and then
>> allocate memory to each SQL Server instance to ensure that sufficient
>> memory is available to support the failover of a SQL Server instance
>> from another node. If a server does not use a lot of memory compared to
>> the available memory on the server, specify dynamic memory and set the
>> maximum amount of memory that the operating system can allocate. By
>> doing so, you leave sufficient memory for the failover to occur quickly
>> and successfully. If an instance uses all of the available physical
>> memory, failover will occur more slowly and can fail if memory is not
>> released quickly enough.
>>
>> --
>> Arnie Rowland, Ph.D.
>> Westwood Consulting, Inc
>>
>> Most good judgment comes from experience.
>> Most experience comes from bad judgment.
>> - Anonymous
>>
>> "GlennThomas5" <glennthomas5@.gmail.com> wrote in message
>> news:1158944385.401752.80530@.e3g2000cwe.googlegroups.com...
>>I would definately suggest using the AWE enabled option set to true.
>> especially if each node is dedicated to sql server. you will have to
>> restart the sql services to have the changes take place. check the
>> BOL: awe enabled option and the section managing awe memory.
>> Sean T Shen wrote:
>> Hi, I have the following questions regarding AWE on SQL 2000 with
>> Windows
>> 2000 Advanced Server and on a cluster (both nodes have an active SQL
>> Server
>> instance running). The SA has upgraded the memory of both nodes to 8G
>> each.
>> And I am tempted to use AWE to boost my SQL Server performance. The
>> server
>> is dedicated to running SQL Server.
>> After AWE is enabled, can I configure my SQL Server max memory to
>> something
>> like 7G?
>> Concern is, while it may run OK during normal operating mode, what
>> will
>> happen in case both SQL Servers are moved to one node?
>> Thanks very much in advance.
>> Sean
>>
>>
>|||That should have read 'above'.
> SQL Server will use memory above 2 GB for data cache only.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:%23Ttdlyn3GHA.476@.TK2MSFTNGP06.phx.gbl...
> Actually, you are not 'wasting' any memory -you are 'buying' high
> availability (fail-over) insurance -and the cost that insurance is
> reflected in the price of licenses, increased hardware costs, memory, etc.
> You have a couple of options. (these comments are NOT germane for 64 bit
> Windows and 64 bit SQL Server.)
> 1. Set memory statically at 3.5 GB per node.
> 2. Set memory for SQL to dynamically acquire, with a min size of 3.5 GB
> and a max size of, perhaps 6 GB. That will leave 1 GB for the failed over
> virtual server to grab a starting point and then to negotiate with the OS
> for more memory from the active virtual server.
> Your other questions:
> Don't use AWE unless a virtual server will have more than 4 GB -so option
> 1 above does not use AWE.
> SQL Server will use memory about 2 GB for data cache only. Procedure
> cache, and all other SQL memory needs come out of 2 GB
> SQL Server does not immediately 'acquire' the max memory, it will increase
> its demand from the OS as need requires.
> SQL Server will use AWE memory above 4 GB for data cache only.
> I hope this, and the articles I posted earlier, help.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Sean T Shen" <seantshen@.optonline.net> wrote in message
> news:OU61lUn3GHA.1304@.TK2MSFTNGP05.phx.gbl...
>>I am facing a dilemma:
>> For each node, the total memory is 8G.
>> If I configure each SQL instance as having 3.5G, then when both of the
>> SQL instances running on the same node (the failover situation), I would
>> have 3.5x2=7G total, dedicated, statically, to SQL Servers, leaving 1G
>> for OS.
>> But, most of the time, I am running each SQL on each node, thus I am
>> wasting all this memory (8-3.5=4.5G for each node).
>> In this case, looks like I am better off not upgrading the physical
>> memory. If I just configure each node with 4G of memory each, and have
>> SQL Server dynamically eat up its uage (with a max server memory set to
>> 3.5), then I still will get 3.5G for each SQL Server. And when both
>> instances fails over to one side, SQL and OS will dynamically negotiate
>> their memory needs.
>> Plus, I have one other cluster with AWE set. they each have 8G. So here
>> is what I did
>> Boot.ini --> /3GB /PAE --> reboot
>> SQL
>> sp_configure 'awe', 1
>> reconfigure
>> sp_configure 'max server memory', 3584 /* 1024x3.5 */
>> reconfigure
>> [I was worry about this failover situation, that is why I only set this
>> to 3.5G to see what happens]
>> You would expect that after this and everything rebooted, I should see my
>> SQL Server statically grabs all the memory up to 3.5G. But it did
>> not!!!! Contrary to what I have read in articles.
>> Now my question is
>> 1. In AWE, does or does not SQL Server use static memory allocation?
>> 2. Will SQL Server ever take advantage of AWE, even if I counter it to
>> use 6 or 7G?
>> 3. Do I need to set anything else in sp_configure other than what I have
>> already done?
>> Thanks for the answer already provided, and additional thanks in advance
>> for any further info.
>> Sean
>> "Arnie Rowland" <arnie@.1568.com> wrote in message
>> news:%238CVjzm3GHA.600@.TK2MSFTNGP05.phx.gbl...
>> Let me add to that, as I understand it, that if you use 4 GB or less for
>> a SQL Server instance, by enabling AWE, you will experience a net loss.
>> See:
>> "AWE is a set of memory management extensions to the Microsoft Win32?
>> API that allows applications to address memory beyond 4 GB."
>> Memory -AWE, Not usable < 4GB
>> http://download.microsoft.com/download/9/c/c/9cc42e30-538b-4451-8fdb-7134a004f94c/Adv64BitEnv.doc
>>
>> --
>> Arnie Rowland, Ph.D.
>> Westwood Consulting, Inc
>> Most good judgment comes from experience.
>> Most experience comes from bad judgment.
>> - Anonymous
>>
>> "Arnie Rowland" <arnie@.1568.com> wrote in message
>> news:usYDLvm3GHA.5032@.TK2MSFTNGP04.phx.gbl...
>> As I understand your situation, by use AWE and setting max memory to 7
>> GB for the active virtual server, there will be no memory available in
>> case of a 'fail-over' (With AWE, the OS requires 1 GB).
>> I believe that you need to set max memory to an amount that leaves
>> adequate memory for the other virtual server in case of fail-over.
>> From:
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/hasog01.mspx
>> Allocating Memory
>> If you are running multiple instances of SQL Server on separate nodes
>> in the cluster, you must determine the total amount of physical memory
>> required for each server and determine the appropriate allocation of
>> memory for each SQL Server instance. Performance of SQL Server is
>> directly related to the amount of physical memory allocated to it by
>> the operating system. SQL Server provides two modes for memory
>> management: dynamic and fixed. Dynamic memory allocation lets SQL
>> acquire and release memory from the operating system as needed. Dynamic
>> memory allows you to set a maximum amount of physical memory that the
>> operating system can allocate to SQL Server. This allows the remaining
>> memory to be reserved for other purposes, such as the operating system
>> and other SQL Server instances. Fixed memory allows you to set a
>> specific amount of physical memory that SQL Server can use. This amount
>> neither grows nor shrinks.
>> Configure each server with the same amount of physical memory, and then
>> allocate memory to each SQL Server instance to ensure that sufficient
>> memory is available to support the failover of a SQL Server instance
>> from another node. If a server does not use a lot of memory compared to
>> the available memory on the server, specify dynamic memory and set the
>> maximum amount of memory that the operating system can allocate. By
>> doing so, you leave sufficient memory for the failover to occur quickly
>> and successfully. If an instance uses all of the available physical
>> memory, failover will occur more slowly and can fail if memory is not
>> released quickly enough.
>>
>> --
>> Arnie Rowland, Ph.D.
>> Westwood Consulting, Inc
>>
>> Most good judgment comes from experience.
>> Most experience comes from bad judgment.
>> - Anonymous
>>
>> "GlennThomas5" <glennthomas5@.gmail.com> wrote in message
>> news:1158944385.401752.80530@.e3g2000cwe.googlegroups.com...
>>I would definately suggest using the AWE enabled option set to true.
>> especially if each node is dedicated to sql server. you will have to
>> restart the sql services to have the changes take place. check the
>> BOL: awe enabled option and the section managing awe memory.
>> Sean T Shen wrote:
>> Hi, I have the following questions regarding AWE on SQL 2000 with
>> Windows
>> 2000 Advanced Server and on a cluster (both nodes have an active SQL
>> Server
>> instance running). The SA has upgraded the memory of both nodes to
>> 8G each.
>> And I am tempted to use AWE to boost my SQL Server performance. The
>> server
>> is dedicated to running SQL Server.
>> After AWE is enabled, can I configure my SQL Server max memory to
>> something
>> like 7G?
>> Concern is, while it may run OK during normal operating mode,
>> what will
>> happen in case both SQL Servers are moved to one node?
>> Thanks very much in advance.
>> Sean
>>
>>
>>
>|||Not sure if I have successfully posted my reply.
Trying it again
Well, this is what I quote from a Microsoft article...do not have it handy
right now...but this is what it says
--
One caution about using the "awe enabled" setting is that after turning it
on, SQL Server no longer dynamically manages memory. Instead, it takes all
of the available RAM (except about 128MB which is left for the operating
system). If you want to prevent SQL Server from taking all of the RAM, you
must set the "max server memory" option (described in more detail later in
this article) to a figure that limits SQL Server to the amount or RAM you
specify.
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:%23Ttdlyn3GHA.476@.TK2MSFTNGP06.phx.gbl...
> Actually, you are not 'wasting' any memory -you are 'buying' high
> availability (fail-over) insurance -and the cost that insurance is
> reflected in the price of licenses, increased hardware costs, memory, etc.
> You have a couple of options. (these comments are NOT germane for 64 bit
> Windows and 64 bit SQL Server.)
> 1. Set memory statically at 3.5 GB per node.
> 2. Set memory for SQL to dynamically acquire, with a min size of 3.5 GB
> and a max size of, perhaps 6 GB. That will leave 1 GB for the failed over
> virtual server to grab a starting point and then to negotiate with the OS
> for more memory from the active virtual server.
> Your other questions:
> Don't use AWE unless a virtual server will have more than 4 GB -so option
> 1 above does not use AWE.
> SQL Server will use memory about 2 GB for data cache only. Procedure
> cache, and all other SQL memory needs come out of 2 GB
> SQL Server does not immediately 'acquire' the max memory, it will increase
> its demand from the OS as need requires.
> SQL Server will use AWE memory above 4 GB for data cache only.
> I hope this, and the articles I posted earlier, help.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Sean T Shen" <seantshen@.optonline.net> wrote in message
> news:OU61lUn3GHA.1304@.TK2MSFTNGP05.phx.gbl...
>>I am facing a dilemma:
>> For each node, the total memory is 8G.
>> If I configure each SQL instance as having 3.5G, then when both of the
>> SQL instances running on the same node (the failover situation), I would
>> have 3.5x2=7G total, dedicated, statically, to SQL Servers, leaving 1G
>> for OS.
>> But, most of the time, I am running each SQL on each node, thus I am
>> wasting all this memory (8-3.5=4.5G for each node).
>> In this case, looks like I am better off not upgrading the physical
>> memory. If I just configure each node with 4G of memory each, and have
>> SQL Server dynamically eat up its uage (with a max server memory set to
>> 3.5), then I still will get 3.5G for each SQL Server. And when both
>> instances fails over to one side, SQL and OS will dynamically negotiate
>> their memory needs.
>> Plus, I have one other cluster with AWE set. they each have 8G. So here
>> is what I did
>> Boot.ini --> /3GB /PAE --> reboot
>> SQL
>> sp_configure 'awe', 1
>> reconfigure
>> sp_configure 'max server memory', 3584 /* 1024x3.5 */
>> reconfigure
>> [I was worry about this failover situation, that is why I only set this
>> to 3.5G to see what happens]
>> You would expect that after this and everything rebooted, I should see my
>> SQL Server statically grabs all the memory up to 3.5G. But it did
>> not!!!! Contrary to what I have read in articles.
>> Now my question is
>> 1. In AWE, does or does not SQL Server use static memory allocation?
>> 2. Will SQL Server ever take advantage of AWE, even if I counter it to
>> use 6 or 7G?
>> 3. Do I need to set anything else in sp_configure other than what I have
>> already done?
>> Thanks for the answer already provided, and additional thanks in advance
>> for any further info.
>> Sean
>> "Arnie Rowland" <arnie@.1568.com> wrote in message
>> news:%238CVjzm3GHA.600@.TK2MSFTNGP05.phx.gbl...
>> Let me add to that, as I understand it, that if you use 4 GB or less for
>> a SQL Server instance, by enabling AWE, you will experience a net loss.
>> See:
>> "AWE is a set of memory management extensions to the Microsoft Win32?
>> API that allows applications to address memory beyond 4 GB."
>> Memory -AWE, Not usable < 4GB
>> http://download.microsoft.com/download/9/c/c/9cc42e30-538b-4451-8fdb-7134a004f94c/Adv64BitEnv.doc
>>
>> --
>> Arnie Rowland, Ph.D.
>> Westwood Consulting, Inc
>> Most good judgment comes from experience.
>> Most experience comes from bad judgment.
>> - Anonymous
>>
>> "Arnie Rowland" <arnie@.1568.com> wrote in message
>> news:usYDLvm3GHA.5032@.TK2MSFTNGP04.phx.gbl...
>> As I understand your situation, by use AWE and setting max memory to 7
>> GB for the active virtual server, there will be no memory available in
>> case of a 'fail-over' (With AWE, the OS requires 1 GB).
>> I believe that you need to set max memory to an amount that leaves
>> adequate memory for the other virtual server in case of fail-over.
>> From:
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/hasog01.mspx
>> Allocating Memory
>> If you are running multiple instances of SQL Server on separate nodes
>> in the cluster, you must determine the total amount of physical memory
>> required for each server and determine the appropriate allocation of
>> memory for each SQL Server instance. Performance of SQL Server is
>> directly related to the amount of physical memory allocated to it by
>> the operating system. SQL Server provides two modes for memory
>> management: dynamic and fixed. Dynamic memory allocation lets SQL
>> acquire and release memory from the operating system as needed. Dynamic
>> memory allows you to set a maximum amount of physical memory that the
>> operating system can allocate to SQL Server. This allows the remaining
>> memory to be reserved for other purposes, such as the operating system
>> and other SQL Server instances. Fixed memory allows you to set a
>> specific amount of physical memory that SQL Server can use. This amount
>> neither grows nor shrinks.
>> Configure each server with the same amount of physical memory, and then
>> allocate memory to each SQL Server instance to ensure that sufficient
>> memory is available to support the failover of a SQL Server instance
>> from another node. If a server does not use a lot of memory compared to
>> the available memory on the server, specify dynamic memory and set the
>> maximum amount of memory that the operating system can allocate. By
>> doing so, you leave sufficient memory for the failover to occur quickly
>> and successfully. If an instance uses all of the available physical
>> memory, failover will occur more slowly and can fail if memory is not
>> released quickly enough.
>>
>> --
>> Arnie Rowland, Ph.D.
>> Westwood Consulting, Inc
>>
>> Most good judgment comes from experience.
>> Most experience comes from bad judgment.
>> - Anonymous
>>
>> "GlennThomas5" <glennthomas5@.gmail.com> wrote in message
>> news:1158944385.401752.80530@.e3g2000cwe.googlegroups.com...
>>I would definately suggest using the AWE enabled option set to true.
>> especially if each node is dedicated to sql server. you will have to
>> restart the sql services to have the changes take place. check the
>> BOL: awe enabled option and the section managing awe memory.
>> Sean T Shen wrote:
>> Hi, I have the following questions regarding AWE on SQL 2000 with
>> Windows
>> 2000 Advanced Server and on a cluster (both nodes have an active SQL
>> Server
>> instance running). The SA has upgraded the memory of both nodes to
>> 8G each.
>> And I am tempted to use AWE to boost my SQL Server performance. The
>> server
>> is dedicated to running SQL Server.
>> After AWE is enabled, can I configure my SQL Server max memory to
>> something
>> like 7G?
>> Concern is, while it may run OK during normal operating mode,
>> what will
>> happen in case both SQL Servers are moved to one node?
>> Thanks very much in advance.
>> Sean
>>
>>
>>
>
Subscribe to:
Posts (Atom)