Either the query optimizer or the query execution layer can generate diagnostic output. To generate diagnostic output in text format, use:
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} }...
Each option specified must be followed by one of normal, brief, long, on,
or off. on and normal are
equivalent. Each show option must include one
of these choices (normal, brief,
and so on); specify more than one option in a single set option
command by separating each option or choice pair with commas.
See “Diagnostic usage scenarios” for examples of using the set options.
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 the 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 |
