The configuration options for SQL Server 6.5 are grouped into three major areas:
Server Options. These global options affect all operations on the currently active server. These options apply to all logons on the server and to all databases and other objects that the server owns. Server options are generally used for performance tuning and capacity or object-handling management.
Database Options. These global options affect all operations on the currently active database. Database options are generally used to limit access to the database and to enable high-speed Bulk Copy (BCP) operations.
Query Options. These local options affect only the query that is executing. Query options enable you to tune and monitor the activities of individual queries and display additional statistical information about the query's execution.
SQL Server provides configuration options for many different types of server installations. With these options, you can customize the way SQL Server's resources are managed. Specifically, the configuration options deal with the following management issues:
NOTE: Symmetric Multi-Processing (SMP) computers are computers that conform to a published standard for incorporating more than one CPU in the system unit. SMP computers typically offer substantial performance improvements over single-CPU boxes because they can distribute the processing workload to as many CPUs as are available. Windows NT has been shown to provide near-linear performance improvements on computers with as many as four CPUs. What this statement means is that for every processor you add to the server, you can expect to see a 100 percent performance increase.
SQL Server has two sets of server configuration options available. The default or basic server options deal with common server management issues. The advanced server options deal with components of the server that you normally don't have to adjust. Unless you have been advised by a technical support center to specifically configure one of the advanced options, you should not change any of the advanced options.
CAUTION: Sometimes you may not be able to restart a server after you change its configuration. One reason for this problem may be that you overcommitted memory as a result of your configuration changes. To restart a server in this situation, you may need to start the server in minimal configuration mode, which enables you to bring up the server without it attempting to apply the configuration that you set. For more information on starting the server in minimal configuration mode, see the section called "Starting SQL Server in Minimal Configuration Mode from the Command Line," later in this chapter.
SQL Server provides two ways to display and set configuration options for the server. The graphical method is to use SQL Server Enterprise Manager. The Transact-SQL method uses the system-stored procedure, sp_configure.
Using SQL Enterprise Manager SQL Enterprise Manager is a very convenient tool to use to change server options. The user interface makes it unnecessary to remember the syntax required for sp_configure or to know all the different options. To use the SQL Enterprise Manager to display and set server options, perform the following steps:
FIG. 16.1
After being started, SQL Enterprise Manager shows that no server is selected.
FIG. 16.2
After you select a particular server, SQL Enterprise Manager shows all of its
prop-erties and object folders.
FIG. 16.3
SQL Enterprise Manager's Server Configuration/Options dialog box shows the Configuration
page.
TIP: Press the F1 key on any SQL Enterprise Manager dialog box to display context-sensitive help that explains the available objects/options.
Using sp_configure Another way of changing server settings is to use sp_configure, a system-stored procedure. The sp_configure procedure is useful for writing automated scripts that update the server without user intervention. The syntax for this procedure is as follows:
sp_configure [configuration option, [configuration value]]
The configuration option is text that describes the option that needs to change in the server. SQL Server uses a LIKE operator on the text that is supplied so that you can use any unique set of characters in the description instead of the full text description. Note that SQL Server requires that any text with spaces or other formatting in the configuration option parameter be enclosed in quotation marks. Listing 16.1 shows several slightly different sp_configure statements that perform the same function because they are resolved using this "closest match" approach.
sp_configure "nested Triggers", 0 go sp_confi-- re "nested", 1 go sp_configure "triggers", 0 go sp_configure "trig", 0 go
NOTE: If you don't give parameters to sp_configure, the resulting output is the current status of the server. Listing 16.2 shows an example of the results returned when sp_con-gure is used without a parameter.
The sp_configure information produced in Listing 16.2 includes the advanced options. Notice that the run value for show advanced option is 1. n
/*---------------------------- sp_configure ----------------------------*/ name minimum maximum config_value run_value -------------------- ---------- ---------- ------------ ---------- allow updates 0 1 0 0 backup buffer size 1 10 1 1 backup threads 0 32 5 5 cursor threshold -1 2147483647 -1 -1 database size 1 10000 2 2 default language 0 9999 0 0 default sortorder id 0 255 52 52 fill factor 0 100 0 0 free buffers 20 524288 409 409 hash buckets 4999 265003 7993 7993 language in cache 3 100 3 3 LE threshold maximum 2 500000 10 10 LE threshold minimum 2 500000 20 10 LE threshold percent 1 100 0 0 locks 5000 2147483647 5000 5000 logwrite sleep (ms) -1 500 0 0 max async IO 1 255 8 8 max lazywrite IO 1 255 8 8 max worker threads 10 1024 255 255 media retention 0 365 0 0 memory 1000 1048576 8192 8192 nested triggers 0 1 1 1 network packet size 512 32767 4096 4096 open databases 5 32767 20 20 open objects 100 2147483647 500 500 priority boost 0 1 0 0 procedure cache 1 99 30 30 RA cache hit limit 1 255 4 4 RA cache miss limit 1 255 3 3 RA delay 0 500 15 15 RA prefetches 1 1000 3 3 RA slots per thread 1 255 5 5 RA worker threads 0 255 3 3 recovery flags 0 1 0 0 recovery interval 1 32767 5 5 remote access 0 1 1 1 remote logon timeout 0 2147483647 5 5 remote query timeout 0 2147483647 0 0 resource timeout 5 2147483647 10 10 set working set size 0 1 0 0 show advanced option 0 1 1 1 SMP concurrency -1 64 0 1 sort pages 64 511 128 128 spin counter 1 2147483647 10000 0 tempdb in ram (MB) 0 2044 0 0 user connections 5 32767 20 20 (1 row(s) affected)
If you're having problems using sp_configure and are getting the following error message:
Msg 15125, Level 16, State 1 Only the System Administrator (SA) may change configuration parameters.
you're not logged into the database as the server administrator (SA). Only the SA can change a server configuration. Log off from ISQL/W or the database tool that you are using and reconnect to the database as the SA user.
Understanding the RECONFIGURE Command After executing sp_configure, the server may return the following:
Configuration option changed. Run the RECONFIGURE command to install.
This message means that the server has changed the internal value of the configuration, but has not yet applied it. The output in Listing 16.3 shows that the configuration is changed in the config_value column, but the run_value column remains unchanged.
/*---------------------------- sp_configure "nested" go sp_configure "nested", 0 ----------------------------*/ name minimum maximum config_value run_value -------------------- ---------- ---------- ------------ ---------- nested triggers 0 1 1 1 Configuration option changed. Run the RECONFIGURE command to install. name minimum maximum config_value run_value -------------------- ---------- ---------- ------------ ---------- nested triggers 0 1 0 1
Executing the RECONFIGURE command applies the change to the server as shown in Listing 16.4.
/*---------------------------- reconfigure go sp_configure "nested" ----------------------------*/ name minimum maximum config_value run_value -------------------- ---------- ---------- ------------ ---------- nested triggers 0 1 0 0
The RECONFIGURE command is available only to dynamic configuration options. These options can be changed without shutting down and restarting the server. The following is a list of the dynamic options that can be set with sp_configure and then applied dynamically with RECONFIGURE:
allow updates | RA cache hit limit |
backup buffer size | RA cache miss limit |
free buffers | RA delay |
LE threshold maximum | RA prefetches |
LE threshold minimum | recovery interval |
LE threshold percent | remote logon timeout |
logwrite sleep (ms) | remote query timeout |
max lazywrite IO | resource timeout |
max worker threads | show advanced option |
nested triggers | sort pages |
network packet size | spin counter |
The following is a comprehensive list of all the available server options. If the word advanced appears in parentheses to the right of the keyword, then this option is only available if you have turned on the Show Advanced Options configuration value. If the word dynamic appears in parentheses to the right of the keyword, then you can change this option without shutting down and restarting the server. See the preceding section on the RECONFIGURE command for more information.
Each of the following sections indicates the minimum, maximum, and default values for the option it describes. These values indicate the range of values that the item can have and the default value to which SQL Server is configured when it is first installed.
affinity mask
Minimum: 0
Maximum: 0x7fffffff
Default: 0
On SMP machines, affinity mask allows a thread to be associated with a processor. This association is done by using a bit mask. The processors on which the processes run are represented by each bit. You can use either decimal or hexadecimal values to specify values for this setting.
allow updates (Dynamic)
Minimum: 0
Maximum: 1
Default: 0
The allow updates configuration option determines whether the system catalog can be updated. If the value is set to 1, the system catalog can be updated. Stored procedures created while the system catalog is updateable will be able to update the system catalog even when this value is returned to 0.
CAUTION: Allowing updates on the system catalog is an extremely dangerous decision. If you decide to allow updates, do so only under very controlled conditions. You also should put the server in single-user mode to prevent other users from accidentally damaging the system catalog. To start the server in single-user mode, execute sqlservr -m from the Win32 command prompt.
Because this option can cause so much harm, you must use an additional keyword, WITH OVERRIDE, when executing the RECONFIGURE command. The following is the syntax to enable allow updates:
sp_configure "allow updates", 1 go reconfigure with override go
backup buffer size (Dynamic)
Minimum: 1
Maximum: 10
Default: 1
With this configuration option, you can control backups by increasing or decreasing the amount of memory available to the SQL Server for holding backup data. The numeric value corresponds to 32 2K pages. If you set the value to 4, for example, the server allocates 256K (4 x 32 x 2K ) for backups. A larger value helps reduce backup times but also reduces available memory for the server.
backup threads
Minimum: 0
Maximum: 32
Default: 5
The backup threads configuration option controls how many NT service threads are allocated to striped backup and load operations. When you use multiple CPUs, you may want to increase this value from the default of 5 to improve backup and load times.
cursor threshold (Dynamic, Advanced)
Minimum: -1
Maximum: 2147483647
Default: 100
The cursor threshold configuration option controls how SQL Server decides to build the results to answer a request for a cursor by a client. The value corresponds to the number of rows expected in the cursor's result set. The accuracy of the cursor threshold is largely based on the currency of the INDEX statistics in the tables for which a cursor is being built. To ensure more accurate picking of the synchronous/asynchronous cursor build, make sure that the statistics on the base tables are up-to-date.
If this option is set to -1, SQL Server will always build the cursor results synchronously, meaning that the server will attempt to build the cursor immediately upon receiving the OPEN CURSOR command. Synchronous cursor generation is usually faster for small result sets.
If this option is set to 0, SQL Server will always build the cursor results asynchronously, meaning that the server will spawn an additional thread to answer the client and it will return control to the client while processing other client requests. For large result sets, this option is preferred because it will stop the server from being bogged down answering a single client's request for a large cursor result.
database size
Minimum: 1
Maximum: 10000
Default: 2
The database size option controls the default number of megabytes to reserve for new databases. If the majority of the databases that are being created on a given server are greater than 2M, you should change this value. Also, if the model database grows to be greater than 2M, you will need to adjust this value.
TIP: Because the minimum database size is 1M, SQL Server databases can exist on floppy disks. See the Chapter 5, "Creating Devices, Databases, and Transaction Logs" section titled "Using Removable Media for Databases" for more information on this option.
default language
Minimum: 0
Maximum: 9999
Default: 0
The default language option controls the default language ID to be used for the server. U.S. English is the default and is always 0. If other languages are added to the server, they will be assigned different language IDs.
default sortorder id (Advanced)
Minimum: 0
Maximum: 255
Default: 52
The default sortorder id option controls the sort order that the server uses. The sort order controls the way SQL Server sorts data and returns it to the client. The default is 52, which is the identification number for the dictionary, case-insensitive sort order. Table 16.1 shows the other sort orders that are available.
ID | Name |
30 | Binary order |
31 | Dictionary order, case-sensitive |
32 | Dictionary order, case-insensitive |
33 | Dictionary order, case-insensitive, uppercase preference |
34 | Dictionary order, case-insensitive, accent-insensitive |
40 | Binary order |
41 | Dictionary order, case-sensitive |
42 | Dictionary order, case-insensitive |
43 | Dictionary order, case-insensitive, uppercase preference |
44 | Dictionary order, case-insensitive, accent-insensitive |
49 | Strict compatibility with Version 1.x case-insensitive databases |
50 | Binary order |
51 | Dictionary order, case-sensitive |
52 | Dictionary order, case-insensitive |
53 | Dictionary order, case-insensitive, uppercase preference |
54 | Dictionary order, case-insensitive, accent-insensitive |
55 | Alternate dictionary order, case-sensitive |
56 | Alternate dictionary order, case-insensitive, uppercase preference |
57 | Alternate dictionary order, case-insensitive, accent-insensitive |
58 | Scandinavian dictionary order, case-insensitive, uppercase preference |
59 | Scandinavian dictionary order, case-sensitive |
61 | Alternate dictionary order, case-insensitive |
CAUTION: Do not use sp_configure to change the sortorder option. Instead, use the SQL Server setup program if you want to change this value. Changing sortorder requires you to unload and reload the database because the data will need to be stored in a different format.
fill factor
Minimum: 0
Maximum: 100
Default: 0
The fill factor configuration option controls the default fill factor to use when creating indexes. The fill factor refers to how much space SQL Server will reserve in an index page for the potential growth of key values in the index. This option is overridden if you specify a fill factor with the CREATE INDEX command.
A fill factor of 100 forces SQL Server to fill the index pages completely and should only be used for extremely static tables whose key values never change. Smaller fill factor values force SQL Server to reserve space on the index page for new values that may be added to the table or index after the initial table load. The default value of 0 is basically the same as a value of 100, with the exception being that the index pages are not filled to capacity before they are split.
See the Chapter 11 section entitled "Creating an Index with CREATE INDEX."
free buffers (Dynamic, Advanced)
Minimum: 20
Maximum: 524288
Default: 204
The free buffers configuration option controls the amount of memory that SQL Server must maintain when lazywriting to disk. Lazywriting increases throughput because values are "written" to memory instead of disk. If this threshold is hit, the lazy writer forces information to disk to ensure that memory pages are kept available.
If you make a change to the memory configuration option, the free buffers option is automatically adjusted to five percent of the memory value. After you restart the server after changing the memory option, you can modify the free buffers value manually to any value within the range specified.
hash buckets (Advanced)
Minimum: 4999
Maximum: 265003
Default: 7993
The hash buckets configuration option controls the amount of buckets that SQL Server reserves for hashing, or indexing, pages of data to memory. A bucket is a logical storage area or counter that SQL Server uses to hold values that it needs to identify memory pages. SQL Server's hashing algorithm requires that a prime number of buckets be made available for use. If you specify a non-prime value, SQL Server will pick the closest prime number.
You probably won't need to change this value unless your server has more than 160M of RAM (8000 x 2K). Note that the default of 7993 value really refers to the closest prime to 8000.
language in cache
Minimum: 3
Maximum: 100
Default: 3
The language in cache configuration option controls the number of languages that SQL Server can store in the language cache simultaneously.
LE threshold maximum (Dynamic)
Minimum: 2
Maximum: 500000
Default: 200
The LE threshold maximum option controls the maximum number of page locks that SQL Server allows a single query to have before escalating a set of page locks on a table to a full table lock. If the number of page locks is exceeded, SQL Server will force a table lock, regardless of the LE threshold percentage configured for the table. This action improves server performance because it prevents memory being allocated unnecessarily to manage the individual page locks.
LE threshold minimum (Dynamic)
Minimum: 2
Maximum: 500000
Default: 20
The LE threshold minimum option controls the minimum number of page locks that a single query must acquire before SQL Server will escalate a set of page locks on a table to a full table lock. This configuration option is provided so that the LE threshold percentage will not hit on tables that have few pages of data.
LE threshold percentage (Dynamic) Minimum: 1
Maximum: 100 Default: 0
The LE threshold percent option controls the percentage of page locks to pages of data in a table that needs to be acquired before SQL Server will escalate the lock to a full table lock. A value of 0, the default, stops SQL Server from performing lock escalation unless the lock escalation threshold maximum is reached.
ocks
Minimum: 5000
Maximum: 2147483647
Default: 5000
The locks option controls the number of locks that SQL Server can maintain at any time. Each lock consumes 32 bytes of RAM, so increasing this value to a large number will most likely require you to make more RAM available to the server. For example, setting this value to 20,000 will result in the lock manager or consuming 625K of RAM (20,000 x 32 bytes = 640,000 bytes).
logwrite sleep (ms) (Dynamic, Advanced)
Minimum: -1
Maximum: 500
Default: 0
The logwrite sleep option controls the number of milliseconds that SQL Server will wait to write a log entry to disk if the buffer is not full. Increasing this option's value can can have dramatic performance gains on highly DML-active databases because it will force SQL Server to write larger blocks of memory to the log at one time, rather than requiring it to write less than full blocks to disk.
A value of -1 is provided to force SQL Server to always write to disk. This value should only be used on systems that are extremely concerned about media failure and are not concerned with I/O throughput performance. A value of 0 forces SQL Server to delay writes to disk if, and only if, other users on the system are in the execute phase of their requests to the server.
max async io
Minimum: 1
Maximum: 255
Default: 8
The max async IO option controls the maximum number of asynchronous I/O requests that SQL Server can make to hardware devices. This value should only be changed from the default on systems that have more than eight physical disks with database devices on them or on systems that are using disk striping to improve performance.
max lazywrite IO (Dynamic, Advanced)
Minimum: 1
Maximum: 255
Default: 8
With the max lazywrite IO option, you can tune the writes from the lazywriter to the real I/O subsystem. This value is dynamically configurable, but it can only be configured up to the value of the max async io option. This option is not typically something you would modify on your system.
max worker threads (Dynamic)
Minimum: 10
Maximum: 1024
Default: 255
The max worker threads option controls the maximum number of threads that SQL Server will spawn to handle database operations. By default, SQL Server spawns at least one thread for each listener service that is installed. In addition, threads are spawned for database checkpointing, lazywriting, and for the read ahead manager. The checkpointing process is a process or server operation that writes dirty, or changed, pages of data that are currently cached from memory directly to disk. The lazywriting process manages cached writes to disk and allows transactions to be batched together for a single I/O to disk containing multiple items instead of writing every transaction to disk as it occurs.
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 (determined by the amount set in this option), SQL Server uses the available threads in a pooling fashion. The next user process request that the server receives is assigned to the first thread that becomes available after it has completed its assigned task.
media retention
Minimum: 0
Maximum: 365
Default: 0
The media retention option controls the number of days that a given backup is expected to be retained before it can be reused. If this value is other than 0, SQL Server warns the user that he or she is performing a backup over an existing backup that has not gone past its number of retention days.
This option is useful for SQL servers that are in remote areas where a full-time administrator is not available to manage the environment and where it is likely that the user may incorrectly reuse backup tapes that should be kept for a prescribed period. A good setting is 7, which will stop the tape from being used more than once a week.
memory
Minimum: 1000
Maximum: 1048576
Default: 4096
The memory configuration option controls the maximum number of 2K pages of memory that SQL Server will consume at startup. To fully optimize your server for use as a database server, you should allocate all available memory to the server after subtracting the minimums required by Windows NT.
CAUTION: If you overcommit the amount of available memory, SQL Server will not start. See the section later in this chapter entitled "Starting SQL Server in Minimal Configuration Mode from the Command Line" to learn how to fix a server that is no longer starting because memory was overcommitted.
To help tune the amount of memory being consumed by SQL Server, you can use the DBCC MEMUSAGE command, which reports the way that memory has been allocated on the server and also shows the top 20 data pages and stored procedures that have been executed. Listing 16.5 shows an example of the output that is displayed without the data page top 20 or the stored procedure top 20.
/*---------------------------- dbcc memusage ----------------------------*/ Memory Usage: Meg. 2K Blks Bytes Configured Memory: 16.0000 8192 16777216 Code size: 1.7166 879 1800000 Static Structures: 0.2473 127 259328 Locks: 0.2480 127 260000 Open Objects: 0.1068 55 112000 Open Databases: 0.0031 2 3220 User Context Areas: 0.8248 423 864824 Page Cache: 8.8951 4555 9327184 Proc Headers: 0.2143 110 224724
Proc Cache Bufs: 3.5996 1843 3774464
nested triggers (Dynamic)
Minimum: 0
Maximum: 1
Default: 1
The nested triggers option controls whether triggers will nest or cascade when executed. If this option is set to 0, SQL Server will only execute the first trigger that fires when an update or delete action occurs.
See Chapter 15, "Creating and Managing Triggers."
network packet size (Dynamic)
Minimum: 512
Maximum: 32767
Default: 4096
The network packet size option controls the server-wide maximum network packet size that a client can request. If the client requests a size less than the value specified in the current value, SQL Server will accept it. Greater values than the current value are negotiated to the maximum value specified in this option, however.
This option can improve performance on networks whose base topology supports wider or larger packets than TCP/IP's default of 4096 bytes. This option is especially useful if you are running over a satellite service and want to batch large packets of data to send through the satellite packet service.
You should adjust this option to a higher value for reporting databases that are not acquiring any locks on the datasets because it will allow larger batches of data to be sent to the client at one time, improving network throughput.
CAUTION: Setting the packet size too high can cause locking problems on databases with many transactions. SQL Server will hold locks for an unnecessarily long time in order to fill up a network packet to send to the client. Take care when adjusting this value and perform statistical analysis to prove that the values you have chosen are benefiting you.
open databases
Minimum: 5
Maximum: 32767
Default: 20
The open database option controls the maximum number of databases that SQL Server can maintain in an open condition at any one time. This option should not be arbitrarily set to a high value because each open database does consume some server memory.
If people are unable to connect to a database that you've just created, check to see whether you have exceeded the maximum number of open databases on the server. If you have, use sp_configure to increase the number of open databases available on the server and then try to connect to the database again.
open objects
Minimum: 100
Maximum: 2147483647
Default: 500
The open objects option controls the maximum number of objects that SQL Server can hold in memory at one time. An object can be a table page, a stored procedure that is executing, or any other object in the database. Increase this value if the server reports that the maximum number of objects has been exceeded. Take care when assigning values to this option, however. Increasing its value may mean that you have to allocate more memory to the server.
priority boost (Advanced)
Minimum: 0
Maximum: 1
Default: 0
The priority boost option controls the priority SQL Server will run at under Windows NT. The default is 0, meaning that SQL Server will run at a normally high priority, but will allow other tasks to request high threading priority as well. If this option is set to 1, SQL Server will run at the highest priority under the Windows NT scheduler. This value should be set to 1 on systems that are dedicating Windows NT to run SQL Server.
procedure cache
Minimum: 1
Maximum: 99
Default: 30
The procedure cache option controls the proportion of memory that SQL Server allocates to store the stored procedures that have most recently been executed. For systems that have large amounts of stored procedures, you may need to set this value higher than 30 percent if the total amount of memory available to SQL Server is relatively low. On systems with more than 512M of RAM, you should reduce this value to 10 percent or less. It is extremely unlikely that the amount of stored procedures in memory cache will exceed 50M.
SQL Server has a stored procedure cache because it does not store the desired query plan/execution plan of the procedure in the database until it is first executed. As a result, procedures take more time to run the first time they are executed because SQL Server has to pull the tokenized procedure text out of private tables, evaluate the text, and determine the correct execution path. This execution path is what is stored in the procedure cache.
RA cache hit limit (Dynamic, Advanced)
Minimum: 1
Maximum: 255
Default: 4
The RA cache hit limit option sets the number of hits in the data page cache that the Read Ahead Manager makes before canceling itself and allowing the query to fetch data from the data page cache instead. You probably won't need to change this option.
RA cache miss limit (Dynamic, Advanced)
Minimum: 1
Maximum: 255
Default: 3
The RA cache miss limit option controls the number of data page cache misses that are acceptable to the SQL Server before the Read Ahead Manager is started. Setting this value to 1 means the Read Ahead Manager will be fired for every access of a data page. This setting will cause terrible performance and a lot of unnecessary disk thrashing. In most cases, you should leave this option set to the default.
RA delay (Dynamic, Advanced)
Minimum: 0
Maximum: 500
Default: 15
The RA delay option controls the number of milliseconds that the Read Ahead Manager waits before executing a request.
RA prefetches (Dynamic, Advanced)
Minimum: 1
Maximum: 1000
Default: 3
The RA prefetches option controls the number of extents (8 x 2K pages) that the Read Ahead Manager will read ahead of the currently scanning execution position.
RA slots per thread (Advanced)
Minimum: 1
Maximum: 255
Default: 5
The RA slots per thread option controls the number of slots per thread that SQL Server will reserve for Read Ahead processes. The number of slots multiplied by the number of allocated worker threads is the total number of concurrent Read Ahead activities that can be executing.
RA worker threads
Minimum: 0
Maximum: 255
Default: 3
The RA worker threads option controls the number of Windows NT threads that SQL Server allocates to the Read Ahead Manager. You should configure this value to the maximum number of concurrent users expected on the system. With the option configured in this way, SQL Server will have a Read Ahead thread available to handle each user process.
recovery flags
Minimum: 0
Maximum: 1
Default: 0
The recovery flags option controls the information that is displayed during the SQL Server startup process. If this option is set to 0, the default, then SQL Server will only report that the database is being recovered/restored by name. If this option is set to 1, then SQL Server will report, in detail, the status of every transaction that was pending at the time the server was shut down and what action SQL Server took to resolve it.
TIP: To view the information captured in the error log, choose the Error Log command from the Server menu in SQL Enterprise Manager.
recovery interval (Dynamic)
Minimum: 1
Maximum: 32767
Default: 5
The recovery interval option controls the number of minutes SQL Server requires to recover a database in the event that a system failure occurs. This option, combined with the amount of activity that is occurring on the server, controls the amount of time between database CHECKPOINTs.
A database CHECKPOINT forces all the changes to dirty data pages from the transaction log information to be written to disk instead of residing in the transaction log buffers or lazywriter buffers. A CHECKPOINT can take considerable time if there has been a lot of activity on the server, but frequent checkpointing will reduce the amount of time required to restart the server because it will not have to ROLLFORWARD as much work from the transaction log.
remote access
Minimum: 0
Maximum: 1
Default: 1
The remote access option controls whether remote SQL Servers are allowed logon access to the server. If this option is set to 0, SQL Server will deny access to remote SQL Servers.
remote conn timeout
Minimum: -1
Maximum: 32767
Default: 60
The value for remote conn timeout represents the number of minutes that may pass without activity for a server-to-server connection. If the value is exceeded, the non-active session is terminated. The only exception to this is when the connection is involved in a DTC-coordinated distribution transaction.
remote logon timeout (Dynamic, Advanced)
Minimum: 0
Maximum: 2147483647
Default: 5
The remote logon timeout option controls the amount of time, in seconds, that SQL Server waits before returning an error to the client process which was requesting the logon to a remote server. Setting this option to 0 causes the SQL Server to wait indefinitely.
remote proc trans
Minimum: 0 (FALSE)
Maximum: 1 (TRUE)
Default: 0 (FALSE)
The remote proc trans option allows users to protect the actions of a server-to-server procedure through a DTC-coordinated distributed transaction. When set to True, it provides a DTC transaction that protects certain properties of transactions. After this option is set, new sessions will inherit the configuration setting as their default.
remote query timeout (Dynamic, Advanced)
Minimum: 0
Maximum: 2147483647
Default: 0
The remote query timeout option controls the amount of time, in seconds, that SQL Server waits before returning an error to the client process which was requesting the execution of a query on a remote server. Setting this option to 0 causes the SQL Server to wait indefinitely.
resource timeout (Dynamic, Advanced)
Minimum: 5
Maximum: 2147483647
Default: 10
The resource timeout option controls the amount of time, in seconds, that SQL Server waits before returning an error to the client process which required a server resource. A server resource could be access to a memory buffer, a disk I/O request, a network I/O request, or a log I/O request. Increase the value of this option if a large number of logwait or bufwait timeout warnings are in the SQL Server error log.
set working set size (Advanced)
Minimum: 0
Maximum: 1
Default: 0
The set working set size option controls whether SQL Server requests Windows NT to physically allocate and lock memory to SQL Server. The amount allocated will be equal to the number of pages in the memory configuration option multiplied by 2K, plus the amount of memory requested for tempdb in the tempdb in RAM configuration option.
See Chapter 5, "Creating Devices, Databases, and Transaction Logs" in the section "Placing tempdb in RAM," for information about having your tempdb in RAM.
show advanced option (Dynamic)
Minimum: 0
Maximum: 1
Default: 1
The show advanced option configuration option controls whether SQL Server displays advanced options and allows you to configure advanced options through sp_configure. If this option is set to 0, SQL Server will respond that an option does not exist if you attempt to change an advanced option. Remember, you must make sure that you have enabled the show advanced option using sp_configure before you'll be allowed to configure advanced options.
SMP concurrency (Advanced)
Minimum: -1
Maximum: 64
Default: 0
The SMP concurrency option controls how SQL Server will operate on a Symmetric Multi-Processing server. The default configuration for a single CPU computer is 0, which means auto-configuration mode. In auto-configuration mode, SQL Server allocates N-1 CPUs to SQL Server from the Windows NT service scheduler, where N is the number of CPUs detected in the server when SQL Server starts.
If SQL Server is installed with Dedicated SMP Support chosen, SQL Server sets this value to -1, which means that all CPUs will be dedicated to SQL Server. If the Windows NT server is not dedicated to running SQL Server and this value is configured to the maximum number of CPUs in the computer, tasks other than SQL Server that are executing on the computer will have poor performance.
sort pages (Dynamic, Advanced)
Minimum: 64
Maximum: 511
Default: 128
The sort pages option controls the number of pages that SQL Server reserves per user for sorting and resolving queries. This value should be closely tuned to the user requirements of the system that is executing on the SQL Server. A higher value will generally result in better performance for systems that do a lot of queries requiring data to be sorted in memory. Setting this value high will cause each user to consume larger amounts of available memory and require you to dedicate more memory to SQL Server.
spin counter (Dynamic, Advanced)
Minimum: 1
Maximum: 2147483647
Default: 10000
The spin counter option sets the maximum number of attempts SQL Server will make to acquire a resource from the SQL Server service manager. This advanced option should not be altered except in extreme situations, such as when you're working with a support center to troubleshoot your system.
tempdb in ram (MB)
Minimum: 0
Maximum: 2044
Default: 0
The tempdb in ram option controls the amount of memory that SQL Server will reserve for tempdb in RAM. If this option is set to 0, tempdb resides on a physical disk device, which is the default for MASTER's device. If this option is set to any value other than 0, tempdb is placed in a memory chunk. This memory chunk will be contiguously allocated.
If tempdb is resized through the ALTER command while it resides in memory, additional contiguous chunks of memory corresponding to the required ALTER size will be allocated to it. These contiguous chunks may not, however, necessarily be next to the chunks previously allocated. You should shut down and restart the server if the size of tempdb is altered.
user connections
Minimum: 5
Maximum: 32767
Default: 20
The user connections option controls the maximum number of user processes that can connect to the server at one time. The logical limit is 32767, but the practical limits of server hardware will probably be exceeded before this limit is ever achieved.
The minimum fixed overhead for each user connection is about 40K. If the user connections option is set to a large value, you may have to allocate more memory to the SQL Server.
If users are reporting that they cannot connect, your user connections value may be set too low. Use sp_configure to increase the number of user connections to a higher value so that more concurrent users are permitted.
user options
Minimum: 0
Maximum: 4095
Default: 0
The user options setting establishes global defaults for users logging on to the system. After a change is made, all new logins will be affected, but existing logons will not change. Users can override these values by using the SET statement.
SYSCONFIGURES and SYSCURCONFIGS are system catalog tables that SQL Server uses to store information about configuration options that the server is using. These tables are stored in the Master database.
SYSCONFIGURES has information from the server about the available options and their defaults. The sp_configure option, in contrast, comes from the spt_values table in the Master database. Rather than relying on the formatted results returned from sp_configure, you may need to be able to process the configurations on the server in a result set. The query in Listing 16.6 shows the defaults for all the configurable options in the server.
/*---------------------------- Select V.NAME, COMMENT = substring( C.COMMENT, 1, 60 ), "DEFAULT" = c.value From MASTER.DBO.SPT_VALUES V, MASTER.DBO.SYSCONFIGURES C Where V.NUMBER = C.CONFIG And V.NAME is not null Order by V.NAME ----------------------------*/ NAME COMMENT DEFAULT -------------------- ------------------------------------ ------ allow updates Allow updates to system tables 0 backup buffer size backup buffer size 1 backup threads backup threads 5 cursor threshold cursor threshold -1 database size Default database size in megabytes 2 default language default language 0 default sortorder id default sortorder ID 52 fill factor Default fill factor percentage 0 free buffers Free buffers 409 hash buckets Hash buckets 7993 language in cache language cache 3 LE threshold maximum LE threshold maximum 10 LE threshold minimum LE threshold minimum 20 LE threshold percent LE threshold percent 0 locks Number of locks for all users 5000 logwrite sleep (ms) logwrite sleep (ms) 0 max async IO Maximum outstanding async IOs 8 max lazywrite IO Maximum lazywrite IO 8 max worker threads Maximum worker threads 255 media retention Tape retention period in days 0 memory Size of avbl phys memory in 2k pages 8192 nested triggers Allow triggers to invoke triggers 0 network packet size Default network packet size 4096 open databases # of open dbs allowed for all users 20 open objects Number of open database objects 500 priority boost Priority boost 0 procedure cache % of memory used for procedure cache 30 RA cache hit limit RA cache hit limit 4 RA cache miss limit RA cache miss limit 3 RA delay RA delay 15 RA prefetches RA prefetches 3 RA slots per thread RA slots per thread 5 RA worker threads RA worker threads 3 recovery flags Recovery flags 0 recovery interval Maximum recovery interval in minutes 5 remote access Allow remote access 1 remote logon timeout remote logon timeout 5 remote query timeout remote query timeout 0 resource timeout resource timeout 10 set working set size set working set size 0 show advanced option show advanced options 1 SMP concurrency SMP concurrency 0 sort pages Number of sort pages 128 spin counter spin counter 10000 tempdb in ram (MB) TempDB in RAM option 0 user connections Number of user connections allowed 20 (46 row(s) affected)
TIP: In the query in Listing 16.6, the reserved SQL Server keyword DEFAULT was used as a column title. To use any reserved words as text in a column title, enclose them in quotation marks.
SYSCURCONFIGS stores the currently configured values that the server is using. The query in Listing 16.7 shows how to get the current values for each of the configurable options in the server. Using SYSCONFIGURES and SYSCURCONFIGS together, you can write your own programs to dynamically set options and report options on the server.
/*---------------------------- Select V.NAME, COMMENT = substring( C.COMMENT, 1, 60 ), "CURRENT VALUE" = c.value From MASTER.DBO.SPT_VALUES V, MASTER.DBO.SYSCURCONFIGS C Where V.NUMBER = C.CONFIG And V.NAME is not null Order by V.NAME ----------------------------*/ NAME COMMENT CURRENT VALUE -------------------- ------------------------------------ ------------ allow updates Allow updates to system tables 0 backup buffer size backup buffer size 1 backup threads backup threads 5 cursor threshold cursor threshold -1 database size Default database size in megabytes 2 default language Default language 0 default sortorder id Default sortorder ID 52 fill factor Default fill factor percentage 0 free buffers Free buffers 409 hash buckets Hash buckets 7993 language in cache # of language information in cache 3 LE threshold maximum Lock Escalation threshold maximum 10 LE threshold minimum Lock Escalation threshold minimum 10 LE threshold percent Lock Escalation threshold percent 0 locks Number of locks for all users 5000 logwrite sleep (ms) logwrite sleep (ms) 0 max async IO Maximum outstanding async IOs 8 max lazywrite IO Maximum lazywrite IO 8 max worker threads Maximum worker threads 255 media retention Media retention period in days 0 memory Size of avbl phys memory in 2k pages 8192 nested triggers Allow triggers to invoke triggers 0 network packet size Default network packet size 4096 open databases # of open dbs allowed for all users 20 open objects Number of open database objects 500 priority boost Priority boost 0 procedure cache % of memory used for procedure cache 30 RA cache hit limit RA cache hit limit 4 RA cache miss limit RA cache miss limit 3 RA delay RA delay 15 RA prefetches RA prefetches 3 RA slots per thread RA slots per thread 5 RA worker threads RA worker threads 3 recovery flags Recovery flags 0 recovery interval Maximum recovery interval in minutes 5 remote access Allow remote access 1 remote logon timeout remote logon timeout 5 remote query timeout remote query timeout 0 resource timeout Resource timeout 10 set working set size set working set size 0 show advanced option Show advanced options 1 SMP concurrency SMP concurrency 1 sort pages Number of sort pages 128 spin counter Spin counter 0 tempdb in ram (MB) Size of TempDB in RAM (MB) 0 user connections Number of user connections allowed 20 (46 row(s) affected)
NOTE: The queries in Listing 16.6 and Listing 16.7 are joined to the SQL Server system table spt_values. SQL Server uses this table to display value/configuration data.
SQL Server has several options available at the database level that enable the database administrator (DBA) to configure how different databases act on a given server.
NOTE: In versions prior to SQL Server 6.0, it was necessary to do a CHECKPOINT command in the modified database after performing a change to a database option. In SQL Server 6.0, Microsoft added the dynamic interpretation of procedural logic to stored procedures, making it possible for them to update the sp_dboption system-stored procedure to automatically do the CHECKPOINT for you.
SQL Server provides two ways to display and set configuration options for the database. The graphical method involves using SQL Server Enterprise Manager. The command-line method uses the sp_dboption system-stored procedure.
Using SQL Enterprise Manager To configure a database using SQL Enterprise Manager, follow these steps:
FIG. 16.4
SQL Enterprise Manager's Edit Database dialog box shows the Options page.
Using sp_dboption You can use the system-stored procedure sp_dboption instead of the SQL Enterprise Manager to set options for the database. The syntax for sp_dboption is as follows:
sp_dboption [database name, database option, database value]
The database name is the name of the database that is being viewed or changed.
The database option is the name of the option being viewed or changed. Place quotation marks around the option being set if it contains any embedded spaces. The database value is the new value for the option. If you don't give sp_dboption any parameters, it will return the available parameters that can be set for any current database. Listing 16.8 shows the result of sp_dboption being executed without parameters.
/*---------------------------- sp_dboption ----------------------------*/ Settable database options: -------------------------- ANSI null default dbo use only no chkpt on recovery offline published read only select into/bulkcopy single user subscribed trunc. log on chkpt.
If a database is supplied as a parameter, but no database option is supplied, sp_dboption returns the currently active configuration options for the specified database. Listing 16.9 shows an example of this procedure.
/*---------------------------- sp_dboption pubs ----------------------------*/ The following options are set: -------------------------- select into/bulkcopy trunc. log on chkpt.
The sp_dboption procedure is similar to the sp_configure procedure in that, for the option being set, it performs a wildcard-style search on the passed-in option parameter so that dbo, dbo use, and dbo use only can all specify the same parameter.
The following is a list of all the available user database configuration options. In parentheses following the option name is the equivalent name that SQL Enterprise Manager uses for the option. Options without equivalent commands in SQL Enterprise Manager must be set with sp_dboption.
NOTE: The only option that is user-configurable for the Master database is the Truncate Log on Checkpoint option. SQL Server requires that all other configurations be left in their default setups for the Master database to operate correctly.
ANSI null default (Columns Null by Default) The ANSI null default database option controls the way the SQL interpreter parses the CREATE TABLE statement when defining columns. By default, if the NULL keyword is omitted in SQL Server, the SQL interpreter assumes that the column is supposed to be NOT NULL. The ANSI standard specifies the reverse, however; unless otherwise specified, a column is NULL. If, when you define a table, you specify whether a given column is NULL or NOT NULL, your indication will override this setting. If the database scripts you are using to create a table or set of tables have been created for an ANSI-compatible database, you must turn on this option so that the tables behave the same way they would on another ANSI-compatible database.
DBO use only (DBO Use Only) The DBO use only database option controls the user access to the database. If this option is set to True, then the only user that may access the database is the database owner, DBO. If this option is turned on while other users are connected to the database, their connections will not be killed. The users will be allowed to stay on the database until they disconnect voluntarily.
no chkpt on recovery (No Checkpoint on Recovery) The no chkpt on recovery database option controls behavior during the recovery of a database. The default is False, meaning that after a recovery of database or transaction log, a CHECKPOINT operation will occur. If multiple databases are being used in a primary and secondary fashion and transaction logs are being rolled forward from one database to another, this option should be set to True in order to stop the database from rejecting further transaction logs being applied.
offline The offline database option, if enabled, will bring a database down into an offline condition. This option is most often used with databases based on removable media, such as a disk or CD-ROM, that need to be swapped out at any given time. Databases that have currently connected or active users cannot be placed offline until those users disconnect. An offline database is not recovered when the server is restarted.
published The published database option controls whether a database is available for publishing- and subscribing-based replication. If this option is enabled, a repl_subscriber user is added to the database, and the transaction log is monitored for transactions that need to be replicated to other databases.
NOTE: The fact that you enable the published option does not cause the database to be replicated. For information about replication and setting up the publisher/subscriber relationship between databases, see Chapter 17, "Setting Up and Managing Replication."
read only (Read Only) The read only database option, if enabled, places a database in read-only mode, making it impossible to insert, update, or delete anything from the database. This is a useful option to turn on for reporting databases. For example, if you are writing an application that simply does a lot of reports for your users, the read only flag will guarantee that the data does not change.
NOTE: If you use this option, don't forget to reverse it in your applications that populate the tables in the database. You must have a way to get information into the database before you can mark it read-only, so your routines that place this information online must disable and then re-enable the option during processing.
select into/bulkcopy (Select Into/Bulk Copy) The select into/bulkcopy database option controls whether non-logged database operations are permitted in the current database. A non-logged operation, such as the SELECT INTO command, is highly optimized and does not write any entries to the database transaction log, making it unrecoverable.
CAUTION: If you set the select into/bulkcopy option to True, you won't be able to use the transaction log for recovery of a problem database. Be certain you set this option to False to ensure your ability to use the transaction dump and restore capabilities of SQL Server if that will be your backup approach.
This option must be enabled if Bulk Copy (BCP) operations are to be executed against a database table without indexes. However, if a table has indexes, SQL Server will always use the slow load algorithm so that it has a chance to update the indexes.
single user (Single User) The single user database option limits database access to a single user. If this option is enabled and a user connects, then that user may stay connected, but any other user will be denied access. If single user mode is turned on, trunc. Log on chkpt. will be disabled because it requires an additional user connection to the database to act as a monitor. The DBO or SA cannot override the single user option. If another user is logged on to a given database, the DBO or SA will not even be able to access the database.
subscribed The subscribed database option controls whether the database can be part of a subscription-based replication. If this option is set to True, a private account, repl_publisher, is given access as a DBO to the database, and the replication services are activated.
trunc. log on chkpt. (Truncate Log on Checkpoint) The trunc. log on chkpt. database option controls whether the database logs are truncated when a checkpoint activity occurs. By default, this option is off and it should always be off in situations when it may be necessary to use the transaction logs for replication, backup, or recovery. If you find that you're executing the checkpoint frequently but still fill up your transaction logs, you need to do one of three things: perform more frequent manual dumping of the transaction logs, enlarge your logs, or use Performance Monitor to run a batch file to dump the logs when they are nearly full.
See Chapter 21, "Optimizing Performance," for information on Performance Monitor.
Note that if you have this option enabled, or set it to True, you cannot dump the transaction log. It only exists between checkpoints, so you'll receive an error message indicating that the transaction log cannot be dumped.
One important consideration when using this option is the fact that you cannot use the transaction log as part of your backup approach. Because you cannot dump the transaction log, you can't back it up. Instead, you need to do complete database backups to ensure the safety of the information in your database.
SQL Server has a number of individual options that you can set while querying the database. These options control the behavior of queries when they are executed. These options are also useful statistical and informational gatherers. They can be helpful in diagnosing query problems, such as queries that run slowly for no apparent reason.
SQL Server provides two ways to display and set configuration options for a query. The graphical method is to use ISQL/w or the Query Analyzer of SQL Server Enterprise Manager. The command-line method is to use the system keyword SET.
Using ISQL/w To use ISQL/w to set or view query options, perform the following steps:
FIG. 16.5
After ISQL/w is started, the pubs database is selected.
FIG. 16.6
ISQL/w's Query Options dialog box shows the Query Flags page.
SET SQL Server provides an SQL keyword, the SET statement, that you can use to set any query option. If used in a stored procedure, the SET statement is in effect for the life of the procedure and overrides any previous settings. The syntax for the SET statement is as follows:
SET Option On | Off
Option is any valid SQL Server query option.
The following is a list of all the query options that are configurable and what they do. In parentheses following the SET option is its equivalent in ISQL/w.
Arithabort (Abort on Arithmetic Error) The ARITHABORT option controls what SQL Server will do when an arithmetic error occurs. If this option is set to True, SQL Server aborts any query that causes division by zero or numeric overflow (in which the value is greater than the defined data type) to occur. You can't capture this error at runtime, so if this option is not set, the output could be NULL. In Listing 16.10, ARITHABORT is used to stop a command batch from continuing with invalid data.
/* Declare working variables */ Declare @nDecimal Decimal( 8, 2 ), @nInteger Integer /* ensure that the error does not cause an abort */ Set ArithAbort off /* do a division that is going to cause an error note that the print statement doesn't get executed because this is a special error condition that SQL Server doesn't "publish" for handling */ Select @nDecimal = 8 / 0 If @@error != 0 Print `Error' /* abort processing if the error occurs again */ Set ArithAbort on /* This time the division will cause an error and the SQL command batch will be terminated, note that the termination stops any further activity and the print statement again is ignored */ Select @nDecimal = 8 / 0 If @@error != 0 Print `Error'
The output is as follows:
Divide by zero occurred. (1 row(s) affected) Msg 8134, Level 16, State 1 Divide by zero error encountered
Arithignore (Ignore Arithmetic Error) ARITHIGNORE is the opposite of ARITHABORT because it stops the SQL Server from reporting an error condition if an arithmetic error occurs. Listing 16.11 demonstrates how ARITHIGNORE is used and shows that SQL Server does not report any error conditions.
/* Declare working variables */ Declare @nDecimal Decimal( 8, 2 ), @nInteger Integer /* ensure that the error does not cause an abort */ Set Arithignore on /* do a division that is going to cause an error note that the print statement doesn't get executed because this is a special error condition that SQL Server doesn't "publish" for handling */ Select @nDecimal = 8 / 0 If @@error != 0 Print `Error' /* do a print so that we know we are through the first part of the query */ Print `Second Query' /* abort processing if the error occurs again */ Set ArithIgnore off /* This time the division will cause an error and the SQL command batch will be terminated, note that the termination stops any further activity and the print statement again is ignored */ Select @nDecimal = 8 / 0 If @@error != 0 Print `Error'
The output is as follows:
(1 row(s) affected) Second Query Divide by zero occurred. (1 row(s) affected)
NOCOUNT (No Count Display) The NOCOUNT option disables the display of the number of rows processed by any SQL statement. The @@ROWCOUNT global variable is still maintained, however. The output in Listing 16.12 shows the effect of NOCOUNT.
/* Make sure that NoCount is Off (the default) */ Set NoCount Off /* Do some SQL */ Select "# Authors" = Count(*) From AUTHORS /* Now turn on NoCount */ Set NoCount On /* Do the same SQL and observe the different results */ Select "# Authors" = Count(*) From AUTHORS
The output is as follows:
# Authors ---------- 23 (1 row(s) affected) # Authors ---------- 23
NOEXEC (No Execute) The NOEXEC option controls whether SQL Server executes an SQL statement. If you turn on this option, SQL Server will not execute the query, but will perform only the work to determine how the query would have been answered. This option is most commonly used when viewing the SHOWPLAN that a query generates without fetching the data.
NOTE: SQL Server processes queries in two phases: compilation and execution. In the compilation phase, SQL Server validates that the query is OK, checks that all the objects exist and are readable, and generates the query plan or best path to the actual data. In the execution phase, SQL Server starts performing the query, which could include updating the records, fetching the data, and so on.
PARSEONLY (Parse Query Only) The PARSEONLY option is like NOEXEC except that SQL Server does not compile the query or generate the access path to the data. All SQL Server does is check that the query is syntactically accurate.
SHOWPLAN (Show Query Plan) The SHOWPLAN option shows the query plan that SQL Server generated to answer a query. The query plan can be interpreted by ISQL/w or by SQL Enter-prise Manager; a graphical representation is also displayed, as shown in Figure 16.7. For a complete and detailed discussion on interpreting the SHOWPLAN output, refer to Chapter 23, "Understanding SHOWPLAN Output," in Microsoft's SQL Server Administrator's Companion.
FIG. 16.7
ISQL/w's graphical SHOWPLAN output can help you understand how a query is
being performed.
STATISTICS TIME (Show Stats Time) The STATISTICS TIME option shows the amount of time the server spent in different areas of parsing, compiling, executing, and answering a query. This information can be very useful when you are adjusting queries. The data, however, can be skewed because of server caching.
STATISTICS IO (Show Stats I/O) The STATISTICS IO option shows the number of logical and physical reads the server did to answer a query. Logical reads are reads that come from cache pages. Physical reads cause the database to go to disk. The number returned is the number of 2K pages read.
Minimal Configuration mode is a last resort that should only be used when the server fails to start because of an invalid configuration specified with sp_configure. Minimal configuration mode starts the minimum number of services to allow the reconfiguration of the server. Minimal Configuration mode is provided in SQL Server 6.x in place of SQL Server 4.2's and Sybase's bldmaster executable. Prior to version 6.x of SQL Server, the configuration options were written to the bldmaster file. If you couldn't start the server, you had to edit this file manually.
CAUTION: This chapter applies only to SQL Server 6.x. The instructions here are not effective for prior releases. See the Microsoft SQL Server documentation on bldmaster to restart a server prior to SQL Server 6.x.
A server started in minimal configuration mode has the following limitations:
TIP: The CHECKPOINT service behaves like a user in the system and is assigned spid 3. You can check that the service is running by executing an sp_who command in ISQL.
To start the server in Minimal Configuration mode from the command line, perform the following steps:
TIP: The start command launches a separate process thread in which SQL Server is executed. This thread allows you to continue using the same command prompt to do other things. You can start any Windows NT task.
NOTE: To start SQL Server independently of the Windows NT Service Control Manager, use the -c command line switch. A server started in this fashion starts more quickly and can be fixed more rapidly in an emergency situation.
To start the server in minimal configuration mode by using the Services application in Control Panel, perform the following steps:
There may be several things you will need to do to repair a server that you had to start in Minimal Configuration mode. If you started your server in Minimal Configuration mode to reset a configuration, do the following:
If you started your server in Minimal Configuration mode because the server ran out of disk space, this is what you should do:
In this chapter, you learned how to configure your server, database, and queries to get optimal performance and to maximize your use of the server. Take a look at the following chapters for more information:
© Copyright, Macmillan Computer Publishing. All rights reserved.