您可以使用 SQL Server Management Studio 或 sp_configure 系统存储过程通过配置选项来管理和优化 SQL Server 资源。大多数常用的服务器配置选项可以通过 SQL Server Management Studio 来使用；而所有配置选项都可通过 sp_configure 来访问。在设置这些选项之前应该认真考虑这些选项对系统的影响。

使用 sp_configure 时，必须在设置配置选项之后运行 RECONFIGURE 或 RECONFIGURE WITH OVERRIDE。RECONFIGURE WITH OVERRIDE 语句通常专门用来设置那些使用起来应当十分小心的配置选项。但是，RECONFIGURE WITH OVERRIDE 可用于所有的配置选项，并且可以用它代替 RECONFIGURE。

 
RECONFIGURE 在事务内部执行。如果任意重新配置选项失败，则所有重新配置操作都将失效。

 

 

每个选项的值都可使用以下语句确定。

  
SELECT * FROM sys.configurationsORDER BY name ; GO

下面的示例显示了如何使用 sp_configure将 fill factor 选项从其默认设置更改为值 100。

  
sp_configure 'show advanced options', 1;GORECONFIGURE;GO sp_configure 'fill factor', 100;GORECONFIGURE; GO

配置选项的分类

配置选项在下面两种情况下生效：

在设置选项并发出 RECONFIGURE（在某些情况下为 RECONFIGURE WITH OVERRIDE）语句之后立即生效。

-或-

执行以上操作并重新启动 SQL Server 实例之后生效。

若要配置高级选项，必须先在将 'show advanced options' 选项设置为 1 时运行 sp_configure，然后运行 RECONFIGURE，如下面的示例所示。

  
sp_configure 'show advanced options', 1;GORECONFIGURE;GO sp_configure 'cursor threshold', 0;GORECONFIGURE; GO

将立即重新配置 cursor threshold 选项。cursor threshold 的新值将出现在配置选项 value_in_use 列和 value 列中。

需要重新启动 SQL Server 的选项最初只在 value 列中显示更改后的值。在重新启动后，新值将出现在 value 列和 value_in_use 列中。

但有些选项需要在重新启动服务器后，新的配置值才能生效。如果设置了新值并在没有重新启动服务器的情况下运行 sp_configure，则新值将出现在配置选项的 value 列中，而不是出现在 value_in_use 列中。重新启动服务器之后，新值将出现在 value_in_use 列中。

自配置选项是指 SQL Server 根据系统需要进行调整的选项。在大多数情况下，这就无需手动设置值。这方面的例子包括 min server memory、max server memory 和 user connections 选项。

配置选项表

下表列出了所有可用的配置选项、可能的设置范围及其默认值。配置选项按以下字母代码标记：

A = 高级选项，仅允许有经验的数据库管理员或经过认证的 SQL Server 技术人员进行更改，并且需要将 show advanced options 设置为 1。

RR = 需要重新启动数据库引擎的选项。

SC = 自配置选项。

    

access check cache bucket count (A)

0

16384

0

access check cache quota (A)

0

2147483647

0

ad hoc distributed queries (A)

0

1

0

affinity I/O mask（A，RR）

-2147483648

2147483647

0

affinity64 I/O mask（A，仅适用于 64 位版本的 SQL Server）

-2147483648

2147483647

0

affinity mask (A)

-2147483648

2147483647

0

affinity64 mask (A, RR)，仅适用于 64 位版本的 SQL Server

-2147483648

2147483647

0

Agent XPs (A)

0

1

0

（当 SQL Server 代理启动时，更改为 1。如果 SQL Server 代理设置为在安装过程中自动启动，则为 1。）

allow updates（已过时。请勿使用。将在重新配置期间导致错误。）

0

1

0

awe enabled（A，RR）

0

1

0

backup compression default

0

1

0

blocked process threshold (A)

0

86400

0

c2 audit mode（A，RR）

0

1

0

clr enabled

0

1

0

common criteria compliance enabled (A，RR)

0

1

0

cost threshold for parallelism (A)

0

32767

5

cross db ownership chaining

0

1

0

cursor threshold (A)

-1

2147483647

-1

Database Mail XPs (A)

0

1

0

default full-text language (A)

0

2147483647

1033

default language

0

9999

0

default trace enabled (A)

0

1

1

disallow results from triggers (A)

0

1

0

EKM provider enabled

0

1

0

enable_force_flush

0

1

0

filestream_access_level

0

2

0

fill factor（A，RR）

0

100

0

ft crawl bandwidth (max)，请参阅 ft crawl bandwidth (A)

0

32767

100

ft crawl bandwidth (min)，请参阅 ft crawl bandwidth (A)

0

32767

0

ft notify bandwidth (max)，请参阅 ft notify bandwidth (A)

0

32767

100

ft notify bandwidth (min)，请参阅 ft notify bandwidth (A)

0

32767

0

index create memory（A，SC）

704

2147483647

0

in-doubt xact resolution (A)

0

2

0

lightweight pooling（A，RR）

0

1

0

locks（A，RR，SC）

5000

2147483647

0

max degree of parallelism (A)

0

64

0

max full-text crawl range (A)

0

256

4

max server memory（A，SC）

16

2147483647

2147483647

max text repl size

0

2147483647

65536

max worker threads（A，RR）

128

32767

（对于 32 位 SQL Server，建议最大为 1024；对于 64 位 SQL Server，建议最大为 2048。）

0

归零操作将根据处理器的数量自动配置最大工作线程数，可以使用公式 (256+(<处理器数> -4)* 8) 来计算 32 位 SQL Server 的线程数，64 位 SQL Server 的线程数为 32 位的 2 倍。

media retention（A，RR）

0

365

0

min memory per query (A)

512

2147483647

1024

min server memory（A，SC）

0

2147483647

0

nested triggers

0

1

1

network packet size (A)

512

32767

4096

Ole Automation Procedures (A)

0

1

0

open objects（A，RR，已过时）

0

2147483647

0

optimize for ad hoc workloads (A)

0

1

0

PH_timeout (A)

1

3600

60

precompute rank (A)

0

1

0

priority boost（A，RR）

0

1

0

query governor cost limit (A)

0

2147483647

0

query wait (A)

-1

2147483647

-1

recovery interval（A，SC）

0

32767

0

remote access (RR)

0

1

1

remote admin connections

0

1

0

remote login timeout

0

2147483647

20

remote proc trans

0

1

0

remote query timeout

0

2147483647

600

Replication XPs 选项 (A)

0

1

0

scan for startup procs（A，RR）

0

1

0

server trigger recursion

0

1

1

set working set size（A，RR，已过时）

0

1

0

show advanced options

0

1

0

SMO and DMO XPs (A)

0

1

1

SQL Mail XPs (A)

0

1

0

transform noise words (A)

0

1

0

two digit year cutoff (A)

1753

9999

2049

user connections（A，RR，SC）

0

32767

0

User Instance Timeout（A，仅出现在 SQL Server 2008 Express 中。）

5

65535

60

user instances enabled（A，仅出现在 SQL Server 2008 Express 中。）

0

1

0

user options

0

32767

0

xp_cmdshell (A)

0

1

0