Sql-server – Load into memory-optimized table gives insufficient memory error

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 was noted in a comment on the question by Kin:

Do you have an application that's running full text queries? There is a hotfix for SQL Server 2008 and 2008 R2. Have you applied this?

• FIX: "There is insufficient system memory in resource pool 'internal' to run this query" error message when you run a full-text query that uses compound words in Microsoft SQL Server 2008 or in Microsoft SQL Server 2008 R2

The fix for this issue was first released in:

• Cumulative Update 9 for SQL Server 2008 Service Pack 1
• Cumulative Update 1 for SQL Server 2008 Service Pack 2.
• Cumulative Update 4 for SQL Server 2008 R2.

KB article for the the version you need:

• Cumulative Update package 4 for SQL Server 2008 R2