AWE and failover clustering

Question

Post reply

AWE and failover clustering

BlueIvy

SSC-Addicted

Points: 486
More actions
July 21, 2005 at 7:19 pm

#186704

Hi,
Just did an upgrade to 8GB RAM on a active/active cluster. With AWE turned on we need to set the max server memory to 3Gb for each node.Before that I had upgraded servers to 4GB RAM with /3GB switch on and I had configured the server's max memory to 3GB as well leaving 1GB to OS.
What puzzles me is that what's the performance difference between the 3GB in AWE and without AWE?
Please advise
Thanks

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Ford Fairlane SSCertifiable Points: 7664 More actions · Answer 1

Hi,

Firstly you need to set the /PAE switch in the boot.ini as well as the /3GB: /3GB /PAE

The /3GB switch is used to tell SQL Server to take advantage of 3GB out of the base 4GB of RAM that Windows 2000 supports natively. If you don't specify this option, then SQL Server will only take advantage of 2GB of the first 4GB of RAM in the server, essentially wasting 1GB of RAM.

Hope this helps

Hope this helps...

Ford Fairlane
Rock and Roll Detective

BlueIvy SSC-Addicted Points: 486 More actions · Answer 2

HI Ford,

thanks for the feedback. Maybe my question here is not clear enough.Here's further elaboration on my question.

2 scenarios:

1. with total 4GB RAM being configured to 3GB memory as SQL Server max server memory (after /3GB is being turned on and AWE DISABLED)

2. with total 8GB RAM being configured to 3GB memory max server memory (due to failover considerations) with AWE ENABLED.

Now as you can see both scenario uses 3GB memory for SQL Server. What I wonder is the performance difference since both also using 3GB memory.

Ford Fairlane SSCertifiable Points: 7664 More actions · Answer 3

Hi Blue,

OK, firstly AWE memory cannot be dynamically managed, like memory is normally managed in SQL Server. This means that SQL Server will automatically grab all the RAM it can when it starts, so you need to set the maximum amount of memory that AWE memory can access, you can use SQL Server's "max server memory" configuration option. For example:

SP_CONFIGURE 'max server memory', 4096

RECONFIGURE

GO

Other wise you wont really see any performance gains etc.

You can track its performance characteristics using some special Performance Monitor counters found under the SQL Server Buffer Manager object. They include:

AWE Lookup Maps/Sec: This counter measures how many times that a specific database page was requested by SQL Server, was found in the buffer pool, and then was mapped as AWE memory (or the server's virtual address space). This would be a combination of the AWE Stolen Maps/Sec and AWE Write Maps/Sec described later.
AWE Stolen Maps/Sec: This counter measures how many times that a free database buffer was taken by SQL Server and mapped as AWE memory.
AWE Write Maps/Sec: When SQL Server runs out of free buffers to map to AWE memory, it has to write to a dirty buffer instead, which hurts performance because a disk write has to occur to clean up the dirty buffer before it can be used. This counter measures the number of times that SQL Server has to map a dirty buffer. If this figure is high, more memory should be considered.
AWE Unmap Call/Sec: Sometimes SQL Server will unmap buffers from AWE memory (because they have not been used lately). This counter measures how many times SQL Server calls for an unmap operation, which can affect one or more buffers at the same time.
AWE Unmap Pages/Sec: Closely related to the above counter, this counter specifically measures the number of SQL Server buffers that are unmapped.

Hope this helps....

Hope this helps...

Ford Fairlane
Rock and Roll Detective

BlueIvy SSC-Addicted Points: 486 More actions · Answer 4

Thanks for the useful tip.

But how about the memory utilisation between the 2 scenario? Which will be more efficient (since both max server memory also being configured to 3GB)?

By the way what is the threshold value for the AWE counters that you monitored? Especially on AWE Write Maps/Sec what value i should take note of and be alert of it?

Appreciate your advice

Ford Fairlane SSCertifiable Points: 7664 More actions · Answer 5

OK...

1. with total 4GB RAM being configured to 3GB memory as SQL Server max server memory (after /3GB is being turned on and AWE DISABLED)

2. with total 8GB RAM being configured to 3GB memory max server memory (due to failover considerations) with AWE ENABLED.

With only the /3GB and AWE the performace will be the same. SQL Server will only use 3GB of memory even if you have 8GB avalible unless you use the /PAE...

Hope this helps...

Ford Fairlane
Rock and Roll Detective

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 6

I understand where you're coming from and my suggestion is that you ignore the /3gb switch. Set /PAE on the server and allocate max and min memory for each instance to 3Gb and enable awe on for each instance.

I expect you're aware a number of sql server programs run out of memory space, if you're using a lot of dts and maint plans ( that use sysmaint.exe ) and especially if you're manipulating xml then don't use the /3gb switch.

With multiple instances it's important you fix the max and min memory for each instance so that you don't either exceed total memory or allow one instance to take out the other(s)

For clusters if it's an active/active failover then the total memory for all instances musn't exceed the total memory on one node. e,g, two nodes each with 16Gb ram - one instance on each assign sql memory total to not exceed 16Gb ( you effectively waste 50% of your memory on an active active )

for an active passive memory for each instance should be say, (total memory - 2gb)/no of instances.

To check out waht memory your instance is using read the memory counters in sysperfinfo in master database .

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

BlueIvy SSC-Addicted Points: 486 More actions · Answer 7

Appreciate all your valueable input guys ... it really solve my puzzle now...

hmm now I can sleep in sound *just kidding*

BlueIvy SSC-Addicted Points: 486 More actions · Answer 8

oh one last question...I have configured my max server memory for node A to 4GB and node B to 3GB is it ok? If not what will be the recommended setting?

Thanks again

Steve-3_5_7_9 SSChampion Points: 10832 More actions · Answer 9

For optimal performance you should reserve 500MB for the OS Kernal. Since this is an active/active cluster and total RAM is 4GB then each instance should have max memory set to 1.5GB RAM