Export (0) Print
Expand All
0 out of 1 rated this helpful - Rate this topic

Transact-SQL Statements That Produce Showplans

This topic lists the types of Transact-SQL statements that produce Showplan output for Showplan SET options and SQL Server Profiler event classes.

ms187886.note(en-US,SQL.90).gifNote:
No Showplan information is generated for encrypted stored procedures or for triggers.

The following table lists which Transact-SQL statements produce Showplan information.

Transact-SQL statement type Showplan SET Option mode1 Statistics SET Option mode2 Query Compile SQL Server Profiler events3 Query Execute SQL Server Profiler events 4

All DML (SELECT, INSERT, UPDATE, and DELETE)

Yes

Yes

Yes

Yes

All DDL that affects metadata only. For example, CREATE TABLE.

Exceptions are listed separately.

No

No

No

No

CREATE INDEX

No

Yes, if table is not empty.

Yes

Yes, if table is not empty.

INSERT INTO ... SELECT FROM

(subqueries)

Yes

Yes

Yes

Yes

INSERT INTO ... EXEC

(subqueries)

No

No

Yes

Yes

Automatically created or updated statistics

No

No

Yes

Yes

Manually created or updated statistics

No

No

Yes

Yes

Dynamic SQL

No

Yes

Yes

Yes

EXECUTE stored_procedure

Yes

Yes

Yes

Yes

CREATE PROCEDURE store_procedure

No

No

No

No

Triggers

(Not called directly, but caused by an INSERT, UPDATE, or DELETE statement)

No

Yes

Yes

Yes

CLR triggers

No

No

No

No

CLR user-defined functions, user-defined aggregates, and user-defined procedures

No

No

No

No

Queries that reference a user-defined function one or more times

Yes

(No for Microsoft SQL Server 2000)

Yes

Yes

(No for SQL Server 2000)

Yes

Create a temporary table, and then SELECT from it, or INSERT INTO it

Yes

(No for SQL Server 2000)

Yes

Yes

(No for SQL Server 2000)

Yes

DBCC commands

No

No

No

No

BULK INSERT

No

Yes

Yes

Yes

Statements submitted by using the sp_executesql stored procedure

No

No

No

No

1 Includes the following Showplan SET option statements:

  • SET SHOWPLAN_XML ON
  • SET SHOWPLAN_ALL ON
  • SET SHOWPLAN_TEXT ON

2 Includes the following Statistics SET option statements:

  • SET STATISTICS XML ON
  • SET STATISTICS PROFILE ON

3 Includes the following SQL Server Profiler event classes:

  • Showplan XML For Query Compile
  • Showplan All For Query Compile

4 Includes the following SQL Server Profiler event classes:

  • Showplan XML
  • Showplan All
  • Showplan Text
  • Showplan XML Statistics Profile
  • Showplan Statistics Profile
Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.