Performance Health / Troubleshooting

Status: Reviewed (deep-dive v1)
Stack: PowerShell (schedule off-peak collectors) → SQL DMV snapshots/procs → Blazor Server UI
Depends on: SQL-01 Inventory; use with SQL-03 (stats/indexes), SQL-04 (jobs), SQL-06 (disk pressure)
Goal: Ops-level visibility into waits, blocking, and expensive queries — not a full tuning engagement every time.

Why this next

Capacity and jobs tell you the plumbing. Performance health tells you whether the instance is hurting right now and what to look at first.

Keep this ops triage, not deep plan analysis essays.

What “good” looks like

Periodic snapshots of waits, blocking, top CPU/IO/duration queries
Blazor cards: hot waits, current blockers, top SQL (fingerprinted)
Clear “first 15 minutes” runbook for a slowdown page
Retention short enough to stay cheap (days–weeks for detail)
No secrets; truncate/normalize query text for display

Ops signals (start here)

Signal	Why it matters	First check
Blocking chain	Users stuck	Head blocker SPID/query
High wait (PAGEIOLATCH, WRITELOG, LCK_*, CXPACKET/CXCONSUMER, SOS_SCHEDULER_YIELD)	Points at IO / log / locking / CPU / parallelism	Correlate with disk (SQL-06) and jobs (SQL-04)
Top CPU / duration / reads	Who’s burning the box	Same query hash repeating?
Thread/worker pressure	Scheduler starvation	Concurrent jobs + parallelism
Plan cache bloat / single-use plans (optional later)	Memory waste	Ad-hoc workload

Checklist — collect (SQL first; PS schedules)

Run light, on a schedule (e.g. every 1–5 min for “now”; hourly rollups). Prefer DMVs; avoid traces unless XE already exists.

[ ] Waits: sys.dm_os_wait_stats delta since last sample (reset-aware)
[ ] Blocking: sys.dm_exec_requests + sys.dm_exec_sessions (+ optional dm_os_waiting_tasks)
[ ] Top queries: sys.dm_exec_query_stats cross apply plan/text — by CPU, duration, logical reads (top N)
[ ] Optional: sys.dm_os_performance_counters (Batch Requests, Page life expectancy — interpret carefully)
[ ] Emit instance key + CollectedAt; store deltas for waits, not only cumulative

PS role: schedule Invoke-Sqlcmd / collector; optionally skip collect if host CPU already pegged by maintenance window flag.

Do not collect full plans for every query in v1 — text + query_hash + stats is enough for the board.

Checklist — store (SQL + stored procedures)

Table	Purpose
`dbo.PerfWaitSample`	Wait deltas by wait_type
`dbo.PerfBlockingEvent`	Observed blocker/victim pairs (sampled)
`dbo.PerfTopQuery`	Top N rows per sample (hash, stats, truncated text)
`dbo.PerfSampleRun`	Run audit

Procs:

[ ] usp_Perf_ApplyWaitDelta
[ ] usp_Perf_ApplyBlockingSample
[ ] usp_Perf_ApplyTopQueries
[ ] usp_Perf_GetHotWaits — Blazor
[ ] usp_Perf_GetBlockingNow — current/recent chains
[ ] usp_Perf_GetTopQueries — filter by metric

Retention: detail 7–14 days; rollups optional later.

Checklist — present (Blazor Server)

[ ] Hot waits (last interval + trend sparkline if cheap)
[ ] Blocking now (tree or flat: blocker → victims)
[ ] Top queries tabs: CPU / duration / reads
[ ] Instance picker; time range (last 15/60 min)
[ ] Link to SQL-03 / SQL-06 / SQL-04 runbooks from wait type hints
[ ] Actions v1: copy query_hash / open runbook — no kill from UI (change control)

First 15 minutes runbook (slowdown page)

Confirm scope: one DB, one instance, or estate?
Blazor → Blocking now — if yes, identify head blocker; don’t kill without approval
Hot waits — pick dominant family:
- IO latch → SQL-06 disk / SAN / Autogrowth storms
- WRITELOG → log disk / sync commit / VLF
- LCK_* → long tran / blocking job / missing index smell
- CPU / SOS_SCHEDULER_YIELD → top CPU queries; parallelism; runaway job (SQL-04)
Top queries — same hash flooding? sudden new hash after deploy?
Check Agent: index rebuild / CHECKDB / backup overlap (SQL-03/04)
Stats: big load without update? (SQL-03)
Capture evidence (screens/hashes/times) before changing anything
Fix under change control; re-check board after next samples

Common failure patterns

Symptom	Likely cause
Everything slow, PAGEIOLATCH	Storage / memory pressure / cold cache after restart
Periodic stalls	Agent job window; autogrowth; backup IO
One app stuck, others fine	Blocking or app-side connection pileup
CXPACKET high	Often symptom; check skew + Cost Threshold / MAXDOP policy with Rick
Board empty	Collector rights; Agent stopped; wrong instance

Guardrails

Prefer query_hash aggregation over raw ad-hoc text walls
Truncate displayed SQL (e.g. first 200–400 chars)
Never store passwords from plans
Killing sessions: DBA only, after confirming not a critical long job

Permissions notes

Collector: VIEW SERVER STATE typical for DMVs
Blazor: read procs only
PS: schedule + invoke only

Done definition for this topic

[ ] Wait deltas + top queries sampling on at least one instance
[ ] Blazor shows hot waits / blocking / top SQL
[ ] One triage drill completed using this runbook
[ ] List item Notes link this doc

Next candidates (when Rick directs)

Patching & build currency · HA/DR awareness · Alerting & runbooks

SQL Dude — SQL-07 Performance Health v1