Capturing Actual Execution Plans From a Script With SET STATISTICS XML

Capture the slow batch's plan through the result stream when it runs outside SSMS. SET STATISTICS XML adds execution-plan XML as an extra result set. The caller needs to handle that result deliberately.

Choose One Execution for SET STATISTICS XML

Actual plans include runtime information from executed work. SET STATISTICS XML does not merely estimate the statement and skip it. If the batch changes data, those changes still occur under its normal transaction behavior. Choose the targeted execution with the same care as any other diagnostic run.

I confirm the statement and its side effects before enabling capture. A slow job can include several operations, and capturing every statement creates more output and overhead. Start with the specific batch that needs investigation. A plan file is useful evidence only when its execution context is known.

Use a disposable database for the demonstration. The table and procedure provide a recognizable cached module for the later last-plan comparison. SQL Server 2022 with compatibility level 160 supplies the generated rows. The plan-capture technique itself is available on much older supported engines.

CREATE TABLE dbo.PlanCaptureDemo
(RowID int NOT NULL PRIMARY KEY,GroupID int NOT NULL,Amount decimal(12,2) NOT NULL);
INSERT dbo.PlanCaptureDemo
SELECT value,value%20,value%100 FROM GENERATE_SERIES(1,10000,1);
GO
CREATE PROCEDURE dbo.ReadPlanCaptureDemo
AS
BEGIN
 SET NOCOUNT ON;
 SELECT GroupID,SUM(Amount) AS TotalAmount
 FROM dbo.PlanCaptureDemo GROUP BY GroupID;
END;
GO

Return SET STATISTICS XML as an Additional Result

The session option surrounds the targeted procedure call. The normal query output remains part of the result stream, and the actual plan is added. Turn the option off afterward so unrelated statements do not continue producing diagnostic results.

SET STATISTICS XML ON;
EXEC dbo.ReadPlanCaptureDemo;
SET STATISTICS XML OFF;

In SSMS, disable its separate Include Actual Execution Plan option when relying on the explicit STATISTICS XML result. Open the returned XML plan result and save the complete document as a .sqlplan file, such as D:\Diagnostics\ReadPlanCaptureDemo.sqlplan. Use an existing approved folder rather than assuming that path exists.

For another caller, process every result set and retain the XML separately from business rows. A SQL Agent step's ordinary text output is not automatically a reliable complete XML artifact. Configure an approved capture client or run the targeted diagnostic through a caller that preserves the extra result. Do not assume a successful job wrote a plan file.

Preserve Permissions and Execution Context

The login needs permission to execute the statement and SHOWPLAN permission for databases referenced by it. Lack of required permissions can abort execution rather than simply omit a pretty picture. Test the capture path under the intended diagnostic identity.

Record the database, compatibility level, parameters, capture time, and engine version with the file. Actual rows from one parameter value do not describe every later execution. Keep sensitive statement text and parameter data under the same approved handling as other operational diagnostics.

I verify the saved XML contains runtime fields before calling it an actual-plan capture. A cached estimated plan saved with a .sqlplan extension remains an estimated plan. The extension describes the file format, not evidence that the statement ran.

Compare Last-Known Actual Plan Statistics

SQL Server 2019 introduced sys.dm_exec_query_plan_stats for last-known execution-plan statistics. Its availability depends on the last-plan-statistics configuration and profiling support. It is not a permanent history of every execution. A later call can replace the runtime information, and cache eviction can remove the plan.

The following test-only block saves the original scoped setting, enables capture, runs the demonstration procedure, and reads its cached plan into a temporary table. It restores an originally disabled setting afterward. Keep the GO after the ALTER, because a procedure run in the same batch returned no runtime counters in testing. Review any production configuration change separately rather than copying this test block into a live job.

