.gif)
This topic applies only to SQL Server 2000 (64-bit).
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 settings can be viewed with the stored system procedure sp_configure.
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 the SQL Server 2000 Windows® CE Edition.
| | SQL Server 2000 (32-bit) | SQL Server 2000 (64-bit) |
| Configuration values | Minimum | Maximum | Minimum | Maximum |
| affinity mask | -2,147,483,648 | 2,147,483,647 | -2,147,483,648 | 2,147,483,647 |
| affinity64 mask | | | -2,147,483,648 | 2,147,483,647 |
| allow updates | 0 | 1 | 0 | 1 |
| AWE enabled1 | 0 | 1 | 0 | 1 |
| c2 audit mode | 0 | 1 | 0 | 1 |
| cost threshold for parallelism | 0 | 32,767 | 0 | 32,767 |
| Cross DB Ownership Chaining | 0 | 1 | 0 | 1 |
| cursor threshold | -1 | 2,147,483,647 | -1 | 2,147,483,647 |
| default full-text language | 0 | 2,147,483,647 | 0 | 2,147,483,647 |
| default language | 0 | 9,999 | 0 | 9,999 |
| fill factor (percent) | 0 | 100 | 0 | 100 |
| index create memory (K) | 704 | 2,147,483,647 | 704 | 2,147,483,647 |
| IO_affinity_mask2 | -2,147,483,648 | 2,147,483,648 | -2,147,483,648 | 2,147,483,648 |
| lightweight pooling | 0 | 1 | 0 | 1 |
| locks | 5,000* | 2,147,483,647* | 5,000* | 2,147,483,647* |
| 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 | 32,767 | 32 | 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,536 |
| 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 |
| scan for startup procs | 0 | 1 | 0 | 1 |
| set working set size | 0 | 1 | 0 | 1 |
| show advanced options | 0 | 1 | 0 | 1 |
| two digit year cutoff | 1,752 | 9,999 | 1,753 | 9,999 |
| user connections | 0* | 32,767 (server)* 3 | 0* | 32,767 (instance)* 3 |
| user options | 0 | 32,767 | 0 | 32,767 |
* Lower or upper limit for objects allocated dynamically.
1 This configuration parameter exists but is ignored by the 64-bit version of SQL Server 2000.
2 The IO affinity can be a hex, octal, or decimal string. On Win64™, the range can be 0 to 0xffffffffffffffff in hex, or 1 to 18446744073709551615 decimal. Invalid representations will be set to 0. Note: The IO_affinity_mask switch should be used in conjunction with the affinity mask configuration option. Users should not enable a CPU for both the IO_affinity_mask switch and affinity_mask options. See Microsoft Knowledge Base article Q298402 on the Microsoft Web site for additional detail.
3 The concurrent workload governor in SQL Server 2000 Personal Edition limits performance when more than 5 batches are executed concurrently.