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