SET STATISTICS XML#
Executes Transact-SQL statements and returns the actual execution plan for each one as an XML document carrying run-time counters. This is the surface SQL Server Management Studio uses for its Include Actual Execution Plan command (Ctrl+M), which renders the returned document as a graphical plan.
Syntax#
SET STATISTICS XML { ON | OFF } [;]
Remarks#
When ON, statements run normally and, after each statement’s data result set (or sets), its plan
is returned as an extra result set with one column named Microsoft SQL Server 2005 XML
Showplan holding one ShowPlanXML document. Data and plan result sets are interleaved per
statement: data, plan, data, plan. Unlike the estimated modes (SET SHOWPLAN_XML), this mode does
not need to be alone in its batch and works over RPC — parameterized commands (sp_executesql) and
prepared statements return their data followed by the plan.
The document carries actual run-time counters (actual row counts and elapsed time) alongside the plan
shape. A statement with no query plan (such as a SELECT with no FROM) produces its data only,
with no plan result set — this matches SQL Server.
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 appears as ordinary relational operators. See
Reading execution plans for how to read these plans.
Counter semantics to be aware of: only the root operator’s actual row count is real (the rows
returned to the client); interior operators carry no run-time row count at all — no
RunTimeInformation is emitted for them. CpuTime is always 0 (there is no per-statement CPU
accounting) and DegreeOfParallelism is always 0. Cost figures (EstimateIO, EstimateCPU,
subtree cost) are always 0 — Querona has no optimizer cost model, so there is nothing to
estimate.
When both this mode and SET STATISTICS PROFILE are ON, each statement returns its result sets
in the order data, then the PROFILE rowset, then this XML document.
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+M (Include Actual Execution Plan) and run a query; SSMS wraps it with the statements below, runs it, and shows the plan next to the results. To get the raw XML, run them yourself:
SET STATISTICS 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 STATISTICS XML OFF;
GO