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 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.
help – indicates that you wish to show usage information. You achieve the same result when you issue 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.
Example 1 To view sp_options usage:
1> sp_options 2> go Usage: sp_options [ [show | help [, <option_name>|<category_name>|null [, dflt | non_dflt | null [, <spid>] ] ] ] ]
Example 2 To view 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)
Example 3 To view 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)
Example 4 To show 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)
Example 5 To show 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)
Example 6 To show 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)
Example 7 To show 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)
Example 8 To show 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)
Example 9 To show 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)
Example 10 To show 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)
Example 11 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)
Example 12 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
