SET SHOWPLAN_ALL#
Returns the estimated execution plan for Transact-SQL statements as a detailed rowset instead of running them. It reports the same plan as SET SHOWPLAN_XML and SET SHOWPLAN_TEXT, rendered as an 18-column result set with one row per plan operator.
Syntax#
SET SHOWPLAN_ALL { ON | OFF } [;]
Remarks#
When ON, the session is put into an estimated-plan mode: every statement is compiled but not
executed, and its estimated plan is returned as rows. Only the matching SET SHOWPLAN_ALL OFF
takes effect and clears the mode; all other statements are planned, not run. Turn the mode OFF to
resume normal execution.
A SET SHOWPLAN_ALL statement must be the only statement in its batch. A batch that mixes it
with other statements fails with error 1067 (class 15) and nothing in the batch runs. The ON
form always raises this error when it is not alone; the OFF form raises it only while an estimated
mode is active (otherwise a stray OFF alongside other statements is a no-op and the batch runs
normally).
Each batch returns one result set with 18 columns — including StmtText, NodeId, Parent,
PhysicalOp, LogicalOp, Argument, EstimateRows, TotalSubtreeCost and OutputList
— with a header row per statement followed by one row per operator. The column widths are sized to the
content of each result set. Statements with no query plan contribute only their header row.
The plan reflects what Querona actually does: work pushed down to a source appears as a
Remote Query operator, while work the engine performs itself appears as ordinary relational
operators. See Reading execution plans for how to read these plans. Cost figures
(EstimateIO, EstimateCPU, TotalSubtreeCost) are always 0 — Querona has no optimizer
cost model, so there is nothing to estimate. EstimateRows is a fixed placeholder (1) on every
operator, not a per-operator cardinality estimate.
Over RPC — parameterized commands (sp_executesql) and prepared statements — an estimated mode
returns an empty response with no plan, no data and no error. This matches SQL Server.
Permissions#
Requires membership in the public role. Unlike SQL Server, Querona does not gate execution-plan output behind a separate SHOWPLAN permission.
Examples#
SET SHOWPLAN_ALL ON;
GO
SELECT * FROM crm.dbo.customers WHERE region = 'EU';
GO
SET SHOWPLAN_ALL OFF;
GO