SET SHOWPLAN_XML#
Returns the estimated execution plan for Transact-SQL statements as an XML document instead of running them. This is the surface SQL Server Management Studio uses for its Display Estimated Execution Plan command (Ctrl+L), which renders the returned document as a graphical plan.
Syntax#
SET SHOWPLAN_XML { 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. Only the matching SET SHOWPLAN_XML OFF takes
effect and clears the mode; all other statements — including data modifications and other SET
statements — are planned, not run. Turn the mode OFF to resume normal execution.
A SET SHOWPLAN_XML 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).
For each query batch, the plan is returned as a single result set with one column named
Microsoft SQL Server 2005 XML Showplan holding one ShowPlanXML document. All statements in the
batch appear as sibling statement nodes in that one document. Statements with no query plan (such as a
SELECT with no FROM) still contribute a statement node.
The plan reflects what Querona actually does: work pushed down to a source appears as a
Remote Query operator carrying the source name and the exact SQL sent there, while work the engine
performs itself (joins, sorts, aggregation) appears as ordinary relational operators. See
Reading execution plans for how to read these plans. Cost figures (EstimateIO,
EstimateCPU, subtree cost) 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. SSMS is
unaffected because it sends the Ctrl+L query as a SQL batch.
Permissions#
Requires membership in the public role. Unlike SQL Server, Querona does not gate execution-plan output behind a separate SHOWPLAN permission.
Examples#
In SSMS, press Ctrl+L (Display Estimated Execution Plan) and run a query; SSMS wraps it with the statements below and renders the graphical plan. To get the raw XML, run them yourself:
SET SHOWPLAN_XML ON;
GO
SELECT c.name, SUM(o.total)
FROM crm.dbo.customers AS c
JOIN sales.public.orders AS o ON o.customer_id = c.id
GROUP BY c.name;
GO
SET SHOWPLAN_XML OFF;
GO