When using sp_configure, you must run either RECONFIGURE or RECONFIGURE WITH OVERRIDE after setting a configuration option. The RECONFIGURE WITH OVERRIDE statement is usually reserved for configuration options that should be used with extreme caution. However, RECONFIGURE WITH OVERRIDE works for all configuration options, and you can use it in place of RECONFIGURE.

Note:
RECONFIGURE executes within a transaction. If any of the reconfigure operations fail, none of the reconfigure operations will take effect.

The value for each option can be determined with the following statement.

SELECT * FROM sys.configurations
ORDER BY name ;
GO

The following example shows how to use sp_configure to change the fill factor option from its default setting to a value of 100.

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

Configuration options take effect either:

• Immediately after setting the option and issuing the RECONFIGURE (or in some cases, RECONFIGURE WITH OVERRIDE) statement.
  -or-
  
• After performing the above actions and restarting the instance of SQL Server.
  

To configure an advanced option, you must first run sp_configure with the 'show advanced options' option set to 1, and then run RECONFIGURE, as shown in the following example.

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

The cursor threshold option is reconfigured immediately. The new value for cursor threshold appears in the configuration options value_in_use column and the value column.

Options that require SQL Server to restart will initially show the changed value only in the value column. After restart, the new value will appear in both the value column and the value_in_use column.

Some options require a server restart before the new configuration value takes effect. If you set the new value and run sp_configure before restarting the server, the new value appears in the configuration options value column, but not in the value_in_use column. After restarting the server, the new value appears in the value_in_use column.

Self-configuring options are those that SQL Server adjusts according to the needs of the system. In most cases, this eliminates the need for setting the values manually. Examples include the min server memory and max server memory options and the user connections option.

The following table lists all available configuration options, the range of possible settings, and default values. Configuration options are marked with letter codes as follows:

• A= Advanced options, which should be changed only by an experienced database administrator or a certified SQL Server technician, and which require setting show advanced options to 1.
  
• RR = Options requiring a restart of the Database Engine.
  
• SC = Self-configuring options.
  

  Configuration option	Minimum value	Maximum value	Default
  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, only available on 64-bit version of SQL Server)	-2147483648	2147483647	0
  affinity mask (A)	-2147483648	2147483647	0
  affinity64 mask (A, RR), only available on 64-bit version of SQL Server	-2147483648	2147483647	0
  Agent XPs (A)	0	1	0 (Changes to 1 when SQL Server Agent is started. Will be 1 if SQL Server Agent is set to automatic start during setup.)
  allow updates (Obsolete. Do not use. Will cause an error during reconfigure.)	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
  filestream_access_level	0	2	0
  fill factor (A, RR)	0	100	0
  ft crawl bandwidth (max) , see ft crawl bandwidth(A)	0	32767	100
  ft crawl bandwidth (min) , see ft crawl bandwidth(A)	0	32767	0
  ft notify bandwidth (max) , see ft notify bandwidth(A)	0	32767	100
  ft notify bandwidth (min) , see 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 (1024 is the maximum recommended for 32-bit SQL Server, 2048 for 64-bit SQL Server.)	0 Zero auto-configures the number of max worker threads depending on the number of processors, using the formula (256+(<processors> -4) * 8) for 32-bit SQL Server and twice that for 64-bit SQL Server.
  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, obsolete)	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 Option (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, obsolete)	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, only appears in SQL Server 2008 Express.)	5	65535	60
  user instances enabled (A, only appears in SQL Server 2008 Express.)	0	1	0
  user options	0	32767	0
  xp_cmdshell (A)	0	1	0

Other Resources

Help and Information