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/
Best Answer
I don't know of a fully accurate and reliable way to track this.
One way to get at least something potentially useful is to keep snapshots of sys.dm_db_index_usage_stats, specifically the
user_lookupscolumn for index_id 0 (RID Lookup) and 1 (Key Lookup). This DMV can be reset for many reasons, including database or instance restarts. Rebuilding (but not reorganizing) and index also clears the related DMV entry on SQL Server 2012 and later, which can make things more difficult. You will need to capture information fairly regularly and use a heuristic to decide if the DMV reset between captures.The index usage stats DMV also only returns a count of the number of times a plan containing a Lookup is executed. A plan that contains a single Key Lookup will increment the counter by 1, regardless of the number of lookups actually performed. It will also increment even if the Lookup is not executed at all.
The sys.dm_db_index_operational_stats DMV records the number of singleton lookups actually performed, but does not distinguish between singleton seeks on the index directly, and those that result from a Key or RID Lookup, so it is not useful for your purpose.
Singleton seeks are another name for Probe Scans as reported by the Access Methods Object. There is no way to distinguish between a 'normal' singleton seek on a unique index, and a singleton seek resulting from a Lookup. This means the Access Methods Probe Scans counter is not useful to you. The AM counters are very noisy anyway, and there's no way to correlate the counters with a particular index.