Posted By

ztuner on 10/03/12


Tagged

memory


Versions (?)

Memory Analysis


 / Published in: SQL
 

System Memory Analysis for SQL Server

  1. SELECT mxml.value('(//Record/@time)[1]','bigint') AS NotificationTime
  2. ,mxml.value('(//Record/ResourceMonitor/Notification)[1]','nvarchar(36)') AS RM_Notification
  3. ,mxml.value('(//Record/ResourceMonitor/Indicators)[1]','int') AS RM_Indicators
  4. ,mxml.value('(//Record/ResourceMonitor/NodeId)[1]','bigint') AS RM_NodeID
  5. ,mxml.value('(//Record/MemoryNode/@id)[1]','bigint') AS MemNode_ID
  6. ,mxml.value('(//Record/MemoryNode/ReservedMemory)[1]','bigint')/1024 AS MemNode_Reserved_MB
  7. ,mxml.value('(//Record/MemoryNode/CommittedMemory)[1]','bigint')/1024 AS MemNode_Committed_MB
  8. ,mxml.value('(//Record/MemoryNode/SharedMemory)[1]','bigint')/1024 AS MemNode_Shared_MB
  9. ,mxml.value('(//Record/MemoryNode/AWEMemory)[1]','bigint')/1024 AS MemNode_AWE_MB
  10. ,mxml.value('(//Record/MemoryNode/SinglePagesMemory)[1]','bigint')/1024 AS MemNode_SinglePages_MB
  11. ,mxml.value('(//Record/MemoryNode/MultiplePagesMemory)[1]','bigint')/1024 AS MemNode_MultiPages_MB
  12. ,mxml.value('(//Record/MemoryNode/CachedMemory)[1]','bigint')/1024 AS MemNode_Cached_MB
  13. ,mxml.value('(//Record/MemoryRecord/MemoryUtilization)[1]','int')/1024 AS Memory_Utilization_MB
  14. ,mxml.value('(//Record/MemoryRecord/TotalPhysicalMemory)[1]','bigint')/1024 AS TotalPhysMemory_MB
  15. ,mxml.value('(//Record/MemoryRecord/AvailablePhysicalMemory)[1]','bigint')/1024 AS AvailPhysMemory_MB
  16. ,mxml.value('(//Record/MemoryRecord/TotalPageFile)[1]','bigint')/1024 AS TotalPF_MB
  17. ,mxml.value('(//Record/MemoryRecord/AvailablePageFile)[1]','bigint')/1024 AS AvailPF_MB
  18. ,mxml.value('(//Record/MemoryRecord/TotalVirtualAddressSpace)[1]','bigint')/1024 AS TotalVAS_MB
  19. ,mxml.value('(//Record/MemoryRecord/AvailableVirtualAddressSpace)[1]','bigint')/1024 AS AvailVAS_MB
  20. ,mxml.value('(//Record/MemoryRecord/AvailableExtendedVirtualAddressSpace)[1]','bigint')/1024 AS AvailExtendedVAS_MB
  21. FROM (SELECT CAST([record] AS XML)
  22. FROM [sys].[dm_os_ring_buffers]
  23. WHERE [ring_buffer_type] = 'RING_BUFFER_RESOURCE_MONITOR') AS R(mxml)
  24. ORDER BY [NotificationTime] DESC

Report this snippet  

You need to login to post a comment.