Sql-server – Recurring Memory Issues

memorysql serversql-server-2008-r2windows-server

We have a Windows Server 2008R2 server with 32GB or RAM running SQL Server 2008R2, along with an IIS server. The machine is also used as a backup server.

The problem is, periodically (every week or two) the SQL takes up all the memory, literally all of it: resmon shows 0MB available. Now, I did look the problem up, and I know SQL is supposed to take up the entire memory, but it really hurts the web performance, and in the end the SQL itself starts stuttering; scheduled jobs don't execute (the logs say "insufficient resources") etc.

I tried limiting the max memory size to 4096MB. The memory footprint dropped – and rose again to full in a week.
I tried scheduling a job that would empty the caches every night. Is that likely to help?
If not, what will?

edit Last time it crashed, already after I limited it to 4GB, I wasn't on-site, unfortunately. But the SQL Server logs show the available page file space dropping below 4MB, and the Windows logs report low virtual memory conditions for hours, with sqlservr.exe consuming the most memory (though only about 1,640,919,040 bytes, for some reason).

Best Answer

This sounds a lot like a problem I have experienced before at a client who was also running IIS and SQL Server 2008 R2 on the same server. The problem would also start with reports of very bad web performance and then slowly grind to a complete halt.

After many hours of trouble shooting we managed to attribute it to a single SQL Server setting found under the "Processors" heading in the Server Properties dialog:

Boost SQL Priority

When this setting was enabled we constantly experienced the degradation in performance. Although this setting is related to CPU resources we also observed increased memory usage on the server as you explained. It looked as if when this setting was enabled SQL became so hungry for resources that IIS was unable to allocate the resources needed to serve the incoming requests and this in turn caused a backlog of SQL operations which in turn made SQL want to consume more resources and so on and so forth. I have no empirical evidence to back up these claims but this was the observed behavior at the time.

I realize correlation does not equal causation, but we were able to confidently replicate and subsequently mitigate the issue on multiple servers by switching the setting on and off.

After seeing this repeated at a few clients we adopted the policy to have this setting switched of at all clients and we went so far as to report on any changes made to this setting. Suffice to say we have since then not yet experienced issues of this nature again.

I am not saying this is definitely the solution to your problem but given the similarities between the issues you experienced and the ours I would recommend you try it as a first resort since it is very easy to attempt.

Related Solutions

Sql-server – SQL Server 2008 R2 There is insufficient system memory in resource pool ‘default’ to run this query

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/

SQL Server – Maximum and Minimum Memory Configuration

Looking at your RAM availablity for this particular server and you are running several databases ranging from 30GB to 5 GB, you definitely need more RAM on this server.

You have not mentioned that this is a stand alone instance or this server is having more than one instance of sql server running.

Your MAX Memory settings seems OK for a server having 8GB RAM. See these suggested best practice settings from Glenn Berry.

I would highly recommend you to do a baseline of your environment using below PERFMON counters to get a good value of your memory configuration :

SQL Server:Buffer Manager\Page Life Expectancy
SQL Server:Buffer Manager\Page reads/sec
Physical Disk\Disk Reads/sec
Memory\Available Mbytes
SQL Server: Memory Manager - Total Server Memory
SQL Server: Memory Manager - Target Server Memory

Total Server Memory: Amount of memory currently allocated to Buffer Pool and not the total amount of memory to SQL Server

Target Server memory: Ideal Size of the buffer pool corresponding to max memory for the instance.

Note: If Total Server Memory > Target Server memory, then it suggests memory pressure.

Below script will help you find LOW or HIGH Memory notifications from sys.dm_os_ring_buffers - system health session :

SELECT CONVERT (varchar(30), GETDATE(), 121) as [RunTime],
dateadd (ms, (rbf.[timestamp] - tme.ms_ticks), GETDATE()) as [Notification_Time],
cast(record as xml).value('(//Record/ResourceMonitor/Notification)[1]', 'varchar(30)') AS [Notification_type],
cast(record as xml).value('(//Record/MemoryRecord/MemoryUtilization)[1]', 'bigint') AS [MemoryUtilization %],
cast(record as xml).value('(//Record/MemoryNode/@id)[1]', 'bigint') AS [Node Id],
cast(record as xml).value('(//Record/ResourceMonitor/IndicatorsProcess)[1]', 'int') AS [Process_Indicator],
cast(record as xml).value('(//Record/ResourceMonitor/IndicatorsSystem)[1]', 'int') AS [System_Indicator],
cast(record as xml).value('(//Record/MemoryNode/ReservedMemory)[1]', 'bigint') AS [SQL_ReservedMemory_KB],
cast(record as xml).value('(//Record/MemoryNode/CommittedMemory)[1]', 'bigint') AS [SQL_CommittedMemory_KB],
cast(record as xml).value('(//Record/MemoryNode/AWEMemory)[1]', 'bigint') AS [SQL_AWEMemory],
cast(record as xml).value('(//Record/MemoryNode/SinglePagesMemory)[1]', 'bigint') AS [SinglePagesMemory],
cast(record as xml).value('(//Record/MemoryNode/MultiplePagesMemory)[1]', 'bigint') AS [MultiplePagesMemory],
cast(record as xml).value('(//Record/MemoryRecord/TotalPhysicalMemory)[1]', 'bigint') AS [TotalPhysicalMemory_KB],
cast(record as xml).value('(//Record/MemoryRecord/AvailablePhysicalMemory)[1]', 'bigint') AS [AvailablePhysicalMemory_KB],
cast(record as xml).value('(//Record/MemoryRecord/TotalPageFile)[1]', 'bigint') AS [TotalPageFile_KB],
cast(record as xml).value('(//Record/MemoryRecord/AvailablePageFile)[1]', 'bigint') AS [AvailablePageFile_KB],
cast(record as xml).value('(//Record/MemoryRecord/TotalVirtualAddressSpace)[1]', 'bigint') AS [TotalVirtualAddressSpace_KB],
cast(record as xml).value('(//Record/MemoryRecord/AvailableVirtualAddressSpace)[1]', 'bigint') AS [AvailableVirtualAddressSpace_KB],
cast(record as xml).value('(//Record/@id)[1]', 'bigint') AS [Record Id],
cast(record as xml).value('(//Record/@type)[1]', 'varchar(30)') AS [Type],
cast(record as xml).value('(//Record/@time)[1]', 'bigint') AS [Record Time],
tme.ms_ticks as [Current Time]
FROM sys.dm_os_ring_buffers rbf
cross join sys.dm_os_sys_info tme
where rbf.ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR' 
--and cast(record as xml).value('(//Record/ResourceMonitor/Notification)[1]', 'varchar(30)') = 'RESOURCE_MEMPHYSICAL_LOW'
ORDER BY rbf.timestamp ASC

Some good references :

How much memory does my SQL Server actually need? - by Jonathan Kehayias
Performance Tuning using the Waits and Queue
Great SQL Server Debates: Lock Pages in Memory

Best Answer

Related Solutions

Sql-server – SQL Server 2008 R2 There is insufficient system memory in resource pool ‘default’ to run this query

SQL Server – Maximum and Minimum Memory Configuration

Related Question