3 4
Some of the parameters just described are advanced parameters, and others are standard parameters. Some parameters take effect immediately, and others require you to restart SQL Server before they take effect. The following table lists the SQL Server parameters and specifies each parameter's classification and requirements for taking effect.
Table A-1. SQL Server parameters
Parameter | Advanced Option | Restart Required |
---|---|---|
affinity mask | Yes | Yes |
allow updates | No | No |
awe enabled | Yes | Yes |
C2 audit mode | Yes | Yes |
cost threshold for parallelism | Yes | No |
cursor threshold | Yes | No |
default full-text language | Yes | No |
default language | No | No |
fill factor | Yes | Yes |
index create memory | Yes | No |
lightweight pooling | Yes | Yes |
locks | Yes | Yes |
max degree of parallelism | Yes | No |
max server memory | Yes | No |
max text repl size | No | No |
max worker threads | Yes | Yes |
media retention | Yes | Yes |
min memory per query | Yes | No |
min server memory | Yes | No |
nested triggers | No | No |
network packet size | Yes | No |
open objects | Yes | Yes |
priority boost | Yes | Yes |
query governor cost limit | Yes | No |
query wait | Yes | No |
recovery interval | Yes | No |
remote access | No | Yes |
remote login timeout | No | No |
remote proc trans | No | No |
remote query timeout | No | No |
scan for startup procs | Yes | Yes |
set working set size | Yes | Yes |
show advanced options | No | No |
two digit year cutoff | No | No |
user connections | Yes | Yes |
user options | No | No |