DROP TABLE IF EXISTS #PlanStatsSetting;
SELECT CONVERT(int,value) AS PreviousSetting
INTO #PlanStatsSetting
FROM sys.database_scoped_configurations WHERE name=N'LAST_QUERY_PLAN_STATS';
ALTER DATABASE SCOPED CONFIGURATION SET LAST_QUERY_PLAN_STATS=ON;
GO
EXEC dbo.ReadPlanCaptureDemo;
DECLARE @Plan xml;
SELECT TOP(1) @Plan=p.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) t
CROSS APPLY sys.dm_exec_query_plan_stats(qs.plan_handle) p
WHERE t.dbid=DB_ID() AND t.objectid=OBJECT_ID(N'dbo.ReadPlanCaptureDemo')
ORDER BY qs.last_execution_time DESC;
DROP TABLE IF EXISTS #CapturedPlan;
SELECT @Plan AS PlanXml INTO #CapturedPlan;
SELECT PlanXml AS LastKnownActualPlan FROM #CapturedPlan;
IF (SELECT PreviousSetting FROM #PlanStatsSetting)=0
 ALTER DATABASE SCOPED CONFIGURATION SET LAST_QUERY_PLAN_STATS=OFF;

If the block fails before restoration, inspect and restore the original setting through the approved test process. The previous value stays in the #PlanStatsSetting temporary table for that session. Do not leave a diagnostic configuration enabled simply because a later query returned no plan.

Read Statement Runtime Fields

Showplan uses an XML namespace. Queries that omit it can return no nodes even when the document contains the expected information. The block above keeps the plan in the #CapturedPlan temporary table. Each query below reloads @Plan from it, so run them in the same session. The same queries can inspect a complete captured STATISTICS XML document assigned to @Plan.

DECLARE @Plan xml = (SELECT PlanXml FROM #CapturedPlan);
WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT s.n.value('@StatementText','nvarchar(max)') AS StatementText,
 s.n.value('(QueryPlan/QueryTimeStats/@CpuTime)[1]','bigint') AS CpuMs,
 s.n.value('(QueryPlan/QueryTimeStats/@ElapsedTime)[1]','bigint') AS ElapsedMs,
 s.n.query('QueryPlan/WaitStats') AS WaitInformation,
 s.n.query('QueryPlan/MemoryGrantInfo') AS MemoryGrantInformation
FROM @Plan.nodes('//StmtSimple') s(n);

QueryTimeStats describes statement runtime timing where available. WaitStats and MemoryGrantInfo expose relevant runtime and allocation context. Some statements or capture paths lack particular fields. Missing information is not automatically a zero value or proof that no waiting occurred.

Inspect Rows Per Operator and Thread

Runtime counters can appear per worker thread. Keep the operator identifier and thread number while reading ActualRows. Summing every operator's rows would count the same data as it moves through multiple operators. Aggregate only the intended operator's workers when a combined row count is needed.

DECLARE @Plan xml = (SELECT PlanXml FROM #CapturedPlan);
WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT r.n.value('@NodeId','int') AS NodeID,
 r.n.value('@PhysicalOp','nvarchar(60)') AS PhysicalOperator,
 c.n.value('@Thread','int') AS ThreadID,
 c.n.value('@ActualRows','bigint') AS ActualRows,
 c.n.value('@ActualExecutions','bigint') AS ActualExecutions
FROM @Plan.nodes('//RelOp') r(n)
CROSS APPLY r.n.nodes('RunTimeInformation/RunTimeCountersPerThread') c(n);

Compare actual and estimated rows at the operator relevant to the predicate or join. Keep repeated executions in mind when interpreting an inner loops input. A count without its operator and execution context can mislead as easily as an estimated percentage shown without measured timing.

Keep SET STATISTICS XML Targeted and Verify the File

Plan capture adds overhead and output. Use it for a controlled diagnostic run rather than enabling it across every production request. The business caller must either handle or deliberately disregard the extra result after preserving the diagnostic artifact.

Open the saved .sqlplan in SSMS and confirm the expected statement and runtime counters are present. Compare the direct capture with last-known statistics only when their executions and inputs are known. Which execution does each artifact actually describe? That final identity check keeps useful plan evidence from becoming an attractive explanation of the wrong run.

Also verify that the capture client preserved the complete document rather than a truncated display value. Large plans can exceed a client's text-display limit even when the server returned valid XML. Opening the saved artifact should show a well-formed plan with the expected statement. Retain the original file before producing summaries or extracting selected nodes. Those summaries help investigation, but the complete capture remains the evidence another reviewer can inspect. SET STATISTICS XML needs a caller that preserves the additional result without confusing it with business rows.

Related reading on this blog: Number of Rows Read: Execution Plan and Capturing Execution Plan for Canceled Query.

An actual plan file is not complete execution history, it is runtime evidence for an identified execution that needs preserved context.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.