您好，我是小DAI，专注于数据库管理员相关的技术问答，请问有什么可以帮您？
sp_options
语法
sp_options [ [show | help
[, <option_name> | <category_name> | null
[, dflt | non_dflt | null [, <spid>] ] ] ] ]
参数
show
列出所有选项的当前值和缺省值，并按这些值的类别进行分组。发出带指定选项名的 sp_options 显示，将为您提供有关单个选项的当前值和缺省值。您还可以指定会话 ID，并指定是要查看具有缺省设置的选项，还是要查看具有非缺省设置的选项。
help
指示您希望显示用法信息。发出不带参数的 sp_options 时会得到相同的结果。
<option_name>
是选项的名称。
<category_name>
是选项的类别。
null
指示要查看其设置的选项。
dflt | non_dflt | null
指示是显示具有缺省设置的选项，还是显示具有非缺省设置的选项。
<spid>
指定会话 ID。使用会话 ID 查看其它会话设置。
示例
示例 1
显示 sp_options 的用法：
1> sp_options
2> go
Usage:
sp_options [ [show | help
[, <option_name>|<category_name>|null
[, dflt | non_dflt | null
[, <spid>] ] ] ] ]
示例 2
显示所有当前选项和缺省选项的列表：
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)
示例 3
显示单个选项的当前设置和缺省设置：
1> sp_options show, "index_intersection"
2> go
<h2>name category currentsetting defaultsetting scope</h2>
index_intersection Query Tuning 0 0 4
(1 row affected)
(return status = 0)
示例 4
仅显示单个选项的缺省设置：
1> sp_options show, "index_intersection", dflt
2> go
<h2>name defaultsetting</h2>
index_intersection 0
(1 row affected)
(return status = 0)
示例 5
显示某个类别的当前设置和缺省设置：
1> sp_options show, "Query Tuning"
2> go
Category: Query Tuning
<h2>name currentsetting defaultsetting scope</h2>
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)
示例 6
显示查询调优类别的缺省设置：
1> sp_options show, "Query Tuning", dflt
2> go
Category: Query Tuning
<h2>name defaultsetting</h2>
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)
示例 7
显示在查询调优类别中使用非缺省设置的选项：
1> sp_options show, "Query Tuning", non_dflt
2> go
Category: Query Tuning
<h2>name currentsetting defaultsetting</h2>
repartition_degree 3 1
scan_parallel_degree 0 1
parallel_degree 0 1
(3 rows affected)
(return status = 0)
示例 8
显示查询调优类别中的选项：
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)
示例 9
显示查询调优类别的缺省设置列表：
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)
示例 10
显示查询调优类别中设置为非缺省设置的选项：
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)
示例 11
如果您输入 sp_options 无法识别的参数，则会收到以下消息：
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:
<h2>category</h2>
Query Tuning
(1 row affected)
(return status = 1)
示例 12
显示正确用法：
1> sp_options help
2> go
Usage:
sp_options [ [show | help
[, <option_name>|<category_name>|null
[, dflt | non_dflt | null
[, <spid>] ] ] ] ]
用法
使用 sp_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