none
How to parse sp_server_diagnostics results in sql 2012

    Question

  • How to parse the xml data that sp_server_diagnostics returns (data for system, resource, query_processing, io_subsystem, events), is there a way to convert them to tables ?

    I attached the xml contents below, I am planning on doing do parsing against the temporary table #SpServerDiagnosticsResult in the sample code

    Thank you


    if object_id('tempdb..#SpServerDiagnosticsResult') is null 
    BEGIN
    	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 sp_server_diagnostics
    END
    
    SELECT *
    FROM(SELECT *
    	, cast(data AS XML) AS xml_data
    FROM #SpServerDiagnosticsResult) k
    

    <system spinlockBackoffs="0" sickSpinlockType="none" sickSpinlockTypeAfterAv="none" latchWarnings="0" isAccessViolationOccurred="0" writeAccessViolationCount="0" totalDumpRequests="0" intervalDumpRequests="0" nonYieldingTasksReported="0" pageFaults="3270" systemCpuUtilization="6" sqlCpuUtilization="0" BadPagesDetected="0" BadPagesFixed="0" LastBadPageAddress="0x0" />
    <resource lastNotification="RESOURCE_MEMPHYSICAL_HIGH" outOfMemoryExceptions="0" isAnyPoolOutOfMemory="0" processOutOfMemoryPeriod="0">
      <memoryReport name="Process/System Counts" unit="Value">
        <entry description="Available Physical Memory" value="1248980992" />
        <entry description="Available Virtual Memory" value="8777178599424" />
        <entry description="Available Paging File" value="8933584896" />
        <entry description="Working Set" value="2574204928" />
        <entry description="Percent of Committed Memory in WS" value="99" />
        <entry description="Page Faults" value="1300050" />
        <entry description="System physical memory high" value="1" />
        <entry description="System physical memory low" value="0" />
        <entry description="Process physical memory low" value="0" />
        <entry description="Process virtual memory low" value="0" />
      </memoryReport>
      <memoryReport name="Memory Manager" unit="KB">
        <entry description="VM Reserved" value="18024612" />
        <entry description="VM Committed" value="2517220" />
        <entry description="Locked Pages Allocated" value="0" />
        <entry description="Large Pages Allocated" value="0" />
        <entry description="Emergency Memory" value="1024" />
        <entry description="Emergency Memory In Use" value="16" />
        <entry description="Target Committed" value="3314512" />
        <entry description="Current Committed" value="2517224" />
        <entry description="Pages Allocated" value="2292536" />
        <entry description="Pages Reserved" value="0" />
        <entry description="Pages Free" value="9456" />
        <entry description="Pages In Use" value="647352" />
        <entry description="Page Alloc Potential" value="7305504" />
        <entry description="NUMA Growth Phase" value="0" />
        <entry description="Last OOM Factor" value="0" />
        <entry description="Last OS Error" value="0" />
      </memoryReport>
    </resource>

    <queryProcessing maxWorkers="512" workersCreated="73" workersIdle="41" tasksCompletedWithinInterval="13" pendingTasks="0" oldestPendingTaskWaitingTime="0" hasUnresolvableDeadlockOccurred="0" hasDeadlockedSchedulersOccurred="0" trackingNonYieldingScheduler="0x0">
      <topWaits>
        <nonPreemptive>
          <byCount>
            <wait waitType="ASYNC_NETWORK_IO" waits="260365" averageWaitTime="35704" maxWaitTime="410" />
            <wait waitType="HADR_FILESTREAM_IOMGR_IOCOMPLETION" waits="240468" averageWaitTime="123234326" maxWaitTime="808" />
            <wait waitType="WRITELOG" waits="60720" averageWaitTime="148886" maxWaitTime="324" />
            <wait waitType="BACKUPIO" waits="22284" averageWaitTime="168345" maxWaitTime="1576" />
            <wait waitType="PAGEIOLATCH_SH" waits="14988" averageWaitTime="102369" maxWaitTime="1332" />
            <wait waitType="DISPATCHER_QUEUE_SEMAPHORE" waits="11000" averageWaitTime="85910396" maxWaitTime="76336948" />
            <wait waitType="SLEEP_BPOOL_FLUSH" waits="8210" averageWaitTime="21585" maxWaitTime="58" />
            <wait waitType="IO_COMPLETION" waits="6603" averageWaitTime="70757" maxWaitTime="889" />
            <wait waitType="PAGEIOLATCH_EX" waits="3227" averageWaitTime="60293" maxWaitTime="1663" />
            <wait waitType="PAGELATCH_EX" waits="2760" averageWaitTime="48" maxWaitTime="4" />
          </byCount>
          <byDuration>
            <wait waitType="CLR_AUTO_EVENT" waits="87" averageWaitTime="244646465" maxWaitTime="68507909" />
            <wait waitType="HADR_FILESTREAM_IOMGR_IOCOMPLETION" waits="240468" averageWaitTime="123234326" maxWaitTime="808" />
            <wait waitType="DISPATCHER_QUEUE_SEMAPHORE" waits="11000" averageWaitTime="85910396" maxWaitTime="76336948" />
            <wait waitType="BACKUPTHREAD" waits="268" averageWaitTime="365188" maxWaitTime="61404" />
            <wait waitType="ASYNC_IO_COMPLETION" waits="51" averageWaitTime="210062" maxWaitTime="33068" />
            <wait waitType="BACKUPIO" waits="22284" averageWaitTime="168345" maxWaitTime="1576" />
            <wait waitType="WRITELOG" waits="60720" averageWaitTime="148886" maxWaitTime="324" />
            <wait waitType="PAGEIOLATCH_SH" waits="14988" averageWaitTime="102369" maxWaitTime="1332" />
            <wait waitType="IO_COMPLETION" waits="6603" averageWaitTime="70757" maxWaitTime="889" />
            <wait waitType="LCK_M_S" waits="8" averageWaitTime="62566" maxWaitTime="12283" />
          </byDuration>
        </nonPreemptive>
        <preemptive>
          <byCount>
            <wait waitType="PREEMPTIVE_OS_WAITFORSINGLEOBJECT" waits="232769" averageWaitTime="34878" maxWaitTime="410" />
            <wait waitType="PREEMPTIVE_OS_QUERYREGISTRY" waits="2063" averageWaitTime="1150" maxWaitTime="1" />
            <wait waitType="PREEMPTIVE_OS_AUTHENTICATIONOPS" waits="1421" averageWaitTime="288" maxWaitTime="144" />
            <wait waitType="PREEMPTIVE_OS_FILEOPS" waits="1229" averageWaitTime="5620" maxWaitTime="375" />
            <wait waitType="PREEMPTIVE_OS_CREATEFILE" waits="953" averageWaitTime="17493" maxWaitTime="1605" />
            <wait waitType="PREEMPTIVE_OS_GETFILEATTRIBUTES" waits="788" averageWaitTime="666" maxWaitTime="476" />
            <wait waitType="PREEMPTIVE_OS_AUTHORIZATIONOPS" waits="415" averageWaitTime="24" maxWaitTime="16" />
            <wait waitType="PREEMPTIVE_OS_REVERTTOSELF" waits="364" averageWaitTime="3" maxWaitTime="0" />
            <wait waitType="PREEMPTIVE_FILESIZEGET" waits="357" averageWaitTime="20" maxWaitTime="0" />
            <wait waitType="PREEMPTIVE_OS_FLUSHFILEBUFFERS" waits="319" averageWaitTime="17898" maxWaitTime="401" />
          </byCount>
          <byDuration>
            <wait waitType="PREEMPTIVE_OS_WRITEFILEGATHER" waits="56" averageWaitTime="367147" maxWaitTime="61528" />
            <wait waitType="PREEMPTIVE_OS_WAITFORSINGLEOBJECT" waits="232769" averageWaitTime="34878" maxWaitTime="410" />
            <wait waitType="PREEMPTIVE_OS_FLUSHFILEBUFFERS" waits="319" averageWaitTime="17898" maxWaitTime="401" />
            <wait waitType="PREEMPTIVE_OS_CREATEFILE" waits="953" averageWaitTime="17493" maxWaitTime="1605" />
            <wait waitType="PREEMPTIVE_OS_LIBRARYOPS" waits="1" averageWaitTime="7058" maxWaitTime="7058" />
            <wait waitType="PREEMPTIVE_OS_FILEOPS" waits="1229" averageWaitTime="5620" maxWaitTime="375" />
            <wait waitType="PREEMPTIVE_OS_WRITEFILE" waits="104" averageWaitTime="4688" maxWaitTime="1585" />
            <wait waitType="PREEMPTIVE_OS_DOMAINSERVICESOPS" waits="1" averageWaitTime="4091" maxWaitTime="4091" />
            <wait waitType="PREEMPTIVE_OS_COMOPS" waits="1" averageWaitTime="2644" maxWaitTime="2644" />
            <wait waitType="PREEMPTIVE_OS_PIPEOPS" waits="4" averageWaitTime="1712" maxWaitTime="743" />
          </byDuration>
        </preemptive>
      </topWaits>
      <cpuIntensiveRequests>
        <request sessionId="55" requestId="0" command="EXECUTE" taskAddress="0x2f547f868" cpuUtilization="0" cpuTimeMs="15" />
      </cpuIntensiveRequests>
      <pendingTasks />
      <blockingTasks />
    </queryProcessing>

    <ioSubsystem ioLatchTimeouts="0" intervalLongIos="0" totalLongIos="0">
      <longestPendingRequests />
    </ioSubsystem>

    <events>
      <session startTime="2012-03-15T03:16:59.753" droppedEvents="0" largestDroppedEvent="0">
        <RingBufferTarget truncated="0" processingTime="0" totalEventsProcessed="7" eventCount="7" droppedCount="0" memoryUsed="756">
          <event name="scheduler_monitor_system_health_ring_buffer_recorded" package="sqlos" timestamp="2012-03-16T17:24:28.016Z">
            <data name="id">
              <type name="uint32" package="package0" />
              <value>0</value>
            </data>
            <data name="timestamp">
              <type name="uint64" package="package0" />
              <value>0</value>
            </data>
            <data name="process_utilization">
              <type name="uint32" package="package0" />
              <value>9</value>
            </data>
            <data name="system_idle">
              <type name="uint32" package="package0" />
              <value>54</value>
            </data>
            <data name="user_mode_time">
              <type name="uint64" package="package0" />
              <value>221833422</value>
            </data>
            <data name="kernel_mode_time">
              <type name="uint64" package="package0" />
              <value>9672062</value>
            </data>
            <data name="page_faults">
              <type name="uint32" package="package0" />
              <value>15605</value>
            </data>
            <data name="working_set_delta">
              <type name="uint64" package="package0" />
              <value>18446744073707728896</value>
            </data>
            <data name="memory_utilization">
              <type name="uint32" package="package0" />
              <value>99</value>
            </data>
            <data name="call_stack">
              <type name="callstack" package="package0" />
              <value />
            </data>
          </event>
          <event name="scheduler_monitor_system_health_ring_buffer_recorded" package="sqlos" timestamp="2012-03-16T17:25:28.022Z">
            <data name="id">
              <type name="uint32" package="package0" />
              <value>0</value>
            </data>
            <data name="timestamp">
              <type name="uint64" package="package0" />
              <value>0</value>
            </data>
            <data name="process_utilization">
              <type name="uint32" package="package0" />
              <value>0</value>
            </data>
            <data name="system_idle">
              <type name="uint32" package="package0" />
              <value>80</value>
            </data>
            <data name="user_mode_time">
              <type name="uint64" package="package0" />
              <value>4680030</value>
            </data>
            <data name="kernel_mode_time">
              <type name="uint64" package="package0" />
              <value>1092007</value>
            </data>
            <data name="page_faults">
              <type name="uint32" package="package0" />
              <value>303</value>
            </data>
            <data name="working_set_delta">
              <type name="uint64" package="package0" />
              <value>995328</value>
            </data>
            <data name="memory_utilization">
              <type name="uint32" package="package0" />
              <value>99</value>
            </data>
            <data name="call_stack">
              <type name="callstack" package="package0" />
              <value />
            </data>
          </event>
          <event name="scheduler_monitor_system_health_ring_buffer_recorded" package="sqlos" timestamp="2012-03-16T17:26:28.028Z">
            <data name="id">
              <type name="uint32" package="package0" />
              <value>0</value>
            </data>
            <data name="timestamp">
              <type name="uint64" package="package0" />
              <value>0</value>
            </data>
            <data name="process_utilization">
              <type name="uint32" package="package0" />
              <value>0</value>
            </data>
            <data name="system_idle">
              <type name="uint32" package="package0" />
              <value>64</value>
            </data>
            <data name="user_mode_time">
              <type name="uint64" package="package0" />
              <value>7020045</value>
            </data>
            <data name="kernel_mode_time">
              <type name="uint64" package="package0" />
              <value>1716011</value>
            </data>
            <data name="page_faults">
              <type name="uint32" package="package0" />
              <value>132</value>
            </data>
            <data name="working_set_delta">
              <type name="uint64" package="package0" />
              <value>270336</value>
            </data>
            <data name="memory_utilization">
              <type name="uint32" package="package0" />
              <value>99</value>
            </data>
            <data name="call_stack">
              <type name="callstack" package="package0" />
              <value />
            </data>
          </event>
          <event name="scheduler_monitor_system_health_ring_buffer_recorded" package="sqlos" timestamp="2012-03-16T17:27:28.034Z">
            <data name="id">
              <type name="uint32" package="package0" />
              <value>0</value>
            </data>
            <data name="timestamp">
              <type name="uint64" package="package0" />
              <value>0</value>
            </data>
            <data name="process_utilization">
              <type name="uint32" package="package0" />
              <value>0</value>
            </data>
            <data name="system_idle">
              <type name="uint32" package="package0" />
              <value>63</value>
            </data>
            <data name="user_mode_time">
              <type name="uint64" package="package0" />
              <value>6708043</value>
            </data>
            <data name="kernel_mode_time">
              <type name="uint64" package="package0" />
              <value>780005</value>
            </data>
            <data name="page_faults">
              <type name="uint32" package="package0" />
              <value>129</value>
            </data>
            <data name="working_set_delta">
              <type name="uint64" package="package0" />
              <value>262144</value>
            </data>
            <data name="memory_utilization">
              <type name="uint32" package="package0" />
              <value>99</value>
            </data>
            <data name="call_stack">
              <type name="callstack" package="package0" />
              <value />
            </data>
          </event>
          <event name="scheduler_monitor_system_health_ring_buffer_recorded" package="sqlos" timestamp="2012-03-16T17:28:28.040Z">
            <data name="id">
              <type name="uint32" package="package0" />
              <value>0</value>
            </data>
            <data name="timestamp">
              <type name="uint64" package="package0" />
              <value>0</value>
            </data>
            <data name="process_utilization">
              <type name="uint32" package="package0" />
              <value>0</value>
            </data>
            <data name="system_idle">
              <type name="uint32" package="package0" />
              <value>87</value>
            </data>
            <data name="user_mode_time">
              <type name="uint64" package="package0" />
              <value>156001</value>
            </data>
            <data name="kernel_mode_time">
              <type name="uint64" package="package0" />
              <value>0</value>
            </data>
            <data name="page_faults">
              <type name="uint32" package="package0" />
              <value>151</value>
            </data>
            <data name="working_set_delta">
              <type name="uint64" package="package0" />
              <value>348160</value>
            </data>
            <data name="memory_utilization">
              <type name="uint32" package="package0" />
              <value>99</value>
            </data>
            <data name="call_stack">
              <type name="callstack" package="package0" />
              <value />
            </data>
          </event>
          <event name="scheduler_monitor_system_health_ring_buffer_recorded" package="sqlos" timestamp="2012-03-16T17:29:28.046Z">
            <data name="id">
              <type name="uint32" package="package0" />
              <value>0</value>
            </data>
            <data name="timestamp">
              <type name="uint64" package="package0" />
              <value>0</value>
            </data>
            <data name="process_utilization">
              <type name="uint32" package="package0" />
              <value>0</value>
            </data>
            <data name="system_idle">
              <type name="uint32" package="package0" />
              <value>92</value>
            </data>
            <data name="user_mode_time">
              <type name="uint64" package="package0" />
              <value>156001</value>
            </data>
            <data name="kernel_mode_time">
              <type name="uint64" package="package0" />
              <value>0</value>
            </data>
            <data name="page_faults">
              <type name="uint32" package="package0" />
              <value>6827</value>
            </data>
            <data name="working_set_delta">
              <type name="uint64" package="package0" />
              <value>729088</value>
            </data>
            <data name="memory_utilization">
              <type name="uint32" package="package0" />
              <value>99</value>
            </data>
            <data name="call_stack">
              <type name="callstack" package="package0" />
              <value />
            </data>
          </event>
          <event name="scheduler_monitor_system_health_ring_buffer_recorded" package="sqlos" timestamp="2012-03-16T17:30:28.052Z">
            <data name="id">
              <type name="uint32" package="package0" />
              <value>0</value>
            </data>
            <data name="timestamp">
              <type name="uint64" package="package0" />
              <value>0</value>
            </data>
            <data name="process_utilization">
              <type name="uint32" package="package0" />
              <value>0</value>
            </data>
            <data name="system_idle">
              <type name="uint32" package="package0" />
              <value>96</value>
            </data>
            <data name="user_mode_time">
              <type name="uint64" package="package0" />
              <value>156001</value>
            </data>
            <data name="kernel_mode_time">
              <type name="uint64" package="package0" />
              <value>0</value>
            </data>
            <data name="page_faults">
              <type name="uint32" package="package0" />
              <value>3396</value>
            </data>
            <data name="working_set_delta">
              <type name="uint64" package="package0" />
              <value>278528</value>
            </data>
            <data name="memory_utilization">
              <type name="uint32" package="package0" />
              <value>99</value>
            </data>
            <data name="call_stack">
              <type name="callstack" package="package0" />
              <value />
            </data>
          </event>
        </RingBufferTarget>
      </session>
    </events>



    Gokhan Varol

    Friday, March 16, 2012 5:37 PM

Answers

  • You could write individual queries for each bit of XML depending on what information your after, or try something generic to turn all attributes into rows.  Something like this?

    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
    
    SELECT 
    	k.rowId, 
    	x.y.value('local-name(../..)', 'VARCHAR(MAX)') parentElementName,
    	x.y.value('local-name(..)', 'VARCHAR(MAX)') elementName,
    	x.y.value('local-name(.)', 'VARCHAR(MAX)') attributeName,
    	x.y.value('.', 'VARCHAR(MAX)') attributeValue
    
    FROM(SELECT *
    	, cast(data AS XML) AS xml_data
    FROM #SpServerDiagnosticsResult) k
    	CROSS APPLY xml_data.nodes('//@*') x(y)

    • Marked as answer by Gokhan Varol Friday, March 16, 2012 6:34 PM
    Friday, March 16, 2012 6:23 PM

All replies

  • You could write individual queries for each bit of XML depending on what information your after, or try something generic to turn all attributes into rows.  Something like this?

    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
    
    SELECT 
    	k.rowId, 
    	x.y.value('local-name(../..)', 'VARCHAR(MAX)') parentElementName,
    	x.y.value('local-name(..)', 'VARCHAR(MAX)') elementName,
    	x.y.value('local-name(.)', 'VARCHAR(MAX)') attributeName,
    	x.y.value('.', 'VARCHAR(MAX)') attributeValue
    
    FROM(SELECT *
    	, cast(data AS XML) AS xml_data
    FROM #SpServerDiagnosticsResult) k
    	CROSS APPLY xml_data.nodes('//@*') x(y)

    • Marked as answer by Gokhan Varol Friday, March 16, 2012 6:34 PM
    Friday, March 16, 2012 6:23 PM
  • perfect :-) Thank you

    Gokhan Varol

    Friday, March 16, 2012 6:34 PM
  • Gokhan, I've written 2 parsers for sp_server_diagnostics.  A couple of the events are easy to do (the waits), but Events is more difficult because there are multiple event types, each of which has its own different columns. I wrote a dynamic parser that returns data pretty fast, and I think it works pretty darn well. 

    http://thebakingdba.blogspot.com/2012/09/spserverdiagnostics-parsing-part-1-of-2.html

    (every component_type but Events)

    http://thebakingdba.blogspot.com/2012/12/spserverdiageventparser-make.html

    (component_type = Events)

    • Proposed as answer by mbourgon Monday, October 01, 2012 4:11 PM
    • Edited by mbourgon Monday, January 07, 2013 3:01 PM
    Monday, October 01, 2012 4:11 PM