Configuration Options

For SQL Server 2000, this chapter will discuss the configurable options in two distinct categories: basic options and advanced options. The advanced options are a super-set of the basic options. As each option is discussed, it will be noted whether it is self-configuring. Several self-configuring options are available that adjust themselves dynamically according to the needs of the system. In most cases, this eliminates the need for setting the values manually. Sometimes you won't want to rely on certain self-configuring values, depending on how SQL Server is being used.

As you can see in Figure 40.2, SQL Server provides configuration parameters that the system administrator can set to maximize the performance of a system. You can set these parameters either by using the sp_configure system-stored procedure or by using SQL Server Enterprise Manager and the server configuration properties.

Figure 40.2. SQL Server 2000 Configuration Properties dialog box.

Now, the only questions that need to get answered are "What configuration options do I need to set that aren't already fine?" And, "How do I set them?"

To answer the "what" question, you will first determine for what purpose the applications are using SQL Server. This must include understanding variables such as the number of potential connections to support, the amount of resources available on the box, the size of the database, the type of data accesses occurring, and the workload being put on SQL Server. After you know this, it will be easy to determine the configuration option setting to adjust.

Figure 40.3 shows a generalization of the types of applications that you might find in the real world that would be implemented using SQL Server 2000 and the general behavior that they elicit. It is not a complete list, just a generalized list. The four basic categories of application processing are online transaction processing (OLTP), applications, data warehouse/data mart applications, online analytic processing (OLAP) applications, and mixed applications (some OLTP along with some data marts on one SQL Server). Because these configuration options are set at the SQL Server level, it is important to know the combined behavior of all application processing.

Figure 40.3. General application processing types for SQL Server.

For each SQL Server configuration option that is discussed, you will address the correct setting based on the generalized application processing behaviors that need to be supported.

The "How do I set them?" question will be discussed now. The next few sections describe all of the types of configuration options available on SQL Server 2000. Those sections will show you how to set these configuration options using both Enterprise Manager and sp_configure system-stored procedure. The rule will be that you can certainly set a configuration option using Enterprise Manager, but that you should keep an sp_configure version of that setting change as a backup in case you need to rebuild the entire server configuration from scratch. In addition, keeping an sp_configure version around in a file will provide a great audit trail of what you did and for what reason.

You have already seen, in Figure 40.2, the Enterprise Manager Configuration Properties dialog box. Now, you will look at the sp_configure equivalent. By executing sp_configure without parameters, you will be given the list of options that can be addressed. When you have just installed a SQL Server instance, you will be able to see only the basic configuration options.

Ten basic configuration options are available:

 sp_configure  go name                           minimum     maximum     config_value run_value ------------------------------ ----------- ----------- ------------ -------- allow updates                       0           1           0            0 default language                    0           9999        0            0 max text repl size (B)              0           2147483647  65536        65536 nested triggers                     0           1           1            1 remote access                       0           1           1            1 remote login timeout (s)            0           2147483647  20           20 remote proc trans                   0           1           0            0 remote query timeout (s)            0           2147483647  600          600 show advanced options               0           1           0            0 user options                        0           32767       0            0 

By default, all SQL Server users have permission to run this system-stored procedure, but only users who have sysadmin and serveradmin fixed server roles (such as sa) can actually set the value of a parameter.

The proper syntax of the sp_configure command is as follows :

 sp_configure  [  parameter_name  [,  parameter_value  ]] 

where parameter_name is the name of the configuration parameter you want to set, and parameter_value is the value for the parameter. Both of these parameters are optional. Parameters set by sp_configure take effect at the server level.

Following is a brief explanation of the output of the sp_configure command. As you can see, the output consists of five columns :

• Name ”Name of the configurable option.

• Minimum ”This is the minimum legal value allowed for this parameter. Passing an illegal value causes SQL Server to return an error.

• Maximum ”This is the maximum legal value allowed for this parameter. Passing an illegal value causes SQL Server to return an error.

• Config_value ”This column reflects the values that are going to take effect the next time SQL Server is started. If you change static parameters, the new values are listed under this column.

• Run_value ”This column reflects the values that SQL Server is currently using. If you change any dynamic parameters, the new values are listed in this column. At the time of SQL Server startup, the config_value for all the parameters is copied into run_value . Immediately after restart, both columns ( run_value and config_value ) should display the same values corresponding to each parameter.

If you specify only the parameter name, SQL Server returns the current configuration value for that particular parameter.

 sp_configure 'allow updates'  go name                           minimum     maximum     config_value run_value ------------------------------ ----------- ----------- ------------ -------- allow updates                       0           1           0            0 

Many more configuration options are available from the total of 36 options. These consist of the original 10 basic options plus 26 advanced options. To see a complete list of all options, you must turn on the Show Advanced Options configuration option with the value 1.

In addition, when using sp_configure to change a setting, use the RECONFIGURE WITH OVERRIDE statement for the change to take effect immediately. You can also choose to use just the RECONFIGURE statement. Depending on the configuration option, it will take effect immediately or not until the server has been restarted.

