Build an Incident Timeline from Logs, XEvents, Query Store, DMVs, and Infrastructure Metrics

Learning outcomes

ServiceHub had a 30-second slowdown at 10:17 UTC. By 10:20, every active request is gone. The useful question is no longer “what is waiting right now?” It is “what evidence existed before, during and after 10:17, and which conclusions are facts versus hypotheses?” Incident analysis is temporal data engineering: normalize timestamps, preserve source/reset context, correlate independent layers, and make uncertainty explicit.

1. Prepare a small evidence ledger before the incident

DMV rows are transient, so create a simple timestamped ledger before injecting the problem. This table is not intended as an enterprise monitoring platform; it demonstrates the shape every durable collector needs: source, event time, identity/context and raw fact. Store interpretations separately so later investigators can challenge the hypothesis without rewriting the observation.

USE ServiceHubObservabilityLab;GOIF OBJECT_ID(N'lab22.IncidentEvidence', N'U') IS NULLBEGIN    CREATE TABLE lab22.IncidentEvidence    (        evidence_id bigint IDENTITY PRIMARY KEY,        event_time_utc datetime2(7) NOT NULL,        source varchar(32) NOT NULL,        session_id int NULL,        fact_name nvarchar(128) NOT NULL,        fact_value nvarchar(max) NULL    );END;TRUNCATE TABLE lab22.IncidentEvidence;GOINSERT lab22.IncidentEvidence(event_time_utc, source, fact_name, fact_value)VALUES(    SYSUTCDATETIME(), 'LAB', 'incident_phase',    N'pre-incident baseline; build=' + CONVERT(nvarchar(128), SERVERPROPERTY('ProductVersion')));GO

If the focused XE session from Lesson 3 still exists, restart it before the incident. If it was dropped, recreate it using Lesson 3. Because that session was filtered to the creator’s SPID, a multi-session blocking drill should instead create a second disposable XE session scoped to the lab database or record the blocker/blocked DMV snapshots in the evidence ledger. The mandatory path uses the ledger; the XE source still contributes statement/error events generated in its owning session.

2. Inject and observe a controlled blocking incident

Open Window A, Window B and Window C. Window A owns the transaction. Window B becomes blocked. Window C records the transient state. The delay is intentionally finite, and the final ROLLBACK protects the baseline row.

USE ServiceHubObservabilityLab;GOBEGIN TRAN;UPDATE lab22.WorkOrderSET status = 'HOLD'WHERE work_order_id = 2;WAITFOR DELAY '00:00:20';ROLLBACK;GO

USE ServiceHubObservabilityLab;GOSELECT work_order_id, status, priorityFROM lab22.WorkOrderWHERE work_order_id = 2;GO

USE ServiceHubObservabilityLab;GOINSERT lab22.IncidentEvidence    (event_time_utc, source, session_id, fact_name, fact_value)SELECT    SYSUTCDATETIME(),    'DMV',    r.session_id,    'active_request',    CONCAT(        N'status=', r.status,        N'; wait_type=', COALESCE(r.wait_type,N''),        N'; blocker=', r.blocking_session_id,        N'; wait_resource=', COALESCE(r.wait_resource,N'')    )FROM sys.dm_exec_requests AS rWHERE r.database_id = DB_ID(N'ServiceHubObservabilityLab')  AND r.session_id <> @@SPID;GO

After Window A rolls back, Window B completes. The row in sys.dm_exec_requests vanishes, but your ledger remains. That single fact demonstrates why incident collection must be enabled before the interesting state disappears.

3. Add persisted SQL sources: Query Store, XEvents, error log, Agent history

Query Store provides aggregated query runtime/wait evidence over intervals. Extended Events provides event-level evidence if the right session was running. The SQL error log records engine/service-level events but does not log every query slowdown. Agent history can explain scheduled work only on editions/topologies where Agent exists. A rigorous timeline allows a source to say “no relevant entry” rather than inventing one.

USE ServiceHubObservabilityLab;GOINSERT lab22.IncidentEvidence    (event_time_utc, source, session_id, fact_name, fact_value)SELECT    CONVERT(datetime2(7), i.end_time),    'QUERY_STORE',    NULL,    CONCAT(N'query_id=', p.query_id, N'; wait=', ws.wait_category_desc),    CONCAT(N'total_wait_ms=', SUM(ws.total_query_wait_time_ms),           N'; max_wait_ms=', MAX(ws.max_query_wait_time_ms))FROM sys.query_store_wait_stats AS wsJOIN sys.query_store_plan AS p  ON p.plan_id = ws.plan_idJOIN sys.query_store_runtime_stats_interval AS i  ON i.runtime_stats_interval_id = ws.runtime_stats_interval_idWHERE i.end_time >= DATEADD(hour,-1,SYSUTCDATETIME())GROUP BY i.end_time, p.query_id, ws.wait_category_desc;GO

