system_health.2012.sql

DECLARE @FileName NVARCHAR(4000)	
SELECT @FileName = 

	LEFT(target_data.value('(EventFileTarget/File/@name)[1]','nvarchar(4000)') ,
		CHARINDEX('system_health',target_data.value('(EventFileTarget/File/@name)[1]','nvarchar(4000)') )-1) + 'system_health*.xel'
	
	FROM (
			SELECT 
				CAST(target_data AS XML) target_data
				FROM sys.dm_xe_sessions s
				JOIN sys.dm_xe_session_targets t
					ON s.address = t.event_session_address
				WHERE s.name = N'system_health'
					and target_name = 'event_file'
		) ft

--SELECT @FileName;

		SELECT CAST(event_data AS XML) as event_data
			INTO #system_health
			FROM sys.fn_xe_file_target_read_file(@FileName , NULL, NULL, NULL)					
	
	SELECT 
		event_data.value('(event/@name)[1]','sysname') event_name,
		event_data.value('(event/@timestamp)[1]','datetime2') event_timestamp, 
		event_data.value('(event/data[@name="component"]/text)[1]','sysname') spsd_component, 
		event_data.value('(event/data[@name="state"]/text)[1]','sysname') spsd_component_state, 	
		--- START SPSD_SYSTEM				
		event_data.value('(event/data[@name="data"]/value/system/@spinlockBackoffs)[1]','nvarchar(4000)') as spinlockBackoffs,
		event_data.value('(event/data[@name="data"]/value/system/@sickSpinlockType)[1]','nvarchar(4000)') as sickSpinlockType,
		event_data.value('(event/data[@name="data"]/value/system/@sickSpinlockTypeAfterAv)[1]','nvarchar(4000)') as sickSpinlockTypeAfterAv,
		event_data.value('(event/data[@name="data"]/value/system/@latchWarnings)[1]','numeric(20,0)') as latchWarnings,
		event_data.value('(event/data[@name="data"]/value/system/@isAccessViolationOccurred)[1]','numeric(20,0)') as isAccessViolationOccurred,
		event_data.value('(event/data[@name="data"]/value/system/@writeAccessViolationCount)[1]','numeric(20,0)') as writeAccessViolationCount,
		event_data.value('(event/data[@name="data"]/value/system/@totalDumpRequests)[1]','numeric(20,0)') as totalDumpRequests,
		event_data.value('(event/data[@name="data"]/value/system/@intervalDumpRequests)[1]','numeric(20,0)') as intervalDumpRequests,
		event_data.value('(event/data[@name="data"]/value/system/@nonYieldingTasksReported)[1]','numeric(20,0)') as nonYieldingTasksReported,
		event_data.value('(event/data[@name="data"]/value/system/@pageFaults)[1]','numeric(20,0)') as pageFaults,
		event_data.value('(event/data[@name="data"]/value/system/@systemCpuUtilization)[1]','numeric(20,0)') as systemCpuUtilization,
		event_data.value('(event/data[@name="data"]/value/system/@sqlCpuUtilization)[1]','numeric(20,0)') as sqlCpuUtilization,
		event_data.value('(event/data[@name="data"]/value/system/@BadPagesDetected)[1]','numeric(20,0)') as BadPagesDetected,
		event_data.value('(event/data[@name="data"]/value/system/@BadPagesFixed)[1]','numeric(20,0)') as BadPagesFixed,
		event_data.value('(event/data[@name="data"]/value/system/@LastBadPageAddress)[1]','varbinary(64)') as LastBadPageAddress,		
		--START SPSD_QUERYPROCESSING							
		event_data.query('event/data[@name="data"]/value/queryProcessing/topWaits/nonPreemptive/byCount') as npwaits_bycount,
		event_data.query('event/data[@name="data"]/value/queryProcessing/topWaits/nonPreemptive/byDuration') as npwaits_byduration,
		event_data.query('event/data[@name="data"]/value/queryProcessing/topWaits/preemptive/byCount') as pwaits_bycount,
		event_data.query('event/data[@name="data"]/value/queryProcessing/topWaits/preemptive/byDuration') as nwaits_byduration,
		event_data.query('event/data[@name="data"]/value/queryProcessing/cpuIntensiveRequests') as cpuIntensiveRequests,
		event_data.query('event/data[@name="data"]/value/queryProcessing/pendingTasks') as pendingTasks,
		event_data.query('event/data[@name="data"]/value/queryProcessing/blockingTasks') as blockingTasks,
		--START SPSD_RESOURCE
		event_data.value('(event/data[@name="data"]/value/resource/@lastNotification)[1]','nvarchar(4000)') as lastNotification,
		event_data.value('(event/data[@name="data"]/value/resource/@outOfMemoryExceptions)[1]','numeric(20,0)') as outOfMemoryExceptions,
		event_data.value('(event/data[@name="data"]/value/resource/@isAnyPoolOutOfMemory)[1]','bit') as isAnyPoolOutOfMemory,
		event_data.value('(event/data[@name="data"]/value/resource/@processOutOfMemoryPeriod)[1]','numeric(20,0)') as processOutOfMemoryPeriod,
		event_data.value('(event/data[@name="data"]/value/resource/memoryReport[@name="Process/System Counts"]/entry[@description="Available Physical Memory"]/@value)[1]','numeric(20,0)') as [available_physical_memory], 
		event_data.value('(event/data[@name="data"]/value/resource/memoryReport[@name="Process/System Counts"]/entry[@description="Available Virtual Memory"]/@value)[1]','nvarchar(4000)') as [available_virtual_memory], 
		event_data.value('(event/data[@name="data"]/value/resource/memoryReport[@name="Process/System Counts"]/entry[@description="Available Paging File"]/@value)[1]','nvarchar(4000)') as [available_paging_file], 
		event_data.value('(event/data[@name="data"]/value/resource/memoryReport[@name="Process/System Counts"]/entry[@description="Working Set"]/@value)[1]','nvarchar(4000)') as [working_set], 
		event_data.value('(event/data[@name="data"]/value/resource/memoryReport[@name="Process/System Counts"]/entry[@description="Percent of Committed Memory in WS"]/@value)[1]','nvarchar(4000)') as [pct_committed_mem_in_working_set], 
		event_data.value('(event/data[@name="data"]/value/resource/memoryReport[@name="Process/System Counts"]/entry[@description="Page Faults"]/@value)[1]','nvarchar(4000)') as [page_faults], 
		event_data.value('(event/data[@name="data"]/value/resource/memoryReport[@name="Process/System Counts"]/entry[@description="System physical memory high"]/@value)[1]','nvarchar(4000)') as [system_physical_memory_high], 
		event_data.value('(event/data[@name="data"]/value/resource/memoryReport[@name="Process/System Counts"]/entry[@description="System physical memory low"]/@value)[1]','nvarchar(4000)') as [system_physical_memory_low], 
		event_data.value('(event/data[@name="data"]/value/resource/memoryReport[@name="Process/System Counts"]/entry[@description="Process physical memory low"]/@value)[1]','nvarchar(4000)') as [process_physical_memory_low], 
		event_data.value('(event/data[@name="data"]/value/resource/memoryReport[@name="Process/System Counts"]/entry[@description="Process virtual memory low"]/@value)[1]','nvarchar(4000)') as [process_virtual_memory_low], 
		event_data.value('(event/data[@name="data"]/value/resource/memoryReport[@name="Memory Manager"]/entry[@description="VM Reserved"]/@value)[1]','nvarchar(4000)') as [vm_reserved], 
		event_data.value('(event/data[@name="data"]/value/resource/memoryReport[@name="Memory Manager"]/entry[@description="VM Committed"]/@value)[1]','nvarchar(4000)') as [vm_committed], 
		event_data.value('(event/data[@name="data"]/value/resource/memoryReport[@name="Memory Manager"]/entry[@description="Locked Pages Allocated"]/@value)[1]','nvarchar(4000)') as [locked_pages_alloc], 
		event_data.value('(event/data[@name="data"]/value/resource/memoryReport[@name="Memory Manager"]/entry[@description="Large Pages Allocated"]/@value)[1]','nvarchar(4000)') as [large_pages_alloc], 
		event_data.value('(event/data[@name="data"]/value/resource/memoryReport[@name="Memory Manager"]/entry[@description="Emergency Memory"]/@value)[1]','nvarchar(4000)') as [emergency_memory], 
		event_data.value('(event/data[@name="data"]/value/resource/memoryReport[@name="Memory Manager"]/entry[@description="Emergency Memory In Use"]/@value)[1]','nvarchar(4000)') as [emergency_memory_in_use], 
		event_data.value('(event/data[@name="data"]/value/resource/memoryReport[@name="Memory Manager"]/entry[@description="Target Committed"]/@value)[1]','nvarchar(4000)') as [target_committed], 
		event_data.value('(event/data[@name="data"]/value/resource/memoryReport[@name="Memory Manager"]/entry[@description="Current Committed"]/@value)[1]','nvarchar(4000)') as [current_committed], 
		event_data.value('(event/data[@name="data"]/value/resource/memoryReport[@name="Memory Manager"]/entry[@description="Pages Allocated"]/@value)[1]','nvarchar(4000)') as [pages_allocated], 
		event_data.value('(event/data[@name="data"]/value/resource/memoryReport[@name="Memory Manager"]/entry[@description="Pages Reserved"]/@value)[1]','nvarchar(4000)') as [pages_reserved], 
		event_data.value('(event/data[@name="data"]/value/resource/memoryReport[@name="Memory Manager"]/entry[@description="Pages Free"]/@value)[1]','nvarchar(4000)') as [pages_free], 
		event_data.value('(event/data[@name="data"]/value/resource/memoryReport[@name="Memory Manager"]/entry[@description="Pages In Use"]/@value)[1]','nvarchar(4000)') as [pages_in_use], 
		event_data.value('(event/data[@name="data"]/value/resource/memoryReport[@name="Memory Manager"]/entry[@description="Page Alloc Potential"]/@value)[1]','nvarchar(4000)') as [page_alloc_potential], 
		event_data.value('(event/data[@name="data"]/value/resource/memoryReport[@name="Memory Manager"]/entry[@description="NUMA Growth Phase"]/@value)[1]','nvarchar(4000)') as [numa_growth_phase], 
		event_data.value('(event/data[@name="data"]/value/resource/memoryReport[@name="Memory Manager"]/entry[@description="Last OOM Factor"]/@value)[1]','nvarchar(4000)') as [last_oom_factor], 
		event_data.value('(event/data[@name="data"]/value/resource/memoryReport[@name="Memory Manager"]/entry[@description="Last OS Error"]/@value)[1]','nvarchar(4000)') as [last_os_error],
		--START SPSD_IO
		event_data.value('(event/data[@name="data"]/value/ioSubsystem/@ioLatchTimeouts)[1]','bigint') as ioLatchTimeouts,
		event_data.value('(event/data[@name="data"]/value/ioSubsystem/@intervalLongIos)[1]','bigint') as intervalLongIos,
		event_data.value('(event/data[@name="data"]/value/ioSubsystem/@totalLongIos)[1]','bigint') as totalLongIos,
		event_data.query('event/data[@name="data"]/value/ioSubsystem/longestPendingRequests') as longestPendingRequests		
		,event_data		
		--,*
		
	FROM #system_health
	--FROM (
	--		SELECT CAST(event_data AS XML) as event_data
	--		FROM sys.fn_xe_file_target_read_file(@FileName , NULL, NULL, NULL)				
	--	 ) parsed_event
	--OUTER APPLY event_data.nodes('event/data[@name="xml_report"]') as deadlock(graph)		
	where event_data.value('(event/@name)[1]','sysname') = 'sp_server_diagnostics_component_result'
	--and event_data.value('(event/data[@name="component"]/text)[1]','sysname') = 'QUERY_PROCESSING'
	order by event_timestamp desc;

	--drop table #system_health


