Autonumber field: cluster

Showing posts with label cluster. Show all posts

Sunday, March 11, 2012

AWE SQL Performance Problem

We recently enabled AWE on a SQL Server cluster. Each box has 8CPU and 8GB R
AM. The setting seems to have made the difference in RAM usage, but we are e
xperiencing extremely high CPU utilization and overall the system does not s
eem to be any faster. If an
ything, it seems to have slowed down some. There are a lot of variables, but
I was wondering of anyone has had adverse effects with the AWE setting.From BOL.
<<
To enable AWE, set awe enabled to 1. SQL Server will reserve almost all
available memory, leaving 128 megabytes (MB) or less, unless a value has
been specified for max server memory.
What is your max memory setting? Leaving 128M for the OS probably isn't a
good idea.
BTW... Profiler may be helpful in understanding the CPU usage.
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Phil Appel" <phil@.chileinc.com> wrote in message
news:774F1405-0770-4A4F-B551-9CC330AE9CE4@.microsoft.com...
> We recently enabled AWE on a SQL Server cluster. Each box has 8CPU and 8GB
RAM. The setting seems to have made the difference in RAM usage, but we are
experiencing extremely high CPU utilization and overall the system does not
seem to be any faster. If anything, it seems to have slowed down some. There
are a lot of variables, but I was wondering of anyone has had adverse
effects with the AWE setting.|||We had some CPU spin and server instability issues that were instantaneously
and permenantly solved by turning off the /3GB switch and reducing max
server mem to 1.5GB less than physical. PSS said there was potential kernel
memory starvation with /3GB and max server memory set to near physical. Not
sure if this applies to your scenario.
"Phil Appel" <phil@.chileinc.com> wrote in message
news:774F1405-0770-4A4F-B551-9CC330AE9CE4@.microsoft.com...
> We recently enabled AWE on a SQL Server cluster. Each box has 8CPU and 8GB
RAM. The setting seems to have made the difference in RAM usage, but we are
experiencing extremely high CPU utilization and overall the system does not
seem to be any faster. If anything, it seems to have slowed down some. There
are a lot of variables, but I was wondering of anyone has had adverse
effects with the AWE setting.|||Hi Phil,
I am viewing your post and since we have not heard from you for some time
and I wonder how things go on your side. To disable the AWE to check if the
performance issue is related to the AWE is the method to narrow down
performance problems to AWE. Also, you could refer to the link of the
followings for reference:
How To Gather Information for Effective Troubleshooting of Performance
Issues
http://support.microsoft.com/defaul...microsoft.com:
80/support/kb/articles/Q175/6/58.ASP&NoWebContent=1
http://support.microsoft.com/defaul...kb;en-us;298475
Also, if the case is critical and it should be troubleshot by some advanced
tools which are not supported in the newsgroup. Therefore, we probably will
not be able to resolve the issue through the newsgroups. I'd recommend
opening a Support incident with Microsoft Support Services, so that a
dedicated Support Professional can assist with your advisory case. Please
be advised that contacting phone support will be a charged call.
To obtain the phone numbers for specific technology request please take a
look at the web site listed below.
http://support.microsoft.com/defaul...US;PHONENUMBERS
If you are outside the US please see http://support.microsoft.com for
regional support phone numbers.
Thanks.
Hope this helps.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.

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

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
>

AWE Memory

Hello
We have 2 servers in a cluster environment with 12 GB each. We install
SQL server 2000 in them and use the switches /3GB /PAE and use AWE enable
option. We allocated 7GB for SQL along with enough room for the OS and other
apps currently running on that box. My question is, when I check the Task
manager, it only shows that SQL uses 150Kb for memory although you can see
under memory counters that 7.5 is been used. Why can we not see SQL using
more that 150KB? is there anything wrong in our setting? Below are the Store
procedures used to enable AWE and allocate 7GB.
SP_CONFIGURE 'show advanced options', 1
RECONFIGURE
GO
SP_CONFIGURE 'awe enabled', 1
RECONFIGURE
GO
SP_CONFIGURE 'max server memory', 7168
RECONFIGURE
GO
Thanks
Firstly... if you have AWE, you dont need /3GB switch
Now coming to your question: SQL Server when its started it starts
taking memory from 1KB and grows as the resources need more memory...
and once it reaches min server memory, it will never go back to less
than Min Server memory..until you restart the SQL again.
And by configuring 7GB as Max Server memory, you are limiting the SQL
to not consume more than that...
And incase in some scenerio where SQL has any amount of memory and if
OS need some of that.. it will release that memory only if SQL doesnt
need that at that point of time.
So if you see that SQL is using only 150KB, then dont be alarmed.. its
expected behaviour
|||Task manager isn't AWE/PAE aware. Use performance monitor.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Dario" <Dario@.discussions.microsoft.com> wrote in message
news:CF884EB2-F09C-4412-8FC9-547CFC6844CC@.microsoft.com...
> Hello
> We have 2 servers in a cluster environment with 12 GB each. We install
> SQL server 2000 in them and use the switches /3GB /PAE and use AWE enable
> option. We allocated 7GB for SQL along with enough room for the OS and other
> apps currently running on that box. My question is, when I check the Task
> manager, it only shows that SQL uses 150Kb for memory although you can see
> under memory counters that 7.5 is been used. Why can we not see SQL using
> more that 150KB? is there anything wrong in our setting? Below are the Store
> procedures used to enable AWE and allocate 7GB.
> SP_CONFIGURE 'show advanced options', 1
> RECONFIGURE
> GO
> SP_CONFIGURE 'awe enabled', 1
> RECONFIGURE
> GO
> SP_CONFIGURE 'max server memory', 7168
> RECONFIGURE
> GO
> Thanks
>
>

