Other Server-Level Configuration Options

From SQLServerPedia

Jump to: navigation, search
See Also: Main_Page - Architecture & Configuration - Configuring SQL Server - Server-Level Configuration

Contents

Allow Updates



This option allows members of the SYSADMIN fixed server role to make changes to values stored in system tables directly, without using system stored procedures. This option has no impact on performance and takes effect without restarting SQL Server.

C2 Audit Mode



This option lets you audit all successful and failed attempts to execute SQL statements and access objects on your database server. The default value is 0.

When turned on, 'C2 Audit Mode' initiates a trace on your server collecting numerous events without any filters. This can cause serious performance degradation on a production server with dozens of users. Furthermore, if SQL Server can't write to the directory where your trace file is located, (if the drive fails, or fills up), the SQL Server service will be automatically stopped.

Unless you have strict security requirements you should not use this option. C2 Audit Mode is an advanced option and requires restarting of SQL Server to take effect. This option can only be configured using sp_configure.

Cursor Threshold



A cursor is a temporary holding area for data returned from a table or multiple tables which will be further manipulated within a cursor. Cursors operate on one row at a time. Typically you should try to avoid cursors because Transact-SQL is a set based language and is optimized for operations on large sets. You will often find it beneficial to rewrite cursors with queries using INSERT, UPDATE, DELETE statements with joins. However, there are times when you can't help but use a cursor.

The Cursor Threshold setting determines the way in which the cursor structure is populated. By default this setting has a value of -1 (minimum supported value), which advises SQL Server to populate all cursors synchronously; this means the entire cursor has to be populated before you can fetch any rows from the cursor. If you specify the value of 0 for this option, all cursors will populate asynchronously. During asynchronous population of cursors users can fetch data from a cursor while it is still being populated.

If Cursor Threshold has a positive value, then whether cursor is populated asynchronously depends on the total number of rows within a cursor. If SQL Server estimates that a cursor will have more rows than specified by Cursor Threshold then this cursor will be populated asynchronously; otherwise synchronous population will be used instead.

Unless you use some sort of batch processing that has to populate a cursor with hundreds of thousands of rows you should leave this option at its default. If you do have such processing you might consider altering Cursor Threshold to force asynchronous population. If you truly wish to optimize your performance, however, you should consider rewriting your batch processing job with joins instead of using cursors.

Cursor Threshold is an advanced option and can be changed through sp_configure. It takes effect immediately.

Default Full-Text Language



The Default full-text Language option determines the default language for full-text indexes. The default value of this option is the language of the server. This option has no effect on SQL Server performance.

Default full-text Language is an advanced option and takes effect immediately.

Default Language



This option specifies the default language at creation of the new login. This option has no impact on performance. Default Language is not an advanced option. It takes effect immediately.

Fill Factor / Index Create Memory



Indexes are data structures that help SQL Server find data within a table. Indexes improve the performance of SELECT queries greatly. However, if a table has too many indexes then INSERT, UPDATE and DELETE queries might become slower. This happens because SQL Server has to maintain indexes while modifying data.

SQL Server stores data and indexes in a storage unit called a page. When an index page fills up due to additional INSERTS SQL Server will have to move approximately half of the data on this page to a new page. This is known as a "page-split". Page-splits add some overhead to the system since a new index page has to be allocated and then data must be moved. Therefore, reducing the number of page-splits can preserve system resources and improve performance.

Index fill factor determines the percentage of an index page that must be full before a page-split occurs. The minimum (and default) value for this setting is 0, which advises SQL Server to fill up the leaf level of index pages 100%, but leave some room on the branch level pages. If you specify fill factor of 100, all pages (branch or leaf) will be completely filled up at creation. Notice that fill factor only makes a difference in how an index is initially created, because SQL Server does not maintain the fill factor. If you need to ensure low fill factor you'll have to drop and re-create the index.

In a read-only database it makes sense to fill all index pages to the brim since you won't be adding any data. Lower fill factor means more data pages and therefore larger index, which will take up more space. In transactional databases, on the other hand, you should allow some room for growth to index pages at index creation.

The Fill Factor setting governs the fill factor for all new indexes on the server level. You can override this setting within CREATE INDEX statements when adding individual indexes.

Typically a fill factor of 60 - 80% should perform the best for transactional tables, however feel free to experiment with this value based on the number of INSERTS you expect for a particular table.

Fill Factor is an advanced option and can be changed through Enterprise Manager or by using sp_configure. This option takes effect after restarting SQL Server.

Creating indexes consumes significant amount of resources. Index creation will often lock the entire table. Therefore you want SQL Server to complete index creation as soon as possible. Furthermore, if possible, you should rebuild indexes only during periods of limited user activity.

