Parameters

3 4

You set SQL Server configuration parameters in one of two ways: by selecting database properties in SQL Server Enterprise Manager or by using the system stored procedure sp_configure. The Enterprise Manager method of setting parameters is described later in this appendix. To use the sp_configure stored procedure, you run the following command:

 sp_configure 'parameter_name', value 

For example, you can set the max worker threads configuration parameter to 200 by using the following command:

 sp_configure 'max worker threads', 200 Go 

The SQL Server configuration parameters are briefly described in the following sections. Complete descriptions of these parameters can be found in SQL Server Books Online

affinity mask

The affinity mask parameter is a bitmap variable that specifies the CPUs that SQL Server is allowed to run on. The default value of 0 lets the Microsoft Windows NT/2000 scheduler determine which CPUs SQL Server will run on. Because the variable is a bitmap, the binary representation of the value determines which CPUs will be used. The first few binary values are shown below.

 0=0000 1=0001 2=0010 3=0011 4=0100 

For example, if you use a four-processor system, you can set affinity mask to 15 (1111) to allow SQL Server to run on all CPUs.

allow updates

The allow updates parameter allows users with the appropriate privileges to update system tables directly. When allow updates is set to 0 (the default), the system tables can be updated only by using the system stored procedures.

awe enabled

The awe enabled parameter allows the use of the Address Windowing Extensions (AWE) option in Microsoft Windows 2000. When awe enabled is set to 1, the use of memory above 4 gigabytes (GB) is allowed.

C2 audit mode

The C2 audit mode parameter enables C2 security mode auditing. This security auditing logs specific events in SQL Server in order to achieve the C2 security level.

cost threshold for parallelism

The cost threshold for parallelism parameter specifies a cost to be used in determining whether parallelism is used for queries. If the cost of the query in serial mode exceeds the value of cost threshold for parallelism, the query will be parallelized. The default value is 5.

cursor threshold

The cursor threshold parameter specifies the number of rows in the cursor set that must be exceeded before the cursor keysets are created asynchronously. If the number of rows is less than the value of cursor threshold, the keysets will be created synchronously. The default value of -1 specifies that all cursor keysets will be created synchronously.

default full-text language

The default full-text language parameter specifies the ID of the default language used by SQL Server full-text indexing. The default of 1033 is the ID of U.S. English.

default language

The default language parameter specifies the ID of the default language used by SQL Server. The default of 0 is the ID of U.S. English.

fill factor

The fill factor parameter specifies how densely SQL Server will pack index pages when they are created. A value of 1 specifies that pages be mostly empty; a value of 100 causes index pages to be completely packed. The default value of 0 specifies that leaf pages be fully packed but that nonleaf pages should have some space left available.

index create memory

The index create memory parameter specifies the amount of memory used for index creation sorts. The default value of 0 specifies that SQL Server will determine this value.

lightweight pooling

The lightweight pooling parameter, which is set to TRUE (1) or FALSE (0), specifies whether SQL Server will use Windows NT/2000 fiber mode scheduling to reduce context switching. Context switches incur a lot of system overhead and will be reduced if SQL Server is allowed to do its own scheduling. The default value is 0, which specifies that fiber mode scheduling not be used.

locks

The locks parameter specifies the maximum number of locks that can be allocated. The default value of 0 allows SQL Server to dynamically allocate and deallocate locks. You can monitor the number of locks in your system by using Windows NT or Windows 2000 Performance Monitor; if you see a lot of allocation and deallocation, you might want to statically allocate locks. In most cases, however, the default value of 0 should be used.

max degree of parallelism

The max degree of parallelism parameter specifies the maximum number of threads that can be allocated for use in a parallel execution. The default value of 0 specifies that all CPUs in the system can be used, making the number of threads equal to the number of CPUs in the system. A value of 1 disables parallel execution. Because parallelism can help improve performance of I/O-bound queries, you might find that better performance can be achieved if you set a higher value for max degree of parallelism. The maximum value is 32.

max server memory

The max server memory parameter is used to specify the maximum amount of memory that can be dynamically allocated by SQL Server. You use this parameter in conjunction with min server memory. The amount of memory allocated by SQL Server will be between the value set for min server memory and max server memory. If you want to reserve additional space for processes other than SQL Server, you can use this parameter. The default value of 0 specifies that SQL Server automatically allocate memory.

max text repl size

