sp_options allows you to view the current optimizer settings for these options:
set plan dump / load
set plan exists check
set forceplan
set plan optgoal
set [optCriteria]
set plan opttimeoutlimit
set plan replace
set statistics simulate
set metrics_capture
set prefetch
set parallel_degree number
set process_limit_action
set resource_granularity number
set scan_parallel_degree number
set repartition_degree number
sp_options queries the sysoptions fake table, which stores information about each set option, its category, and its current and default settings. sysoptions also contains a bitmap that provides detailed information for each option
The syntax for sp_options is:
sp_options [ [show | help [, option_name | category_name |null [, dflt | non_dflt | null [, spid] ] ] ] ]
where:
show – lists the current and default values of all options, grouped according to their category. Issuing sp_options show with an option name specified shows you the current and default value for the individual option. You can also specify a session ID, and whether you want to view options with default settings or options with nondefault settings.
help – show usage information. Achieve the same result by issuing sp_options with no parameters.
null – indicates the option for which you want to view the settings.
dflt | non_dflt | null – indicates whether to show options with default settings or to show options with non-default settings.
spid – specifies the session ID. Use the session ID to view other session settings.
For example, to display the current optimizer settings shown below, enter:
sp_options show Category: Query Tuning name currentsetting defaultsetting scope -------------------------------------------------------------------- ------------------ --------------------- --------- optlevel ase_default ase_current 3 optgoal allrows_mix allrows_mix 3 opttimeoutlimit 10 10 2 repartition_degree 1 1 2 scan_parallel_degree 0 1 2 resource_granularity 10 10 2 . . . outer_join_costing: outer join row counts and histogramming 0 0 7 join_duplicate_estimates: avoid overestimates of dups in joins 0 0 7 imdb_costing: 0 PIO costing for scans for in-memory database 1 1 7 auto_temptable_stats: auto generation of statistics for #temptables 0 0 7 use_mixed_dt_sarg_under_specialor: allow special OR in case of mixed . . . 0 0 7 timeout_cart_product: timeout queries involving cartesian product and more . . . 0 0 7 (81 rows affected)
See Adaptive Server Reference Manual: Procedures.
Any user can query sysoptions:
You can also use string manipulation or a cast. For example, if an option is numeric, you can query sysoptions by entering:
if (isnumeric(currentsetting)) select@int_val = convert(int, currentsetting) ... else select@char_val = currentsetting ...
For more infomation about sysoptions, see Adaptive Server Reference Manual: Tables.
