You say you enabled AWE on SQL Server but did you activate /PAE in the boot.ini file?
PAE enables Large Memory support.
Activate PAE in Windows 32Bit Enterprise
c:\boot.ini
[boot loader]
timeout=30
default=multi(0)disk(2)rdisk(0)partition(1)\WINDOWS
[operating systems]
multi(0)disk(0)rdisk(0)partition(2)\WINDOWS="Windows Server 2003, Enterprise" /fastdetect /PAE
http://support.microsoft.com/kb/283037
Lock Pages in Memory
As part of enabling AWE, you would also need to give the SQL Server Service user the right to Lock Pages in Memory. Enabling this option in the Group Policy dialog (gpedit.msc), prevents Windows from paging memory out of the SQL Server working set for its own needs, especially if memory starts to run low on Windows.
http://msdn.microsoft.com/en-us/library/ms190730.aspx
Monitoring AWE Memory Usage
To test AWE usage, I setup a Windows 2003 Enterprise SP2 with PAE enabled in boot.ini. I used SQL Server 2005 32bit Standard SP4.
The total physical memory is 8192Mb. The max memory setting for SQL Server is configured at 8192 and the minimum is at 2048.
Does SQL Server see AWE on startup.
2012-02-09 07:27:51.35 Server Address Windowing Extensions is
enabled. This is an informational message only; no user action is
required.
Is SQL Server using Locked Pages for the Buffer Pool
According to some blogs, I should be seeing this message, but I'm not.
Using locked pages for buffer pool.
This is an informational message only; no user action is required.
Memory being allocated through AWE
SELECT SUM(awe_allocated_kb) / 1024 AS awe_allocated_mb
FROM sys.dm_os_memory_clerks ;
[3648]
Memory allocated to Multi-Page memory.
select sum(multi_pages_kb)/1024 as [MultiPage Memory, MB] from sys.dm_os_memory_clerks
[14]
Multi-page memory can not use AWE allocated memory
Memory being used outside the buffer pool
SELECT sum(multi_pages_kb
+ virtual_memory_committed_kb + shared_memory_committed_kb) AS
[Memory used outside BPool, mb]
FROM sys.dm_os_memory_clerks
WHERE type <> 'MEMORYCLERK_SQLBUFFERPOOL'
[24]
Overall memory allocation by component
SELECT type, (single_pages_kb)/1024 as Single_Pages_MB, (multi_pages_kb)/1024
AS Multi_Pages_MB, (awe_allocated_kb)/1024 as AWE_allocated_MB
FROM sys.dm_os_memory_clerks
GROUP BY type
ORDER BY 2 DESC
This will show you how single-page and multi-page memory is being used by each component. It will also tell you which one is able to benefit from AWE.
Mutli-page : when request exceeds 8Kb
Single-page : when request is less than or equal to 8Kb
As a side note, I have found sp_whoisactive very helpful in finding slow queries, memory hogs and just about everything that's going on in SQL Server. Here is a link that Brent Ozar provides on how to set it up and use it.
http://www.brentozar.com/archive/tag/sp_whoisactive/
As you might know, the reason it allocates 1.7 GB of working set is 32-bit memory limitations. On 32-bit editions of Windows, by default each process gets 2 GB of usable virtual address space, and by default 32-bit editions of SQL Server leave a few hundred MB outside of its largest consumer of memory, the buffer pool.
It is most likely just a misunderstanding that SQL Server allocated only 60 MB of memory when you enabled locked pages. Memory allocated through AWE doesn't show up on the processes tab in Task Manager (or other tools that report a figure based on the processes' working set size) because it isn't part of the process' working set. If SQL Server used a smaller amount of memory when you enabled Lock Pages in Memory, this suggests that AWE was working and the SQL Server process had most likely allocated more than 60 MB of memory.
There are several ways to view memory allocated by AWE, including but not limited to the following.
Query sys.dm_os_memory_clerks
, for example as follows:
SELECT SUM(awe_allocated_kb) AS [Total KB AWE Allocated]
FROM sys.dm_os_memory_clerks;
View the Performance Monitor counter SQL Server: Memory Manager\Total Server Memory (KB)
A few notes on your question:
As you probably know, basically anyone who does this for a living will tell you that it's long past time to migrate off the 32-bit operating system and 32-bit SQL Server. You'll be able to use far more RAM without any hassles on a 64-bit platform.
It's highly unusual for SQL Server to be running as the Network Service account.
Processes allocate memory, not RAM (which may not be the same thing)
You shouldn't need to add /PAE to boot.ini. Windows has booted a PAE-aware kernel by default since Windows 2003 SP1 unless you also disable Data Execution Prevention with the switch '/noexecute=alwaysoff'. (You don't want to do that, by the way.)
Using locked pages can cause out-of-memory errors if you don't set a reasonable memory limit in SQL Server or if something other than the SQL Server buffer pool -- either another program or another part of SQL Server -- is competing for memory in Windows.
References:
How It Works: SQL Server 32 bit PAE/AWE on (SQL 2005, 2008, and 2008 R2) – Not Using As Much RAM As Expected!
Fun with Locked Pages, AWE, Task Manager, and the Working Set…
Great SQL Server Debates: Lock Pages in Memory
Best Answer
Oh, goodness, I have some bad news here.
On a 32-bit OS, SQL Server only uses the first 4GB of memory for things like query workspace. (And it's fighting the OS for that 4GB, too - any other running apps will also compete for that memory.)
4GB might sound like a lot, but it's relatively easy to write a query that needs several GB of memory in order to run. When enough queries demand enough memory, SQL Server throws RESOURCE_SEMAPHORE waits because queries can't get enough memory in order to start. RESOURCE_SEMAPHORE_QUERY_COMPILE means they can't even get enough memory to compile an execution plan - and yeah, that's pretty bad.
So how do you fix it?
I hesitate to even say that last one, because the 32-bit issue is so bad, and it's really hard on the older versions of SQL Server. If you were on a current one, you could go through the plan cache and sort queries by memory grant, find the biggest grant recipients, and tune those. Not an option on this old antique, though.