Skip to content

CLI Monitoring

radix29 edited this page Oct 31, 2026 · 1 revision

← goSSMS Home · ← CLI

CLI — Monitoring and Extended Events

The monitor group reads what the server is doing right now — sessions, blocking chains, waits, the costliest cached statements, Query Store's history, recorded deadlocks — and ends a session; xevent lists event sessions and captures events for a while. These are the DMVs behind SSMS's Activity Monitor and its performance reports, through gosmo.

Two ground rules:

  • Permissions. Every monitor command but querystore — and plan cached and xevent sessions — asks for VIEW SERVER STATE (2022+: VIEW SERVER PERFORMANCE STATE) before it reads anything, and answers PERMISSION_DENIED (exit 9) without it: the server would reply with the login's own session only, an empty list that misreads as "nothing is running". querystore needs VIEW DATABASE STATE. agent context lists all three, plus ALTER ANY CONNECTION for kill.
  • No helper procedures. sessions and blocking come from gosmo's ActiveSessions, never from sp_WhoIsActive (which the TUI's tabs install) — a CLI read writes nothing, and its columns do not depend on what a server has installed.

monitor sessions — the live view

gossms monitor sessions -p prod-ro
gossms monitor sessions --active --session-database Sales -p prod-ro -o json
gossms monitor sessions --login app_user --program "Microsoft SQL Server Management Studio" -p dev

One row per session, by spid (the CLI's own left out; --include-system adds the system ones): spid, login, host, program, database, status, command, open_tran, cpu_ms, reads, writes, logical_reads, elapsed_ms, blocked_by, wait_type, wait_ms, wait_resource, text.

  • command and the wait_* are the running request's, NULL for an idle session. cpu_ms/reads/writes/logical_reads are the session's totals since login, the running request's so far included (DMVs move them only when a request ends — alone they would show 0 for a query blocked for minutes). elapsed_ms is how long the running request has run.
  • text is the running request's batch — or, for an idle session, the batch its connection sent last: what opened that idle transaction. First 512 characters, in the server's auto-parameterized form when it was parameterized.
  • blocked_by counts a parallel request's blocked worker — and the wait_* columns are then that worker's.
  • Filters: --active, --session-database, --login, --program (equal, case-insensitive). -d is the connection's database, not a filter.

The idle-with-an-open-transaction blocker lives here: open_tran > 0 with status='sleeping', and text names the batch that opened it.

monitor blocking — the chains, as a tree

gossms monitor blocking -p prod-ro
gossms monitor blocking -p prod-ro -o json

One sessions --include-system read, built into chains in gossms: a head blocks and is not blocked; each chain prints the head, then the sessions it blocks, depth first, by spid at each level; chains ordered by head spid. Columns: head, depth (0 the head), cycle, spid, blocked_by, wait_type, wait_ms, wait_resource, login, host, program, database, open_tran, text. No blocking is an empty set, exit 0.

  • A session blocked by one the read does not show (ended in between; -2 an orphaned distributed transaction; -4 a latch owner) heads its own chain, blocked_by still naming the phantom.
  • A cycle — a deadlock the monitor has not resolved yet — is reported once, from its lowest spid, cycle 1 on its members.
  • The usual head is an idle session with an open transaction: read its text, and end it with monitor kill (below, admin) when the business says so.

monitor waits — what the server waits on

gossms monitor waits --top 10 -p prod-ro
gossms monitor waits --interval 10 -p prod-ro -o json

sys.dm_os_wait_stats is cumulative since the instance started (or the last DBCC SQLPERF(…,'WAIT_STATS','CLEAR'), which the server does not record): by default since is the start and elapsedMs the uptime. --interval <sec> (1–300) reads twice and reports the difference — what the server waits on now — without clearing anyone's baseline (there is no --reset-baseline on purpose: a CLEAR resets every other tool's view of the server). Ctrl+C during the wait is exit 130, no second read. A wait type new in the second read counts from zero; one whose totals fell (cleared between reads by someone else) is left out with a warning.

  • Set waits: wait_type, category, waiting_tasks, wait_ms, signal_ms (time runnable, waiting for a CPU; clamped to wait_ms), resource_ms (= wait − signal), pct (share of all wait time counted, not only the rows shown), avg_ms. --top default 20, 0 all.
  • Set categories: the eight categories (CPU, Disk IO, Locking, Latches, Memory, Log, Network, Other) summed over every type counted — this is where the time goes.
  • Idle and background waits (sleeps, queue waits, Extended Events, Query Store, Service Broker, idle HADR — the TUI's Waits chart's list) are left out unless --all, and so are types with no time and no task.

