MS SQL Server - SQL 2000 and awe memory

Author

SQL 2000 and awe memory

paul

2006-12-06, 7:12 pm

Hello, anyone here with large memory and AWE experience? We have a server
with 32GB RAM, enabled AWE option, and /pae switch in boot.ini. I'm looking
for ways to verify if SQL Server is using all that extra RAM (which
performance counters, etc.)?
Thanks, Paul.

Geoff N. Hiten

2006-12-06, 7:12 pm

Yep. Been there, done that.

Obviously you have figured out that task manager cannot count high enough.
The performance monitor counter SQLServer:MEmoru Manager | Total Server
Memory is a reliable indicator of actual SQL Server Memory usage. The quick
way is to look at the PF usage chart in task manager. It will display AWE
committed memory as PF usage due to some interesting Windows internals.

--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP

"paul" <paul@discussions.microsoft.com> wrote in message
news:5E2CC3A4-E76D-4BE0-9246- A2867C5AEA29@microso
ft.com...
> Hello, anyone here with large memory and AWE experience? We have a server
> with 32GB RAM, enabled AWE option, and /pae switch in boot.ini. I'm
> looking
> for ways to verify if SQL Server is using all that extra RAM (which
> performance counters, etc.)?
> Thanks, Paul.
>

paul

2006-12-06, 7:12 pm

Thanks Geoff. That leaves me with trying to figure out why it's only using
10.5GB.
Here is what I have:
32GB RAM
Win Server 2003 enterprise edition SP1
/pae in boot.ini
SQL 2000 Enterprise SP4 (sqlservr.exe version 8.00.2187)
awe enabled 1
max server memory (MB) 28000
min server memory (MB) 3072
set working set size 1

Perfmon shws:
Total server memory 10.5 GB
Target server memeory 10.5 GB

Task manager PF usage 10.5 GB.

It looks like it is exhibiting calssic symptoms of KB899761, where only half
the memory is being used when on SQL Sp4.

The hotfix contains version of sqlservr.exe :
14-May-2005 2000.80.2040.0 (9,150,464 bytes)

My version sqlservr.exe is:
9-march-2006 2000.080.2187.00 (9,162,752 bytes)

So my version is newer... you think I need to apply this hotfix? you see
anything else wrong with my SQL config options?

Thanks again,
Paul

Geoff N. Hiten

2006-12-06, 7:12 pm

Looks good to me.

Hotfixes won't install on a higher version so re-installing 2040 is not an
option. Your service account needs 'Lock pages in Memory" rights for this
to work.

Note that the memory settings are not dynamic when AWE is involved. You
must stop and restart SQL server for the new allocation settings to take
effect.

--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP

"paul" <paul@discussions.microsoft.com> wrote in message
news:0805403E-68C9-4786-AFA7- D304A087528E@microso
ft.com...
> Thanks Geoff. That leaves me with trying to figure out why it's only using
> 10.5GB.
> Here is what I have:
> 32GB RAM
> Win Server 2003 enterprise edition SP1
> /pae in boot.ini
> SQL 2000 Enterprise SP4 (sqlservr.exe version 8.00.2187)
> awe enabled 1
> max server memory (MB) 28000
> min server memory (MB) 3072
> set working set size 1
>
> Perfmon shws:
> Total server memory 10.5 GB
> Target server memeory 10.5 GB
>
> Task manager PF usage 10.5 GB.
>
>
> It looks like it is exhibiting calssic symptoms of KB899761, where only
> half
> the memory is being used when on SQL Sp4.
>
> The hotfix contains version of sqlservr.exe :
> 14-May-2005 2000.80.2040.0 (9,150,464 bytes)
>
> My version sqlservr.exe is:
> 9-march-2006 2000.080.2187.00 (9,162,752 bytes)
>
> So my version is newer... you think I need to apply this hotfix? you see
> anything else wrong with my SQL config options?
>
> Thanks again,
> Paul

paul

2006-12-06, 7:12 pm

Our service account has "lock pages in memory" rights.

I guess I could set min and max memory to the same value, or set the fixed
size to 28 GB.

Also, our "set working set size" is switched to On (ie. reserve phys memeory
for sql server), is this option doing any good with AWE memory? Is it good to
use here?

Besides this and possibly the hotfix for KB899761, I can't see why our
server is only using 10.5 GB out of the 28GB that is configured in max memory.

Geoff, thanks for all your time and help with this.

Geoff N. Hiten

2006-12-06, 7:12 pm

The only thing I can think of is that another process or processes has that
memory locked and reserved. The KB899761 would show up as only 16GB
available to SQL Server, not 10.5 GB Setting min and max to the same value
shouldn't cause any problems, but AWE enabled effectively does exactly that.

--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP

"paul" <paul@discussions.microsoft.com> wrote in message
news:444C170D-5132-49FC-A44B- 60D684C86E7D@microso
ft.com...
> Our service account has "lock pages in memory" rights.
>
> I guess I could set min and max memory to the same value, or set the fixed
> size to 28 GB.
>
> Also, our "set working set size" is switched to On (ie. reserve phys
> memeory
> for sql server), is this option doing any good with AWE memory? Is it good
> to
> use here?
>
> Besides this and possibly the hotfix for KB899761, I can't see why our
> server is only using 10.5 GB out of the 28GB that is configured in max
> memory.
>
> Geoff, thanks for all your time and help with this.

Sponsored Links