-- Optional on a disposable local instance with sufficient permission.-- The marker helps demonstrate clock correlation; do not spam production logs.RAISERROR(N'ServiceHub Chapter22 incident marker', 10, 1) WITH LOG;GOEXEC sys.xp_readerrorlog 0, 1, N'ServiceHub Chapter22 incident marker';GO

USE msdb;GOSELECT TOP (30)    j.name AS job_name,    h.step_id,    h.step_name,    h.run_status,    msdb.dbo.agent_datetime(h.run_date,h.run_time) AS run_started_local,    h.run_duration,    h.messageFROM dbo.sysjobhistory AS hJOIN dbo.sysjobs AS j ON j.job_id = h.job_idWHERE h.run_date >= CONVERT(int, CONVERT(char(8), GETDATE(), 112))ORDER BY h.instance_id DESC;GO

Agent timestamps are commonly represented in local server time while XE and several engine timestamps are UTC or offset-aware. Normalize them before ordering events. Record the original timezone/offset so investigators can reconstruct the conversion. Clock drift across hosts matters even more in distributed systems.

4. Construct the timeline and separate fact from hypothesis

USE ServiceHubObservabilityLab;GOSELECT    evidence_id,    event_time_utc,    source,    session_id,    fact_name,    fact_valueFROM lab22.IncidentEvidenceORDER BY event_time_utc, evidence_id;GO

Now write an incident note with four columns: time, fact, hypothesis, and action/validation. Example: fact—“Window B waited on LCK_M_... and reported Window A’s session id as blocker.” Hypothesis—“an application transaction remained open too long.” Action—“inspect Window A’s transaction age/query/application context; rollback only the disposable lab transaction.” Validation—“Window B completed immediately after rollback; row value returned to baseline.”

Earliest causal evidence is not always the earliest timestamp in the dataset. A host CPU spike could precede a lock wait while being unrelated. Prefer evidence that explains the dependency chain and survives falsification. If the remediation fixes the symptom but your causal story predicts nothing testable, the incident is not fully understood.

5. Close the loop: validation, prevention, and cleanup

For this lab, rollback of Window A is the remediation. Verify the row, confirm no active lab blockers remain, review Query Store/XE evidence, and record which layers were missing. In production, prevention could mean an application transaction-scope fix, timeout/retry policy, index/query change, connection-pool correction, workload isolation, or simply better telemetry. Do not choose the prevention action solely from the wait name.

USE master;GOIF EXISTS (SELECT 1 FROM sys.server_event_sessions WHERE name=N'ServiceHub_Ch22_Focused')BEGIN    IF EXISTS (SELECT 1 FROM sys.dm_xe_sessions WHERE name=N'ServiceHub_Ch22_Focused')        ALTER EVENT SESSION ServiceHub_Ch22_Focused ON SERVER STATE=STOP;    DROP EVENT SESSION ServiceHub_Ch22_Focused ON SERVER;END;GOIF EXISTS (SELECT 1 FROM sys.server_event_sessions WHERE name=N'ServiceHub_Ch22_FileExample')BEGIN    IF EXISTS (SELECT 1 FROM sys.dm_xe_sessions WHERE name=N'ServiceHub_Ch22_FileExample')        ALTER EVENT SESSION ServiceHub_Ch22_FileExample ON SERVER STATE=STOP;    DROP EVENT SESSION ServiceHub_Ch22_FileExample ON SERVER;END;GOIF DB_ID(N'ServiceHubObservabilityLab') IS NOT NULLBEGIN    ALTER DATABASE ServiceHubObservabilityLab SET SINGLE_USER WITH ROLLBACK IMMEDIATE;    DROP DATABASE ServiceHubObservabilityLab;END;GO-- This cleanup does not delete arbitrary .xel files or alter global wait counters.

Chapter 23 moves from observing conventional relational workloads to SQL Server 2025’s vector/AI-oriented capabilities. Carry the observability discipline forward: new data types and search operators still need measurable latency, resource, security and correctness evidence.

Authoritative references

• Use the system_health Extended Events session
• Monitor performance by using Query Store
• sys.query_store_wait_stats
• sys.dm_exec_requests
• Extended Events targets
• SQL Server Agent history