The max degree of parallelism controls the threads allocated to parallel query processing on SMP machines.

max additional query mem

The max additional query mem specifies the maximum amount of memory, in KB, that is allocated per user for queries above the memory required for the query.

max query wait

The max query wait controls the time out period in seconds for long running queries.

max repl text size

The max repl text size specifies the maximum amount of data, in bytes, that can be added to a replicated column in a single insert, update, writettext, or updatetext command.

max worker threads

SQL Server will spawn threads to handle each of the processes currently running on the server. The max worker threads specifs the maximum number of threads that SQL Server can spawn. SQL Server will spawn, by default, at least one thread for each listener service such as the SQL Server Agent. Additional threads are spawned for checkpointing, lazywriting, and the read ahead manager.

The checkpointing process is essentially a heartbeat for the server. It occurs approximately once per minute and is the process that writes changed pages of data that are currently cached from memory directly to disk.

Instead of immediately writing every transaction to disk, SQL Server batches these writes for more efficient writing of data. The lazywriting process manages this process of batching writes together into a single group so that they can be written to disk at the same time.

The rest of the available threads are allocated for user processes that are making requests. If the number of users is greater than the number of available threads allocated by the server, up to the maximum configured, then SQL Server will use the available threads in a pooling fashion. The next request by a user process that is received at the server will be assigned to the first thread that becomes available after it has completed its assigned task.

You should generally set this value at approximately 1.5 – 2 times the number of user connections configured for the server. This ensures that a free worker thread will be available to service a request. If the number of requests exceeds the number of worker threads, processes will begin to accumulate on the server waiting to be processed. You can monitor this with the command queue length counter to ensure that it remains at or near zero.

media retention

The media retention controls the amount of time, in days, that backup media is protected from overwriting. If a backup is attempted to meedia that has not exceeded the retention period, a warning message will be generated.

SQL Server 7.0 Server, Database, and Query options 1 2 3 4 5 6 7 8