This is my third post about sp_server_diagnostics, in which I've written two parsers to look at the various data returned from it. This post is about the events component_type. Next post: query_processing.
50000 foot view: my code in this post takes the data from "events" component type, and returns:
For more info on sp_server_diagnostics in general, since it's going to be the replacement for the system_health session (SQL 2008) and the black box trace (SQL 2005), I'd recommend reading:
My first post on sp_server_diagnostics (http://thebakingdba.blogspot.com/2012/09/spserverdiagnostics-parsing-part-1-of-2.html) covered two of the rows & was pretty simple - it simply returned the memory info from "resource", and the waits from "query_processing". Which showed sp_server_diag's power.
However, while 3-4 of the rows are fairly readable, even in XML, the component_name "events" is a bit trickier - there are multiple types of event, each one with its own different elements:
- ...and even more
My thought was, rather than to hard-code it (which could mean I miss something), I would parse the XML dynamically, in two passes. The first pass gets the various elements for each event type, the second uses that info to write another query to get the info from each event type. As a side effect, it also allows me to format it better. This is a modified version from my original post, which had some flaws.
What flaws? Mostly that it really slow on servers with a lot of info, and it didn't return the full error message in some cases. Fortunately, I was able to speed it up substantially through a trivial change that took me the better part of an afternoon to find - went from a pulling the XML in a derived sub-queries, to a variable. From that one change, it now runs between 6 seconds and 2 minutes. I also tweaked it to return more data in long error strings.
You will see cryptic fields like "callstack : : " in the results.
It comes from (sample XML, formatted strange so that it shows up here):
<type name="callstack" package="package0" />
As you can see, there's not really anything to report. At the same point, I'm not sure why that's there, and I'd rather leave it in case things change in the future. The nice side-effect is that it converts from:
type name="connectivity_record_tds_flag" package="sqlserver" />
text>DisconnectDueToReadError, NetworkErrorFoundInInputStream, ErrorFoundBeforeLogin, SessionIsKilled, NormalDisconnect</text>
connectivity_record_tds_flag : DisconnectDueToReadError, NetworkErrorFoundInInputStream, ErrorFoundBeforeLogin, SessionIsKilled, No : 0x0000003e
If you do see a field with a value like: "int8: : 15" (where there are two semicolons), please let me know; while I've trapped all the various event datatypes that I could find, I literally found one (int16) while writing this post.
To use: Copy/paste the entire code below, create the SP, then run it (or just run all the code save the CREATE. It can return several sets of data. You'll also see where they reference each other: a row in connectivity_ring_buffer_recorded can reference a TDs error, which can then show up in error_reported as a network error message.
I hope this helps you troubleshoot SQL Server 2012.
Feel free to share, feel free to send me changes and enhancements. Tell me what you like and dislike!
(Note that when copy/pasting, you may see "WHILE @min <= @max;" in the code, which SSMS can't use, and the proc won't compile. That should be a LESS-THAN symbol, followed by an EQUALS sign)
CREATE PROCEDURE sp_server_diag_event_parser
--SP_SERVER_DIAGNOSTICS Dynamic Parser for "events", v1.23
--You may use this at will, you may share it provided this header remains.
-- Copyright 2012 Michael Bourgon
-- Commercial use or sale prohibited without permission. Personal, Internal Company, or Private use is fine.
-- If you're just running this as your job as a DBA, enjoy.
-- Please feel free to share, and feel free to send corrections or enhancements - thebakingdba.blogspot.com
-- Thanks to Marc_S on Stackoverflow for the help on parsing XML.
-- Thanks to Stack Overflow for forcing me to come up with a good question - so I found the flawed derived table slowdown.
-- mdb 2012/12/28 found the massive slowdown in the pre-parse name/datatype - changed from derived table (4 minutes)
-- to a variable: 18 seconds. Wow. Then split that out to a table variable (5 seconds). Even on my trouble
-- servers it now runs in under 2 minutes.
-- mdb 2013/01/08 even faster (18 sec!), by changing my end query to use the variable as well
--InANutShell: fast shred on the EVENTS portion of SP_SERVER_DIAGNOSTICS, getting the event type, sub-type and datatype.
-- Then query the XML, pulling out each event type with its specific attributes. End result: human readable,
-- though with enough data to choke a horse.
--takes 5 seconds to run, only valid on 2012 servers
if object_id('tempdb..#SpServerDiagnosticsResult') is null
CREATE TABLE #SpServerDiagnosticsResult
rowId INT IDENTITY PRIMARY KEY,
INSERT INTO #SpServerDiagnosticsResult
SET NOCOUNT ON
DECLARE @events TABLE (id INT IDENTITY PRIMARY KEY, EventName VARCHAR(100))
DECLARE @sql NVARCHAR(max)
DECLARE @min int, @max INT, @eventtype VARCHAR(100), @xml XML
DECLARE @full_data_info TABLE (EventName NVARCHAR(100), SubEventName NVARCHAR(100), SubDataType NVARCHAR(50))
DECLARE @parmdefinition NVARCHAR(500)
--get a list of event types, then walk through each separately; columns won't match
INSERT INTO @events (EventName)
DISTINCT EventName = Evt.value('(@name)', 'varchar(100)')
SELECT CAST(data AS XML) AS xml_data
WHERE component_name = 'events'
CROSS APPLY xml_data.nodes('/events/session/RingBufferTarget/event') Tbl(Evt)
SELECT @xml = CAST(data AS XML) FROM #SpServerDiagnosticsResult WHERE component_name = 'events'
--break out each event type for the larger query; could just use nvarchar/varchar for everything, but returning the right data type is cleaner
-- (and we need to know when it's a non-standard type for the name/text/value)
INSERT INTO @full_data_info
EventName = Evt.value('(../@name)', 'nvarchar(100)'),
SubEventName = Evt.value('(@name)', 'nvarchar(100)'),
SubDataType = CASE Evt.value('(type/@name)', 'nvarchar(100)')
WHEN 'int16' THEN N'int'
WHEN 'int32' THEN N'int'
WHEN 'uint16' THEN N'int'
WHEN 'boolean' THEN N'bit'
WHEN 'unicode_string' THEN N'nvarchar(1000)'
WHEN 'uint32' THEN N'bigint'
WHEN 'uint64' THEN N'nvarchar(1000)'
WHEN 'guid' THEN N'uniqueidentifier'
WHEN 'ansi_string' THEN N'nvarchar(1000)'
ELSE N'nvarchar(150)' END --if unknown, then probably name/text/value.
SELECT @xml AS xml_data
CROSS APPLY xml_data.nodes('/events/session/RingBufferTarget/event/data') Tbl(Evt)
--Loop - for each event type, generate a SQL script for those columns
SELECT @min = MIN(id), @max = MAX(id) FROM @events
WHILE @min <= @max
SET @sql = NULL
SELECT @eventtype = EventName FROM @events WHERE id = @min
--header for the query
SELECT @sql = N'select
EventName = Evt.value(''(@name)'', ''varchar(100)'')
,OriginalTime = Evt.value(''(@timestamp)'', ''varchar(100)'')' + CHAR(10) + CHAR(9)
--meat of the query - get the data for each unique TYPE, if a normal value.
-- if the subdatatype is not a "normal" type, we assume we want a name/text/value
-- we use varchar(100) for that, rather than a separate CASE, for speed
-- SO, don't just add varchar(100) to this CASE without understanding why.
SELECT @sql = @sql +
N' ,' + SubEventName +
+ CASE SubDataType
when N'int' THEN N' = Evt.value(''(data[@name="' + SubEventName + '"]/value' + ')'', ''' + SubDataType + ''')' + CHAR(10) + CHAR(9)
WHEN N'bigint' THEN N' = Evt.value(''(data[@name="' + SubEventName + '"]/value'+ ')'', ''' + SubDataType + ''')' + CHAR(10) + CHAR(9)
WHEN N'unicode_string' THEN N' = Evt.value(''(data[@name="' + SubEventName + '"]/value' + ')'', ''' + SubDataType + ''')' + CHAR(10) + CHAR(9)
WHEN N'uniqueidentifier' THEN N' = Evt.value(''(data[@name="' + SubEventName + '"]/value' + ')'', ''' + SubDataType + ''')' + CHAR(10) + CHAR(9)
WHEN N'nvarchar(1000)' THEN N' = Evt.value(''(data[@name="' + SubEventName + '"]/value' + ')'', ''' + SubDataType + ''')' + CHAR(10) + CHAR(9)
WHEN N'bit' THEN N' = Evt.value(''(data[@name="' + SubEventName + '"]/value' + ')'', ''' + SubDataType + ''')' + CHAR(10) + CHAR(9)
ELSE N' = isnull(Evt.value(''(data[@name="' + SubEventName + '"]/type/@name)'', ''varchar(100)''),'''') + '' : ''
+ isnull(Evt.value(''(data[@name="' + SubEventName + '"]/text)'', ''varchar(100)''),'''') + '' : ''
+ isnull(Evt.value(''(data[@name="' + SubEventName + '"]/value)'', ''varchar(100)''),'''')' + CHAR(10) + CHAR(9)
FROM @full_data_info full_data_info
WHERE EventName = @eventtype
--and the footer for our query; might be able to do a dual CROSS APPLY, but this is more readable
SELECT @sql = @sql + N'
SELECT @eventxml AS xml_data
CROSS APPLY xml_data.nodes(''/events/session/RingBufferTarget/event'') Tbl(Evt)
WHERE Evt.value(''(@name)'', ''varchar(100)'') = ''' + @eventtype + ''''
SET @ParmDefinition = N'@eventxml xml'
EXEC sp_executesql @sql, @parmdefinition, @eventxml = @xml
SET @min = @min + 1
DROP TABLE #SpServerDiagnosticsResult