The max text repl size parameter specifies the maximum number of bytes of text and image data that can be added to a replicated column in a single SQL statement.

max worker threads

You use the max worker threads parameter to specify the maximum number of Windows threads that SQL Server can use. This parameter can be adjusted in order to allow more threads for processing within SQL Server. If SQL Server uses too many threads, however, the operating system will become overloaded.

media retention

The media retention parameter specifies the number of days that a backup medium is retained. SQL Server will not overwrite a backup medium until this time has passed.

min memory per query

The min memory per query parameter specifies the minimum amount of memory that will be allocated for a query. The default value is 1024, but you can choose a value from a large range of values between 512 bytes and 2 GB. Setting this parameter such that memory is allocated when the query begins can help the performance of large sorts and hashing operations.

min server memory

The min server memory parameter is used in conjunction with max server memory to manually set the minimum amount and maximum amount of memory that SQL Server will use. The default value of 0 specifies that SQL Server automatically allocate memory.

nested triggers

The nested triggers parameter specifies whether a trigger can initiate another trigger. The default value of 1 specifies that this be allowed.

network packet size

With the network packet size parameter, you can specify the size of incoming and outgoing SQL Server data packets. The default value of 4096 specifies a packet size of 4 kilobytes (KB). If many of your result sets are large, you might want to increase this value.

open objects

The open objects parameter specifies the maximum number of objects that can be opened at one time in the SQL Server database. The default number of open objects is 500 with a maximum of 2 billion.

priority boost

A value of 1 for the priority boost parameter specifies that SQL Server run at a higher Windows NT/2000 scheduling priority than it normally would. The default value of 0 disables priority boost. Setting this parameter to 1 can improve SQL Server performance but can keep other processes from getting sufficient CPU time. Only if SQL Server is the only program running on the Windows NT system should you set this value. Changing this value can cause problems if you are not careful. Change this parameter at your own risk.

query governor cost limit

The query governor cost limit parameter specifies the maximum amount of time, in seconds, that a query can run. Before a query is executed, Query Optimizer estimates how long the query will run. When set, this option prevents large queries from running.

query wait

When sufficient memory for a query to run is not available, SQL Server will queue the query until the resources are available. By default, the wait time is 25 times the estimated cost of the query. By setting the query wait parameter, you can set the time-out value.

recovery interval

The recovery interval parameter is quite important. The setting of the recovery interval parameter determines how often checkpoints occur by specifying the maximum amount of time that SQL Server can take to recover the database in the event of a system failure. The default value of 0 lets SQL Server determine this value automatically.

remote access

The remote access parameter is a Yes/No parameter that specifies whether logins from remote SQL Server systems are allowed. The default value of 1 allows remote logins.

remote login timeout

The remote login timeout parameter specifies how long, in seconds, a remote login waits before timing out. The default value is 5.

remote proc trans

When you set the remote proc trans parameter to 1, remote transactions running under Microsoft Distributed Transaction Coordinator (MS DTC) support ACID properties of the transactions.

remote query timeout

The remote query timeout parameter specifies how many seconds must elapse before a remote query times out. The default value of 0 specifies that queries do not time out.

scan for startup procs

The scan for startup procs parameter is a Yes/No parameter that specifies whether SQL Server will automatically scan for automatic execution of stored procedures. The default value of 0 specifies that SQL Server will not scan for these stored procedures, and thus they will never be executed.

set working set size

The set working set size parameter works in conjunction with min server memory and max server memory. When set, the set working set size parameter specifies that SQL Server memory not be paged out, even when SQL Server is idle. If you are allowing SQL Server to allocate memory dynamically, do not set this parameter to 1. The default value of 0 disables set working set size.

show advanced options

When show advanced options is set to 1, you can use the sp_configure stored procedure to display the advanced parameters.

two digit year cutoff

The two digit year cutoff parameter specifies how Y2K behavior works in SQL Server. For dates that are two digits in length, a value less than this number indicates a 20xx date, and a value greater than this setting specifies a 19xx date. For example, if two digit year cutoff were set to 2049 (the default), a date of 51 would be interpreted as 1951 and a date of 48 would be interpreted as 2048.

user connections

Use the user connections parameter to specify the maximum number of users that can be connected into SQL Server. By default, SQL Server will dynamically adjust the number of allowed user connections, but this dynamic operation causes additional overhead. This parameter allows you to statically set the number of allowed user connections.

user options

The user options parameter is used to specify global defaults for all users.