---- WAIT_INFO PARSE

	SELECT 
		event_data.value('(event/@name)[1]','sysname') event_name,
		event_data.value('(event/@timestamp)[1]','datetime2') event_timestamp, 
		event_data.value('(event/data[@name="wait_type"]/text)[1]','sysname') wait_type, 
		event_data.value('(event/data[@name="opcode"]/text)[1]','sysname') opcode, 
		event_data.value('(event/data[@name="duration"]/value)[1]','bigint') wait_duration, 
		event_data.value('(event/action[@name="session_id"]/value)[1]','int') session_id, 
		event_data.value('(event/action[@name="sql_text"]/value)[1]','nvarchar(max)') sql_text,
		event_data.value('(event/action[@name="callstack"]/value)[1]','nvarchar(max)') callstack,
		event_data	
	FROM #system_health
	where event_data.value('(event/@name)[1]','sysname') like 'wait_info%'
	order by event_timestamp desc;

---- ERROR PARSE

	SELECT 
		event_data.value('(event/@name)[1]','sysname') event_name,
		event_data.value('(event/@timestamp)[1]','datetime2') event_timestamp, 
		event_data.value('(event/data[@name="error_number"]/value)[1]','int') wait_type, 
		event_data.value('(event/data[@name="severity"]/value)[1]','int') severity, 
		event_data.value('(event/data[@name="state"]/value)[1]','int') state, 
		event_data.value('(event/data[@name="user_defined"]/value)[1]','varchar(25)') user_defined, 
		event_data.value('(event/data[@name="category"]/value)[1]','int') category_id, 
		event_data.value('(event/data[@name="category"]/text)[1]','varchar(25)') category_desc, 		
		event_data.value('(event/data[@name="destination"]/value)[1]','int') destination_id, 
		event_data.value('(event/data[@name="destination"]/text)[1]','varchar(25)') destination_desc, 
		event_data.value('(event/data[@name="is_intercepted"]/text)[1]','varchar(25)') is_intercepted, 
		event_data.value('(event/data[@name="message"]/text)[1]','varchar(max)') message, 
		event_data.value('(event/action[@name="callstack"]/value)[1]','nvarchar(max)') callstack,
		event_data.value('(event/action[@name="session_id"]/value)[1]','int') session_id,
		event_data.value('(event/action[@name="database_id"]/value)[1]','int') database_id,		
		event_data
	
	FROM #system_health
	where event_data.value('(event/@name)[1]','sysname') = 'error_reported'
		order by event_timestamp desc;
