Show option values.
sp_options [ [show | help [, option_name | category_name | null [, dflt | non_dflt | null [, spid] ] ] ] ]
lists the current and default values of all options, grouped according to their category. Issuing sp_options show with an option name specified gives 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 non-default settings.
indicates that you wish to show usage information. You achieve the same result when you issue sp_options with no parameters.
is the name of the option.
is the category of the option.
indicates the option for which you want to view the settings.
indicates whether to show options with default settings or to show options with non-default settings.
specifies the session ID. Use the session ID to view other session settings.
Views sp_options usage:
1> sp_options 2> go
Usage: sp_options [ [show | help [, <option_name>|<category_name>|null [, dflt | non_dflt | null [, <spid>] ] ] ] ]
Vews a list of all current and default options:
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)
Views the current and default setting for an individual option:
1> sp_options show, "index_intersection" 2> go name category currentsetting defaultsetting scope ----------------------------------------------------------------------- index_intersection Query Tuning 0 0 4 (1 row affected) (return status = 0)
Shows just the default setting for an individual option:
1> sp_options show, "index_intersection", dflt 2> go name defaultsetting -------------------------------------- index_intersection 0 (1 row affected) (return status = 0)
Shows the current and default settings for a category:
1> sp_options show, "Query Tuning" 2> go Category: Query Tuning name currentsetting defaultsetting scope ----------------------------------------------------------------- optgoal allrows_mix allrows_mix 0 opttimeoutlimit 10 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)
Shows the default settings for the Query Tuning category:
1> sp_options show, "Query Tuning", dflt 2> go Category: Query Tuning name defaultsetting ------------------------------------------ optgoal allrows_mix opttimeoutlimit 10 merge_join 1 hash_join 0 nl_join 1 distinct_sorted 1 distinct_sorting 1 distinct_hashing 1 group_sorted 1 group_hashing 1 group_inserting 0 order_sorting 1 append_union_all 1 merge_union_all 1 merge_union_distinct 1 hash_union_distinct 1 store_index 1 bushy_space_search 0 parallel_query 1 replicated_partition 0 ase125_primed 0 index_intersection 0 index_union 1 multi_table_store_ind 0 advanced_aggregation 0 opportunistic_distinct_view 1 repartition_degree 1 scan_parallel_degree 1 resource_granularity 10 parallel_degree 1 statistics simulate 0 forceplan 0 prefetch 1 metrics_capture 0 process_limit_action quiet plan replace 0 plan exists check 0 plan dump 0 plan load 0 (39 rows affected) (return status = 0)
Shows the options set to a non-default setting in the Query Tuning category:
1> sp_options show, "Query Tuning", non_dflt 2> go Category: Query Tuning name currentsetting defaultsetting ---------------------------------------------------- repartition_degree 3 1 scan_parallel_degree 0 1 parallel_degree 0 1 (3 rows affected) (return status = 0)
Shows the options in the Query Tuning category:
1> sp_options, show, null 2> go Category: Query Tuning name currentsetting defaultsetting scope ------------------------------------------ -------------- ----- optgoal allrows_mix allrows_mix 0 opttimeoutlimit 10 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)
Shows a list of the default settings for the Query Tuning category:
1> sp_options show, null, dflt 2> go Category: Query Tuning name defaultsetting --------------------------- -------------- optgoal allrows_mix opttimeoutlimit 10 merge_join 1 hash_join 0 nl_join 1 distinct_sorted 1 distinct_sorting 1 distinct_hashing 1 group_sorted 1 group_hashing 1 group_inserting 0 order_sorting 1 append_union_all 1 merge_union_all 1 merge_union_distinct 1 hash_union_distinct 1 store_index 1 bushy_space_search 0 parallel_query 1 replicated_partition 0 ase125_primed 0 index_intersection 0 index_union 1 multi_table_store_ind 0 advanced_aggregation 0 opportunistic_distinct_view 1 repartition_degree 1 scan_parallel_degree 1 resource_granularity 10 parallel_degree 1 statistics simulate 0 forceplan 0 prefetch 1 metrics_capture 0 process_limit_action quiet plan replace 0 plan exists check 0 plan dump 0 plan load 0 (39 rows affected) (return status = 0)
Shows the options set to a non-default setting in the Query Tuning category:
1> sp_options show, null, non_dflt 2> go
Category: Query Tuning name currentsetting defaultsetting -------------------- -------------- -------------- repartition_degree 3 1 scan_parallel_degree 0 1 parallel_degree 0 1 (3 rows affected) (return status = 0)
If you enter a parameter that sp_options does not understand, you receive the following message:
1> sp_options show, "incorrect option" 2> go
Msg 19615, Level 16, State 1: Procedure 'sp_options', Line 436: No option or category matching 'incorrect option' is found. Valid categories are: category ------------ Query Tuning (1 row affected) (return status = 1)
To see correct usage:
1> sp_options help 2> go
Usage: sp_options [ [show | help [, <option_name>|<category_name>|null [, dflt | non_dflt | null [, <spid>] ] ] ] ]
Use sp_options to view settings for the following 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
Any user can execute sp_options. Permission checks do not differ based on the granular permissions settings.
Values in event and extrainfo columns from the sysaudits table are:
Event |
Audit option |
Command or access audited |
Information in extrainfo |
|---|---|---|---|
38 |
exec_procedure |
Execution of a procedure |
|
