Overview
Wait Statistics is the wait-centric diagnostics module. It combines cumulative SQL Server wait data, active waiting sessions, blocking-chain analysis, historical trend snapshots, and optional query-context correlation to help you identify the dominant source of performance pressure.
What You Can Do
- Inspect dominant wait types and category pressure.
- Review active waiting sessions and blocking chains.
- Compare against a saved baseline or explicit before/after snapshots.
- Configure lightweight alerts, scheduled snapshots, and custom categories.
- Correlate waits with a specific query and plan when query context is available.
Main Screen Areas
- Header card
- Filter bar
- Main content tabs
- Right-side insight panel
Data Sources & Analysis Model
Core Wait Data
- Reads cumulative wait data and active waiting requests from SQL Server DMVs.
- Summarizes total wait, signal wait, resource wait, and active waiting sessions.
- Builds top-wait, category, and current-wait views from the refresh result.
Historical Trend
- Prefers Query Store wait history for longer trend windows.
- Falls back to locally persisted refresh snapshots when Query Store wait history is unavailable.
- Trend source can therefore be query-store, local history, or none.
Blocking & Chain Analysis
- Uses shared blocking logic to explain live contention chains.
- Shows root blockers and blocked sessions with filtered tree rendering.
- Complements cumulative wait counters with live session context.
Query Context Mode
- When a query ID is passed in, the module can switch to query-correlated waits.
- The Wait / Plan panel becomes meaningful only in this mode.
- The module therefore has distinct server-level and query-context behaviors.
Header, Filters & Main Tabs
Header Card
- Optional focus context label
- Refresh
- Set Baseline
- Export
Filter Bar
- Trend Window
- DB Filter
- App Filter
- Min Wait and Apply Filter
- Right-aligned status or progress label
Top Waits
- Shows scrollable wait rows rather than a plain table.
- Each row includes wait type, category, action hint, sparkline, wait time, tasks, percent, and max wait.
- Rows can come from cumulative waits, grouped current waits, or query-correlated waits depending on mode.
Trend & Blocking
- Displays historical trend text output and a blocking-chain tree.
- The trend display supports Daily Summary, Dominant Category, and Category Breakdown views.
- The blocking tree is filtered by DB, App, and Min Wait, with a safety truncation limit for responsiveness.
Automation & Admin Controls
Scheduled Snapshot
- Supports scheduled snapshot and report generation.
- Exposes enabled state, interval, and Save Schedule in the current UI.
- Execution occurs when refresh runs and the saved interval is due.
5s Alert Monitor
- Can enable 5-second monitoring while the view is visible.
- Exposes threshold inputs for total wait, lock, and blocked counts.
- The service supports more alert fields than the current screen exposes.
Custom Category Rules
- Supports regex-based operator-defined wait groups.
- Uses Add Category and Remove Category actions.
- Only enabled rules contribute to current custom-category totals.
Admin Tools
- Admin tools start locked for the session.
- Clear Wait Stats requires explicit arming, confirmation, and exact phrase entry.
- This action resets server wait counters and is audit-logged.
Right-Side Insights
Summary & Categories
- Summary shows Total Wait, Signal Wait, Resource Wait, and Current Waiters.
- Health badge is driven mainly by resource-wait percentage.
- Wait Categories break total wait into CPU, I/O, Lock, Latch, Memory, Network, Buffer, and Other.
Alerts & Signature
- Alerts highlight threshold breaches and live risk signals.
- Signature summarizes the dominant diagnosed pattern with confidence and evidence.
- Examples include CPU pressure, I/O bottleneck, lock contention, memory grant pressure, and balanced wait profile.
Before / After & Actions
- Before / After is separate from baseline comparison.
- Actions include Save Before, Save After, Compare, Custom Category, and Remove Custom.
- The panel reports improved, degraded, or stable once both explicit snapshots exist.
Wait / Plan, Monitoring & Intelligence
- Wait / Plan becomes useful only when the module receives query context.
- Monitoring summarizes configured outbound targets.
- Actionable Intelligence turns waits, alerts, signatures, and trend direction into a short operator playbook.
Behavior Notes, Workflow & Safety
Important Behavior Notes
- DB, App, and Min Wait filters do not fully rewrite cumulative wait-summary totals.
- Set Baseline and Save Before / Save After are different comparison systems.
- Trend source can change between Query Store, local history, and none.
- Some service fields exist but are not fully editable in the current main screen.
Typical Workflow
- Click Refresh and review Summary, Wait Categories, and Alerts.
- Inspect Top Waits to identify dominant waits and suggested action hints.
- Use Trend & Blocking to decide whether the problem is persistent or tied to live blockers.
- Capture a baseline or explicit before/after snapshots when change tracking matters.
- Use Automation for monitoring, thresholds, scheduled snapshots, or multi-server comparison.
- When opened from Query Statistics, use Wait / Plan to connect waits to a specific plan shape.
Operating Principles & Safety
- The module is primarily an analysis and monitoring surface.
- Its only destructive capability is the admin-only Clear Wait Stats command.
- That action is deliberately locked behind extra confirmation because it resets server-wide wait counters.