“SQL Server Black Box Analysis” script give you a MRI report of SQL Server. Haha ha…

Script…

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