----- SYSTEM HEALTH RING BUFFER
SELECT 
		event_data.value('(event/@name)[1]','sysname') event_name,
		event_data.value('(event/@timestamp)[1]','datetime2') event_timestamp, 
		event_data.value('(event/data[@name="id"]/value)[1]','decimal(30,0)') id, 
		event_data.value('(event/data[@name="process_utilization"]/value)[1]','decimal(30,0)') process_utilization, 
		event_data.value('(event/data[@name="system_idle"]/value)[1]','decimal(30,0)') system_idle, 
		event_data.value('(event/data[@name="user_mode_time"]/value)[1]','decimal(30,0)') user_mode_time, 
		event_data.value('(event/data[@name="kernel_mode_time"]/value)[1]','decimal(30,0)') kernel_mode_time, 
		event_data.value('(event/data[@name="page_faults"]/value)[1]','decimal(30,0)') page_faults, 
		event_data.value('(event/data[@name="working_set_delta"]/value)[1]','decimal(30,0)') working_set_delta, 
		event_data.value('(event/data[@name="memory_utilization"]/value)[1]','decimal(30,0)') memory_utilization, 
		event_data.value('(event/data[@name="call_stack"]/value)[1]','nvarchar(max)') call_stack, 
		event_data
	FROM #system_health
	where event_data.value('(event/@name)[1]','sysname') = 'scheduler_monitor_system_health_ring_buffer_recorded'
		order by event_timestamp desc;
----- MEMORY BROKER RING BUFFER
SELECT 
		event_data.value('(event/@name)[1]','sysname') event_name,
		event_data.value('(event/@timestamp)[1]','datetime2') event_timestamp, 
		event_data.value('(event/data[@name="id"]/value)[1]','integer') id, 
		event_data.value('(event/data[@name="broker"]/value)[1]','sysname') broker, 
		event_data.value('(event/data[@name="notification"]/value)[1]','sysname') notification, 
		event_data.value('(event/data[@name="delta_time"]/value)[1]','decimal(30,0)') delta_time, 
		event_data.value('(event/data[@name="memory_ratio"]/value)[1]','decimal(30,0)') memory_ratio, 
		event_data.value('(event/data[@name="new_target"]/value)[1]','decimal(30,0)') new_target, 
		event_data.value('(event/data[@name="overall"]/value)[1]','decimal(30,0)') overall, 
		event_data.value('(event/data[@name="rate"]/value)[1]','decimal(30,0)') rate, 
		event_data.value('(event/data[@name="currently_predicated"]/value)[1]','decimal(30,0)') currently_predicated, 
		event_data.value('(event/data[@name="currently_allocated"]/value)[1]','decimal(30,0)') currently_allocated, 
		event_data.value('(event/data[@name="previously_allocated"]/value)[1]','decimal(30,0)') previously_allocated, 
		event_data.value('(event/data[@name="call_stack"]/value)[1]','nvarchar(max)') call_stack, 
		event_data
	FROM #system_health
	where event_data.value('(event/@name)[1]','sysname') = 'memory_broker_ring_buffer_recorded'
	order by event_timestamp desc;

----- OOM RING BUFFER RECORDED
SELECT 
		event_data.value('(event/@name)[1]','sysname') event_name,
		event_data.value('(event/@timestamp)[1]','datetime2') event_timestamp, 
		event_data.value('(event/data[@name="id"]/value)[1]','integer') id, 
		event_data.value('(event/data[@name="available_extended_virtual_address_space_kb"]/value)[1]','bigint') available_extended_virtual_address_space_kb, 
		event_data.value('(event/data[@name="available_page_file_kb"]/value)[1]','bigint') available_page_file_kb, 
		event_data.value('(event/data[@name="available_physical_memory_kb"]/value)[1]','bigint') available_physical_memory_kb, 
		event_data.value('(event/data[@name="available_virtual_address_space_kb"]/value)[1]','bigint') available_virtual_address_space_kb, 
		event_data.value('(event/data[@name="awe_kb"]/value)[1]','bigint') awe_kb, 
		event_data.value('(event/data[@name="committed_kb"]/value)[1]','bigint') committed_kb, 
		event_data.query('event/name[@name="factor"]') factor,
		event_data.query('event/name[@name="failure"]') failure,
		event_data.value('(event/data[@name="last_error"]/value)[1]','int') last_error, 
		event_data.value('(event/data[@name="memory_node_id"]/value)[1]','int') memory_node_id, 
		event_data.value('(event/data[@name="memory_utilization_pct"]/value)[1]','int') memory_utilization_pct, 
		event_data.value('(event/data[@name="pages_kb"]/value)[1]','bigint') pages_kb, 		
		event_data.value('(event/data[@name="pool_metadata_id"]/value)[1]','int') pool_metadata_id, 		
		event_data.value('(event/data[@name="reserved_kb"]/value)[1]','bigint') reserved_kb, 		
		event_data.value('(event/data[@name="resources"]/value)[1]','bigint') resources, 		
		event_data.value('(event/data[@name="shared_committed_kb"]/value)[1]','bigint') shared_committed_kb, 		
		event_data.value('(event/data[@name="target_kb"]/value)[1]','bigint') target_kb, 		
		event_data.query('event/name[@name="task"]') failure,
		event_data.value('(event/data[@name="total_page_file_kb"]/value)[1]','bigint') total_page_file_kb, 		
		event_data.value('(event/data[@name="total_physical_memory_kb"]/value)[1]','bigint') total_physical_memory_kb, 	
		event_data.value('(event/data[@name="total_virtual_address_space_kb"]/value)[1]','bigint') total_virtual_address_space_kb, 	
		event_data.value('(event/data[@name="call_stack"]/value)[1]','nvarchar(max)') call_stack, 
		event_data.value('(event/data[@name="session_id"]/value)[1]','int') session_id,
		event_data.value('(event/data[@name="sql_text"]/value)[1]','nvarchar(max)') sql_text, 
		event_data
	FROM #system_health
	where event_data.value('(event/@name)[1]','sysname') = 'memory_node_oom_ring_buffer_recorded'
	order by event_timestamp desc;

----- SECURITY ERROR RING BUFFER
SELECT 
		event_data.value('(event/@name)[1]','sysname') event_name,
		event_data.value('(event/@timestamp)[1]','datetime2') event_timestamp, 
		event_data.value('(event/data[@name="id"]/value)[1]','integer') id, 
		event_data.value('(event/data[@name="session_id"]/value)[1]','int') session_id, 
		event_data.value('(event/data[@name="error_code"]/value)[1]','sysname') error_code, 
		event_data.value('(event/data[@name="api_name"]/value)[1]','nvarchar(max)') api_name, 
		event_data.value('(event/data[@name="calling_api_name"]/value)[1]','nvarchar(max)') calling_api_name, 
		event_data.value('(event/data[@name="call_stack"]/value)[1]','nvarchar(max)') call_stack, 
		event_data
	FROM #system_health
	where event_data.value('(event/@name)[1]','sysname') = 'security_error_ring_buffer_recorded'
	order by event_timestamp desc;
	
----- CONNECTIVITY RING BUFFER
SELECT 
		event_data.value('(event/@name)[1]','sysname') event_name,
		event_data.value('(event/@timestamp)[1]','datetime2') event_timestamp, 
		event_data.value('(event/data[@name="id"]/value)[1]','integer') id, 
		event_data.value('(event/data[@name="type"]/text)[1]','sysname') type, 
		event_data.value('(event/data[@name="source"]/text)[1]','sysname') source, 
		event_data.value('(event/data[@name="session_id"]/value)[1]','int') session_id, 
		event_data.value('(event/data[@name="os_error"]/value)[1]','int') os_error, 
		event_data.value('(event/data[@name="sni_error"]/value)[1]','int') sni_error, 
		event_data.value('(event/data[@name="sni_consumer_error"]/value)[1]','int') sni_consumer_error, 
		event_data.value('(event/data[@name="sni_provider"]/value)[1]','int') sni_provider, 
		event_data.value('(event/data[@name="state"]/value)[1]','int') state, 
		event_data.value('(event/data[@name="local_port"]/value)[1]','int') local_port, 
		event_data.value('(event/data[@name="remote_port"]/value)[1]','int') remote_port, 
		event_data.value('(event/data[@name="tds_input_buffer_error"]/value)[1]','int') tds_input_buffer_error, 
		event_data.value('(event/data[@name="tds_output_buffer_error"]/value)[1]','int') tds_output_buffer_error, 
		event_data.value('(event/data[@name="tds_input_buffer_bytes"]/value)[1]','int') tds_input_buffer_bytes, 
		event_data.value('(event/data[@name="tds_flags"]/value)[1]','nvarchar(32)') tds_flags_bin,
		event_data.value('(event/data[@name="tds_flags"]/text)[1]','nvarchar(4000)') tds_flags_text,
		event_data.value('(event/data[@name="total_login_time_ms"]/value)[1]','int') total_login_time_ms, 
		event_data.value('(event/data[@name="login_task_enqueued_ms"]/value)[1]','int') login_task_enqueued_ms, 
		event_data.value('(event/data[@name="network_writes_ms"]/value)[1]','int') network_writes_ms, 
		event_data.value('(event/data[@name="network_reads_ms"]/value)[1]','int') network_reads_ms, 
		event_data.value('(event/data[@name="ssl_processing_ms"]/value)[1]','int') ssl_processing_ms, 
		event_data.value('(event/data[@name="sspi_processing_ms"]/value)[1]','int') sspi_processing_ms, 
		event_data.value('(event/data[@name="login_trigger_and_resource_governor_processing_ms"]/value)[1]','int') login_trigger_and_resource_governor_processing_ms, 
		event_data.value('(event/data[@name="connection_id"]/value)[1]','varchar(32)') connection_id, 
		event_data.value('(event/data[@name="connection_peer_id"]/value)[1]','varchar(32)') connection_peer_id, 
		event_data.value('(event/data[@name="local_host"]/value)[1]','varchar(15)') local_host, 
		event_data.value('(event/data[@name="remote_host"]/value)[1]','varchar(15)') remote_host, 
		event_data.value('(event/data[@name="call_stack"]/value)[1]','nvarchar(max)') call_stack, 
		event_data
	FROM #system_health
	where event_data.value('(event/@name)[1]','sysname') = 'connectivity_ring_buffer_recorded'
	order by event_timestamp desc;
------- XML Deadlock

	SELECT 
		event_data.value('(event/@name)[1]','sysname') event_name,
		event_data.value('(event/@timestamp)[1]','datetime2') event_timestamp, 
		event_data.query('event/data[@name="xml_report"]/value/deadlock') deadlock_graph,
		event_data
	
	FROM #system_health
	where event_data.value('(event/@name)[1]','sysname') = 'xml_deadlock_report'
	order by event_timestamp desc;



