set commands for text format messages

Either the query optimizer or the query execution layer can generate diagnostic output. To generate diagnostic output in text format, use this set option command:

set option
   {   {show | show_lop | show_managers | show_log_props |
       show_parallel | show_histograms | show_abstract_plan |
       show_search_engine | show_counters | show_best_plan |
       show_code_gen | show_pio_costing | show_lio_costing |
       show_pll_costing | show_elimination | show_missing_stats}
      {normal | brief | long | on | off} }...

NoteEach option specified must be followed by a choice of normal, brief, long, on, or off. On and normal are equivalent. More than one option, and its corresponding choice, may be specified in a set option command, with each pair separated by a comma.

Table 3-1: Optimizer set commands for text format messages

Option

Definition

show

Shows a reasonable collection of details, where the collection depends on the choice of {normal | brief | long | on | off}.

show_lop

Shows the logical operators used.

show_managers

Shows data structure managers used during optimization.

show_log_props

Shows the logical properties evaluated.

show_parallel

Shows details of parallel query optimization.

show_histograms

Shows the processing of histograms associated with SARG/join columns.

show_abstract_plan

Shows the details of an abstract plan.

show_search_engine

Shows the details of the join-ordering algorithm.

show_counters

Shows the optimization counters.

show_best_plan

Shows the details of the best query plan selected by the optimizer.

show_code_gen

Shows details of code generation.

show_pio_costing

Shows estimates of physical input/output (reads/writes from/to the disk).

show_lio_costing

Shows estimates of logical input/output (reads/writes from/to memory).

show_pll_costing

Shows estimates relating to costing for parallel execution.

show_elimination

Shows partition elimination.

show_missing_stats

Shows details of useful statistics missing from SARG/join columns.