The following commands will set the Show Advanced Options configuration option and then retrieve the complete list of these options:

 exec sp_configure 'Show Advanced Options', 1/* Advanced config options */  go RECONFIGURE WITH OVERRIDE               /* to have it take effect immediately */ go sp_configure go name                           minimum     maximum     config_value run_value ------------------------------ ----------- ----------- ------------ -------- affinity mask                       -2147483648 2147483647  0            0 allow updates                       0           1           0            0 awe enabled                         0           1           0            0 c2 audit mode                       0           1           0            0 cost threshold for parallelism      0           32767       5            5 cursor threshold                    -1          2147483647  -1           -1 default full-text language          0           2147483647  1033         1033 default language                    0           9999        0            0 fill factor (%)                     0           100         0            0 index create memory (KB)            704         2147483647  0            0 lightweight pooling                 0           1           0            0 locks                               5000        2147483647  0            0 max degree of parallelism           0           32          0            0 max server memory (MB)         4           2147483647  2147483647   2147483647 max text repl size (B)              0           2147483647  65536        65536 max worker threads                  32          32767       255          255 media retention                     0           365         0            0 min memory per query (KB)           512         2147483647  1024         1024 min server memory (MB)              0           2147483647  0            0 nested triggers                     0           1           1            1 network packet size (B)             512         65536       4096         4096 open objects                        0           2147483647  0            0 priority boost                      0           1           0            0 query governor cost limit           0           2147483647  0            0 query wait (s)                      -1          2147483647  -1           -1 recovery interval (min)             0           32767       0            0 remote access                       0           1           1            1 remote login timeout (s)            0           2147483647  20           20 remote proc trans                   0           1           0            0 remote query timeout (s)            0           2147483647  600          600 scan for startup procs              0           1           1            1 set working set size                0           1           0            0 show advanced options               0           1           1            1 two digit year cutoff               1753        9999        2049         2049 user connections                    0           32767       0            0 user options                        0           32767       0            0 

Microsoft suggests that only highly experienced SQL Server administrators change these advanced configuration options. You have been warned !

SQL Server internally maintains two tables: syscurconfigs and sysconfigures. The syscurconfigs table contains the current configuration values of SQL Server parameters. These values are shown under the run_value column of sp_configure. The following is what you might expect to see if you query this table directly:

 SELECT value, config, substring (comment,1,50),status from master..syscurconfigs  Go value       config                                                    status ----------- ------ -------------------------------------------------- ------ 8           1      Major revision number of config data.              0 0           2      Minor revision number of config data.              0 13          3      Reconfigure revision number of config data         0 2           4      Configuration boot source.                         0 0           101    Maximum recovery interval in minutes               3 0           102    Allow updates to system tables                     1 0           103    Number of user connections allowed                 2 0           106    Number of locks for all users                      2 0           107    Number of open database objects                    2 0           109    Default fill factor percentage                     2 0           1537   Media retention period in days                     2 1           115    Allow triggers to be invoked within triggers       1 1           117    Allow remote access                                0 2049        1127   two digit year cutoff                              3 1033        1126   default full-text language                         3 0           124    default language                                   1 255         503    Maximum worker threads.                            2 0           542    Create DTC Transaction for RPC                     1 10          543    remote connection inactivity timeout               0 4096        505    network packet size                                3 0           1505   Memory for index create sorts (kBytes)             3 0           1517   Priority boost                                     2 1           518    Show advanced options                              1 20          1519   remote login timeout                               1 600         1520   remote query timeout                               1 -1          1531   cursor threshold                                   3 1024        1540   minimum memory per query (kBytes)                  3 -1          1541   query wait (s)                                     3 0           1532   set working set size                               2 0           1534   user options                                       1 0           1535   affinity mask                                      2 65536       1536   max text repl size                                 1 5           1538   cost threshold for parallelism                     3 0           1539   maximum degree of parallelism                      3 0           1543   Minimum Server Memory size (MB)                    3 2147483647  1544   Maximum Server Memory size (MB)                    3 0           1545   Maximum estimated cost of query allowed to run by  3 0           1546   User mode scheduler uses lightweight pooling       2 1           1547   scan for startup stored procedures                 2 0           544    c2 audit mode                                      2 0           1548   AWE enabled in server                              2 

The sysconfigures table stores the new values of the parameters that were changed since the last SQL Server startup. These values are shown in the config_value column of sp_configure.

 SELECT value, config, substring (comment,1,50),status from master..sysconfigures  Go value       config                                                    status ----------- ------ -------------------------------------------------- ------ 0           101    Maximum recovery interval in minutes               3 0           102    Allow updates to system tables                     1 0           103    Number of user connections allowed                 2 0           106    Number of locks for all users                      2 0           107    Number of open database objects                    2 0           109    Default fill factor percentage                     2 1           115    Allow triggers to be invoked within triggers       1 1           117    Allow remote access                                0 0           124    default language                                   1 255         503    Maximum worker threads                             2 4096        505    Network packet size                                3 1           518    show advanced options                              1 0           542    Create DTC transaction for remote procedures       1 0           544    c2 audit mode                                      2 1033        1126   default full-text language                         3 2049        1127   two digit year cutoff                              3 0           1505   Memory for index create sorts (kBytes)             3 0           1517   Priority boost                                     2 20          1519   remote login timeout                               1 600         1520   remote query timeout                               1 -1          1531   cursor threshold                                   3 0           1532   set working set size                               2 0           1534   user options                                       1 0           1535   affinity mask                                      2 65536       1536   Maximum size of a text field in replication.       1 0           1537   Tape retention period in days                      2 5           1538   cost threshold for parallelism                     3 0           1539   maximum degree of parallelism                      3 1024        1540   minimum memory per query (kBytes)                  3 -1          1541   maximum time to wait for query memory (s)          3 0           1543   Minimum size of server memory (MB)                 3 2147483647  1544   Maximum size of server memory (MB)                 3 0           1545   Maximum estimated cost allowed by query governor   3 0           1546   User mode scheduler uses lightweight pooling       2 1           1547   scan for startup stored procedures                 2 0           1548   AWE enabled in the server                          2 0           1549   affinity64 mask                                    2 

Dynamic parameters are written to both of these tables. Static parameters are written only to the sysconfigures table. At SQL Server restart, all of the values are copied from the sysconfigures table to the syscurconfigs table.