sp_server_diagnostics - parsing (part 1 - RESOURCE)

SQL Server 2012 offers a new way to view problems on your server.  It's light-weight, it's already running, and it captures all sorts of information.

It's called sp_server_diagnostics.

There's just one problem - the results are in XML and are hard to read.  We can fix that.  We have the Xquery.

Here's part 1, the results from the memory and waits.
Part 2 is the tricky part - events.  Forthcoming.


First, let's catch the data.

--takes 5 seconds to run, only valid on 2012 servers
if object_id('tempdb..##SpServerDiagnosticsResult') is null 
BEGIN
CREATE TABLE ##SpServerDiagnosticsResult 
(
 rowId INT IDENTITY PRIMARY KEY,
 create_time DateTime,
 component_type varchar(128),
 component_name varchar(128),
 state int,
 state_desc varchar(20),
 data varchar(max)
)

INSERT INTO ##SpServerDiagnosticsResult
EXEC sys.sp_server_diagnostics
END

--now to parse

--idea pilfered from Neil Hambly, written by M Bourgon

--Memory Info

SELECT a.b.value('@description','varchar(100)') AS descript, a.b.value('@value','bigint') AS val FROM 

(

SELECT CAST(data AS XML) AS xml_data FROM ##SpServerDiagnosticsResult

)aaa

CROSS APPLY xml_data.nodes('/resource/memoryReport/*') a(b)

--top 10 wait types

SELECT a.b.value('@waitType','varchar(100)') AS WaitType,

  a.b.value('@waits','bigint') AS WaitType_Waits,

  a.b.value('@averageWaitTime','bigint') AS WaitType_Avg,

  a.b.value('@maxWaitTime','bigint') AS WaitType_Max

FROM 

(

SELECT CAST(data AS XML) AS xml_data FROM ##SpServerDiagnosticsResult

)aaa

CROSS APPLY xml_data.nodes('/queryProcessing/topWaits/nonPreemptive/byCount/*') a(b)