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:
1> sp_options show 2> go Category: Query Tuning name currentsetting defaultsetting scope ------------------------------------------ ------------------------------ optgoal allrows_mix allrows_mix 0 opttimeoutlimit 40 10 0 merge_join 1 1 4 hash_join 0 0 4 nl_join 1 1 4 distinct_sorted 1 1 4 distinct_sorting 1 1 4 distinct_hashing 1 1 4 group_sorted 1 1 4 group_hashing 1 1 4 group_inserting 0 0 4 order_sorting 1 1 4 append_union_all 1 1 4 merge_union_all 1 1 4 merge_union_distinct 1 1 4 hash_union_distinct 1 1 4 store_index 1 1 4 bushy_space_search 0 0 4 parallel_query 1 1 4 replicated_partition 0 0 4 ase125_primed 0 0 4 index_intersection 0 0 4 index_union 1 1 4 multi_table_store_ind 0 0 4 advanced_aggregation 0 0 4 opportunistic_distinct_view 1 1 4 repartition_degree 3 1 2 scan_parallel_degree 0 1 2 resource_granularity 10 10 2 parallel_degree 0 1 2 statistics simulate 0 0 4 forceplan 0 0 7 prefetch 1 1 6 metrics_capture 0 0 6 process_limit_action quiet quiet 2 plan replace 0 0 4 plan exists check 0 0 4 plan dump 0 0 4 plan load 0 0 4 (39 rows affected) (return status = 0)
For more information about sp_options, 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.
