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

Finding The Last Non-Null Value With Snowflake

Koen Verbeeck shows how two words makes solving a problem with Snowflake a lot easier than with SQL Server: Sometimes you need to find the previous value in a column. Easy enough, the LAG window function makes this a breeze (available since SQL Server 2012). But what if the previous value cannot be null? You […]

Read More

Concerns With DISTINCT

Anvesh Patel does not like DISTINCT: I am telling you personally that I hate the use of DISTINCT.DISTINCT used by those people, who are not sure about their data set or SELECT statement or JOINS.Whenever I get any query with DISTINCT, immediately I suggest to remove it. I agree with this sentiment about 85% of […]

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