create table #ServerStats (create_time datetime,component_type sysname,component_name sysname, state int,state_desc sysname, data xml)

insert into #ServerStats execute sp_server_diagnostics

-- Overview 
-- Note: this events component will always show a status of unknown
select create_time as "Date",
       component_name as "Component",
	   state_desc as "Status" 
  from #ServerStats

-- System
select  'System' as "System",
		data.value('(/system/@systemCpuUtilization)[1]','bigint') as "System CPU",
        data.value('(/system/@sqlCpuUtilization)[1]','bigint') as "SQL CPU",
        data.value('(/system/@nonYieldingTasksReported)[1]','bigint') as "Non-yielding Tasks",
		data.value('(/system/@pageFaults)[1]','bigint') as "Page Faults",
		data.value('(/system/@latchWarnings)[1]','bigint') as "LatchWarnings"
  from #ServerStats 
 where component_name like 'system'

-- Memory
select 'Memory' as "Memory",
       data.value('(/resource/memoryReport/entry[@description="Working Set"]/@value)[1]','float')/1024/1024 "Memory Used by SQL Server (MB)",
       data.value('(/resource/memoryReport/entry[@description="Available Physical Memory"]/@value)[1]','float')/1024/1024 "Physical Memory Available (MB)",
	   data.value('(/resource/@lastNotification)[1]','varchar(100)') as "Last Notification",
       data.value('(/resource/@outOfMemoryExceptions)[1]','bigint') as "Out of Memory Exceptions"
  from #ServerStats 
 where component_name like 'resource'

-- Nonpreemptive waits by duration
select 'Non Preemptive by duration' as "Wait",
	   tbl.evt.value('(@waitType)','varchar(100)') as "Wait Type",
  	   tbl.evt.value('(@waits)','bigint') as "Waits",
	   tbl.evt.value('(@averageWaitTime)','bigint') as "Avg Wait Time",
	   tbl.evt.value('(@maxWaitTime)','bigint') as "Max Wait Time"
from #ServerStats 
    CROSS APPLY data.nodes('/queryProcessing/topWaits/nonPreemptive/byDuration/wait') AS tbl(evt)
 where component_name like 'query_processing'

-- Preemptive waits by duration
select 'Preemptive by duration' as "Wait",
	   tbl.evt.value('(@waitType)','varchar(100)') as "Wait Type",
  	   tbl.evt.value('(@waits)','bigint') as "Waits",
	   tbl.evt.value('(@averageWaitTime)','bigint') as "Avg Wait Time",
	   tbl.evt.value('(@maxWaitTime)','bigint') as "Max Wait Time"
from #ServerStats 
    CROSS APPLY data.nodes('/queryProcessing/topWaits/preemptive/byDuration/wait') AS tbl(evt)
 where component_name like 'query_processing'

-- CPU intensive queries
select 'CPU Intensive Queries' as "CPU Intensive Queries",
	   tbl.evt.value('(@sessionId)','bigint') as "Session ID",
	   tbl.evt.value('(@command)','varchar(100)') as "Command",
	   tbl.evt.value('(@cpuUtilization)','bigint') as "CPU",
	   tbl.evt.value('(@cpuTimeMs)','bigint') as "CPU Time (ms)"
  from #ServerStats 
    CROSS APPLY data.nodes('/queryProcessing/cpuIntensiveRequests/request') AS tbl(evt)
 where component_name like 'query_processing'

-- Blocked Process Reports
select 'Blocked Process Report' as "Blocked Process Report",
       tbl.evt.query('.') as "Report XML"
from #ServerStats 
    CROSS APPLY data.nodes('/queryProcessing/blockingTasks/blocked-process-report') AS tbl(evt)
 where component_name like 'query_processing'

-- IO report
select 'IO Subsystem' as "IO Subsystem",
       data.value('(/ioSubsystem/@ioLatchTimeouts)[1]','bigint') as "Latch Timeouts",
	   data.value('(/ioSubsystem/@totalLongIos)[1]','bigint') as "Total Long IOs"
from #ServerStats 
 where component_name like 'io_subsystem'

-- Event information
select tbl.evt.value('(@name)','varchar(100)') as "Event Name",
       tbl.evt.value('(@package)','varchar(100)') as "Package",
       tbl.evt.value('(@timestamp)','datetime') as "Event Time",
       tbl.evt.query('.') as "Event Data"
 from #ServerStats 
  CROSS APPLY data.nodes('/events/session/RingBufferTarget/event') AS tbl(evt)
where component_name like 'events'
 
drop table #ServerStats