AWE Enabled But SQL Service Only Using 175Mb RAM?

Question

Post reply

AWE Enabled But SQL Service Only Using 175Mb RAM?

Richard Bates

SSC Enthusiast

Points: 163
More actions
August 1, 2007 at 5:09 am

#118033

Hello,
I've just implemented a new SQL 2000 server running Windows 2003 Enterprise and SQL Server 2000 Enterprise. The server has 24Gb RAM of which SQL was only using 2Gb. So after a bit of googling, I found I should add the /PAE switch to the boot.ini and enable AWE within SQL. I did this and now task manager shows 12.6Gb RAM in use although the SQL process is only using 175Mb ??? Is this right? I understand that after AWE is enabled, SQL will reserve as much RAM as possible at boot up, but I would expect this memory to show as being allocated to the SQL process. I've not used the /3GB in the boot.ini and I've not used the max server memory option. Should I do the later?
Thanks in advance.
R.

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

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

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 1

Richard,

first of all Taskmanager is not very reliable when showing SQL Server memory. Use Performance Monitor instead.

Second SQL Server wil not reserve RAM at startup unless you defined a minimum value. Anyhow if you're using more than 4 Gb of memory it is recommend to set the minimum and the max memory setting to the same value.

You should not use the 3Gb switch because if your server as more than 16 gb of RAM 1 Gb is not enough for the OS.

See also http://www.sql-server-performance.com/articles/per/awe_memory_sql2000_p1.aspx

You might also want to check this article: http://support.microsoft.com/kb/899761

Markus

[font="Verdana"]Markus Bohse[/font]

Richard Bates SSC Enthusiast Points: 163 More actions · Answer 2

Hi Mark,

Thanks for the reply.

Performance monitor is ugly! I've looked at the available memory counters but there's not one that shows me memory in use or not an obvious one anyway. I would really like to see how much RAM is being used by SQL. Is there a better tool for the job?

I read the above article before I changed the AWE setting on the server. I didn't add the /3GB switch because I have more than 16Gb RAM but I did add the /PAE. So with 24Gb RAM, what would you recommend setting the min and max server ram too? The max server memory is currently set to 2147483647. According to Perfmon, I have 11076mb free and according to Taskman I have 12.6Gb in use. So I make that 24Gb minus 12 = 12gb which is about right, but I can't really see what SQL is doing because I still have 12Gb of memory doing nothing.

Cheers.

R.

george sibbald SSC Guru Points: 104210 More actions · Answer 3

In perfmon check out SQL server:Memory Manager Total and target server memory, this will show you memory being used by SQL (values should be the same). If you have not capped the memory SLQ can use in any way perhaps 175MB is all the memory it needs!

---------------------------------------------------------------------

Jeff Gray SSChampion Points: 10667 More actions · Answer 4

"I understand that after AWE is enabled, SQL will reserve as much RAM as possible at boot up"

When the SQL service starts it will reserve the memory, not at boot up. It reserves that memory in such a way that the task manager only knows that it is used, but not by who or what. So it shows up as allocated but not to a specific process.

This is an oversimplification, but what you see is normal for AWE.

Richard Bates SSC Enthusiast Points: 163 More actions · Answer 5

Thanks for your replies. I added the target server memory as you suggested and perfmon shows 12496264 in use which still means s I have nearly half my RAM available. Is this right or can I tell SQL to use more?

Cheers.

TGG.

george sibbald SSC Guru Points: 104210 More actions · Answer 6

reading your first entry again, set your max server memory in SQL. Leave enough for any other processes on the server. The OS should have at least 2GB.

see http://www.sql-server-performance.com/articles/per/awe_memory_sql2000_p1.aspx

---------------------------------------------------------------------

Richard Bates SSC Enthusiast Points: 163 More actions · Answer 7

Hi George,

Would you recommend the below:

SP_CONFIGURE 'max server memory', 20480

RECONFIGURE

GO

This should leave about 4Gb for everything other than SQL.

After I apply the bit of script and restart SQL, should I see a change in memory usage? Currently the server is only using 12 of the 24Gb of ram. We have several databases approx 20 - 30Gb each running on the server.

Thanks again.

TGG.