monitor top-queries — the cached-plan ranking

gossms monitor top-queries -p prod-ro
gossms monitor top-queries --by reads --avg --top 10 -p prod-ro -o json
gossms monitor top-queries --plan-database Sales --by duration -p prod-ro

sys.dm_exec_query_stats: one row per cached statement, its totals since its plan was cached (cached_at) — a plan leaves the cache at eviction, recompile or restart, so this is no fixed window; Query Store is the durable history (next section). Ranked by --by cpu|reads|writes|duration|executions (total) or --avg (per execution; not with executions — exit 2). --top default 20, at least 1.

Set queries, highest first: n, database (NULL when the plan names none the login can see), executions, cpu_ms/avg_cpu_ms, duration_ms/ avg_duration_ms, logical_reads/avg_logical_reads, logical_writes/avg_logical_writes, last_execution, cached_at, query_hash, plan_handle, statement_start, statement_end — the address to hand to plan cached — and text (the statement cut from its batch; NULL when the batch left the cache or is encrypted). Times are ms with three decimals (the DMV keeps µs); handles are 0x upper-case hex. --plan-database filters by the database the plan was compiled in (unknown name → exit 8; costs more on a large cache).

monitor querystore — the durable history

gossms monitor querystore -d Sales -p prod-ro
gossms monitor querystore -d Sales --regressed --since 2026-10-29 -p prod-ro -o json
gossms monitor querystore -d Sales --report waits --wait-category Lock --top 10 -p prod-ro
gossms monitor querystore -d Sales --report variation --metric cpu --min-exec 10 -p prod-ro

Query Store's history of the connection's database (-d): SSMS's reports, from sys.query_store_*.

