Using showplan options

showplan in Adaptive Server 15.0 replaces many of the diagnostic trace flags used with set options. This is the syntax:

set option show_option (normal/brief/long/on/off)

See the Reference Manual: Commands for more information about set option syntax.

Usage issues

Before you use set option, consider the following:

Some options require that you set dbcc traceon (3604) or 3605 to see the ouput on the client side or in the error log.
Execute set option show on before you set other, more restrictive, options. This allows you to specify the brief or long options, which override the more general level of detail of the show command.
If you enabled query metrics capture, set option may not produce output.

Example

This example displays the query plan, reports the statistics, and displays the abstract plan (which allows you to issue create plan to solve problems later on).

set showplan on
set option show_missing_stats on
set option show_abstract_plan on

To view missing statistics, issue:

set option show_missing_stats long
dbcc traceon(3604)
go

DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.

select * from part, partsupp
where p_partkey = ps_partkey and p_itemtype = ps_itemtype
go

NO STATS on column part.p_partkey
NO STATS on column part.p_itemtype
NO STATS on column partsupp.ps_itemtype
NO STATS on density set for E={p_partkey, p_itemtype}
NO STATS on density set for F={ps_partkey, ps_itemtype}

This example debugs the index selection, IO costing, and so on (previous releases did this with trace flags 302, 310, 315, and so on):

dbcc traceon(3604)
set showplan on
set option show long