Home | Articles | Functions | Tips & Tricks | SQL Server 2005 | SQL Server 2008 | SQL Server 2012 | SQL Server 2014 | SQL Server 2016 | FAQ | Practice Test |    
Tip of the Day : Example Uses of the CHARINDEX Function
Home > SQL Server 2005 > Configuration Options
Configuration Options Comparison Table

Configuration Options Comparison Table

The following table compares all available configuraion options for both SQL Server 2000 and SQL Server 2005, showing 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.
  SQL Server 2000 SQL Server 2005
Configuration Option Minimum Value Maximum Value Default Minimum Value Maximum Value Default
Ad Hoc Distributed Queries (A) N/A N/A N/A 0 1 0
affinity I/O mask (A, RR) N/A N/A N/A -2147483648 2147483647 0
affinity64 I/O mask (A) 1 N/A N/A N/A -2147483648 2147483647 0
affinity mask (A, RR) 0 2147483647 0 -2147483648 2147483647 0
affinity64 mask (A) 1 N/A N/A N/A -2147483648 2147483647 0
Agent XPs (A) N/A N/A N/A 0 1 0 2
allow updates 3 0 1 0 0 1 0
awe enabled (A, RR) 0 1 0 0 1 0
blocked process threshold (A) N/A N/A N/A 0 86400 0
c2 audit mode (A, RR) 0 1 0 0 1 0
clr enabled N/A N/A N/A 0 1 0
cost threshold for parallelism (A) 0 32767 5 0 32767 5
cross db ownership chaining N/A N/A N/A 0 1 0
cursor threshold (A) -1 2147483647 -1 -1 2147483647 -1
Database Mail XPs (A) N/A N/A N/A 0 1 0
default full-text language (A) 0 2147683647 1033 0 2147483647 1033
default language 0 9999 0 0 9999 0
default trace enabled (A) N/A N/A N/A 0 1 1
disallow results from triggers (A) N/A N/A N/A 0 1 0s
fill factor (A, RR) 0 100 0 0 100 0
ft crawl bandwidth (max) (A) N/A N/A N/A 0 32767 100
ft crawl bandwidth (min) (A) N/A N/A N/A 0 32767 0
ft notify bandwidth (max) (A) N/A N/A N/A 0 32767 100
ft notify bandwidth (min) (A) N/A N/A N/A 0 32767 0
index create memory (A, SC) 704 2147483647 0 704 2147483647 0
in-doubt xact resolution (A) N/A N/A N/A 0 2 0
lightweight pooling (A, RR) 0 1 0 0 1 0
locks (A, RAR, SC) 5000 2147483647 0 5000 2147483647 0
max degree of parallelism (A) 0 32 0 0 64 0
max full-text crawl range (A) N/A N/A N/A 0 256 4
max server memory (A, SC) 4 2147483647 2147483647 16 2147483647 2147483647
max text repl size 0 2147483647 65536 0 2147483647 65536
max worker threads (A, RR) 32 32767 255 128 32767 4 0 5
media retention (A, RR) 0 365 0 0 365 0
min memory per query (A) 512 2147483647 1024 512 2147483647 1024
min server memory (A, SC) 0 2147483647 0 0 2147483647 8
nested triggers 0 1 1 0 1 1
network packet size (A) 512 65536 4086 512 32767 4096
Ole Automation Procedures (A) N/A N/A N/A 0 1 0
open objects (A, RR, SC) 3 0 2147483647 0 0 2147483647 0
PH timeout (A) N/A N/A N/A 1 3600 60
precompute rank (A) N/A N/A N/A 0 1 0
priority boost (A, RR) 0 1 0 0 1 0
query governor cost limit (A) 0 2147483647 0 0 2147486347 0
query wait (A) -1 2147483647 -1 -1 2147483647 -1
recovery interval (A, SC) 0 32767 0 0 32767 0
remote access (RR) 0 1 1 0 1 1
remote admin connections N/A N/A N/A 0 1 0
remote login timeout 0 2147483647 20 0 2147483647 0
remote proc trans 0 1 0 0 1 0
remote query timeout 0 2147483647 600 0 2147483647 600
scan for startup procs (A, RR) 0 1 0 0 1 0
server trigger recursion N/A N/A N/A 0 1 1
set working set size (A, RR) 3 0 1 0 0 1 0
show advanced options 0 1 0 0 1 0
SMO and DMO XPs (A) N/A N/A N/A 0 1 1
SQL Mail XPs (A) N/A N/A N/A 0 1 0
transform noise words (A) N/A N/A N/A 0 1 0
two digit year cutoff (A) 1753 9999 2049 1753 9999 2049
user connections (A, RR, SC) 0 32767 0 0 32767 0
User Instance Timeout (A) 6 N/A N/A N/A 5 65535 60
user instances enabled (A) 6 N/A N/A N/A 0 1 0
user options 0 32767 0 0 32767 0
Web Assistant Procedures (A) N/A N/A N/A 0 1 0
xp_cmdshell (A) N/A N/A N/A 0 1 0

1 Only available on 64-bit version of SQL Server.

2 Changes to 1 when SQL Server Agent is started.  Will be 1 if SQL Server Agent is set to automatic start during setup.

3 This option is already obsolete starting with SQL Server 2005.

4 1024 is the maximum recommended for 32-bit SQL Server, 2048 for 64-bit SQL Server.

5 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.

6 Only appears in SQL Server 2005 Express Edition.

References: