SQL using only 80Mb of 7Gb available RAM

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

SQL using only 80Mb of 7Gb available RAM

Author

Topic

chaithanya
Starting Member

17 Posts

Posted - 2005-07-15 : 03:02:02

Hi All,

Environment:
Windows 2000 Advanced Server, Service Pack 4
MS SQL Server 2000 Enterprise Ed., Service Pack 3
7GB RAM

When i looked at MemUsage in the Process tab of Task Manager; SQL Server was using only 1.7 GB of Memory.

So enabled /PAE in boot.ini and AWE in SQL Server.

Now it's using only 80 MB of Memory.

Is this a bug or do i need to correct any other settings?

Chaithanya J

franco
Constraint Violating Yak Guru

255 Posts

Posted - 2005-07-15 : 04:00:57

Make sure your boot.ini contain both the “/3GB /PAE” switches.
If you don't have /3GB in your boot.ini will mean to have 2GB of RAM reserved for the operating system, instead of 1GB remaining free with the “/3GB” switch.
The “/PAE” switch is required if you want SQL Server to see more than 4GB of RAM.

Make also sure that the SQL Server service account has been granted “Lock Pages in Memory”) privileges.

Franco

chaithanya
Starting Member

17 Posts

Posted - 2005-07-15 : 04:41:18

Hi Franco,

Thank you. “Lock Pages in Memory” privilege has been granted..
Is it not true that /3GB switch restricts SQL Server Memory utilisation to 3GB? Out of the avilable 7GB i want SQL Server to utilise it to the maximum. I don't want to restrict to 3GB.
Did u get me?

Chaithanya J

franco
Constraint Violating Yak Guru

255 Posts

Posted - 2005-07-15 : 04:52:27

No, it's not true.
/3GB instruct OS to take 1GB instead of 2GB.
Please also note that once AWE is enabled, SQL Server will no longer dynamically manage memory.
SQL Server will take all available physical memory, leaving only 128MB for OS.
To enable xGB as the maximum SQL Server memory allowed to be consumed, issue the following command:

sp_configure ‘show advanced options’, 1
RECONFIGURE
GO

sp_configure ‘max server memory’, x
RECONFIGURE
GO

sp_configure ‘show advanced options’, 0
RECONFIGURE
GO

Reboot your Server.

Franco

chaithanya
Starting Member

17 Posts

Posted - 2005-07-15 : 05:45:31

Franco..do u mean to say all the “/3GB /PAE” and AWE should be enabled?
I've tried different versions like enabling /3GB alone and enabling /PAE and AWE. I've not tried enabling all the three..
If that is the case i'll ask my client to do so..

thank you..

Chaithanya J

franco
Constraint Violating Yak Guru

255 Posts

Posted - 2005-07-15 : 08:24:24

For a complete description of the /3GB, /PAE and AWE please refer to:
http://support.microsoft.com/default.aspx?scid=kb;en-us;283037

Franco

SQLServerDBA_Dan
Aged Yak Warrior

752 Posts

Posted - 2005-07-15 : 14:45:32

quote:
Originally posted by chaithanya

Franco..do u mean to say all the “/3GB /PAE” and AWE should be enabled?
I've tried different versions like enabling /3GB alone and enabling /PAE and AWE. I've not tried enabling all the three..
If that is the case i'll ask my client to do so..

thank you..

Chaithanya J

There is a bug with SP4 and AWE. This doesnt apply since you said you are running SP3, but you might take a look anyhow:

[url]http://www.microsoft.com/downloads/details.aspx?familyid=7C407047-3F1F-48B8-9E4C-DC32875E1961&displaylang=en[/url]

Daniel
SQL Server DBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources