Chapter 16 - Understanding Server, Database, and Query Options

How to configure your server, database, and query with the available options - This chapter will show you how to configure options in SQL Server.
The purpose of each server, database, and query option - SQL Server has numerous options that can be configured. This chapter will help you understand them.
How to recover a server that won't start - Recovering SQL Server in Minimal Configuration Mode is quite difficult. This chapter will walk you through the steps involved.

SQL Server 6.5 has a number of ways of being configured. Those ways are roughly broken down into the three following areas:

Server Options—These are global options that 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 are global options that affect all operations on the currently active or used database. Database options are generally used to limit access to the database and to enable high-speed BCP operations.
Query Options—These are local options that affect only the currently executing query. Query options allow the tuning and monitoring of the activities of individual queries, and they allow the displaying of additional statistical information about the query's execution.

Defining Server Options

SQL Server provides a number of configuration options to enable varied and different installations of the server. These options are used to customize the way SQL Server's resources are managed. The sorts of resources that are available for management are the following:

Memory
User and logon handling
Sizes of objects
Network and physical I/O handling
Memory, disk, procedure, and read-ahead cache
Symmetric Multi-Processing (SMP) management

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 available. Windows NT has been shown to provide near-linear performance improvements on computers with as many as four CPUs.

SQL Server has two sets of server configuration options available. The default or basic server options contain the normal day-to-day sorts of things that will need to be managed. The advanced server options are provided to allow tuning of things in the server not generally considered necessary. Unless you have been advised by a technical support center to specifically configure one of the advanced options, it is not recommended that you change any of the advanced options. The advanced options can be enabled by turning on the configuration option, Show Advanced Option. For detailed instructions on how to set this and any option, see the section entitled Displaying and Setting Server Options later in this chapter.

Changing server configurations can sometimes make a server unable to be restarted after the change (such as over-committed memory). To restart a server in this situation, it may be necessary to start the server in minimal configuration mode, which enables you to bring up the server without it attempting to apply the configurations that you set. For more information on starting the server in minimal configuration mode, see the end of this chapter.

Displaying and Setting Server Options

SQL Server provides two ways to display and set configuration options for the server. The graphical method is via SQL Server Enterprise Manager. The Transact-SQL method is by using the system-stored procedure, sp_configure.

Using SQL Enterprise Manager

SQL Enterprise Manager is a very convenient tool for the DBA to use when changing 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:

Run SQL Enterprise Manager from the Microsoft SQL Server 6.5 group (see fig. 16.1).
Fig. 16.1 - After just being started, SQL Enterprise Manager shows that no server is selected.
Select the server that you want to work on (see fig. 16.2).
Fig. 16.2 - After selecting a particular server, SQL Enterprise Manager shows all of its properties and objects folders.
From the Server menu select SQL Server Configure. When the Server Configuration/Options dialog box is displayed, click the Configuration page (see fig. 16.3).
Fig. 16.3 - SQL Enterprise Manager's Server Configuration/Options dialog box shows the Configuration page.
To change any of the settings for the server, enter the required value in the Current column and then either click Apply Now to make a change but leave the dialog box up, or click OK to apply the changes and return to the main SQL Enterprise Manager window.

Press the F1 key on any SQL Enterprise Manager dialog box to bring up context sensitive help that will explain the different objects/options available.

Using the System-Stored Procedure sp_configure

sp_configure is a system-stored procedure that is provided to allow the changing of settings on the server. sp_configure is useful for writing automated scripts that update the server without user intervention.

Sp_configure's syntax is as follows:

sp_configure [configuration option, [configuration value]]

The configuration option is the value that is needed to change in the server. SQL Server uses a LIKE operator on the text that is supplied so that any unique set of characters is recognized without requiring the full text value. 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 that all of the sp_configure statements perform the same function.

Listing 16.1—sp_configure Requires Only That the Option Being Changed is Uniquely Identified

sp_configure "nested Triggers", 0
go
sp_configure "nested", 1
go
sp_configure "triggers", 0
go
sp_configure "trig", 0
go

If no parameters are supplied 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_configure is used without a parameter.

Listing 16.2—Executing sp_configure Without Any Options Returns the Current Server Configuration

/*-----------------------------
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 pre-fetches       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)

The sp_configure information produced above includes the advanced options. Notice that the run value for show advanced option is 1.

I'm trying to change a configuration option and sp_configure keeps returning:

Msg 15125, Level 16, State 1
Only the System Administrator (SA) may change configuration parameters.

You're not logged in to the database as SA. Only 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 means that the server has changed the internal value of the configuration, but has not yet applied it. The output shown in Listing 16.3 shows that the configuration before and after is changed in the config value column, but the run value column remains unchanged.

Listing 16.3—Some sp_configure Options Require Reconfiguration of the Server

/*-----------------------------
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 (see Listing 16.4).

Listing 16.4—reconfigure Forces SQL Server to Adjust the Run Value of a Server Option

/*-----------------------------
reconfigure
go
sp_configure "nested"
-----------------------------*/
name                 minimum     maximum     config_value run_value
-------------------- ----------- ----------- ------------ -----------
nested triggers      0           1           0            0

RECONfigURE is available only to dynamic configuration options. These are options that can be changed without requiring the server to be shutdown and restarted. 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 pre-fetches

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

Server Options Explained

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 configuration value, Show Advanced Options. If the word dynamic appears in parentheses to the right of the keyword, then this option can be changed without the shutting down and restarting of the server. See the section on the RECONfigURE command above for more information.

In each item below, there are indications of the minimim, maximum and default values. These values indicate the range of values that the item can have, and the default value to which SQL Server is configured when first installed.

allow updates (dynamic)

Minimum: 0

Maximum: 1

Default: 0

The allow updates configuration option allows the system catalog to be updated. If the value is set to 1 (one) then the system catalog is updateable. 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 (zero).

Allowing updates on the system catalog is an extremely dangerous decision. It should only be done under very controlled situations and should probably be done with 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, it requires an additional keyword, WITH OVERRIDE, when executing the RECONfigURE command. So the following is the correct 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

This configuration option allows the tuning of 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. So by setting the value to 4, the server will be allocating 4 [ts] 32 [ts] 2K = 256K for backups. A larger value will help reduce backup times but will also reduce available memory for the server.

backup threads

Minimum: 0

Maximum: 32

Default: 5

This configuration option controls how many NT service threads will be allocated to striped backup and load operations. Increase this value from the default of 5 when running on multiple CPU machines to improve backup and load times.

cursor threshold (dynamic, advanced)

Minimum: –1

Maximum: 2147483647

Default: 100

This 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 on 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 are up-to-date on the base tables.

If 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 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 still processing other client requests. For large result sets this is the preferred option 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

This option controls the default number of megabytes to reserve for a new database being created. If the majority of the databases that are being created on a given server are greater than 2MB, it would be advisable to change this value. If the model database grows to be greater than 2MB, it will be required to adjust this value.

Because the minimum database size is 1MB, SQL Server databases can exist on floppy disks. See the Chapter 4 section entitled Using Removable Media for Databases for more information.

default language

Minimum: 0

Maximum: 9999

Default: 0

This option controls the default language ID to be used for the server. US English is the default and is always 0 (zero). 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

This option controls the sort order that the server will be using. The sort order controls the way SQL Server sorts data and returns it to the client. The default is Dictionary, Case Insensitive.

Do not use sp_configure to change the sortorder. Use the SQL Server setup program if you wish to change this value. Changing the sortorder will require that you 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

This 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 on the index. This option is overridden if a fill factor is specified when the CREATE INDEX command is executed.

A fill factor of 100 will force SQL Server to fill the index pages completely and should only be used for extremely static tables whose key values never change, grow, or are inserted into. Smaller fill factor values will allow/force SQL Server to reserve space on the index page for new values that may be added to the table/index after the initial table load.

See the Chapter 10 section entitled Creating an Index with CREATE INDEX

free buffers (dynamic, advanced)

Minimum: 20

Maximum: 524288

Default: 204

This 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, then the lazy writer forces information to disk to ensure that memory pages are maintained free.

If a change is made to the memory configuration option, this option is automatically adjusted to five percent of the memory value. After the server has restarted, this value may be modified manually to any value within the range specified above.

hash buckets (advanced)

Minimum: 4999

Maximum: 265003

Default: 7993

This 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; therefore, if a non-prime value is specified, SQL Server will pick the closest prime number to it.

It is unlikely that this value will require modification unless the server has more than 160MB of RAM because 8000 [ts] 2K = 160 MB. Note that the default of 7993 really refers to the closest prime to 8000.

language in cache

Minimum: 3

Maximum: 100

Default: 3

This 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

This configuration option controls the maximum number of page locks that SQL Server will permit a single query before escalating a set of page locks on a table to a full table lock. If the number of pages is exceeded, SQL Server will force a table lock—irrespective of the LE threshold percentage configured for the table. Lock escalation is performed to improve performance on the server because of unnecessary memory being allocated to manage the individual page locks.

LE threshold minimum (dynamic)

Minimum: 2

Maximum: 500000

Default: 20

This configuration option controls the minimum number of page locks that SQL Server will require a single query to acquire before escalating 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 with small numbers of pages of data.

LE threshold percent (dynamic)

Minimum: 1

Maximum: 100

Default: 0

This configuration option controls the percentage of page locks to pages of data in the table, that need to be acquired on a table before SQL Server will escalate the lock to a full table lock. A value of 0 (zero), the default, will not allow SQL Server to perform lock escalation unless the lock escalation threshold maximum is reached.

locks

Minimum: 5000

Maximum: 2147483647

Default: 5000

This configuration option controls the number of locks that the SQL Server can maintain at any time. Each lock consumes 32 bytes of RAM, and so increasing this value to a large number will most likely require more RAM to be made available to the server. For example, setting this value to 20,000 will result in 20,000 * 32 bytes = 640,000 bytes or 625K of RAM consumed just by the lock manager.

logwrite sleep (ms) (dynamic, advanced)

Minimum: –1

Maximum: 500

Default: 0

This option controls the number of milliseconds that SQL Server will wait to write a log entry to disk if the buffer is not full. This 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 write less than full blocks to disk.

A value of –1 is provided to force SQL Server to always write to disk, which should only be used on systems that are extremely concerned about media failure and are not concerned with IO throughput performance. A value of 0 (zero) will force SQL Server to delay writes to disk if, and only if, there are other users on the system in the execute phase of their requests to the server.

max async io

Minimum: 1

Maximum: 255

Default: 8

This configuration option controls the maximum number of asynchronous IO requests that the SQL Server can make to the 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

This configuration option is used to tune the writes from the lazywriter to the real IO subsystem. This value is dynamically configurable, but can only be configured up to the value of actual max async IO. It is not recommended that this value be modified unless someone in your primary support center tells you to.

max worker threads (dynamic)

Minimum: 10

Maximum: 1024

Default: 255

This configuration option controls the maximum number of threads that SQL Server will spawn to handle database operations. By default, SQL Server will spawn at least one thread for each listener service that is installed. In addition, there will be a thread 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 threads that are available 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 (up to the maximum here configured), then SQL Server will use the available threads in a pooling fashion. The next request by a user process that is received at the server will be assigned to the first thread that becomes available after it has completed its assigned task.

media retention

Minimum: 0

Maximum: 365

Default: 0

This configuration 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 (zero), SQL Server will warn the user that they are performing a backup over an existing backup that has not expired its number of retention days.

This is a useful configuration 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.

My users keep overwriting their historical backup tapes with the latest backups of the database. I'm happy they're doing backups at all, but how can I stop them from using the same tape twice.

It is impossible to stop a user from using the same tape twice. However, by using the media retention configuration option, you can stop them from using the tape too quickly after a backup is made. 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

This configuration option controls the maximum number of 2K pages of memory that SQL Server will consume upon 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.

If you over commit 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 fix a server that is no longer starting because memory was over committed.

To help tune the amount of memory being consumed by SQL Server, you can use the DBCC MEMUSAGE command, which will report the way that memory has been allocated on the server and will also show 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 buffer page top 20 or the stored procedure top 20.

Listing 16.5—Using dbcc memusage to Display Information About Memory in Use on the Server

/*-----------------------------
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

This configuration option controls whether triggers will nest or be executed cascaded. If set to 0 (zero), SQL Server will only execute the first trigger that fires when an update or delete action occurs.

I'm concerned that we are having too many triggers firing and it seems like triggers are causing other triggers to fire and I don't know how to monitor this effectively.

You can use the Nested Triggers configuration to stop SQL Server from allowing a trigger to cause another trigger to execute a procedure. Turning off this option will allow you to more closely examine the behavior of triggers in your application.

network packet size (dynamic)

Minimum: 512

Maximum: 32767

Default: 4096

This configuration option controls the server-wide maximum network packet size that is requested by a client. If the client requests a size less than the value specified in the current value, SQL Server will accept it; however, greater values than the current value will be negotiated to the maximum value specified here.

This option can improve performance on networks whose base topology supports wider or larger packets than TCP/IP's default of 4096 bytes. This is especially useful if you are running over a satellite service and wish to batch large packets of data to send through the satellite packet service.

This option should be adjusted to a higher value for reporting-only 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.

Setting the packet size to be too high can cause locking problems on highly transactionally active databases. This is because 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 provided benefits to you.

open databases

Minimum: 5

Maximum: 32767

Default: 20

This configuration option controls the maximum number of databases that SQL Server can maintain in an open condition at any one time. It should not be arbitrarily set to a high value because each open database does consume some server memory.

The DBA has created a new database, but nobody can connect to it.

Check to see that you haven't exceeded the 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 retry to connect.

open objects

Minimum: 100

Maximum: 2147483647

Default: 500

This configuration option controls the maximum number of objects that SQL Server can hold in memory at one time. An object can be a table page, stored procedure that is executing, or any other object in the database. Increase this value if the server ever reports that the maximum number of objects has been exceeded.

Take care when assigning values to this option because it may be necessary to allocate more memory to the server due to the consumption of memory resources by the open objects configuration option.

priority boost (advanced)

Minimum: 0

Maximum: 1

Default: 0

This configuration option controls the priority SQL Server will run at under Windows NT. The default is 0 (zero), meaning that SQL Server will run at a normally high priority, but will allow other tasks to request high threading priority too. If set to 1 (one), 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

This configuration option controls the proportion of memory that SQL Server grabs and allocates to store the stored procedures that have most recently been executed. For systems that have large amounts of stored procedures, it may be necessary to set this value higher than 30 percent if the total amount of memory available to SQL Server is relatively low.

It is recommended that this value be reduced to 10 percent or less on systems with more than 512MB of RAM. It is extremely unlikely that the amount of stored procedures in memory cache will exceed 50MB.

The reason SQL Server has a stored procedure cache is because it does not store the desired query plan/execution plan of the procedure in the database until it is first executed. This explains why the first time a procedure is executed it takes more time to run. SQL Server is pulling the tokenized procedure text out of private tables and is evaluating the text and determining 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

This configuration option is used to control 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 of the Read Ahead Manager.

Changing this value may seriously affect performance on your server. Do not modify this value unless your primary support center has asked you to.

RA cache miss limit (dynamic, advanced)

Minimum: 1

Maximum: 255

Default: 3

This configuration 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 will cause the Read Ahead Manager to be fired for every access of a data page. This will cause terrible performance and a lot of unnecessary disk thrashing.

Changing this value may seriously affect performance on your server. Do not modify this value unless your primary support center has asked you to.

RA delay (dynamic, advanced)

Minimum: 0

Maximum: 500

Default: 15

This configuration option controls the number of milliseconds that the Read Ahead Manager will delay its own execution after a request has been made.

RA pre-fetches (dynamic, advanced)

Minimum: 1

Maximum: 1000

Default: 3

This configuration option controls the number of extents (8 [ts] 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

This configuration 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 may be executing.

RA worker threads

Minimum: 0

Maximum: 255

Default: 3

This configuration option controls the number of Windows NT threads that SQL Server will allocate to the Read Ahead Manager. It is recommended that this value be configured 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

This configuration option controls the information that is displayed during the SQL Server startup process. If set to 0 (zero), the default, then SQL Server will only report that the database is being recovered/restored by name. If set to 1 (one), then SQL Server will report in detail the status of every transaction that was pending at the time the server was shutdown, and what activity SQL Server took to resolve it.

To view the information captured in the error log, select Error Log... from the Server menu in SQL Enterprise Manager.

recovery interval (dynamic)

Minimum: 1

Maximum: 32767

Default: 5

This configuration option controls the number of minutes that SQL Server will require to recover a database in the event that there is a system failure of some kind. 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 the writing of all the changes to dirty data pages from the transaction log information 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

This configuration option controls whether remote SQL Servers are allowed logon access to the server. If set to 0 (zero), SQL Server will deny access to remote SQL Servers.

remote logon timeout (dynamic, advanced)

Minimum: 0

Maximum: 2147483647

Default: 5

This configuration option controls the amount of time (in seconds) that SQL Server will wait before returning an error to the client process that was requesting the logon to a remote server. Setting this value to 0 (zero) will cause the SQL Server to wait indefinitely.

remote query timeout (dynamic, advanced)

Minimum: 0

Maximum: 2147483647

Default: 0

This configuration option controls the amount of time (in seconds) that SQL Server will wait before returning an error to the client process that was requesting the execution of a query on a remote server. Setting this value to 0 (zero) will cause the SQL Server to wait indefinitely.

resource timeout (dynamic, advanced)

Minimum: 5

Maximum: 2147483647

Default: 10

This configuration option controls the amount of time (in seconds) that SQL Server will wait before returning an error to the client process required a server resource. A server resource could be access to a memory buffer, a disk IO request, a network IO request, or a log IO request. This option should be increased 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

This configuration option controls whether SQL Server will request that Windows NT physically allocate and lock memory to the SQL Server. The amount allocated will be equal to the number of pages in the memory configuration option [ts] 2K, plus the amount of memory requested for Tempdb, if tempdb in RAM is configured on.

show advanced option (dynamic)

Minimum: 0

Maximum: 1

Default: 1

This configuration option controls whether SQL Server will display and allow the configuration of other advanced options through sp_configure. If set to 0 (zero), SQL Server will respond that an option does not exist if an advanced option is attempted to be changed.

sp_configure is not allowing me to configure one of the advanced configuration options.

Before being allowed to configure an advanced option, you must first make sure that you have enabled the show advanced option using sp_configure.

SMP concurrency (advanced)

Minimum: –1

Maximum: 64

Default: 0

This configuration option controls how SQL Server will operate on a Symmetric Multi-Processing server. The default configuration for a single CPU computer is 0 (zero), 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 will set 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 box, then this will result in poor performance for any other tasks (besides SQL Server) that are executing on the box.

sort pages (dynamic, advanced)

Minimum: 64

Maximum: 511

Default: 128

This configuration option controls the number of pages that SQL Server will reserve 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 that require data to be sorted in memory. Setting this value high will cause each user to consume larger amounts of available memory and may necessitate that more memory be dedicated to SQL Server.

spin counter (dynamic, advanced)

Minimum: 1

Maximum: 2147483647

Default: 10000

This configuration option controls the maximum number of attempts SQL Server will make to acquire a resource from the SQL Server service manager.

This is an advanced option and should not be altered unless you are advised to do so by a Microsoft Service Center.

tempdb in ram (MB)

Minimum: 0

Maximum: 2044

Default: 0

This configuration option controls the amount of memory that SQL Server will reserve for tempdb in RAM. If set to 0 (zero), tempdb will reside on a physical disk device (the default for which is MASTER's device). If set to any value other than 0 (zero), tempdb will be 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. However, these contiguous chunks may not necessarily be next to the chunks previously allocated. It is recommended that the server be shutdown and restarted if the size of tempdb is altered.

user connections

Minimum: 5

Maximum: 32767

Default: 20

This configuration option controls the maximum number of user processes that can connect to the server at one time. The logical limit is 32767; however, it is very likely that practical limits of server hardware will be exceeded before this limit is ever achieved.

There is a minimum fixed overhead for each user connection of about 40K. If this value is set to a large value it may be necessary to allocate more memory to the SQL Server.

Periodically—usually at times of heavy load on the server—users are reporting that they can't connect.

It's possible that you are running out of available user connections on the server. Use sp_configure to increase the number of user connections to a higher value so that more concurrent users are permitted.

affinity mask

Minimum: 0

Maximum: 0x7fffffff

Default: 0

On SMP machines, affinity mask allows a thread to be associated with a processor. This is done using a bit mask. The processors on which the processes run is represented by each bit. Decimal or hexadecimal values can be used to specify values for this setting.

remote conn timeout

Minimum: –1

Maximum: 32767

Default: 60

This value 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 will be terminated. The only exception to this is when the connection is involved in a DTC-coordinated distribution transaction.

remote proc trans

Minimum: 0

Maximum: 1

Default: 0

This feature 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.

user options

Minimum: 0

Maximum: 4095

Default: 0

The user options are used to set global defaults for users logging into the system. After a change is made, all new logins will be affected but existing logins will not change. Users can override these values by using the SET statement.

SYSCONfigURES and SYSCURCONfigS: System Catalog Tables

SYSCONfigURES and SYSCURCONfigS are system catalog tables that SQL Server uses to store information about configuration options that are in use on the server. They are stored in the master database.

SYSCONfigURES has information about the available options and their defaults that the server has created. Note that the sp_configure option that you see comes from the spt_values table in the master database. Rather than relying on the formatted results returned from sp_configure, it's sometimes necessary to be able to select back (and process in a result set) the configurations available and configured on the server. In Listing 16.6, the query shows you the defaults for all the configurable options in the server.

Listing 16.6 16_01.SQL—Querying the SYSCONfigURES Table to Review the Defaults.

/*-----------------------------
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 pre-fetches       RA pre-fetches                       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)

In the above query, the reserved SQL Server keyword DEFAULT was used as a column title. To use any reserved words as text as a column title, enclose it in quotation marks.

SYSCURCONfigS stores the currently configured values that are being used by the server. In Listing 16.7 the query shows how to get the current values for each of the configurable options in the server. Using SYSCONfigURES and SYSCURCONfigS together will allow you to write your own programs to dynamically set options and report options on the server.

Listing 16.7 16_02.SQL—Querying the SYSCURCONfigS Table to Review the Current Server Configurations

/*-----------------------------
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 pre-fetches       RA pre-fetches                        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)

Both queries above are joined to the SQL Server system table spt_values. This is a special table that SQL Server uses for displaying value/configuration data.

Defining Database Options

SQL Server has several options available at a per database level that enable the database administrator (DBA) to configure how different databases perform/act on a given server.

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.

Displaying and Setting Database Options

SQL Server provides two ways to display and set configuration options for the database. The graphical method is via SQL Server Enterprise Manager. The command-line method is by using the system-stored procedure, sp_dboption.

Using SQL Enterprise Manager

To configure a database using SQL Enterprise Manager, follow these steps:

Run SQL Enterprise Manager from the Microsoft SQL Server 6.5 group.
Select the Server and Database that you want to work on.
Either double-click the database that you want to edit or press the right-mouse button and select Edit from the menu. When the Edit Database dialog box is displayed, click the Options page (see fig. 16.4).
Fig. 16.4 - SQL Enterprise Manager's Edit Database dialog box shows the Options page.
To change any of the settings for the database, click the required options and then press OK to apply the changes and return to the main SQL Enterprise Manager window.

Using sp_dboption

The system-stored procedure sp_dboption can be used 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]

database name is the name of the database that is being viewed or changed.

database option is the name of the option being viewed/changed. Place quotation marks around the option being set if it contains any embedded spaces.

database value is the new value for the option.

If no parameters are supplied to sp_dboption, it will return the available parameters that can be set for any current database. In Listing 16.8, you can see sp_dboption being executed without parameters.

Listing 16.8—Using sp_dboption to Report All the Configurable Database Options

/*-----------------------------
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 configuration option is supplied, sp_dboption returns the currently active configuration options for the database that was indicated (see Listing 16.9).

Listing 16.9 Seeing the set Option on a Database Using sp_dboption

/*-----------------------------
sp_dboption pubs
-----------------------------*/
The following options are set:
------------------------------
select into/bulkcopy
trunc. log on chkpt.

sp_dboption is similar to sp_configure 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 each is the same parameter).

Database Options Explained

The following is a list of all the database options that are available for configuration in user databases. 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.

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 setup to operate correctly.

ANSI null default (Columns Null by Default)

This database option controls the way the CREATE TABLE statement is parsed by the SQL interpreter 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. However, the ANSI standard specifies the reverse, that if not specified a column is NULL.

If the database scripts being used to create a table or set of tables have been created for an ANSI compatible database, it will be necessary to have this option turned on so that the tables generated/created behave the same way as they would on another ANSI compatible database.

DBO use only (DBO Use Only)

This database option controls the user access to the database. If set to True, then the only user that may access the database is the database owner (dbo). If this option is turned on while existing users have connected, they will not be killed. They will be allowed to stay on the database until they disconnect voluntarily.

no chkpt on recovery (No Checkpoint on Recovery)

This database option controls the behavior on 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 turned on. It will stop the database from rejecting further transaction logs being applied.

offline

This database option, if enabled, will bring a database "down" into an offline condition. This option is most often used with removable media, such as floppy diskette or CD-ROM based databases, 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

This database option controls whether a database is available for publishing and subscribing based replication. If 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.

read only (Read Only)

This database option, if enabled, places a database in read-only mode, making it impossible for any Inserts, Updates, or Deletes. 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.

select into/bulkcopy (Select Into/Bulk Copy)

This 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.

This option must be enabled if bulkcopy (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)

This database option limits database access to a single user. If enabled and a user connects, then that user may stay connected; however, any other user will be denied access. If single user mode is turned on, then trunc. Log on chkpt. will be disabled because it requires an additional user connection to the database to act as a monitor.

subscribed

This database option controls whether or not the database can be part of a subscription based replication. If 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)

This database option controls whether or not the database logs will be truncated when a checkpoint activity occurs. By default this option is off and it should always be off in production when it may be necessary to use the transaction logs for replication, backup, or recovery.

I'm checkpointing frequently, but my logs are filling up really quickly and are running out of space, stopping all activity on the server.

Either perform more frequent manual dumping of the transaction logs, enlarge your logs, or use Performance Monitor to run a batch file that will dump the logs when the logs are approaching a full state. For information on Performance Monitor, see Chapter 17, "Optimizing Performance"

Understanding Query Options

SQL Server has a number of individual options that can be set while querying the database. These options control the behavior of queries when they are executed. They are also useful statistical and informational gatherers that can be helpful in diagnosing query problems, such as queries that run really slow for no apparent reason.

Displaying and Setting Database Options

SQL Server provides two ways to display and set configuration options for a query. The graphical method is via ISQL/w or through the Query Analyzer of SQL Server Enterprise Manager. The command-line method is by using the system keyword SET.

Using ISQL/w

To use ISQL/w to set or view query options, perform the following steps:

Run ISQL/w from the Microsoft SQL Server 6.5 group, and logon to the required server (see fig. 16.5).
Fig. 16.5 - The pubs database has been selected to be worked on just after starting ISQL/w.
From the Query menu, select Set Options to display the Query Options dialog box (see fig. 16.6).
Fig. 16.6 - ISQL/w's Query Options dialog box shows the Query Flags page.
To change any of the settings for the query, click the required options and then click OK to apply the changes and return to the main ISQL/w.

SET

SQL Server provides a SQL keyword, the SET statement, that can be used 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.

Query Options Explained

The following is a list of all the query options that are configurable and what they do. In parentheses following the SET option is the equivalent in ISQL/w.

Arithabort (Abort on Arithmetic Error)

This option controls what SQL Server will do when an arithmetic error occurs. If set to True, SQL Server will abort any query that causes a divide by zero or numeric overflow (value is greater than the defined datatype) condition to occur. There is no opportunity to capture this error at runtime, so if this option is not set, the resulting output could be NULL results.

In Listing 16.10, Arithabort is used to stop a command batch from continuing with invalid data:

Listing 16.10 16_03.SQL—Using the Arithabort Option

/* 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'

Here is the output:

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 in that it will stop the SQL Server from reporting an error condition if an arithmetic error occurs. In Listing 16.11, arithignore is demonstrated and shows SQL Server not reporting any error conditions.

Listing 16.11 16_04.SQL—Using arithignore to Ignore Arithmetic Overflows

/* 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'

Here is the output:

(1 row(s) affected)

Second Query
Divide by zero occurred.

(1 row(s) affected)

NOCOUNT (No Count Display)

This option disables the display of the number of rows processed by any SQL statement. The @@ROWCOUNT global variable is still maintained even though this option is turned off. The ouput in Listing 16.12 shows the effect of NOCOUNT.

Listing 16.12 16_5.SQL—Using NOCOUNT to Stop the Reporting of Rows Affected by SQL

/* 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

Here is the output:

# Authors
-----------
23

(1 row(s) affected)

# Authors
-----------
23

NOEXEC (No Execute)

This option controls whether SQL Server will actually execute a SQL statement. If you turn this option on, 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.

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 be updating the records, fetching the data, etc.

PARSEONLY (Parse Query Only)

This option is like NOEXEC except that SQL Server does not even compile the query (generate the access path to the data); all it does is check that the query is syntactically accurate.

SHOWPLAN (Show Query Plan)

This 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 Enterprise Manager, and a graphical representation will be shown (see fig. 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)

This option shows the amount of time the server spent in different areas of parsing, compilation and execution, and answering a query. This information can be very useful in helping to tune queries; however, the data can be skewed because of server caching.

STATISTICS IO (Show Stats I/O)

This option shows the number of logical and physical reads that the server did to answer a query. Logical reads are reads that come from cache pages; physical reads caused the database to go to disk. The number returned is the number of 2K pages read.

Starting the Server in Minimal Configuration Mode

Minimal configuration mode is a mode of 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.0 in place of SQL Server 4.2's and Sybase's bldmaster executable. Prior to version 6.0 of SQL Server the configuration options were written to the bldmaster file and if the server was unstartable, it was necessary to edit this file manually.

This chapter applies only to SQL Server 6.x; the instructions here are not effective against 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:

All configuration values that affect memory, database, and server are set to their minimums as shown in sp_configure.
The stored procedure cache is set to 50 percent of available memory as configured in the minimums of sp_configure. Therefore memory is set 1000 2K pages (or 2MB) and procedure cache is 500 pages (or 1MB).
SQL Server is started in single user mode at the server level (equivalent to the -m command-line option of SQL Server). Because the server is in single-user mode, the CHECKPOINT service is not started. This service is required to guarantee that transactions are written to disk.

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.
Remote Access is disabled because the Remote Access service (that acts as a user) is not able to connect to the server due to single-user limitations.
Read Ahead paging is disabled because the Read Ahead service (that acts as a user) is not able to connect to the server due to single-user limitations.
SQL Enterprise Manager will be unable to be used because it requires more than one connection to the server and consumes more resources than are available during minimal configuration mode. You must use ISQL to fix the configuration option that is causing the server to be unstartable.
No autoexec procedures are run on server startup. Again, these procedures rely on being able to connect to the database and this connection is reserved for the user to correct the server configuration problem through ISQL.
Tempdb is moved into RAM with 2MB of RAM designated for its use.

Starting SQL Server in Minimal Configuration Mode from the Command line

To start the server in minimal configuration mode from the command line, perform the following steps:

Start a command prompt by double-clicking the Command Prompt icon in the Main group of Program Manager.
Type start sqlservr –f.

The start command launches a separate process thread in which SQL Server is executed. This allows you to continue using the same command prompt to do other things. Any Windows NT task can be started.

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 allows more rapid fixing of the server in an emergency situation.

Starting SQL Server in Minimal Configuration Mode with the Services Application in Control Panel

To start the server in minimal configuration mode from using the Services application in Control Panel, perform the following steps:

Start Control Panel by double-clicking the Control Panel icon in the Main group of Program Manager.
Start the Services application by double-clicking the Services icon in the Control Panel window.
Type start sqlservr –f.

Repairing a Server Started in Minimal Configuration Mode

There may be several things that you will need to do to repair a server that has needed to be started in minimal configuration mode.

If you started your server in minimal configuration mode to reset a configuration, then this is what you should do:

Start the server in minimal configuration mode.
Run ISQL.
Execute sp_configure to change the offending configuration value.
Execute reconfigure to change the server value.
Execute shutdown with nowait to shutdown the server.
Restart the server as you normally would, and confirm that it starts OK. If the server still does not start follow these guidelines again and adjust another configuration value.

If you started your server in minimal configuration mode because the server ran out of disk space then, this is what you should do:

Start the server in minimal configuration mode.
Run ISQL.
Execute DISK RESIZE to extend a disk device or DISK INIT to create another disk device.
Execute ALTER DATABASE to extend the database onto, or to add the database to, a new device.
Execute shutdown with nowait to shutdown the server.
Restart the server as you normally would, and confirm that it starts OK. If the server still does not start, follow these guidelines again and modify the devices as appropriate.

From Here...

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:

See Chapter 6, "Retrieving Data with Transact-SQL" to take advantage of some of the configuration options discussed in this chapter and improve the performance of your SELECTs.
See Chapter 13, "Managing Stored Procedures and Using Flow-Control Statements" to take advantage of the new options to stop your stored procedures from getting error conditions and aborting.

QUE Home Page

For technical support For our books And software contact support@mcp.com

Table of Contents

15 - Creating and Using Cursors

17 - Optimizing Performance