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
