SQL Server 2012 sp_server_diagnostics

I'm starting on my promise of providing 1 (sometimes more) technical blog, each & every week during 2012

 

So with this being Week #1 of 2012, I thought it would be nice to start with a new system stored procedure in the SQL 2012 (CTP3 | RC0) builds

It is called nicely sp_server_diagnostics So any guesses on what this does ? 

Well here is the link to BOL on this http://msdn.microsoft.com/en-us/library/ff878233(v=sql.110).aspx and the tag line for it from BOL on its purpose

Captures diagnostic data and health information about SQL Server to detect potential failures. The procedure runs in repeat mode and sends results periodically. It can be invoked from either a regular or a DAC connection

Incidentally during my session @ SQLPASS Summit 2011 on Wait Statistics, I used this new SP, that is because one of the 5 data values returned provides an information snapshot

of the TOP 10 Waits by counter & duration for both pre-emptive & non-preemptive, this means we get 4 sets of 10 results on TOP waits- I wanted to track the most prevalent wait_types


Below is code I have put together to capture data @ 10 second intervals, and then tracking the variouis wait_types from snapshot to snapshot

 

USE tempdb

 

--DROP TABLE dbo.tmp_sp_server_diagnostics

CREATE TABLE dbo.tmp_sp_server_diagnostics

       (

       [create_time] datetime,

       [component_type] nvarchar(20),

       [component_name] nvarchar(20),

       [state] int,

       [state_desc] nvarchar(20),

       [data] xml

       )

 

/*

This doesn't work if we also supply the @Repeat_interval

*/

INSERT dbo.tmp_sp_server_diagnostics

EXEC sys.sp_server_diagnostics

  --    @Repeat_interval = 10  

 

Select * From tempdb.dbo.tmp_sp_server_diagnostics

 

 

/*

Note as we can't use the @Repeat_interval - if you wanted to capture & store data with can't simply use the INSERT method

So we have to collect the data from the output of sp_server_diagnostics SP via Extended Events

 

## Rememeber to Set your own filename Path in the following code ##

*/

 

CREATE EVENT SESSION [diag]

ON SERVER

       ADD EVENT [sp_server_diagnostics_component_result] (set collect_data=1)

    ADD TARGET package0.event_file

              (SET filename=N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\diag.xel')

GO

 

ALTER EVENT SESSION [diag] ON SERVER STATE = start

GO

 

/*

So we now can run with the @Repeat_interval parameter supplied and the XE session will capture the information

*/

 

EXEC sys.sp_server_diagnostics

       @Repeat_interval = 10  

 

/*

We can now analyze the results of the runs completed

*/

 

--DROP TABLE TopWaits

CREATE TABLE TopWaits ([RowN] INT, [XMLData] xml);

 

;WITH CTE_SD ([Name],[Package],[Time],[Component],[State],[State_desc],[Data])

AS(        

SELECT

  xml_data.value('(/event/@name)[1]','varchar(max)')                                      AS [Name]

, xml_data.value('(/event/@package)[1]', 'varchar(max)')                                  AS [Package]

, xml_data.value('(/event/@timestamp)[1]', 'datetime')                                    AS [Time]

, xml_data.value('(/event/data[@name=''component'']/value)[1]','varchar(max)')            AS [Component]

, xml_data.value('(/event/data[@name=''state'']/value)[1]','int')                         AS [State]

, xml_data.value('(/event/data[@name=''state_desc'']/value)[1]','varchar(max)')           AS [State_desc]

, xml_data.query('(/event/data[@name="data"]/value/*)')                                   AS [Data]

FROM

       (

    SELECT  OBJECT_NAME as event,CONVERT(xml, event_data) as xml_data

    FROM  sys.fn_xe_file_target_read_file

              ('C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\diag*.xel', NULL, NULL, NULL)

    ) AS XEventData

)

,[ParsedData] ([RowN],[Data])

AS

(

SELECT

ROW_NUMBER() OVER(PARTITION BY [Component] ORDER BY [Time] DESC) AS [RowN],

CAST([Data] AS XML) AS [Data]

FROM CTE_SD

WHERE [Component] = 2 -- <queryProcessing ... >

)

INSERT INTO TopWaits([RowN],[XMLData])

Select [RowN], [Data]

from ParsedData

 

Select

        Prev_interval.RowN

    ,Prev_interval.Wait_Type

    ,Prev_interval.WaitsCnt - Next_interval.WaitsCnt AS WaitsCnt

    ,Prev_interval.AvgWT - Next_interval.AvgWT       AS AvgWT

    ,Prev_interval.MaxWT - Next_interval.MaxWT       AS MaxWT

FROM

(

SELECT

       RowN,

    c.p.value('@waitType', 'varchar(50)')         [Wait_Type],

       c.p.value('@waits', 'int')                [WaitsCnt],

    c.p.value('@averageWaitTime', 'int')      [AvgWT],

    c.p.value('@maxWaitTime', 'int')          [MaxWT]

FROM TopWaits

CROSS APPLY XMLData.nodes('/queryProcessing/topWaits/nonPreemptive/byCount/*') c(p)

) AS Prev_interval ([RowN],[Wait_Type],[WaitsCnt],[AvgWT],[MaxWT])

INNER JOIN

(

SELECT

       RowN,

    c.p.value('@waitType', 'varchar(50)')         [Wait_Type],

    c.p.value('@waits', 'int')                [WaitsCnt],

    c.p.value('@averageWaitTime', 'int')      [AvgWT],

    c.p.value('@maxWaitTime', 'int')          [MaxWT]

FROM TopWaits

CROSS APPLY XMLData.nodes('/queryProcessing/topWaits/nonPreemptive/byCount/*') c(p)

) AS Next_interval ([RowN],[Wait_Type],[WaitsCnt],[AvgWT],[MaxWT])

ON Prev_interval.[Wait_Type] = Next_interval.[Wait_Type]

and Prev_interval.RowN +1 = Next_interval.RowN

 

Published Tuesday, January 3, 2012 5:59 PM by NeilHambly

Comments

# Recupera????o de erros de Mem??ria no SQL Server 2012 &#8211; Parte 2 | Vladimir M. B. Magalh??es &#8211; Learn and Share

Pingback from  Recupera????o de erros de Mem??ria no SQL Server 2012 &#8211; Parte 2 | Vladimir M. B. Magalh??es &#8211; Learn and Share

# Recupera????o de erros de Mem??ria no SQL Server 2012 &#8211; Parte 2 | Vladimir M. B. Magalh??es &#8211; Learn and Share

Pingback from  Recupera????o de erros de Mem??ria no SQL Server 2012 &#8211; Parte 2 | Vladimir M. B. Magalh??es &#8211; Learn and Share

Wednesday, October 3, 2012 3:48 PM by mdbourgon

# re: SQL Server 2012 sp_server_diagnostics

Neil, thanks for this.  I used this as a jumping off point for a parser for the EVENTS in sp_server_diag: thebakingdba.blogspot.com/.../spserverdiagnostics-dynamic-events.html