Low instance memory.....or is it.

Question

Post reply

Low instance memory.....or is it.

InvisibleCat

Default port

Points: 1496
More actions
November 29, 2012 at 1:44 am

#271323

Hi,

I was wondering if someone could help me out with something, I'm investigating an issue on one of our instances, the instance is set to use 1GB of RAM.

I've been seeing some 'RESOURCE_MEMPHYSICAL_LOW' in the ring buffer, periodically through the day, so I focus in on one of these (18:08), I also have a perfmon running monitoring some things on the machine, the odd thing is, despite the ring buffer stating memory is low, the perfmon records the page life to in the thousands (normal), free pages equally high, and no sign of any waits on memory grants.

So if I'm reading this right I am getting contradictory results, ring buffer says there is memory pressure, the counters that there isn't.

Any idea's as to why this might be?

Thanks,

Nic

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 1

RESOURCE_MEMPHYSICAL_LOW indicates external memory pressure, the OS is short of memory.

The counters you mention indicate that there is no internal memory pressure, that SQL has enough memory.

How much memory on the server? Max server memory set to 1 GB? What else is running on the server? What's the Available MB counter look like?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

InvisibleCat Default port Points: 1496 More actions · Answer 2

Hi Gail,

Thanks for the response.

Regarding the machine, it has 3GB in it at the moment, this instance is set to 1GB max and there is another instance which is also set to 1GB, leaving 1GB for the OS.

I'm just in the process of sizing up a virtual machine for them and I wanted to examine their memory usage. In theory, neither of the instances should need a lot of memory for what they do but I want to double check it.

You mention that the MEMPHYSICAL_LOW is to do with external pressure, so the OS is short of memory (wouldn't surprise me) but the instance itself isn't.

I can't remember where I read it, but I thought the 'Process_Indicator' flag - which is 2 (sorry should have mentioned that) indicated its something within SQL Server itself. The 'System_Indicator' is zero so I took that to mean that the system (OS) is fine and a process in SQL didn't have memory. I'm incorrect in saying that then?

Thanks for your help with this, it's appreciated.

Nic

InvisibleCat Default port Points: 1496 More actions · Answer 3

Hi,

This is where I have read about it;

http://blogs.msdn.com/b/psssql/archive/2009/09/17/how-it-works-what-are-the-ring-buffer-resource-monitor-telling-me.aspx

Although re-reading it, my previous post was incorrect, I now take it to me;

IndicatorsProcess - 2 - This is the RESOURCE_MEMPHYSICAL_LOW type

IndicatorsSystem - 0 - This means that it is not a system wide (OS) memory issue.

If I am interpreting this correctly, that would imply that the OS was fine for memory and that SQL was reporting the issue (somewhere), although I would expect the perfmon to reflect this as well, which it doesn't.

I also came across this;

http://blogs.msdn.com/b/mvpawardprogram/archive/2012/06/04/using-sys-dm-os-ring-buffers-to-diagnose-memory-issues-in-sql-server.aspx

which states

"If the IndicatorsProcess returns 0 and the IndicatorsSystem returns a value then the problem was system wide. But, in our case the IndicatorsProcess has a value and IndicatorsSystem is returning 0. This means that this alert was for a single process that ran suffered from low memory, not the entire system

"

So am I mis-understanding this?

Regards,

Nic

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 4

Nic-306421 (11/29/2012)

I can't remember where I read it, but I thought the 'Process_Indicator' flag - which is 2 (sorry should have mentioned that) indicated its something within SQL Server itself. The 'System_Indicator' is zero so I took that to mean that the system (OS) is fine and a process in SQL didn't have memory.

Yes, it would have helped if you'd mentioned that...

Were you monitoring counters and ring buffer at the same time?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

InvisibleCat Default port Points: 1496 More actions · Answer 5

GilaMonster (11/29/2012)

Nic-306421 (11/29/2012)

I can't remember where I read it, but I thought the 'Process_Indicator' flag - which is 2 (sorry should have mentioned that) indicated its something within SQL Server itself. The 'System_Indicator' is zero so I took that to mean that the system (OS) is fine and a process in SQL didn't have memory.

Yes, it would have helped if you'd mentioned that...

Were you monitoring counters and ring buffer at the same time?

Sorry, my mistake.

Yes the monitoring counters were running at the same time as the buffer ring recorded the issue.

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 6

You should be able to query the Memory Broker notifications and see which Broker triggered the memory notification:

SELECT



    EventTime,

    n.value('(Pool)[1]', 'int') AS [Pool],

    n.value('(Broker)[1]', 'varchar(40)') AS [Broker],

    n.value('(Notification)[1]', 'varchar(40)') AS [Notification],

    n.value('(MemoryRatio)[1]', 'int') AS [MemoryRatio],

    n.value('(NewTarget)[1]', 'int') AS [NewTarget],

    n.value('(Overall)[1]', 'int') AS [Overall],

    n.value('(Rate)[1]', 'int') AS [Rate],

    n.value('(CurrentlyPredicted)[1]', 'int') AS [CurrentlyPredicted],

    n.value('(CurrentlyAllocated)[1]', 'int') AS [CurrentlyAllocated]

FROM (

	SELECT

		DATEADD (ss, (-1 * ((cpu_ticks / CONVERT (float, ( cpu_ticks / ms_ticks ))) - [timestamp])/1000), GETDATE()) AS EventTime,

		CONVERT (xml, record) AS record

	FROM sys.dm_os_ring_buffers

	CROSS JOIN sys.dm_os_sys_info

	WHERE ring_buffer_type = 'RING_BUFFER_MEMORY_BROKER') AS t

CROSS APPLY record.nodes('/Record/MemoryBroker') AS x(n)

ORDER BY EventTime DESC;

If it is MEMORYBROKER_FOR_CACHE then look at your plan cache usage, if you have an adhoc workload it would be a internal trim of the cache and you'd see that in a drop in the Buffer Manager\Stolen Pages counter. I demo this in our IE2 Performance Tuning memory tuning module. Look for the SHRINK notification and then which broker and if you use Resource Governor, pool_id that caused it to occur. My bet would be that the server has an adhoc workload and the plan cache is shrinking due to excessive single use plans from my experience.

InvisibleCat Default port Points: 1496 More actions · Answer 7

Hi,

Thanks for the SELECT.

I've picked out a time where the 'RESOURCE_MEMPHYSICAL_LOW' is raised in the ring buffer (process - 2, system 0) under the buffer type 'RING_BUFFER_RESOURCE_MONITOR'

I've then gone through the data output by your query, I get a number of records but fo the ones of interest (the shrinks) the brokers are;

MEMORYBROKER_FOR_STEAL

MEMORYBROKER_FOR_CACHE

So if I am understanding this correctly, it's as you say, an adhoc workload is running and it needs more memory so it trims the plan cache.

I can see events after this for MEMORYBROKER_FOR_CACHE where the notification type is GROW so I'm guessing it is reclaiming the lost space?

Thanks for your help with this.

Nic