---------- PARSE RING BUFFER RECORDS
--INSERT INTO analysis.system_health.system_health_ring_buffers
SELECT
		event_data.value('(event/@name)[1]','sysname') event_name,
		event_data.value('(event/@timestamp)[1]','datetime2') event_timestamp, 
		event_data.value('(event/data[@name="id"]/value)[1]','int') id, 
		--base
		event_data.value('(event/data[@name="call_stack"]/value)[1]','nvarchar(max)') call_stack, 
		event_data.value('(event/data[@name="user_mode_time"]/value)[1]','bigint') user_mode_time, 
		event_data.value('(event/data[@name="kernel_mode_time"]/value)[1]','bigint') kernel_mode_time, 
		event_data.value('(event/data[@name="memory_utilization"]/value)[1]','int') memory_utilization,
		event_data.value('(event/data[@name="memory_allocated"]/value)[1]','bigint') memory_allocated,
		event_data.value('(event/data[@name="node_id"]/value)[1]','int') node_id,
		event_data.value('(event/data[@name="opcode"]/text)[1]','sysname') opcode,
		event_data.value('(event/data[@name="page_faults"]/value)[1]','decimal(30,0)') page_faults,
		event_data.value('(event/data[@name="process_utilization"]/value)[1]','int') process_utilization,
		event_data.value('(event/data[@name="system_idle"]/value)[1]','int') system_idle,
		event_data.value('(event/data[@name="working_set_delta"]/value)[1]','decimal(30,0)') working_set_delta,
		event_data.value('(event/data[@name="worker"]/value)[1]','nvarchar(max)') worker,
		event_data.value('(event/data[@name="scheduler"]/value)[1]','int') scheduler,
		event_data.value('(event/data[@name="worker_utilization"]/value)[1]','decimal(30,0)') worker_utilization,
		event_data.value('(event/data[@name="yields"]/value)[1]','decimal(30,0)') yields,
		--connectivity
		event_data.value('(event/data[@name="type"]/text)[1]','sysname') type, 
		event_data.value('(event/data[@name="source"]/text)[1]','sysname') source, 
		event_data.value('(event/data[@name="session_id"]/value)[1]','int') session_id, 
		event_data.value('(event/data[@name="os_error"]/value)[1]','int') os_error, 
		event_data.value('(event/data[@name="sni_error"]/value)[1]','int') sni_error, 
		event_data.value('(event/data[@name="sni_consumer_error"]/value)[1]','int') sni_consumer_error, 
		event_data.value('(event/data[@name="sni_provider"]/value)[1]','int') sni_provider, 
		event_data.value('(event/data[@name="state"]/value)[1]','int') state, 
		event_data.value('(event/data[@name="local_port"]/value)[1]','int') local_port, 
		event_data.value('(event/data[@name="remote_port"]/value)[1]','int') remote_port, 
		event_data.value('(event/data[@name="tds_input_buffer_error"]/value)[1]','int') tds_input_buffer_error, 
		event_data.value('(event/data[@name="tds_output_buffer_error"]/value)[1]','int') tds_output_buffer_error, 
		event_data.value('(event/data[@name="tds_input_buffer_bytes"]/value)[1]','int') tds_input_buffer_bytes, 
		event_data.value('(event/data[@name="tds_flags"]/value)[1]','nvarchar(32)') tds_flags_bin,
		event_data.value('(event/data[@name="tds_flags"]/text)[1]','nvarchar(4000)') tds_flags_text,
		event_data.value('(event/data[@name="total_login_time_ms"]/value)[1]','int') total_login_time_ms, 
		event_data.value('(event/data[@name="login_task_enqueued_ms"]/value)[1]','int') login_task_enqueued_ms, 
		event_data.value('(event/data[@name="network_writes_ms"]/value)[1]','int') network_writes_ms, 
		event_data.value('(event/data[@name="network_reads_ms"]/value)[1]','int') network_reads_ms, 
		event_data.value('(event/data[@name="ssl_processing_ms"]/value)[1]','int') ssl_processing_ms, 
		event_data.value('(event/data[@name="sspi_processing_ms"]/value)[1]','int') sspi_processing_ms, 
		event_data.value('(event/data[@name="login_trigger_and_resource_governor_processing_ms"]/value)[1]','int') login_trigger_and_resource_governor_processing_ms, 
		event_data.value('(event/data[@name="connection_id"]/value)[1]','varchar(32)') connection_id, 
		event_data.value('(event/data[@name="connection_peer_id"]/value)[1]','varchar(32)') connection_peer_id, 
		event_data.value('(event/data[@name="local_host"]/value)[1]','varchar(15)') local_host, 
		event_data.value('(event/data[@name="remote_host"]/value)[1]','varchar(15)') remote_host, 
		--security		
		event_data.value('(event/data[@name="error_code"]/value)[1]','sysname') error_code, 
		event_data.value('(event/data[@name="api_name"]/value)[1]','nvarchar(max)') api_name, 
		event_data.value('(event/data[@name="calling_api_name"]/value)[1]','nvarchar(max)') calling_api_name, 		
		--oom
		event_data.value('(event/data[@name="available_extended_virtual_address_space_kb"]/value)[1]','decimal(30,0)') available_extended_virtual_address_space_kb, 
		event_data.value('(event/data[@name="available_page_file_kb"]/value)[1]','decimal(30,0)') available_page_file_kb, 
		event_data.value('(event/data[@name="available_physical_memory_kb"]/value)[1]','decimal(30,0)') available_physical_memory_kb, 
		event_data.value('(event/data[@name="available_virtual_address_space_kb"]/value)[1]','decimal(30,0)') available_virtual_address_space_kb, 
		event_data.value('(event/data[@name="awe_kb"]/value)[1]','decimal(30,0)') awe_kb, 
		event_data.value('(event/data[@name="committed_kb"]/value)[1]','decimal(30,0)') committed_kb, 
		event_data.query('event/name[@name="factor"]') factor,
		event_data.query('event/name[@name="failure"]') failure,
		event_data.value('(event/data[@name="last_error"]/value)[1]','sysname') last_error, 
		event_data.value('(event/data[@name="memory_node_id"]/value)[1]','int') memory_node_id, 
		event_data.value('(event/data[@name="memory_utilization_pct"]/value)[1]','int') memory_utilization_pct, 
		event_data.value('(event/data[@name="pages_kb"]/value)[1]','decimal(30,0)') pages_kb, 		
		event_data.value('(event/data[@name="pool_metadata_id"]/value)[1]','int') pool_metadata_id, 		
		event_data.value('(event/data[@name="reserved_kb"]/value)[1]','decimal(30,0)') reserved_kb, 		
		event_data.value('(event/data[@name="resources"]/value)[1]','decimal(30,0)') resources, 		
		event_data.value('(event/data[@name="shared_committed_kb"]/value)[1]','decimal(30,0)') shared_committed_kb, 		
		event_data.value('(event/data[@name="target_kb"]/value)[1]','decimal(30,0)') target_kb, 		
		event_data.query('event/name[@name="task"]') task,
		event_data.value('(event/data[@name="total_page_file_kb"]/value)[1]','decimal(30,0)') total_page_file_kb, 		
		event_data.value('(event/data[@name="total_physical_memory_kb"]/value)[1]','decimal(30,0)') total_physical_memory_kb, 	
		event_data.value('(event/data[@name="total_virtual_address_space_kb"]/value)[1]','decimal(30,0)') total_virtual_address_space_kb, 					
		event_data.value('(event/data[@name="sql_text"]/value)[1]','nvarchar(max)') sql_text, 
		--memory broker
		event_data.value('(event/data[@name="broker"]/value)[1]','sysname') broker, 
		event_data.value('(event/data[@name="notification"]/value)[1]','sysname') notification, 
		event_data.value('(event/data[@name="delta_time"]/value)[1]','decimal(30,0)') delta_time, 
		event_data.value('(event/data[@name="memory_ratio"]/value)[1]','decimal(30,0)') memory_ratio, 
		event_data.value('(event/data[@name="new_target"]/value)[1]','decimal(30,0)') new_target, 
		event_data.value('(event/data[@name="overall"]/value)[1]','decimal(30,0)') overall, 
		event_data.value('(event/data[@name="rate"]/value)[1]','decimal(30,0)') rate, 
		event_data.value('(event/data[@name="currently_predicated"]/value)[1]','decimal(30,0)') currently_predicated, 
		event_data.value('(event/data[@name="currently_allocated"]/value)[1]','decimal(30,0)') currently_allocated, 
		event_data.value('(event/data[@name="previously_allocated"]/value)[1]','decimal(30,0)') previously_allocated, 				
		--SYSTEM HEALTH														 
		event_data	
	FROM #system_health
	where event_data.value('(event/@name)[1]','sysname') like '%ring_buffer_recorded'
	order by event_timestamp desc;

drop table #system_health