Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Ctrl-alt-geek

Matt Bowler is a DBA at Trade Me, and teaches database design and administration courses at the local institute of technology. A recent but avid discoverer of SQL Server, Matt is a regular contributor to forums at MSDN, Experts Exchange and SSC and he blogs at mattsql.wordpress.com.

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

Comments

Leave a comment on the original post [mattsql.wordpress.com, opens in a new window]

Loading comments...