Tuesday, September 25, 2012

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 
CREATE TABLE ##SpServerDiagnosticsResult 
 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

--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
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
SELECT CAST(data AS XML) AS xml_data FROM ##SpServerDiagnosticsResult
CROSS APPLY xml_data.nodes('/queryProcessing/topWaits/nonPreemptive/byCount/*') a(b)

No comments: