Skip to content

CLI Query and Plans

radix29 edited this page Oct 31, 2026 · 1 revision

← goSSMS Home · ← CLI

CLI — Query and plans

The query group runs, parses and classifies T-SQL; the plan group answers execution plans — estimated without running, actual while running, and a plan already in the server's cache. Together they carry the same model as a query window in the TUI: one session, GO batches, results — plus things a query window does not give you: a classifier that refuses writes by default, a rollback wrapper that measures a change without making it, and a --dry-run on every step.

T-SQL input

Every query and plan command takes its script from exactly one of:

Option Alias
--sql <text> -Q Inline T-SQL — fine for one line
--file <path> -i A file; - reads stdin. UTF-8 (BOM dropped) or UTF-16 with a BOM
--sql-base64 <b64> Base64 of the UTF-8 text, padded or not

None, or more than one, is USAGE_ERROR; so are an unreadable file, text that is not valid UTF-8, and a script over 64 MB. --file - with --password-stdin is refused: both want stdin.

Prefer --file - or --sql-base64 to --sql for anything but one line — no shell quoting can damage them:

gossms query run -p dev --file - <<'SQL'
SELECT name, create_date FROM sys.databases WHERE database_id > 4;
SQL

base64 -w0 fix.sql | xargs -I{} gossms query classify --sql-base64 {}

query run — run T-SQL

gossms query run -p dev --file report.sql
gossms query run -p dev --file - -o json
gossms query run -p dev -Q "SELECT DB_NAME(), @@VERSION;"
gossms query run -p dev --file big.sql --out-file rows.csv
gossms query run -p dev --file by-id.sql --param id=42:int --param since=2026-01-01:date

The script runs on one session, as a query window does: temp tables, SET options and USE carry across its GO batches; nothing carries across calls. The order is fixed: read and classify the script → check it against --allow and the profile's ceiling (a refusal is exit 7 before anything connects) → connect → run → answer. The script's result sets come back as result sets, PRINTs as messages, and the header carries ROWS_AFFECTED.

Option
--allow <read|write|ddl|admin> Highest class the script may reach (default read); the ceiling caps it
--dry-run Classify and check without connecting: query classify's answer with allowedClass; exit 7 when a run would be refused
--transaction <auto|commit|rollback> Wrap the whole script in one transaction (below)
--max-rows-affected <n> Commit only if at most n rows change (below)
--continue-on-error Send every batch even after one fails (default stops after the first failing batch, warning naming how many were sent; exit 10 PARTIAL when some succeeded)
--timeout <sec> How long the script may run (default 60, 0 none). Past it: TIMEOUT (exit 6). --connect-timeout bounds the connect
--statistics <io,time> SET STATISTICS IO/TIME ON; their output is in messages
--param <name>=<value>:<type> Typed sp_executesql parameters (below); repeatable
  • Errors. Severity 11+ goes to errors with its number, severity, state, script line and procedure — SQL_ERROR (exit 5), or PERMISSION_DENIED (exit 9) when the server says the login lacks a permission. A missing object stays SQL_ERROR: NOT_FOUND is for what the command line names. A connection lost mid-run is CONNECTION_FAILED (exit 3).
  • Rows affected sums the counts of statements that returned no rows (INSERT/UPDATE/DELETE/MERGE/SELECT INTO, triggers' statements). A SELECT's count is not a change — and neither, the protocol cannot tell them apart, is a DML statement's with an OUTPUT clause. SET NOCOUNT ON sends no counts. Output limits never stop the run: every row's script runs whatever --max-rows keeps, so a later UPDATE still runs.
  • Ctrl+C cancels the run and closes the session; the server rolls back an open transaction. Exit 130.

Parameters — values, never string-concatenation

--param name=value:type (repeatable) runs the script through sp_executesql with @name declared as type, so a value is never pasted into the T-SQL:

gossms query run -p dev --file by-id.sql \
  --param id=42:int --param "who=O'Brien:nvarchar(50)"
# sends: EXEC sp_executesql N'<by-id.sql>', N'@id int, @who nvarchar(50)',
#        @id = 42, @who = N'O''Brien';
  • The name runs to the first =, the type starts after the last : — a value may contain both (at=12:30:00:time(0)) and commas. A leading @ is optional.
  • Names and types are matched, never escaped — they are T-SQL in the declaration list. A name is a regular identifier; a type is a system type from a closed list: bigint int smallint tinyint bit money smallmoney real date datetime smalldatetime uniqueidentifier xml sql_variant sysname; decimal(p[,s]), numeric(p[,s]); float(n), time(n), datetime2(n), datetimeoffset(n); char nchar varchar nvarchar binary varbinary with (n) or (max). Anything else — a user type, OUTPUT, a second declaration — is USAGE_ERROR.
  • The value is sent as a Unicode string literal and the server converts it to the declared type; a value that does not convert is SQL_ERROR (Msg 8114). binary/varbinary take 0x… hex. There is no NULL value — write NULL in the script.
  • The script must be one batch, sent once (no GO): inside it, as in any dynamic SQL, USE/SET last only until it ends and a #temp it creates is gone afterwards. An error inside it still names its script line.
  • The script is classified as written, not as the sp_executesql carrying it (which alone would fail closed to write).

Transactions — rehearse, size, then commit

--transaction rollback|commit and --max-rows-affected wrap the whole script — every batch, one session — in one transaction (SET XACT_ABORT ON; BEGIN TRANSACTION … the script … end). --continue-on-error is refused (exit 2) with a wrapper: under XACT_ABORT an error rolls everything back and later batches would commit on their own. The record's transaction says how it ended:

transaction When
rolled-back rollback; any error; --max-rows-affected exceeded; a timeout or Ctrl+C
committed commit (or the rows-affected limit within bounds) and every batch succeeded
ended-by-script The transaction was gone when the script finished: a procedure or dynamic SQL committed or rolled it back (Msg 266) — reported as SQL_ERROR, exit 5: check the data
  • The check refuses (exit 7) a script that controls transactions itself (BEGIN TRAN, COMMIT, ROLLBACK, SAVE TRAN, SET IMPLICIT_TRANSACTIONS ON) or holds a statement SQL Server refuses inside a transaction (ALTER DATABASE, BACKUP, RESTORE, …).
  • The guarantee covers the script's own statements, not procedure bodies: a procedure's COMMIT ends the wrapper and commits what came before it.
  • --max-rows-affected n commits only while the server's counts stay at or under n; over it: rolled back, BLOCKED, exit 7. It can only trust what the server counts, so it refuses (exit 7, before connecting) a script with SET NOCOUNT ON, dynamic SQL, a procedure call off the read allow-list, or an OUTPUT clause. Triggers' counts are included — it over-counts rather than under-counts.

The full rehearsal ladder for a change (from the agent guide's Make a safe data fix):

gossms query classify --file fix.sql --allow write            # is it really a write? no connecting
gossms query parse -p dev --file fix.sql                      # the server checks the syntax
gossms query run -p dev --file fix.sql --allow write \
         --transaction rollback -o json                        # runs, reports rowsAffected, keeps nothing
gossms query run -p dev --file fix.sql --allow write \
         --max-rows-affected 10                                # commits only a change you sized for

query parse — the server checks the syntax

gossms query parse -p dev --file fix.sql
gossms query parse -p dev --file - -o json

Sends SET PARSEONLY ON, then every batch (so every syntax error is reported in one call, each with its script line), then OFF. It runs nothing, so it needs no --allow. Names are not resolved: a missing table or column is not an error here, only at run time. A script naming PARSEONLY, NOEXEC or SHOWPLAN_* anywhere outside literals and comments — OFF included, which would switch the mode off and run the rest — is BLOCKED, exit 7.

query classify — the classes, without connecting

gossms query classify --file fix.sql
gossms query classify --file - --allow write -o json
gossms query classify --sql-base64 VVBEQVRFIGRiby50IFNFVCB4ID0gMQ==

One row per statement — batch, repeat (GO n), line, class, leader (the leading keywords), object, reason (why the class is above the leader's), flags (dynamic-sql, transaction-control, nocount, output-clause, …) — and the script's detectedClass. Needs no connection. With --allow <class> it also applies the check a run would and answers BLOCKED (exit 7) when a run would be refused. How the classes work: Safety and audit.

plan estimated — compile, run nothing

gossms plan estimated -p dev --file report.sql
gossms plan estimated -p dev --file - -o json
gossms plan estimated -p dev --file report.sql --plan-format tree
gossms plan estimated -p dev --file report.sql --plan-format xml --plan-file report.sqlplan

Sends every batch under SET SHOWPLAN_XML ON on one session: the server compiles each statement, returns its plan and runs nothing. So it needs no --allow and the ceiling does not apply, whatever the script would do; detectedClass says what running it would need. The login needs SHOWPLAN in the database (Msg 262 → exit 9, hint naming the grant).

  • A #temp table the script creates earlier does not exist to a statement that uses it: Msg 208, as in SSMS — nothing ran to create it, and the error's hint says so. Every batch is still sent, so every compile error is reported at once, and the plans of the batches that compiled are returned.
  • No --param: under SHOWPLAN_XML the server plans an sp_executesql call as the EXEC alone — no operators. Write the values into the script.
  • SET PARSEONLY/NOEXEC/SHOWPLAN_* anywhere in the script (even OFF) is BLOCKED, exit 7, before connecting.

--plan-format summary (default) answers four named sets:

Set
statements One per statement: n, type, cost (estimated subtree cost), est_rows, dop, grant_kb, warnings, text (one line, cut to 200) — plus the actual columns, NULL here
operators The --top (default 10, 0 all) operators with the highest own estimated cost across all statements — the plan viewer's ranking: stmt, node, physical, logical, object, cost_pct (share of its statement), est_rows, parallel, mode, warnings, misestimate (actual > 10× estimate, either way; needs an actual plan)
missing_indexes stmt, impact, database, table, equality, inequality, include, create_statement — the plan viewer's one-line CREATE NONCLUSTERED INDEX, to review, not to run as given
warnings stmt, node, operator, warning (both NULL for a statement's own warning)

--plan-format tree answers one tree set — every operator in preorder, statement by statement, with depth and parent columns. --plan-format xml answers the plan document as the verbatim field planXml (one ShowPlanXML per batch, joined by a line break).

--plan-file <path> writes that document, UTF-8, exactly as the query editor's Save Execution Plan As — .sqlplan opens in SSMS and in gossms's plan viewer. An existing file is exit 2 before connecting unless --force; the file is written only when the command succeeds.

plan actual — run it, and measure it

gossms plan actual -p dev --file report.sql
gossms plan actual -p dev --file fix.sql --allow write --transaction rollback
gossms plan actual -p dev --file by-id.sql --param id=42:int --plan-format tree
gossms plan actual -p dev --file report.sql --plan-format xml --plan-file report.sqlplan

plan actual is query run with the plan captured (SET STATISTICS XML ON around the script, same session): the class check, --allow, the ceiling, --transaction, --max-rows-affected, --param, --continue-on-error, --timeout, --dry-run all apply unchanged — a write needs --allow write, and --transaction rollback gives the write's real plan without keeping the change (the row estimates and actuals are the ones a real run would see). Only --statistics is query run's alone.

  • Rows are counted, not returned: after the format's sets comes result_sets — n, rows, columns per set the script returned, empty ones included. query run is for the rows. ROWS_AFFECTED and the messages are query run's.
  • SET STATISTICS XML in the script (any mode, any list position — SET STATISTICS IO, XML ON counts) is BLOCKED, exit 7: the script's OFF would lose the plans after it.
  • Plans, in run order, for every statement that ran a plan; a trivial SELECT 1 or PRINT has none (a warning, statementCount 0). On an actual plan the actual columns fill in: statements actual_rows/elapsed_ms/ cpu_ms; operators/tree actual_rows (over all executions), elapsed_ms and misestimate.
  • A failed or timed-out run still answers the plans the server sent before it stopped; a batch cut by --timeout loses its own (not yet sent). --plan-file is written only on success.

plan cached — the plan the server is reusing

gossms plan cached 0x06000500…0000 --statement-start 112 --statement-end 418 -p prod-ro
gossms plan cached 0x06000500…0000 -p prod-ro --plan-format xml --plan-file slow.sqlplan

Reads one statement's plan from the plan cache (sys.dm_exec_text_query_plan, which survives deeply nested plans dm_exec_query_plan cannot): the estimated plan it compiled and keeps reusing — no runtime figures. The arguments come from monitor top-queries: its plan_handle (the 0x optional), statement_start/statement_end (byte offsets; 0 / −1 default = the whole batch). A non-hex handle or a bad offset is exit 2 before connecting; a plan that has left the cache, or a deferred compile, is NOT_FOUND (exit 8, hint: rank again). Needs VIEW SERVER STATE (exit 9). Formats, --top and --plan-file are plan estimated's.

Recipes

# tune a slow query end to end
gossms object definition -p dev Reporting.usp_MonthlySales            # what it does
gossms plan estimated -p dev --file - <<'SQL'                          # what it would do
EXEC Reporting.usp_MonthlySales @Year = 2026, @Month = 9;
SQL
gossms plan actual -p dev --file slow.sql --transaction rollback       # what it really did, kept nothing
gossms object indexes -p dev Sales.SalesOrderDetail --fragmentation limited
gossms query run -p dev --file fix_index.sql --allow ddl --dry-run     # the CREATE INDEX, as a plan

# the same plan in the cache, next time it is slow
gossms monitor top-queries --by duration --top 5 -p prod -o json |
  jq -r '.resultSets[0].rows[] | [.plan_handle, .statement_start, .statement_end] | @tsv'
gossms plan cached '<plan_handle>' --statement-start <n> --statement-end <n> \
  -p prod --plan-file slow.sqlplan                                     # open in SSMS

# CI: fail on syntax errors, review the class before approving
gossms query parse    -p ci  --file migration.sql || exit 1
gossms query classify        --file migration.sql --allow ddl -o json | jq '.data'

Clone this wiki locally