sp_server_diag_event_parser - Make SP_Server_Diagnostics EVENTS human-readable!

(update 2013/01/08 - just made another speed fix - now runs in under 20 seconds!)

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:

•  http://msdn.microsoft.com/en-us/library/ff878233.aspx
• http://blogs.msdn.com/b/psssql/archive/2012/03/08/sql-server-2012-true-black-box-recorder.aspx
•  http://www.mytechmantra.com/LearnSQLServer/Using-sp_server_diagnostics-Quickly-Capture-Diagnostic-Data-and-Health-Information-SQL-Server-2012.html
•  http://www.sqlskills.com/blogs/joe/post/Under-the-covers-with-sp_server_diagnostics-data-(Part-I).aspx

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:

• connectivity_ring_buffer_recorded
• error_reported
• resource_monitor_ring_buffer_recorded
• scheduler_monitor_system_health_ring_buffer_recorded
• security_error_ring_buffer_recorded
• scheduler_monitor_non_yielding_ring_buffer_recorded
• ...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):

       
<data name="call_stack">

<type name="callstack" package="package0" />

<value />

</data>

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:

       

         
<data name="tds_flags">

<type name="connectivity_record_tds_flag" package="sqlserver" />

<value>0x0000003e</value>

<text>DisconnectDueToReadError, NetworkErrorFoundInInputStream, ErrorFoundBeforeLogin, SessionIsKilled, NormalDisconnect</text>

</data>

