Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a 32-Bit Windows 2003 Enterprise Edition SP2 Server running SQL Server 2008 R2 (10.50.1617.0) Enterprise Edition. The server is a dedicated SQL Server box for our SharePoint 2007 environment.

The server has 36 GB of ram and I have enabled AWE in SQL Server.

I am continuously receiving the error: "There is insufficient system memory in resource pool 'default' to run this query" in the Windows event logs.

I am running perfmon and monitoring the SQL Server Memory Manager counters for 'Target Server Memory' and 'Total Server Memory' as well as the Memory counter for 'Available KBytes'. Currently the 'Total Server Memory' counter is sitting at 5 GB.

I have configured the 'Minimum server memory' to 25 GB and the 'Maximum server memory' to 30 GB in SQL Server. The server is showing 31 GB of available memory, but I continue to constantly receive this error.

The index creation memory is set to 0 KB (dynamic memory) and the Minimum memory per query is set to 1024 KB.

I have experimented with the Minimum memory per query value but it did not stop the error from occuring.

If my server has 31 GB of available memory (20 GB of this are available to SQL Server), why am I receiving this error?

I have spent hours searching in forms, but others who are receiving this error seem to be low on virtual memory, which is not the case for me.

Does anyone have any ideas what could be causing this error?

I have tried restarting SQL Server and even restarting the server itself, but this error always returns within a minute or two.

I am planning on updating to Service Pack 1 as soon as I have approval to do so, but I doubt that will solve my problems.

Any advice would be greatly appreciated.

share|improve this question

migrated from stackoverflow.com Feb 7 '12 at 16:15

This question came from our site for professional and enthusiast programmers.

    
It would be easier to read if you format that blob of text :) –  juergen d Feb 7 '12 at 15:29
1  
have you read these before ?stackoverflow.com/questions/1404128/… –  Sergey Benner Feb 7 '12 at 16:24
    
@Sergey Benner This server is used exclusively for SharePoint 2007 databases. I am not running any other scripts or jobs on the server. When trying to run a trace in SQL Server Profiler I get the error "Not enough memory was available for trace". Also, I do not have the resource governor enabled on this server. Would enabling it help me in this situation? –  user1194829 Feb 7 '12 at 17:29
    
not necessary. read these connect.microsoft.com/SQLServer/feedback/details/342691/… and objectmix.com/commerce-server/…. Have you checked all the statistic numbers using the perfmon? memory,sqlserver's buffers,.. PTEs and so on...? You might have allocated too much for it. Are there any other services like IIS running there? –  Sergey Benner Feb 8 '12 at 0:13

1 Answer 1

up vote 2 down vote accepted

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/

share|improve this answer
    
I did confirm that /PAE is activated in the boot.ini file. I also confirmed that the SQL Server Service account has the right to Lock Pages in Memory group policy. sys.dm_os_sys_info shows bpool_visible = 86016, bpool_commit_target = 3129344 and bpool_committed = 3129344. Does this indicate anything to you? –  user1194829 Feb 8 '12 at 16:01
    
According to Process Explorer, the Working Set of sqlservr.exe is 175,128 K –  user1194829 Feb 8 '12 at 20:11
    
I updated my answer with queries and tests I ran on a Windows 2003 32bit Enterprise SP2 with SQL Server 2005 Standard. I did see 3G allocated by AWE to the buffer pool, all the other components can only use the 2GB memory not accessible via AWE. In your case, if you are not seeing AWE memory being allocated to the buffer pool, I would use Process Explorer to figure out if that memory is being allocated elsewhere. –  Craig Efrein Feb 9 '12 at 7:42
    
The problem ended up being with a database for an application that was placed on the server a long time ago and shouldn't even have been there. The application was hitting the server with really expensive queries. Your post helped me track down the problem, thanks Craig! –  user1194829 Feb 10 '12 at 17:53
    
My pleasure, thank you for letting me know. –  Craig Efrein Feb 10 '12 at 19:01

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.