Shredding sp_server_diagnostics Output Into Health Rows

Open the health XML and turn its useful attributes into readable rows. Shredding sp_server_diagnostics turns component state and selected attributes into rows you can read during a focused server investigation.

Capture One Result Set Before Shredding sp_server_diagnostics

sp_server_diagnostics returns component rows with a common capture timestamp, a component name, a state description, and detailed data. Use its default nonrepeating call for INSERT EXEC. A repeating diagnostic call is not the bounded capture demonstrated here.

I keep the state description beside the extracted details. A single attractive metric is not the whole component assessment. The official capture pattern accepts data into an XML column. That gives nodes and value methods a typed input. Run with the required server visibility permission for your version. This procedure exposes instance diagnostics, not just facts about the current database. The resulting table is a local test capture. It does not become a durable collector merely because the word diagnostics appears in its name.

CREATE TABLE #Health
(
    create_time datetime, component_type sysname, component_name sysname,
    [state] int, state_desc sysname, [data] xml
);
INSERT #Health EXEC sys.sp_server_diagnostics;
SELECT create_time, component_name, state_desc FROM #Health;

Read Selected System, Resource, and I/O Attributes

Use the component-specific XML paths and preserve NULL when an attribute is absent. A missing attribute can reflect a version or shape difference. Do not convert that absence into zero, because zero describes a value that was actually reported.

I inspect the raw XML when an extraction unexpectedly becomes empty. The query uses nodes to obtain each relevant root and value to read selected attributes. It keeps state_desc in every row. The XML methods need QUOTED_IDENTIFIER ON. SSMS sets it by default, but sqlcmd does not, so the query sets it first. The resource example reads the last notification rather than asserting a universal memory-size interpretation. System CPU utilization and I/O timeout attributes give leads that need workload context. Which component changed during the complaint? Compare captures close to that period. The snapshot will not confess what happened before you asked for it.

SET QUOTED_IDENTIFIER ON;
SELECT h.component_name,h.state_desc,
       n.x.value('(@sqlCpuUtilization)[1]','int') AS SqlCpuUtilization
FROM #Health AS h CROSS APPLY h.data.nodes('/system') AS n(x)
WHERE h.component_name='system';
SELECT h.component_name,h.state_desc,
       n.x.value('(@lastNotification)[1]','varchar(100)') AS LastNotification
FROM #Health AS h CROSS APPLY h.data.nodes('/resource') AS n(x)
WHERE h.component_name='resource';
SELECT h.component_name,h.state_desc,
       n.x.value('(@ioLatchTimeouts)[1]','bigint') AS IoLatchTimeouts
FROM #Health AS h CROSS APPLY h.data.nodes('/ioSubsystem') AS n(x)
WHERE h.component_name='io_subsystem';

Turn Query-Processing Wait Nodes Into Rows

The query-processing component uses queryProcessing in its XML root, while component_name uses query_processing in the rowset. Keep that naming distinction. The nodes call expands the wait elements into one row per reported wait, then extracts their attributes.

Shredding sp_server_diagnostics is useful for a quick triage view. It is not equivalent to reading all wait statistics or all blocked requests. The top waits are a selected diagnostic representation. State_desc still matters, and raw XML preserves other details you have not selected. Add paths only after checking the actual payload on your version. XML structure is lightly documented and can change. A parser that returns no rows after an update needs investigation before it can report that the server has no waits.

SELECT h.create_time,h.state_desc,
       w.x.value('(@waitType)[1]','varchar(100)') AS WaitType,
       w.x.value('(@waits)[1]','bigint') AS WaitCount,
       w.x.value('(@averageWaitTime)[1]','bigint') AS AverageWaitTime
FROM #Health AS h
CROSS APPLY h.data.nodes(
'/queryProcessing/topWaits/nonPreemptive/byDuration/wait') AS w(x)
WHERE h.component_name='query_processing';

Keep the Snapshot in Its Proper Role

The system_health session and related engine health mechanisms provide diagnostic history that complements this on-demand call. Use their retained evidence when the problem ended before your capture. Do not build a full monitoring system around undocumented assumptions about one XML layout.

Store the raw payload with the parsed fields if you retain captures. Version the parser and validate expected paths after server changes. Separate unknown state from a clean state. The events component reports a different kind of information and should not be forced into the same health interpretation as every other component. Use the snapshot to choose the next specific inspection, such as memory pressure, blocking, or I/O. Preserve the distinction between a quick lead and a demonstrated root cause.

Define the Health Capture Record

Decide what one saved row means. Each call returned five rows in my test, one per component, all sharing a single create_time. Keep that timestamp as the capture key, so the rows of one call always travel together.

Separate unknown from clean. In my capture, four components reported clean, and the events component reported unknown with state 0. Unknown there describes a component that carries event data rather than a health verdict. Treat it as its own category in any summary.

Watch the size of what you keep. The events component carried by far the largest XML payload in my capture, while io_subsystem carried the smallest. If you store captures, decide whether the events payload is needed, and protect the table, since event data can include query details.

Test Shredding sp_server_diagnostics on Actual Payloads

List the attributes before writing a path. A query over /system/@* returned the attribute names on my build, including sqlCpuUtilization, systemCpuUtilization, pageFaults, and nonYieldingTasksReported. Build the extraction from that list rather than from memory, and rerun it after each update.

Test the parser against a name that is not there. Asking the system node for an attribute that does not exist returned NULL in my test, with no error. A silent NULL therefore deserves a check against the raw XML before anyone reads it as zero.

Check the root names too. The rowset says query_processing and io_subsystem, while the XML roots are queryProcessing and ioSubsystem. A path written with the rowset spelling returns no rows at all, which looks exactly like a server with nothing to report.

Keep Shredding sp_server_diagnostics Output With Its Limits

Save captures only when a question needs history. Insert the component rows with their shared create_time into a table that keeps the raw XML column. The system_health session already records these component results in the background, so a manual capture belongs to a specific investigation window.

Read the wait rows with their meaning in mind. In my capture on an idle test server, the top waits by duration were mostly background waits, such as dispatcher and filestream I/O waits. Filter known background wait types before a busy-looking list sends you after the wrong problem.

Keep a raw XML sample from the actual server build with the parser’s output. When shredding sp_server_diagnostics returns fewer rows after an update, compare the payload shape before declaring cleaner health. Preserve component names and state descriptions with every extracted attribute. The next inspection should follow the specific component evidence. A snapshot that guides a focused check is useful without pretending to replace a durable health history.

Related reading on this blog: Where is Rows Affected in Output? and Deeper Diagnostics and Actionable Dashboards.

A diagnostic XML snapshot is not a monitoring history, it is a structured look at the current evidence.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.