The Index Create Memory option controls the amount of memory (in Kilobytes) used for creating indexes. The default value of 0 allows SQL Server to manage this setting dynamically as needed. This setting should perform fine in most environments. However, if you experience problems with index creation you can adjust this setting to fit your needs. This option takes effect immediately. Index Create Memory is an advanced option and can be configured through Enterprise Manager as well as sp_configure.

Max Text Repl Size



This option specifies the maximum size of TEXT and IMAGE data that can be added to a replicated column in a single INSERT or UPDATE statement. This value is specified in bytes and defaults to 65536. You can change this option if you expect to replicate larger additions to your TEXT / IMAGE columns.

Max Text Repl Size is not an advanced option and it takes effect immediately. In environments without replication this setting can be ignored.

If you intend to use replication you should avoid TEXT and IMAGE data types in your database schema. It is extremely difficult to predict the maximum size of data added to such columns. In addition, the size of snapshots grows very fast if you try to replicate large TEXT data. Large snapshots will take longer to generate and longer to apply at the subscribers. If you do use replication and must have TEXT data types, attempt to refresh tables with TEXT columns nightly to reduce the overhead on the system.

Books On-Line incorrectly states that TEXT columns cannot be replicated.

Media Retention



This option determines the number of days before a backup media can be overwritten. Media Retention defaults to 0 days. This is an advanced option and takes effect after restarting SQL Server.

This setting has no effect on performance.

Nested Triggers



Triggers are used to perform a set of actions each time a row is inserted, updated or deleted from a table. Triggers always execute within a transaction, so if an error occurs the data modification initiating the trigger will be rolled back along with any work done within a trigger.

The Nested Triggers option (sometimes referred to as cascading triggers) controls whether an action within a trigger can initiate another trigger on a different table. This is a dangerous setting that can easily allow an infinite loop. The default value of 0 prohibits the use of nested triggers. Do NOT change this value unless you have a good reason to do so.

Nested Triggers is an advanced option. It takes effect immediately and can be changed from Enterprise Manger or sp_configure. By itself the Nested Triggers option has no effect on performance.

Network Packet Size



This option determines the network packet size in bytes sent to and from SQL Server. The default value is 4096, which will suffice in most cases. If your server has to handle large bulk copy operations with BCP or DTS, or if you perform a lot of TEXT and IMAGE read / write activity, then performance might benefit from a larger packet size. We recommend leaving this option at its default value.

Network Packet Size is an advanced option. It takes effect immediately and can be changed through sp_configure.

Open Objects



This setting controls the number of database objects that SQL Server can have open at one time. Database objects are tables, views, stored procedures, user-defined functions, etc.

By default, this option has a value of 0, which advises SQL Server to tune this setting depending on system's needs. We recommend leaving this setting alone.

Open objects is an advanced option and takes effect after SQL Server is restarted. This option can only be changed through sp_configure.

Query Governor Cost Limit



This option specifies the number of seconds that a query can run for. If you specify a non-negative value (the default is 0) for this option, queries exceeding that value will be disallowed. The default value allows all queries.

We recommend leaving Query Governor Cost Limit at its default. Unless you expect some huge queries that will keep majority of your users from accessing the database you should not modify this option. By itself Query Governor Cost Limit has no effect on performance.

Query Governor Cost Limit is an advanced option and takes effect immediately. This option can be changed only through sp_configure.

Query Wait



This option specifies the number of seconds a query will wait for resources until timing out. Memory intensive queries will queue up until an appropriate amount of resources are available. By default Query Wait has a value of -1, which means it will wait 25 times the estimated cost of execution (in seconds) until timing out.

We recommend leaving this option at default. Keep in mind that a query that is waiting on resources might be keeping a transaction open and holding locks. Therefore if you specify a large value for this setting it might reduce the concurrency on your system. Specifying a really low value, on the other hand, can mean that queries returning large result sets will timeout.

Query Wait is an advanced option and takes effect immediately. This option can be changed using sp_configure.

Recovery Interval



The Recovery Interval setting specifies the maximum number of minutes it will take the server to recover from failure. "Recovery" in this case means preserving data integrity. When SQL Server starts up it checks for any active transactions in the transaction log of each database. All committed transactions are written to disk and uncommitted transactions are rolled back.

SQL Server maintains the recovery interval by issuing CHECKPOINTS periodically. CHECKPOINT flushes all the modified data pages to disk, so that transactions that occurred prior to checkpoint do not need to be re-examined during recovery. The more often SQL Server checkpoints a database, the less time it will require to recover. The flipside of this coin is that checkpoint causes intensive writes to disk and therefore slows down user transactions for a while.

By default, Recovery Interval has a value of 0 and is managed by SQL Server. Default recovery time is approximately 1 minute. If checkpoints are occurring too frequently you can modify this value at the risk of longer recovery times at next startup.

Notice that if you have a long running transaction, such as building a clustered index on a table with millions of rows recovery will take considerably longer than specified with Recovery Interval. That is because the long running transaction will also take a long time to undo.

Recovery Interval is an advanced option and can be configured through Enterprise Manager or sp_configure. It takes effect immediately.

Remote Access



The Remote Access setting controls whether users can execute remote stored procedures (RPC - remote procedure calls) from remote servers and access your server. The default value is 1, which allows RPC.

This option has no effect on performance. However, if you wish to use replication or setup linked servers you must allow Remote Access.

This option takes effect immediately and can be configured through Enterprise Manager or sp_configure.

Remote Login Timeout



This option specifies the number of seconds to wait until returning from a failed attempt to connect to SQL Server remotely. The default value is 20; changing this value to 0 will cause waiting indefinitely.

This option has no effect on performance and takes effect immediately. Remote Login Timeout can be changed only through sp_configure.

Remote Proc Trans



If this option is set to 1, SQL Server is explicitly advised to use Distributed Transaction Coordinator to protect the ACID properties of every distributed transaction. In order to use MS DTC, both SQL Servers participating in a distributed transaction must have this option turned on. If this option is true on one server and not on the other you will receive an error that MS DTC is unable to enforce a distributed transaction, even if you have MS DTC running on both servers.

This option has no effect on performance and takes effect immediately. Remote Proc Trans can be changed through Enterprise Manager, which refers to it as "Enforce Distributed Transactions (MTS)" , or through sp_configure.

Remote Query Timeout



This option specifies the number of seconds to wait until SQL Server assumes that a remote command failed or timed out. The default value is 600. Feel free to modify this value according to your remote querying needs. This setting has no effect on performance.

Remote Query Timeout can be changed through Enterprise Manager or sp_configure. It takes effect immediately.

Scan for Startup Procs



This option determines whether SQL Server should scan for stored procedures that are marked for execution at SQL Server startup. Although you can change the default value of 0 (do not scan) with sp_configure you really don't have to. If you create a procedure and mark it for startup with sp_procoption it will automatically change this value of Scan for Startup Procs option to 1.

Scan for Startup Procs is an advanced option and takes effect after restarting SQL Server. It can be changed through sp_configure. This option has no effect on performance.

Show Advanced Options



This option advises SQL Server to return all configurable options when executing sp_configure. By default this option is turned off. This setting has no effect on performance.

Two Digit Year Cutoff



This option advises SQL Server to use a specified year as the cut-off for interpreting two-digit years as four digit years. By default anything less than 50 is considered to belong to 21st century, so '1/1/35' will be interpreted to mean January 1, 2035. Anything greater than or equal to 50, on the other hand, is assumed to belong to 20th century.

This option has no effect on performance. It can be changed through sp_configure or Enterprise Manager and takes effect immediately.

User Connections



This option lets you specify the maximum number of connections SQL Server will allow concurrently. Keep in mind that if using per seat licensing mode, the total number of user connections may not (legally) exceed the number of licenses you have purchased. Furthermore, each connection requires approximately 40Kb of memory, so the total number of connections is also limited by resources you have available for SQL Server.

The maximum number of connections supported by SQL Server is 32767, although the actual number of connections will be significantly less on most systems. The default value for this option is 0, which allows an unlimited number of connections. Leave this option alone unless you have limited number of licenses.

User Connections is an advanced option and takes effect after restarting SQL Server. It can be modified through sp_configure or Enterprise Manager.

User Options



This option lets you specify a bitmap with all connection level setting defaults for a new login. Within user options you can specify whether ANSI warnings are issued, whether row count is returned with each query and so forth. This option has no effect on performance. Changes to User Options take effect immediately and can be made through sp_configure.

Cross Database Ownership Chaining



This option is available only with service pack 3 of SQL Server 2000. This option allows a user to own programmable objects such as stored procedures and user-defined functions, which reference objects owned by the same user in a different database. Turning this option on allows for certain security risks since db_ddladmin and db_owner of a single database can create objects in another database, where they don't have such permissions. This setting has no effect on performance.

Automatic Error Reporting



The automatic error reporting feature allows SQL Server to notify Microsoft of internal SQL Server errors when they occur. This option is available only with service pack 3 of SQL Server 2000 and can be turned on only through Enterprise Manager.
Retrieved from "http://sqlserverpedia.com/wiki/Other_Server-Level_Configuration_Options"