AWE Memory

Hello
We have 2 servers in a cluster environment with 12 GB each. We install
SQL server 2000 in them and use the switches /3GB /PAE and use AWE enable
option. We allocated 7GB for SQL along with enough room for the OS and other
apps currently running on that box. My question is, when I check the Task
manager, it only shows that SQL uses 150Kb for memory although you can see
under memory counters that 7.5 is been used. Why can we not see SQL using
more that 150KB? is there anything wrong in our setting? Below are the Store
procedures used to enable AWE and allocate 7GB.
SP_CONFIGURE 'show advanced options', 1
RECONFIGURE
GO
SP_CONFIGURE 'awe enabled', 1
RECONFIGURE
GO
SP_CONFIGURE 'max server memory', 7168
RECONFIGURE
GO
ThanksFirstly... if you have AWE, you dont need /3GB switch
Now coming to your question: SQL Server when its started it starts
taking memory from 1KB and grows as the resources need more memory...
and once it reaches min server memory, it will never go back to less
than Min Server memory..until you restart the SQL again.
And by configuring 7GB as Max Server memory, you are limiting the SQL
to not consume more than that...
And incase in some scenerio where SQL has any amount of memory and if
OS need some of that.. it will release that memory only if SQL doesnt
need that at that point of time.
So if you see that SQL is using only 150KB, then dont be alarmed.. its
expected behaviour|||Task manager isn't AWE/PAE aware. Use performance monitor.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Dario" <Dario@.discussions.microsoft.com> wrote in message
news:CF884EB2-F09C-4412-8FC9-547CFC6844CC@.microsoft.com...
> Hello
> We have 2 servers in a cluster environment with 12 GB each. We install
> SQL server 2000 in them and use the switches /3GB /PAE and use AWE enable
> option. We allocated 7GB for SQL along with enough room for the OS and other
> apps currently running on that box. My question is, when I check the Task
> manager, it only shows that SQL uses 150Kb for memory although you can see
> under memory counters that 7.5 is been used. Why can we not see SQL using
> more that 150KB? is there anything wrong in our setting? Below are the Store
> procedures used to enable AWE and allocate 7GB.
> SP_CONFIGURE 'show advanced options', 1
> RECONFIGURE
> GO
> SP_CONFIGURE 'awe enabled', 1
> RECONFIGURE
> GO
> SP_CONFIGURE 'max server memory', 7168
> RECONFIGURE
> GO
> Thanks
>
>

AWE Memory

Hello
We have 2 servers in a cluster environment with 12 GB each. We install
SQL server 2000 in them and use the switches /3GB /PAE and use AWE enable
option. We allocated 7GB for SQL along with enough room for the OS and other
apps currently running on that box. My question is, when I check the Task
manager, it only shows that SQL uses 150Kb for memory although you can see
under memory counters that 7.5 is been used. Why can we not see SQL using
more that 150KB? is there anything wrong in our setting? Below are the Store
procedures used to enable AWE and allocate 7GB.
SP_CONFIGURE 'show advanced options', 1
RECONFIGURE
GO
SP_CONFIGURE 'awe enabled', 1
RECONFIGURE
GO
SP_CONFIGURE 'max server memory', 7168
RECONFIGURE
GO
ThanksFirstly... if you have AWE, you dont need /3GB switch
Now coming to your question: SQL Server when its started it starts
taking memory from 1KB and grows as the resources need more memory...
and once it reaches min server memory, it will never go back to less
than Min Server memory..until you restart the SQL again.
And by configuring 7GB as Max Server memory, you are limiting the SQL
to not consume more than that...
And incase in some scenerio where SQL has any amount of memory and if
OS need some of that.. it will release that memory only if SQL doesnt
need that at that point of time.
So if you see that SQL is using only 150KB, then dont be alarmed.. its
expected behaviour|||Task manager isn't AWE/PAE aware. Use performance monitor.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Dario" <Dario@.discussions.microsoft.com> wrote in message
news:CF884EB2-F09C-4412-8FC9-547CFC6844CC@.microsoft.com...
> Hello
> We have 2 servers in a cluster environment with 12 GB each. We instal
l
> SQL server 2000 in them and use the switches /3GB /PAE and use AWE enable
> option. We allocated 7GB for SQL along with enough room for the OS and oth
er
> apps currently running on that box. My question is, when I check the Task
> manager, it only shows that SQL uses 150Kb for memory although you can see
> under memory counters that 7.5 is been used. Why can we not see SQL using
> more that 150KB? is there anything wrong in our setting? Below are the Sto
re
> procedures used to enable AWE and allocate 7GB.
> SP_CONFIGURE 'show advanced options', 1
> RECONFIGURE
> GO
> SP_CONFIGURE 'awe enabled', 1
> RECONFIGURE
> GO
> SP_CONFIGURE 'max server memory', 7168
> RECONFIGURE
> GO
> Thanks
>
>

