August 6, 2013 at 12:44 pm
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?
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy