Configuration Option Specifications

SQL Server Architecture

SQL Server Architecture

Configuration Option Specifications

Microsoft® SQL Server™ 2000 contains improved algorithms for controlling computer resources. Many of the options that must be configured manually in earlier versions of SQL Server are managed dynamically in SQL Server 2000. These configuration options are not applicable in SQL Server 7.0 and are marked N/A in this table.

Several configuration options are still specified in SQL Server 2000; however, instead of specifying the size of a static allocation, the options now specify the upper limit for the number of objects allocated dynamically as needed. These options are marked with an asterisk (*) in this table. The information in this table does not pertain to Microsoft® SQL Server 2000™ Windows® CE Edition.

  SQL Server version 7.0 SQL Server 2000
Configuration values Minimum Maximum Minimum Maximum
affinity mask 0 2,147,483,647 0 2,147,483,647
allow updates 0 1 0 1
AWE enabled N/A N/A 0 1
c2 audit mode N/A N/A 0 1
cost threshold for parallelism 0 32,767 0 32,767
cursor threshold -1 2,147,483,647 -1 2,147,483,647
default full-text language N/A N/A 0 2,147,483,647
default language 0 9,999 0 9,999
default sort order id 0 255 N/A N/A
extended memory size (MB) 0 2,147,483,647 N/A N/A
fill factor (%) 0 100 0 100
index create memory (K) 704 1,600,000 704 2,147,483,647
language in cache 3 100 N/A N/A
language neutral full-text indexing 0 1 N/A N/A
lightweight pooling 0 1 0 1
locks 5,000* 2,147,483,647* 5,000* 2,147,483,647*
max async IO 1 255 N/A N/A
max degree of parallelism 0 32 0 32
max server memory (MB) 4* 2,147,483,647* 4* 2,147,483,647*
max text repl size 0 2,147,483,647 0 2,147,483,647
max worker threads 10 1,024 10 32,767
media retention 0 365 0 365
min memory per query (K) 512 2,147,483,647 512 2,147,483,647
min server memory (MB) 0* 2,147,483,647* 0* 2,147,483,647*
nested triggers (bytes) 0 1 0 1
network packet size 512 65,535 512 65,532
open objects 0* 2,147,483,647* 0* 2,147,483,647*
priority boost 0 1 0 1
query governor cost limit 0 2,147,483,647 0 2,147,483,647
query wait (sec) -1 2,147,483,647 -1 2,147,483,647
recovery interval (min) 0 32,767 0 32,767
remote access 0 1 0 1
remote login timeout (sec) 0 2,147,483,647 0 2,147,483,647
remote proc trans 0 1 0 1
remote query timeout (sec) 0 2,147,483,647 0 2,147,483,647
resource timeout 5 2,147,483,647 N/A N/A
scan for startup procs 0 1 0 1
set working set size 0 1 0 1
show advanced options 0 1 0 1
spin counter 1 2,147,483,647 N/A N/A
time slice 50 1,000 N/A N/A
two digit year cutoff 1,752 9,999 1,752 9,999
unicode comparison style 0 2,147,483,647 N/A N/A
unicode locale id 0 2,147,483,647 N/A N/A
user connections 0* 32,767 (server)* 1 0* 32,767 (instance)* 1
user options 0 4,095 0 16,383

* Lower or upper limit for objects allocated dynamically.
1 The concurrent workload governor in SQL Server 2000 Personal Edition and SQL Server 2000 Desktop Engine limits performance when more than 5 batches are executed concurrently.