Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Configuring SQL Server
SQL Server is largely self-configuring, and in many cases it works correctly "right out of the box." You should understand some options, however, in case your circumstances require changes from the default behavior. You can change all configuration options with Transact-SQL, many of them with Enterprise Manager, and all of them with SQL DMO.
This section discusses generic options, covering options that relate to specific tasks as the task is discussed. Configuration options are classified on two different dimensions:
Standard versus advanced. Standard options are the most commonly changed, whereas advanced options are infrequently changed.
Static versus dynamic. Static options do not take effect until the server is restarted, whereas the dynamic options take effect immediately.
In SQL Server 7.0, the advanced options are visible by default. If you turn off the Show Advanced Options, you can't modify any of the advanced options using Transact-SQL. In the following discussions on options, these categories are in parentheses at the end of the description of the option:
(S, S). Standard option, requires restart
(S, D). Standard option, takes effect immediately
(A, S). Advanced option, requires restart
(A, D). Advanced option, takes effect immediately
Use these category descriptions to understand when options take effect and when you are required to restart SQL for them to take effect.
Miscellaneous Options
Some options apply to the server as a whole and don't fit into a neat category such as "memory." I've grouped them here.
Allow Updates. When this option is set to true, direct updates to the system tables are permitted. In most cases, you do not want this option to be turned on. In those rare cases in which you must modify system tables, I recommended that you do it with the server in single-user mode. Note that if this setting is true when you create a stored procedure, the stored procedure can modify system tables even after the option is turned off. (S, D)
Default Language. This option specifies the ID (taken from syslanguages) for the default language, and controls default formats for displaying dates and gives meaning to the date parts.
If you have a localized version of SQL Server (French, German, Spanish, Japanese), the default language specifies the language in which error messages display. If you have a Japanese SQL Server, and the default language is Japanese, for example, SQL Server error messages display in Japanese. If the default language on a localized SQL Server is not the local language, the messages displays in U.S. English. If you have a French SQL Server and specify Italian as the default language, for example, all error messages will be in U.S. English. (S, D)
Language in Cache. This option specifies the number of languages that can be held in cache. The default is 3. (S, S)
Network Packet Size (B). The network packet size specifies the network packet size in bytes. By default, SQL Server uses 4096 bytes as the packet size. If you routinely send large amounts of data across the network, you may benefit from a larger packet size. Conversely, if most of your transmissions are small, you might want to set this to 512 bytes, which is sufficient for many small data transmissions. If you have multiple protocols, set the network packet size to that which is appropriate for the most commonly used protocol. Note that a client application can specify a packet size that differs from the one specified here. (A, D)
Recovery Interval (Min). This option specifies the number of minutes it will take SQL Server to recover all databases on the system at startup. It influences the frequency with which SQL Server takes checkpoints. By default, it is 0, which means that SQL Server configures it appropriately. In general, you should leave this option alone unless you find that checkpoints are being taken too frequently (see Chapter 6 for a discussion of checkpoints). In that case, you may want to experiment with increasing the value in small increments. (A, D)
Show Advanced Options. This option determines whether you can see or change the advanced options when you run sp_configure. By default, it is set so that advanced options are visible.
Note that if you run the Upgrade Wizard (see Chapter 2, "Installation and Upgrade," for more information on this) and ask to migrate SQL Server 6.5 configuration options, you will find that this option is set to off. (S, D)
Danger
Updating system tables directly can cause your server to fail to start or to behave erratically.
Memory-Related Options
The options in this section all relate to how SQL Server uses memory. In SQL Server 7.0, memory is largely self-configuring. If you have a machine running only SQL Server, you probably won't have to modify these settings. If you are running other applications, however, you may want to adjust some of these settings.
By default, SQL Server monitors its environment. When the operating system has less than 5MB (± 200KB) of free memory, SQL Server gives memory it would have normally kept for its available pool back to the operating system. When there is more than 5MB free, SQL Server takes the memory back.
The memory options available are as follows:
Extended Memory Size (MB). This option is primarily for forward compatibility. It will be available in future versions of SQL Server running on a future version of Windows NT that has support for 64-bit addressing. Some hardware vendors may support this capability under Windows NT 4.0. (A, S)
Locks. In previous versions of SQL Server, the amount of space available for locks was fixed, and it was necessary to tinker with this option. In SQL Server 7.0, SQL Server allocates 2% of the available memory for locks. When additional memory is needed, SQL Server allocates it unless doing so would reduce the amount of free memory for the OS to less than 5MB. If this happens, SQL Server issues a message that you are out of locks. You should change the number of locks only if you receive this message. The default for this option is 0, which means that it is self-configuring. If you explicitly specify this value, the value must be between 5,000 and 2,147,483,647. (A, S)
Min Server Memory (MB), Max Server Memory (MB). These two options work together. If you set Min Server Memory and Max Server Memory to the same value, SQL Server uses a fixed portion of memory. If SQL Server is the only application running on the machine, leave these settings alone. If other applications are running, setting a Max Server Memory influences how quickly they start up, because there is normally a delay between when the competing application begins and when SQL Server frees memory for it. If you want to guarantee that SQL Server has a set minimum amount of memory no matter what, specify a Min Server Memory. The smallest value you can specify for Max Server Memory is 4MB; the highest value is limited by the resources available on your system. If you specify Min Server Memory, and the server is involved with replication, the value must be greater than or equal to 16.
If your server is periodically idle for long periods of time, you may want to set Min Server Memory so that the memory is immediately available when a query does come. If you have other critical applications on the machine that might also have idle applications, you might want to set Max Server Memory so that memory is available to those applications when they become active. (A, S)
Set Working Set Size. This option reserves physical memory for SQL Server. If Windows NT must page, it must swap out other processes. By default, this option is off, and idle SQL Server memory can be swapped out. It is best to leave it off. (A, S)
Open Objects. An open object is any table, stored procedure, view, and so on currently in use. Note that no matter how many users there are for an object, it is one open object. This option is self-configuring by default, and in most cases you should leave it that way. If you receive repeated instances of the following message in your Error log, you may want to consider setting it to some value:
I would start with 10,000 and keep increasing it until the messages go away. Note that doing this reserves memory for open objects that cannot be reused for other purposes. (A, S)
General Tip
If you have installed Full-Text Indexing and are running the MS Search Service, you must specify a Max Server Memory so that there is enough room for the Search Service to run. In this case, you must configure NT's virtual memory so that there is virtual memory (virtual memory for SQL Server and virtual memory for any other concurrently running applications equal to 1.5 times the machine's physical memory for the search service):
NT virtual memory "e 1.5 x physical memory
You also need to specify a Max Server Memory if you are running SQL Server on the same machine as Exchange Server.
Warning
OPEN OBJECTS parameter may be too low; attempt was made to free up descriptors in localdes(). Run sp_configure to increase parameter value.
Look, But Don't Touch Options
All the options listed in this section enable you to look at information. You should never modify the sort order ID or the Unicode information. The only successful way to change these is to reinstall SQL Server. Likewise, there is nothing to be gained from modifying the User Connections option. Following are the "look, but don't touch" options:
Default SortOrder ID, Unicode Comparison Style, Unicode Locale ID. These options show you the sort order ID, Unicode comparison style, and Unicode locale that you chose when you installed SQL Server. These options are numbers; to find out what they mean, issue the following command:
sp_helpsort
User Connections. In previous versions of SQL Server, it was necessary to specify the maximum number of concurrent connections to SQL Server. In this release, SQL Server manages the number of connections dynamically and there is no reason to specify a value for this option. If you set it too low, users may be denied access to SQL Server. (A, S)
General Tip
Don't confuse connection with user. Each user may have many simultaneous connections to the server. You can't use the User Connections option to limit concurrent users.
Options That Affect Applications
Some of the SQL Server options can change the behavior of applications. The following list identifies these options:
Two Digit Year Cutoff. Since time immemorial, SQL Server has observed the rule that, if dates are entered without a century, values less than 50 are assumed to be in 20xx and values greater than 50 are assumed to be 19xx. Therefore, 1/14/44 is interpreted as 1/14/2044, and 1/14/56 is interpreted as 1956. However, the OLE automation interface, including the capability to maintain data in Enterprise Manager (and ADO as well), has a cutoff of 30. With this rule, 1/14/44 becomes 1/14/1944. If at the time you are reading this you still have any applications that are not providing the century as part of the date, you probably have bigger things to worry about than this option. Your best option is to leave it alone and make sure the century is always supplied.
Nested Triggers. When you are working with triggers, you can set up your applications so that a trigger on Table A performs some operation on Table B. With nested triggers, if there is a trigger on Table B, the trigger fires when Table A's trigger modifies Table B. This makes triggers much smaller and much more modular. It is unusual, although not impossible, for there to be situations in which this behavior is undesirable. In such a case, you turn nested triggers off. In most cases, you do not want to do so. Nested triggers has been SQL Server's default since 4.2; be very careful changing this if you have legacy applications that have been upgraded. This is a serverwide option. Don't confuse it with "recursive triggers," which are requested at a database level. (S, D)
User Options. Many session-level (connection-level) settings control the behavior of applications. These options are specified with the SET statement. Some of these can be specified at the server level by using the appropriate bit masks for the User Options configuration option. The following options can be set at the server level.
Danger
It is very easy to enter incorrect date data in Enterprise Manager's Open Table, Return All Rows dialog box because the dates display in mm/dd/yy format without century. Enterprise Manager, being a graphical tool going through OLE automation, uses 30 as the pivot point.
|
SET Option |
Bit Mask |
|
DISABLE_DEF_CNST_CHK |
1 |
|
IMPLICIT_TRANSACTIONS |
2 |
|
CURSOR_CLOSE_ON_COMMIT |
4 |
|
ANSI_WARNINGS |
8 |
|
ANSI_PADDING |
16 |
|
ANSI_NULLS |
32 |
|
ARITHABORT |
64 |
|
ARITHIGNORE |
128 |
|
QUOTED_IDENTIFIER |
256 |
|
NOCOUNT |
512 |
|
ANSI_NULL_DFLT_ON |
1024 |
|
ANSI_NULL_DFLT_OFF |
2048 |
- - See the discussion of the SET statement in your SQL Server documentation for the meaning of these settings. If you are not comfortable working with bit masks, you can set these on or off through the Enterprise Manager graphical user interface. (S, D)
Changing Configuration Options with Transact-SQL
To change a configuration option in Transact-SQL, use the following command:
sp_configure [option [, value]
If you just issue the command sp_configure in Query Analyzer, a report appears showing all the configuration options available together with their minimum, maximum, current, and next values. This report looks like the one shown in here.
|
Name |
Minimum |
Maximum |
config_value |
run_value |
|
Affinity Mask |
0 |
2147483647 |
0 |
0 |
|
Allow Updates |
0 |
1 |
0 |
0 |
|
Cost Threshold for Parallelism |
0 |
32767 |
5 |
5 |
|
Cursor Threshold |
–1 |
2147483647 |
–1 |
–1 |
|
Default Language |
0 |
9999 |
0 |
0 |
|
Default SortOrder ID |
0 |
255 |
51 |
51 |
|
Extended Memory Size (MB) |
0 |
2147483647 |
0 |
0 |
The Name column in this report is the name of the particular option, and what you use if you want to change that option. The column headed config_value is the value that will be used the next time SQL Server is started. The column headed run_value is the value SQL Server is currently using. A minimum and maximum of 0 and 1, respectively, indicates that it is a true-false option. If there is a minimum and maximum, but the run_value is 0, it's most likely one of the options that SQL Server manages dynamically.
If you issue the sp_configure command with just the name of an option, you will see the line of the preceding report for only that option. For example:
sp_configure 'allow updates'
This shows you the settings for just the Allow Updates option. You don't need to spell the option name out in full; any unique left substring works. Therefore, for Allow Updates, you can use 'al', 'allow', and so on.
To change the value of an option, add a value to the command such as this:
sp_configure 'allow',
After you have done this, you must use the reconfigure (or, in some cases, the reconfigure with override) command to make the option take effect. If the option is dynamic, the change is made immediately. If it's a static option, you must stop the server and restart it before the change takes effect.
Changing Configuration Options with Enterprise Manager
Not all configuration options can be changed in Enterprise Manager. To change options using Enterprise Manager, right-click the server in the console pane and choose Properties. The SQL Server Properties dialog box displays, which includes several various tabs and options. Two screens in this dialog box can be confusing if you've never worked in them before. The first is the Memory Configuration screen shown in Figure 3.2.
){kind=link}
Figure 3.2 Memory Configuration screen.
The check box labeled Reserve Physical Memory for SQL Server is the same as the Set Working Set Size option discussed previously.
Another confusing screen is the Connection Options screen shown in Figure 3.3.
){kind=link}
Figure 3.3 Connection Options screen.
The check boxes for the Default Connection Options correspond to the bit masks for the User Options configuration option described previously.
In Enterprise Manager, the Apply and OK buttons cause the dynamic options to take effect. In addition, Enterprise Manager asks you whether you want to stop and restart the server when you modify a static option. If you are ready to stop the server, choose Yes. If you want to wait until a later time to stop the server (remembering that the option won't take effect until you do so), choose No.
Changing Configuration Options with DMO
The SQL Server object has a configuration object, which contains the ConfigValues collection. Each ConfigValue object has the following useful properties.
|
Property |
Description |
|
CurrentValue |
Long |
|
RunningValue |
Long |
|
MaximumValue |
Long |
|
MinimumValue |
Long |
|
Name |
String |
|
DynamicReconfigure |
True if it's a dynamic option; false if it's a static option |
To change a configuration option, you set the value for the appropriate option (using the names specified) as follows:
Set oConfigValue = oSQLServer.Configuration.ConfigValues("Show Advanced _Options")oConfigValue.CurrentValue =
When you are ready to apply the changes, you use the ReconfigureCurrentValues method of the configuration object. If the configuration option requires with override as does the Allow Updates option, use the ReconfigureWithOverride method instead. If any of the changes are to static options, you must use the Stop and Start methods of the SQL Server object to make the changes take effect.
Behind the Scenes
Information about configuration option settings is contained in two different tables, sysconfigures and syscurconfigs:
sysconfigures. This table contains the options SQL Server started with as well as any changes made to dynamic options since the server was started.
syscurconfigs. This table contains values used the next time SQL Server is started. Note that syscurconfigs is a dynamic table built only when it is referenced.