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