Server, Database, and Query Options

Server Options

Server options affect the behavior of SQL Server on a global scale. There are a significant number of configuration options available for tuning a server. The first thing many inexperienced DBAs will do is tweak as many server options as possible. This is the wrong approach. In many cases, the default settings will be sufficient in most environments. Only under special circumstances should you change the configuration options available.

Many of the options described below have a effect on the performance of the SQL Server. These options cut both ways. Properly tuning a server can increase performance. Improperly tuning a server can decrease performance and cause instabilities. The best rule of tumb is: "If it ain’t broke, don’t fix it." If server performance is acceptable, you should not change configuration parameters. Only when you can demonstrate a significant performance improvement or the performance is unacceptable, should you change options.

affinity mask

SQL Server is a multi-threaded database. It also has the capability to run on multi-processor machines. The affinity mask controls the behavior of SQL Server under SMP machines. It will generally have an effect on machiens with more than 4 processors. The affinity mask will cause a particular thread to ave an "affinity" for a particular processor. This allows the processor to run more efficiently since its onboard cache does not have to be purged of data as often.

allow updates

This is an option that should never be set on a server in production. It should also only be set in extreme cases and only long enough to correct a problem.

The system tables contain all of the information about your environment such as databases, users, configuration options, etc. This information is added to the system tables in a variety of manners, but generally when creating an object. Some information is entered using system stored procedures. These are the only two methods that should be used.

Normally, you can not issue direct SQL statements such as insert, update, and delete against the system tables. By turning on allow updates, you give the ability to directly modify the contents of the system tables. If someone issues an incorrect statement while this option is turned on, you will find yourself scrambling for the most recent backup.

Only turn this option on when directed to do so my Microsoft technical suport or as a last resort to fix a problem.

cost threshold for parallelism

SQL Server 7.0 has added capability to perform parallel queries. This gives the server the ability to split a large, long running query over several processors in order to take full advantage of the hardware that is available. This obviously only works on multi-processor machines. The cost threshold determines the point at which SQL Server will switch from a serial query to a parallel query.

cursor threshold

The cursor threshold determines how SQL Server builds the result set for a cursor request. The result set can be built either synchronously or asynchronously. Synchronous works best for small result sets and asychronous works best for large result sets.

The default setting is to build synchronously. Before considering changing this option to asynchronous operation, you should carefully evaluate whether you really need the cursor. In my experience, cursors are rarely needed. If you find yourself needing to use a large number of cursor with large result sets, you need to redesign the database.

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