The memory option controls the maximum number amount of memory that is allocated to SQL Server in MB. Only in rare cases should this value be set to anything other than zero.

A setting of 0, which allows SQL Server to dynamically manage memory requirements. The lazywriter periodically queries the system for free memory. It expands or shrinks the buffer cache to maintain the free memory on the system at approximately 5MB. If the free memory for the system falls below approximately 5MB, NT will begin to page to disk.

If free memory falls below 5MB, the lazywriter releases memory from the buffer cache to bring the free memory back above the threshold. This process only occurs for a server under load. If there is no activity on the server, the buffer cache is not expanded as memory gets is freed on the system.

If you need to manually configure the memory setting, the maximum amount of RAM that can be addressed by SQL Server is 3072 MB or 3GB. If you manually configure the memory and allocate too much memory to SQL Server, it will fail to start.

nested triggers

The nested triggers option controls whether triggers will nest or cascade execution. If set to zero (0), SQL Server will only executes only the first trigger that fires when an update or delete action occurs.

network packet size

The network packet size controls the maximum network packet size that is requested by a client.

The default packet size on a TCP/IP network is 4096 bytes. If the vast majority of client requests ask for significantly more than 4096 bytes, then performance and network throughput can be improved by increasing the network packet size. This will allow SQL Server to batch more data together and send it across the network in larger bundles than normal. This does reduce the number of packets being transmitted, but does place a larger bandwidth requirement on the network. Reducing the network packet size should not be attempted since this will cause a very large number of packets to be transmitted which can cause a severe drain on network bandwidth.

open objects

A open object can be thought of as a descriptor or pointer to a memory structure where an object resides. The number of open objects controls how large of a pool of descriptors is allocated by SQL Server. Each object allocated does incur an overhead so you should be careful to not seriously overallocate and take valuable memory away from SQL Server. In most cases, the number of open objects allocated should not significantly exceed 2 times the number of objects on a server.

priority boost

Processes inside NT run at different priority levels which determine the amount of the time slice each process receives from the processor. The priority boost will cause SQL Server processes to run at a higher priority than normal which can improve response. However, this option should never be set unless explicitly directed to do so my Microsoft Technical Support. NT does a very good job of managing processes to get the best overall response time. Changing the priority can have several severe consequences as SQL Server pushes critical NT processes into the background. Under severe loads, setting the priority boost can cause your server response to slow to a crawl or even cause a server crash.

recovery interval

The recovery interval controls the amount of time in minutes that SQL Server will require to recover a database. Essentially this interval will control the frequemcy of the checkpoint process.

When a server is started back up after a crash, any open transactions are rolled back and any committed transactions that have not yet been written to disk are saved to disk. The checkpoint process will execute periodically to flush any changed data from memory structures to disk. The frequency of the checkpoint process will ensure that the amount of time required to recover a database is met.

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