Quick tip: Using sp_server_diagnostics

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
About these ads
This entry was posted in SQL Server, T-SQL. Bookmark the permalink.

2 Responses to Quick tip: Using sp_server_diagnostics

  1. Dark Helmut says:

    Thanks for the info. Not currently using 2012 at work, but the plans are in the works. This could be a helpful piece of knowledge.

    Why not store the data as XML instead of VARCHAR(MAX)? This would save the required conversion and the impact on CPU and IO should be relatively minimal. I don’t have any proof to back it up, but SQL Server is optimized to store XML data as the XML datatype so performance should be good. If the XML schema is also known and specified then the difference could be even more pronounced.

    Granted, if this just a one off instance to look at the data returned from the SP then it is of little import. On the other hand if this SP were used to keep a performance history log, then I would think it would be worth it.

    Cheers and thanks for the information!
    DH

    • matt.bowler says:

      Thanks for the comment. As a first pass at this I simply created my table with the data types that were surfaced by the sproc. If these were being persisted in a maintenance table and manipulated/queried multiple times it would definitely make sense to optimise this.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s