to:

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.  This should be fixed now - converted it to a GIST on 2014/02/24)

	CREATE PROCEDURE sp_server_diag_event_parser
	as
	--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
	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
	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)
	select
	DISTINCT EventName = Evt.value('(@name)[1]', 'varchar(100)')
	FROM
	(
	SELECT CAST(data AS XML) AS xml_data
	FROM #SpServerDiagnosticsResult
	WHERE component_name = 'events'
	)getlistofsubevents
	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
	select distinct
	EventName = Evt.value('(../@name)[1]', 'nvarchar(100)'),
	SubEventName = Evt.value('(@name)[1]', 'nvarchar(100)'),
	SubDataType = CASE Evt.value('(type/@name)[1]', '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.
	FROM
	(
	SELECT @xml AS xml_data
	)event_xml_record
	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
	BEGIN
	SET @sql = NULL
	SELECT @eventtype = EventName FROM @events WHERE id = @min
	--header for the query
	SELECT @sql = N'select
	EventName = Evt.value(''(@name)[1]'', ''varchar(100)'')
	,OriginalTime = Evt.value(''(@timestamp)[1]'', ''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' + ')[1]'', ''' + SubDataType + ''')' + CHAR(10) + CHAR(9)
	WHEN N'bigint' THEN N' = Evt.value(''(data[@name="' + SubEventName + '"]/value'+ ')[1]'', ''' + SubDataType + ''')' + CHAR(10) + CHAR(9)
	WHEN N'unicode_string' THEN N' = Evt.value(''(data[@name="' + SubEventName + '"]/value' + ')[1]'', ''' + SubDataType + ''')' + CHAR(10) + CHAR(9)
	WHEN N'uniqueidentifier' THEN N' = Evt.value(''(data[@name="' + SubEventName + '"]/value' + ')[1]'', ''' + SubDataType + ''')' + CHAR(10) + CHAR(9)
	WHEN N'nvarchar(1000)' THEN N' = Evt.value(''(data[@name="' + SubEventName + '"]/value' + ')[1]'', ''' + SubDataType + ''')' + CHAR(10) + CHAR(9)
	WHEN N'bit' THEN N' = Evt.value(''(data[@name="' + SubEventName + '"]/value' + ')[1]'', ''' + SubDataType + ''')' + CHAR(10) + CHAR(9)
	ELSE N' = isnull(Evt.value(''(data[@name="' + SubEventName + '"]/type/@name)[1]'', ''varchar(100)''),'''') + '' : ''
	+ isnull(Evt.value(''(data[@name="' + SubEventName + '"]/text)[1]'', ''varchar(100)''),'''') + '' : ''
	+ isnull(Evt.value(''(data[@name="' + SubEventName + '"]/value)[1]'', ''varchar(100)''),'''')' + CHAR(10) + CHAR(9)
	end
	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'
	from (
	SELECT @eventxml AS xml_data
	)spserverdiageventparser
	CROSS APPLY xml_data.nodes(''/events/session/RingBufferTarget/event'') Tbl(Evt)
	WHERE Evt.value(''(@name)[1]'', ''varchar(100)'') = ''' + @eventtype + ''''
	SET @ParmDefinition = N'@eventxml xml'
	EXEC sp_executesql @sql, @parmdefinition, @eventxml = @xml
	PRINT @sql
	SET @min = @min + 1
	END
	DROP TABLE #SpServerDiagnosticsResult
	GO

view raw sp_server_diag_event_parser.sql hosted with ❤ by GitHub

Powershell - run a query against multiple servers in parallel, saving the data to a central table

(update 2013/06/13: I've superceded this with a new framework that will run almost any piece of code, powershell or sql, saving the results to a table, and running with multiple threads. Please give it a look!
http://thebakingdba.blogspot.com/2013/04/servers-extensible-powershell.html )


Update at 8:30pm: tweaked to add a throttled scheduler!  Runs 8 simultaneous threads to prevent too much workload on the box running the PS script.

SO...
I have a repository server.
Said repository server has a list of servers I monitor.
I want to be able to run a piece of code against all of them, AT THE SAME TIME, and save those results to  a table on my repository.  The code currently takes an hour to run, I'd like to shrink that.  Looking online, it looks like this is fairly basic code, but I hadn't seen it put together like this.

First, we need a table to save the results to.  Simple results, simple table.  Proof of concept, here, so only two fields.

CREATE TABLE myserverinfo (servername sysname, thedate datetime)

The next part needs two existing Powershell scripts written by "Hey Scripting Guy", invoke-sqlcmd2 and write-datatable.  You will need to get those.  I save (and reference) them in c:\sql_tools.

The powershell script.  Feel free to drag, copy, cut, paste and save as a .ps1 file:

#Run SQL query against multiple servers in parallel, saving results to a central table
# 1.00 2012/12/13 mdb / TBD
# 1.10 2012/12/13 mdb / TBD.  Adding throttling scheduler
# 1.11 2012/12/13 mdb adding comment on WHY you want the wait-job
# Code cribbed and lessons learned courtesy of: Hey Scripting Guy, 
# Aaron Bertrand (the bit with $args[0], his "sad panda face" post)
# Kendra Little.  Throttling scheduler is from "start-automating" on 
# Stackoverflow.  Errors are mine, not theirs.  
# Please keep this header and let me know how it works. thebakingdba.blogspot.com
. C:\sql_tools\invoke-sqlcmd2.ps1;
$serverlist = invoke-sqlcmd2 -serverinstance "myrepositoryserver" -query "SELECT server FROM dba.dbo.myserverlist WHERE active = 1" #get the list of your servers to monitor
foreach ($server in $serverlist)
{
    $jobsrunning = @(Get-Job | Where-Object { $_.JobStateInfo.State -eq 'Running' })
    if ($jobsrunning.Count -le 8) #keeps 8 jobs going simultaneously
    {
        start-job -argumentlist $server.server -scriptblock `
        {
        #have to reimport functions here due to the scriptblock IIRC
        . C:\sql_tools\invoke-sqlcmd2.ps1;
        . C:\sql_tools\write-datatable.ps1;
        $quer = invoke-sqlcmd2 -serverinstance $args[0] -database "master" `
            -query "waitfor delay '00:00:10'; select @@servername as servername, getdate() as thedate" -As 'DataTable'
        Write-DataTable -ServerInstance "myrepositoryserver" -Database "dba" -TableName "myserverinfo" -Data $quer
        } 
    } 
    else
    {
        $jobsrunning | Wait-Job -Any  #as soon as any job finishes, do the next
    }
}

    
get-job | wait-job -timeout 10         #wait 10 seconds or until all jobs are 
# done, whichever comes first.  Gives us a timeout before killing the slowpokes.
#additionally, if you schedule a job to run this, not setting a -timeout allows 
#it to wait for all the jobs to finish before quitting.  They wont run in the 
#background like you think they should.  

get-job|Remove-Job -force #cleanup and remove all jobs.  Kills stragglers.

SQLSentry - new feature in 7 (7.1?) that old customers need to know about

So, we lost our SQL Sentry database server last week - thing took a dive.  And, oddly enough, we didn't get a notification from SQL Sentry, even though the database server was being monitored.

Grrrr.

However, from talking with their tech support (who, honestly, are pretty awesome), there's a new notification that can be enabled, that fixes that.  It's not on by default (though I told them they need to change that), so you SHOULD enable it.  

In the Navigator Pane, go to Shared Groups (Global)
Open Failsafe Actions (on mine, it's a tab on the right side of the app)
Add "Monitoring Service: SQL Sentry Database Offline", Send Email
Add "Monitoring Service: SQL Sentry Database Online", Send Email

It also seems like they've improved the way the monitoring services work these days - it was scary easy to add a secondary site that uses our main SQL Sentry database, but monitors only servers at that site.  It's also really easy to add a backup/load-balancing monitoring service in general - install the service on another box, point it at your main SQL Sentry database, and away you go.

May my pain be your gain.