In SQL Server 2012 a new procedure sp_server_diagnostics returns a payload of information about server health and performance. The sproc is leveraged by the Windows Failover Cluster service and is designed to run in repeat mode. However the sproc can be run in non-repeat mode and loaded into a temp table. From there we can slice and dice the XML payload as desired.

Create a temp table and load the results of sp_server_diagnostics:

create table #servdiag
(
create_time datetime,
component_type sysname,
component_name sysname,
[state] int,
state_desc sysname,
data varchar(max)
)
insert into #servdiag
exec sp_server_diagnostics

And have a look at the results:

select * from #servdiag

To have a look at the XML we can do a quick convert:

select cast(data as xml) as xml_data 
from #servdiag for xml auto,elements

Clicking on this link will show all five XML fragments concatenated into a single fragment.

From here, there’s a number of ways to shred the XML into relational data – xquery, XML data type methods or openxml. I’ll present some queries that use openxml. This method begins by preparing an XML document so that SQL Server is able to parse the resultant DOM. Using the XML data type methods will implicitly create this DOM per query. The data type methods are slightly more efficient for single queries, however if you are using multiple queries there might be some advantage in preparing the DOM once and then accessing it multiple times. Your mileage may vary.

Here are some queries against our temp table from above.

Memory metrics:

declare @x varchar(max)
declare @dochandle int
select @x = data 
from #servdiag 
where component_name = 'resource'
exec sp_xml_preparedocument @dochandle output, @x
select * 
from openxml(@dochandle, '/resource/memoryReport/entry', 3) 
with (description varchar(255), value bigint)
exec sp_xml_removedocument @dochandle

Top 10 waits by count:

declare @x varchar(max)
declare @dochandle int
select @x = data 
from #servdiag 
where component_name = 'query_processing'
exec sp_xml_preparedocument @dochandle output, @x
select * 
from openxml(@dochandle, '/queryProcessing/topWaits/nonPreemptive/byCount/wait', 3) 
with (waitType varchar(255), waits bigint, averageWaitTime bigint, maxWaitTime bigint)
exec sp_xml_removedocument @dochandle

Top 10 waits by duration:

declare @x varchar(max)
declare @dochandle int
select @x = data 
from #servdiag 
where component_name = 'query_processing'
exec sp_xml_preparedocument @dochandle output, @x
select * 
from openxml(@dochandle, '/queryProcessing/topWaits/nonPreemptive/byDuration/wait', 3) 
with (waitType varchar(255), waits bigint, averageWaitTime bigint, maxWaitTime bigint)
exec sp_xml_removedocument @dochandle

Drop the temp table when you’ve finished playing:

drop table #servdiag