Wednesday, March 7, 2012

AWE in SQL Cluster

I have a two node cluster with SQL Server 2005 that I am having difficulty
getting AWE to work. I have granted the "lock pages in memory" right to the
domain account that the SQL Server service is running under on both nodes
and run the following commands to setup the AWE memory.
sp_configure 'awe enabled', 1
go
reconfigure with override
go
sp_configure 'max server memory','6144'
go
reconfigure with override
go
I get the following error message when these statements complete:
Msg 5845, Level 16, State 1, Line 1
Address Windowing Extensions (AWE) requires the 'lock pages in memory'
privilege which is not currently present in the access token of the process.
Don't grant the privilege to the account. Grant it to the domain group you
designated when you installed SQL.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Cgal" <cgallelli@.newsgroups.nospam> wrote in message
news:uREFgcPrHHA.4324@.TK2MSFTNGP04.phx.gbl...
>I have a two node cluster with SQL Server 2005 that I am having difficulty
>getting AWE to work. I have granted the "lock pages in memory" right to
>the domain account that the SQL Server service is running under on both
>nodes and run the following commands to setup the AWE memory.
> sp_configure 'awe enabled', 1
> go
> reconfigure with override
> go
> sp_configure 'max server memory','6144'
> go
> reconfigure with override
> go
> I get the following error message when these statements complete:
> Msg 5845, Level 16, State 1, Line 1
> Address Windowing Extensions (AWE) requires the 'lock pages in memory'
> privilege which is not currently present in the access token of the
> process.
>
>
>
|||We got the same error, we restarted SQL, went back in and looked - it is now
enabled ;) We are on SP2 of Windows and SQL 2005.
Cheers,
Rodney R. Fournier
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://msmvps.com/clustering - Blog
http://www.clusterhelp.com - Cluster Training
ClusterHelp.com is a Microsoft Certified Gold Partner
"Cgal" <cgallelli@.newsgroups.nospam> wrote in message
news:uREFgcPrHHA.4324@.TK2MSFTNGP04.phx.gbl...
>I have a two node cluster with SQL Server 2005 that I am having difficulty
>getting AWE to work. I have granted the "lock pages in memory" right to
>the domain account that the SQL Server service is running under on both
>nodes and run the following commands to setup the AWE memory.
> sp_configure 'awe enabled', 1
> go
> reconfigure with override
> go
> sp_configure 'max server memory','6144'
> go
> reconfigure with override
> go
> I get the following error message when these statements complete:
> Msg 5845, Level 16, State 1, Line 1
> Address Windowing Extensions (AWE) requires the 'lock pages in memory'
> privilege which is not currently present in the access token of the
> process.
>
>
>
|||The Microsoft documentation said to add the security account but I did
actually add both the account and the security group before I got the error.
Do you think that I need to reboot to aleviate the error message?
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:eBNxIPQrHHA.1508@.TK2MSFTNGP05.phx.gbl...
> Don't grant the privilege to the account. Grant it to the domain group
> you designated when you installed SQL.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Cgal" <cgallelli@.newsgroups.nospam> wrote in message
> news:uREFgcPrHHA.4324@.TK2MSFTNGP04.phx.gbl...
>
|||A reboot probably won't hurt.
It does seem to fix a lot of things in the Microsoft world.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Cgal" <cgallelli@.newsgroups.nospam> wrote in message
news:OEtitcTrHHA.3248@.TK2MSFTNGP03.phx.gbl...
> The Microsoft documentation said to add the security account but I did
> actually add both the account and the security group before I got the
> error.
> Do you think that I need to reboot to aleviate the error message?
> "Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
> news:eBNxIPQrHHA.1508@.TK2MSFTNGP05.phx.gbl...
>
|||In order for new Windows level security permissions to be active (such as the
lock pages in memory right) the account must log out and back in. As this is
a service account, the services simply need to be restarted.
Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration /
Microsoft Office SharePoint Server 2007: Configuration)
MCITP (dbadmin, dbdev)
"Cgal" wrote:

> The Microsoft documentation said to add the security account but I did
> actually add both the account and the security group before I got the error.
> Do you think that I need to reboot to aleviate the error message?
> "Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
> news:eBNxIPQrHHA.1508@.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
>>
>>
>>
>

Sunday, March 11, 2012

AWE SQL Performance Problem

Thursday, March 8, 2012

AWE questions

AWE on 4GB multi-instance box?

AWE Memory

AWE Memory

AWE Memory

Wednesday, March 7, 2012

AWE in SQL Cluster

AWE and SQL Cluster

AWE and SQL Cluster

AWE and SQL Cluster

Autonumber field

Blog Archive

About Me