/*****************************************************************************
*   Presentation: Module 11 - DMV's
*   FileName:  dm_os_ring_buffers.sql
*
*   Summary: Demonstrates how to parse the contents of sys.dm_os_ring_buffers.
*
*   Date: March 14, 2011 
*
*   SQL Server Versions:
*         2008, 2008 R2
*         
******************************************************************************
*   Copyright (C) 2011 Jonathan M. Kehayias, SQLskills.com
*   All rights reserved. 
*
*   For more scripts and sample code, check out 
*      http://sqlskills.com/blogs/jonathan
*
*   You may alter this code for your own *non-commercial* purposes. You may
*   republish altered code as long as you include this copyright and give 
*	due credit. 
*
*
*   THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF 
*   ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED 
*   TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
*   PARTICULAR PURPOSE. 
*
******************************************************************************/


--System Memory Usage
SELECT  
	EventTime,
	--record,
    record.value('(/Record/ResourceMonitor/Notification)[1]', 'varchar(max)') as [Type], 
    record.value('(/Record/MemoryRecord/AvailablePhysicalMemory)[1]', 'bigint') AS [Avail Phys Mem, Kb], 
    record.value('(/Record/MemoryRecord/AvailableVirtualAddressSpace)[1]', 'bigint') AS [Avail VAS, Kb] 
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_RESOURCE_MONITOR') AS tab 
ORDER BY EventTime DESC

-- Get Startup Memory Utilization
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

-- Out of Memory Notifications
SELECT 
	EventTime,
	n.value('(OOM/Action)[1]', 'varchar(50)') as Action,
	n.value('(OOM/Resources)[1]', 'int') as Resources,
	n.value('(OOM/Task)[1]', 'varchar(20)') as Task,
	n.value('(OOM/Pool)[1]', 'int') as PoolID,
	n.value('(MemoryRecord/MemoryUtilization)[1]', 'int') as MemoryUtilization,
	n.value('(MemoryRecord/AvailablePhysicalMemory)[1]', 'int') as AvailablePhysicalMemory,
	n.value('(MemoryRecord/AvailableVirtualAddressSpace)[1]', 'int') as AvailableVirtualAddressSpace
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_OOM') as tab
CROSS APPLY record.nodes('/Record') AS x(n)