Do I have a lack of memory

• Robert Klimes

  SSCoach

  Points: 17865

  More actions

  August 6, 2013 at 12:44 pm

  #276728

  I was looking into whether an existing server could handle added load and I came across some values in RING_BUFFER that look to me like there is memory pressure but it doesn't appear so in perfmon.

  I am just wondering if I am misinterpreting the results or if i do, in fact, have a memory issue.

  here is query and excerpt of result from the RING_BUFFER_RESOURCE_MONITOR

  ;WITH RingBuffer

  AS (SELECT CAST(dorb.record AS XML) AS xRecord,

  dorb.timestamp

  FROM sys.dm_os_ring_buffers AS dorb

  WHERE dorb.ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR'

  )

  SELECT xr.value('(ResourceMonitor/Notification)[1]', 'varchar(75)') AS RmNotification,

  xr.value('(ResourceMonitor/IndicatorsProcess)[1]','tinyint') AS IndicatorsProcess,

  xr.value('(ResourceMonitor/IndicatorsSystem)[1]','tinyint') AS IndicatorsSystem,

  DATEADD(ms, -1 * (dosi.ms_ticks - rb.timestamp), GETDATE()) AS RmDateTime,

  xr.value('(MemoryNode/CommittedMemory)[1]','bigint') AS CommitedMemory,

  xr.value('(MemoryRecord/TotalPhysicalMemory)[1]','bigint') AS TotalPhysicalMemory,

  xr.value('(MemoryRecord/AvailablePhysicalMemory)[1]','bigint') AS AvailablePhysicalMemory,

  xr.value('(MemoryRecord/TotalPageFile)[1]','bigint') AS TotalPageFile,

  xr.value('(MemoryRecord/AvailablePageFile)[1]','bigint') AS AvailablePageFile--,

  FROM RingBuffer AS rb

  CROSS APPLY rb.xRecord.nodes('Record') record (xr)

  CROSS JOIN sys.dm_os_sys_info AS dosi

  ORDER BY RmDateTime DESC;

  RmNotificationIndicatorsProcessIndicatorsSystemRmDateTimeCommitedMemoryTotalPhysicalMemory AvailablePhysicalMemoryTotalPageFileAvailablePageFile

  RESOURCE_MEM_STEADY002013-08-06 13:28:52.00340355806291000654948125801406995960

  RESOURCE_MEMPHYSICAL_HIGH012013-08-06 13:28:52.00340355806291000654948125801406995960

  RESOURCE_MEMPHYSICAL_LOW202013-08-06 13:28:51.89040461486291000645704125801406985736

  RESOURCE_MEM_STEADY002013-08-06 11:43:20.03740314846291000613664125801406991752

  RESOURCE_MEMPHYSICAL_HIGH012013-08-06 11:43:20.03740314846291000613664125801406991752

  RESOURCE_MEMPHYSICAL_LOW202013-08-06 11:43:19.93040348926291000610772125801406988688

  here is query and excerpt of result from the RING_BUFFER_MEMORY_BROKER

  with RingBuffer as(

  SELECT CAST(dorb.record AS XML) AS xRecord,

  dorb.timestamp

  FROM sys.dm_os_ring_buffers AS dorb

  WHERE dorb.ring_buffer_type = 'RING_BUFFER_MEMORY_BROKER')

  select DATEADD(ms, -1 * (dosi.ms_ticks - rb.timestamp), GETDATE()) AS RmDateTime,

  xr.value('(MemoryBroker/DeltaTime)[1]','bigint') as DeltaTime,

  xr.value('(MemoryBroker/Pool)[1]','int') as [Pool],

  xr.value('(MemoryBroker/Broker)[1]','varchar(50)') as [Broker],

  xr.value('(MemoryBroker/Notification)[1]','varchar(50)') as [Notification],

  xr.value('(MemoryBroker/MemoryRatio)[1]','bigint') as MemoryRatio,

  xr.value('(MemoryBroker/NewTarget)[1]','bigint') as NewTarget,

  xr.value('(MemoryBroker/Overall)[1]','bigint') as Overall,

  xr.value('(MemoryBroker/Rate)[1]','bigint') as Rate,

  xr.value('(MemoryBroker/CurrentlyPredicted)[1]','bigint') as CurrentlyPredicted,

  xr.value('(MemoryBroker/CurrentlyAllocated)[1]','bigint') as CurrentlyAllocated,

  xr.value('(MemoryBroker/PreviouslyAllocated)[1]','bigint') as PreviouslyAllocated

  from RingBuffer AS rb

  CROSS APPLY rb.xRecord.nodes('Record') record (xr)

  CROSS JOIN sys.dm_os_sys_info AS dosi

  where xr.value('(MemoryBroker/Broker)[1]','varchar(50)') ='MEMORYBROKER_FOR_CACHE'

  ORDER BY RmDateTime DESC;

  RmDateTimeDeltaTimePoolBrokerNotificationMemoryRatioNewTargetOverallRateCurrentlyPredictedCurrentlyAllocatedPreviouslyAllocated

  2013-08-06 13:36:27.8031011MEMORYBROKER_FOR_CACHEGROW100285242367718-96021201660201660211363

  2013-08-06 13:36:27.69710001MEMORYBROKER_FOR_CACHESHRINK9620304136771865211428211363211305

  2013-08-06 13:28:51.9801141MEMORYBROKER_FOR_CACHEGROW100283971367718-47803209645209645215098

  2013-08-06 13:28:51.86010001MEMORYBROKER_FOR_CACHESHRINK9520460436771841215139215098215066

  2013-08-06 11:43:20.0031051MEMORYBROKER_FOR_CACHEGROW100285564367718-83068191289191289200014

  2013-08-06 11:43:19.88710001MEMORYBROKER_FOR_CACHESHRINK9919820136771837200051200014199977

  The above result look to me that the SQL server is regularly indicating it is low on memory. If I look in perfmon, PLE is hovering around 1500 sec almost no reads /write to physical disk which looks like there is ample memory. I do have some(~600MB) physical memory currently available.

  Am I correct in thinking that the SQL Server is starved for memory and should increase the max server memory?

  Bob
  -----------------------------------------------------------------------------
  How to post to get the best help[/url]