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.