set Options That Save Diagnostic Information to a Trace File

You can use set tracefile in combination with other set commands and options that provide diagnostic information for a better understanding of slow-running queries.

These are the set commands and options that save diagnostic information to a file:
  • set show_sqltext [on | off]

  • set showplan [on | off]

  • set statistics io [on | off]

  • set statistics time [on | off]

  • set statistics plancost [on | off]

These are the set options:
  • set option show [normal | brief | long | on | off]

  • set option show_lop [normal | brief | long | on | off]

  • set option show_parallel [normal | brief | long | on | off]

  • set option show_search_engine [normal | brief | long | on | off]

  • set option show_counters [normal | brief | long | on | off]

  • set option show_managers [normal | brief | long | on | off]

  • set option show_histograms [normal | brief | long | on | off]

  • set option show_abstract_plan [normal | brief | long | on | off]

  • set option show_best_plan [normal | brief | long | on | off]

  • set option show_code_gen [normal | brief | long | on | off]

  • set option show_pio_costing [normal | brief | long | on | off]

  • set option show_lio_costing [normal | brief | long | on | off]

  • set option show_log_props [normal | brief | long | on | off]

  • set option show_elimination [normal | brief | long | on | off]