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/
I'm guessing the 'memory left' output you posted is from Christian Bolton's VAS usage analysis script. Is that correct? If so, you have only 17-18 MB available in the memtoleave
area, which is likely to cause a problem at some point and could cause all or most of the error messages that you pasted.
This query from Jonathan Kehayias here will give you an idea of what is using memory in the memtoleave
area:
SELECT type, virtual_memory_committed_kb, multi_pages_kb
FROM sys.dm_os_memory_clerks
WHERE virtual_memory_committed_kb > 0 OR multi_pages_kb > 0
The post also explains memtoleave
well.
Whether the size of the caches you asked about is a problem depends on a number of things. Since this is a 32-bit instance, one of the more relevant things to consider is whether the allocations for those caches are allocated using the single-page allocator, which uses the buffer pool, or the multi-page allocator, which prior to SQL Server 2012 doesn't use the buffer pool and instead consumes memory in the memtoleave
area. You have about 13 GB allocated to the buffer pool, so a few hundred MB allocated to this or that isn't necessarily going to cause a problem. However, a few hundred MB in the memtoleave
area is enough to cause some of the errors you listed.
Kehayias' post explains these concepts as well as your options for solving the problem if it is indeed memtoleave
exhaustion.
I would focus mainly on what is starving the memtoleave
area, as well as what overall memory availability in Windows looks like, along with general measures of memory availability in SQL Server such as page life expectancy.
I would also consider whether the problem helps make a business case for migrating to a 64-bit version of SQL Server, as this avoids memtoleave
issues unless the whole server is simply running out of memory. Understanding and managing SQL Server's memory usage is significantly simpler on 64-bit builds, especially on SQL Server 2012 where all allocations go through the buffer pool. You may also want to consider applying SP4 as there are a number of known bugs in SP3.
Best Answer
Essentially, the error is saying that there is not enough memory available to Sql Server for it to grow its internal buffers - this generally happens if Windows is having to page a lot or other processes are consuming the memory.
You should also remember that just because you tell Sql Server to use a minimum amount of RAM, it can use less if the host OS needs it more - the host always takes priority and the buffer pool will get trimmed if the host starts to detect memory pressure. For instance, in the documentation for Server Memory Configuration Options it says:
I would check to see what other processes are running (for instance Service Broker), and how much strain they are putting on the server. If possible, increase the size of the VM or move some of the other processes onto other boxes.
Lock Pages In Memory Option
It is a common misconception that when you use Lock Pages In Memory that it can never be released. Using the LPIM setting prevents Windows moving pages in memory to virtual memory on disk (the page file). It does not however, prevent Sql Server from reducing its memory footprint if it detects that the host OS is low on memory. The snippet below is taken from the same page the previous snippet is taken from:
Sql Server uses the Memory Notification API's within Microsoft Windows to detect when the host is running low on system resources. If the host is running low, Sql Server will begin to trim the buffer pool in response. This is an important fact - it is Sql Server that is releasing the memory in response to an external event - Windows is not taking it from Sql Server. The below is taken from the Memory Management Architecture Guide: