This topic lists the types of Transact-SQL statements that produce Showplan output for Showplan SET options and SQL Server Profiler event classes.
.gif) 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
Reference
Displaying Graphical Execution Plans (SQL Server Management Studio)
Concepts
Displaying Execution Plans by Using the Showplan SET Options (Transact-SQL)
Displaying Execution Plans by Using SQL Server Profiler Event Classes
Help and Information
Getting SQL Server 2008 Assistance