sp_options

Shows option values.

Syntax

sp_options [ [show | help
	[, option_name | category_name | null
	[, dflt | non_dflt | null [, spid] ] ] ] ]

Parameters

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.
option_name – is the name of the option.
category_name – is the category of the option.
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.

Examples

Example 1 – Shows 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 – Shows 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 – Shows 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 – Shows only 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 – 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)

Example 6 – 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)

Example 7 – Shows the options that use non-default settings 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 – 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)

Example 9 – 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)

Example 10 – Shows the options that are 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 – Shows correct usage:

1> sp_options help
2> go

Usage:
sp_options [ [show | help
            [, <option_name>|<category_name>|null
               [, dflt | non_dflt | null
                    [, <spid>] ] ] ] ]

Usage

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

Permissions

Any user can execute sp_options. Permission checks do not differ based on the granular permissions settings.

Auditing

Values in event and extrainfo columns from the sysaudits table are:

Information	Values
Event	38
Audit option	exec_procedure
Command or access audited	Execution of a procedure
Information in `extrainfo`	Roles – Current active roles Keywords or options – NULL Previous value – NULL Current value – NULL Other information – All input parameters Proxy information – Original login name, if set proxy in effect