The following table lists each configuration option, its minimum and maximum acceptable value, and its default setting.
Many configuration options are considered advanced and should be changed only under very special and usually rare circumstances. Advanced options are displayed or managed only when show advanced option is set to 1. Standard options are shown in bold type and advanced options are shown in plain type.
Dynamic options are noted with an asterisk (*). A new setting for a dynamic option takes effect immediately. A new setting for a non dynamic option takes effect after the server is restarted.
| Option Name |
Minimum Value |
Maximum Value |
Default Value |
|---|
| allow updates* | 0 | 1 | 0 |
| backup buffer size* | 1 | 10 | 1 |
| backup threads | 0 | 32 | 5 |
| cursor threshold* | -1 | 2147483647 | -1 |
| database size | 1 | 10000 | 2 |
| default language | 0 | 9999 | 0 |
| default sortorder id | 0 | 255 | 52 |
| fill factor | 0 | 100 | 0 |
| free buffers* | 20 | 524288 | 204 |
| hash buckets | 4999 | 26500 | 7993 |
| language in cache | 3 | 100 | 3 |
| LE threshold maximum* | 2 | 500000 | 200 |
| LE threshold minimum* | 2 | 500000 | 20 |
| LE threshold percent* | 1 | 100 | 0 |
| locks | 5000 | 2147483647 | 5000 |
| logwrite sleep (ms)* | -1 | 500 | 0 |
| max async IO | 1 | 255 | 8 |
| max lazywrite IO* | 1 | 255 | 8 |
| max worker threads* | 10 | 1024 | 255 |
| media retention | 0 | 365 | 0 |
| memory | 1000 | 1048576 | 4096 |
| nested triggers* | 0 | 1 | 1 |
| network packet size* | 512 | 32767 | 4096 |
| open databases | 5 | 32767 | 20 |
| open objects | 100 | 2147483647 | 500 |
| priority boost | 0 | 1 | 0 |
| procedure cache | 1 | 99 | 30 |
| RA cache hit limit* | 1 | 255 | 4 |
| RA cache miss limit* | 1 | 255 | 3 |
| RA delay* | 0 | 500 | 15 |
| RA pre-fetches* | 1 | 1000 | 3 |
| RA slots per thread | 1 | 255 | 5 |
| RA worker threads | 0 | 255 | 3 |
| recovery flags | 0 | 1 | 0 |
| recovery interval* | 1 | 32767 | 5 |
| remote access | 0 | 1 | 1 |
| remote login timeout* | 0 | 2147483647 | 5 |
| remote query timeout* | 0 | 2147483647 | 0 |
| resource timeout | -1 | 2147483647 | 10 |
| set working set size | 0 | 1 | 0 |
| show advanced option* | 0 | 1 | 0 |
| SMP concurrency | -1 | 64 | 0 |
| sort pages* | 50 | 511 | 64 |
| spin counter* | 1 | 2147483647 | 10000 |
| tempdb in ram (MB) | 0 | 2044 | 0 |
| user connections | 5 | 32767 | 20 |
| * Dynamic option. | |||
For detailed information describing the configuration options and discussing the effects of changing the values, and for information about using sp_configure, see sp_configure System Stored Procedure in the Microsoft SQL Server Transact-SQL Reference.
For more information about setting the memory and tempdb in RAM options, see Setting the Memory Option, and When to Use Tempdb in RAM, later in this chapter.