SET STATISTICS XML

Kevin Feasel

2016-05-23

Syntax

Daniel Hutmacher explains how SET STATISTICS XML will generate execution plans for certain segments of code:

But sometimes you want to run a series of statements or procedures where you only want the execution plan for some of the statements. Here’s how:

The actual execution plan is enabled by turning on SET STATISTICS XML., not unlike enabling STATISTICS IO or TIME. And just like SET NOCOUNT, the SET statements apply to the current context, which could be a stored procedure, a session, etc. When this context ends, the setting reverts to that of the parent context.

I see code snippets with STATISTICS IO and TIME fairly regularly, but almost never see STATISTICS XML; instead, I see people (including myself) hit Ctrl-M or select the “Include Actual Execution Plan” button when generating execution plans is desirable.

Related Posts

Testing Maximum Rows in Table Value Constructors

Solomon Rutzky dives into how big a table value constructor can be in terms of rows: On 2019-05-08, a helpful individual, Michael B, commented on my answer saying that the 1000-row limit only existed when using a TVC as the VALUES clause of an INSERT statement. And, that there was no limit when using a TVC as a derived table. […]

Read More

T-SQL Tips Regarding Subqueries

Itzik Ben-Gan provides quality information on working with subqueries in SQL Server: In this plan you see a Nested Loops (Left Semi Join) operator, with a scan of the clustered index on Customers as the outer input and a seek in the index on the customerid column in the Orders as the inner input. You […]

Read More

1 Comment

  • Daniel Hutmacher on 2016-05-23

    That’s the point – use Ctrl+M like you’re used to, but this way you won’t have to see the execution plan of everything that happens, only the particular statements you’re interested in. 🙂

    Thanks for the reblog!

Comments are closed

Categories

May 2016
MTWTFSS
« Apr Jun »
 1
2345678
9101112131415
16171819202122
23242526272829
3031