SQL Server Internal Memory Notifications

Below Query helps you to read the notifications that SQL Server has generated internally.



SELECT CONVERT (varchar(30), GETDATE(), 121) as runtime,

DATEADD (ms, -1 * (sys.ms_ticks - a.[Record Time]), GETDATE()) AS Notification_time,

a.* , sys.ms_ticks AS [Current Time]

FROM

(SELECT x.value('(//Record/ResourceMonitor/Notification)[1]', 'varchar(30)') AS [Notification_type],

x.value('(//Record/MemoryRecord/MemoryUtilization)[1]', 'bigint') AS [MemoryUtilization %],

x.value('(//Record/MemoryRecord/TotalPhysicalMemory)[1]', 'bigint') AS [TotalPhysicalMemory_KB],

x.value('(//Record/MemoryRecord/AvailablePhysicalMemory)[1]', 'bigint') AS [AvailablePhysicalMemory_KB],

x.value('(//Record/MemoryRecord/TotalPageFile)[1]', 'bigint') AS [TotalPageFile_KB],

x.value('(//Record/MemoryRecord/AvailablePageFile)[1]', 'bigint') AS [AvailablePageFile_KB],

x.value('(//Record/MemoryRecord/TotalVirtualAddressSpace)[1]', 'bigint') AS [TotalVirtualAddressSpace_KB],

x.value('(//Record/MemoryRecord/AvailableVirtualAddressSpace)[1]', 'bigint') AS [AvailableVirtualAddressSpace_KB],

x.value('(//Record/MemoryNode/@id)[1]', 'bigint') AS [Node Id],

x.value('(//Record/MemoryNode/ReservedMemory)[1]', 'bigint') AS [SQL_ReservedMemory_KB],

x.value('(//Record/MemoryNode/CommittedMemory)[1]', 'bigint') AS [SQL_CommittedMemory_KB],

x.value('(//Record/@id)[1]', 'bigint') AS [Record Id],

x.value('(//Record/@type)[1]', 'varchar(30)') AS [Type],

x.value('(//Record/ResourceMonitor/Indicators)[1]', 'bigint') AS [Indicators],

x.value('(//Record/@time)[1]', 'bigint') AS [Record Time]

FROM (SELECT CAST (record as xml) FROM sys.dm_os_ring_buffers

WHERE ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR') AS R(x)) a

CROSS JOIN sys.dm_os_sys_info sys

ORDER BY a.[Record Time] ASC



...Happy SQLing / Thanks for Visiting and Sharing your Views