--report top (default) Rank by the metric
--report regressed (--regressed) How much each query grew from the first half of the window to the second (the TUI's Regressed Queries); a query with no first-half executions is new, not regressed — left out
--report variation Coefficient of variation (stdev / avg) — the flaky plans
--report forced Queries with a forced plan (forced_plan_id)
--report waits (2017+) Set categories (wait time per category) then the queries that waited most
  • The window: --since–--until, default the 24 hours to the server's now. A time with no zone is the server's local wall clock (read in its own offset); with Z or an offset, that instant. Every stored interval overlapping the window counts whole; an empty window is exit 2.
  • --metric (duration default; cpu logical-reads logical-writes physical-reads clr dop memory rows, plus 2017+ log-memory tempdb — exit 11 before 2017); --stat avg|max|min|stdev|total (default total for top and waits, avg otherwise). waits refuses --metric and --min-exec, and any --stat but avg/total unless --wait-category names one (CPU, Lock, Buffer IO, … as the server spells it).
  • Set queries: n, query_id, object (the module; NULL for ad hoc), executions, plans, forced_plan_id, last_execution, value, text — plus baseline_value, baseline_executions, regression, regression_pct (regressed) or variation. The record answers report, from, to, baselineFrom/baselineTo, metric, statistic, unit (ms — Query Store keeps µs —, 8 KB pages, bytes, count). --min-exec, --min-regression-pct (whole percentage) cut the list.
  • Query Store OFF or in ERROR, or master/tempdb: exit 8 with the db set <db> --option QUERY_STORE=ON hint; READ_ONLY reads. Plan forcing is not offered (read-only group).

monitor deadlocks — what system_health caught

gossms monitor deadlocks -p prod-ro
gossms monitor deadlocks --since 2026-10-27T00:00:00Z -p prod-ro
gossms monitor deadlocks --top 1 --deadlock-format xml --deadlock-file ./deadlocks -p prod-ro

Reads the xml_deadlock_report events of the system_health session — its event_file target (the server keeping only those events), else its ring_buffer. Newest first, --top (default 20); deadlocksInWindow counts before the cut.

  • --since/--until are UTC (the events record UTC): a time with no zone is UTC, one with Z/offset converted; --until exclusive; default the 24 hours to the server's now. (Unlike server log and querystore, which are server-local — each matches where its data comes from.)
  • system_health keeps what its files hold (2019+: up to 10 × 100 MB, shared with every other event), so an old deadlock may be gone — and the server parses every event in the files to find them: on a busy server this read takes a minute or more. system_health missing, stopped, or with no readable target is exit 8, hint naming the fix.
  • Set deadlocks: n, time_utc, victims (no victim for an intra-query parallelism deadlock, which kills nobody), processes, databases, objects. Set processes (summary mode): n, spid, ecid (a parallel plan's workers share a spid), victim, login, host, program, database, isolation, lock_mode, wait_resource, transaction, statement (the innermost frame, or the batch).
  • --deadlock-format xml answers each graph whole as deadlock1… fields instead of the processes set. --deadlock-file <dir> writes each as deadlock_<yyyymmdd_hhmmss_mmm>.xdl (UTC) — SSMS opens the graph; every name is checked first, and an existing file without --force is exit 2 with no file written. An undecodable report is a warning, left out.

monitor kill — end a session

An admin command; do it with the business's approval — it rolls back the session's open transaction.

gossms monitor kill 87 --confirm 87 -p dev --allow admin --dry-run
gossms monitor kill 87 --confirm 87 -p dev --allow admin

KILL <spid> through gosmo, so the dry run prints it. Checked in order, each before anything is sent:

Check Result
spid outside 1–32767 exit 2
--confirm not the spid exactly as given (087 is not 87) BLOCKED, exit 7, before connecting
No --allow admin (ceiling applies) BLOCKED, exit 7
No VIEW SERVER STATE exit 9 — without it every other session looks ended
The session gone / a system session / one of this process's own connections (client pid and host, not just @@SPID — the KILL could travel on another pooled connection) exit 8 / exit 7 / exit 2
The server's refusals 6106 ended meanwhile → 8; 6107 system → 7; 6104 already being killed → 2; 6102 lacks ALTER ANY CONNECTION → 9

The target's monitor sessions row is answered as set session in every outcome, dry run included. After a real KILL the session is read again: killed, and ended — false while the rollback is still running (it can take as long as the work it undoes), a warning naming KILL <spid> WITH STATUSONLY, not waited for.

xevent sessions

gossms xevent sessions -p prod-ro
gossms xevent sessions -p dev -o json

One row per event session (sys.server_event_sessions × sys.dm_xe_sessions): name, scope (server; database on Azure SQL Database, which needs no VIEW SERVER STATE there), running, startup, started (server local), dropped_events — events a running session could not buffer since it started (NULL for a stopped one: it is the "widen the buffer" signal), targets, events, file (the event_file target's .xel). Engine-private sessions with no definition (hkenginexesession, sp_server_diagnostics session) are not listed. Needs VIEW SERVER STATE (exit 9).

xevent capture — a profiler for a minute

An admin command: it creates a server object, and one left behind by a killed process is a lasting change.

gossms xevent capture --template batches --duration 60 --min-duration-ms 500 -p dev --allow admin
gossms xevent capture --template errors --session-database Sales -p dev --allow admin -o jsonl
gossms xevent capture --template deadlocks --duration 300 -p dev --allow admin --dry-run

One short-lived session per run, gossms_cli_<UTC time>_<4 hex>: the template's events, a ring_buffer target holding the newest --max-events (1–100000, default 1000; ~4 MB cap), MAX_DISPATCH_LATENCY = 1 s. Create → start → wait --duration (1–600 s, default 30) + 1 s → read the buffer → stop and drop, on every exit path — failed step, a CREATE that errored but left the session (looked up by name), Ctrl+C (exit 130 after the drop), cleanup under its own 15 s context. A failed drop is a warning naming the session and the DROP EVENT SESSION to run; sessionDropped says which; and xevent sessions lists any gossms_cli_ session a process killed with SIGKILL left behind.

Template Collects
standard Logins, logouts, attentions, every completed batch and RPC
tsql Every batch and RPC as it starts, with logins/logouts
batches Completed batches and RPCs, attentions, errors (Query Batch Tracking)
statements Batches and RPCs with every statement and module they ran (Query Detail Tracking)
waits Every finished wait, with the batches/RPCs around it (the CLI adds session_id > 0: background waits stay out)
connections Logins, logouts, failed logins
deadlocks Every deadlock graph
errors Every error of severity 11+ (gossms' own template)
  • Server-side filters, ANDed into every event's predicate: client_app_name <> 'goSSMS - CLI' always (your own noise), plus --session-database, --login, --program (exact names) and --min-duration-ms (on completed batch/RPC/statement/module events only).
  • Set events, oldest first: time_utc, event, session_id, database, login, program, host, duration_ms/cpu_ms (µs → ms, three decimals; a wait_info's duration is its wait), logical_reads, writes, row_count, result, error, severity, text (the batch, statement, message, deadlock graph, or wait type). Under -o jsonl each row adds data — {"fields":…, "actions":…}, a real JSON column with every field and action of the event.
  • eventsSeen vs eventsKept, or a truncated buffer, is a warning: shorten --duration or filter tighter.
  • Needs ALTER ANY EVENT SESSION (2022: the four narrow rights CREATE / ALTER…ENABLE / ALTER…DISABLE / DROP ANY EVENT SESSION together) and VIEW SERVER STATE — a known lack is exit 9 with nothing created. Azure SQL Database: exit 11 (no server-scoped sessions). --dry-run prints the CREATE, START, STOP and DROP as tsql.

Recipes

# "someone is blocking everyone" triage
gossms monitor blocking -p prod                        # the head, its tree, a cycle?
gossms monitor sessions -p prod --include-system -o json |
  jq -r '.resultSets[0].rows[] | select(.open_tran>0 and .status=="sleeping") | [.spid,.login,.text] | @tsv'
gossms monitor kill 87 --confirm 87 -p prod --allow admin --dry-run   # show what would die
gossms monitor kill 87 --confirm 87 -p prod --allow admin

# is it CPU, disk or locks? measure the next 30 seconds
gossms monitor waits --interval 30 -p prod -o json |
  jq -r '.resultSets[] | select(.name=="categories") | .rows[] | [.category,.wait_ms,.pct] | @tsv'
gossms monitor top-queries --by cpu -p prod            # CPU    -> rank by cpu
gossms monitor top-queries --by reads -p prod          # Disk IO -> rank by reads
gossms monitor blocking -p prod                        # Locking -> the chain

# "it was slow at 3 am" — the cache is gone; Query Store is not
gossms monitor querystore -d Sales --since 2026-10-30T00:00:00Z --until 2026-10-30T05:00:00Z --top 10 -p prod

# find tonight's deadlocks, as graphs for SSMS
gossms monitor deadlocks -p prod --since 2026-10-31T00:00:00Z --deadlock-file ./dl

# who is logging in, and what did the slowest batches do?
gossms xevent capture --template connections --duration 60 -p prod --allow admin
gossms xevent capture --template batches --duration 120 --min-duration-ms 1000 \
  -p prod --allow admin -o jsonl > batches.jsonl

Clone this wiki locally