Delen via


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.

Note

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