SQL Server 2000 Configuration Performance Checklist

Performance Audit Checklist

Enter your results in the table above.

Most SQL Server Configuration Settings Should Not Be Changed

In this section, we are going to take a look at some of the performance-related SQL Server configuration settings. These are SQL Server-specific settings that can be modified using either Enterprise Manager or SP_CONFIGURE.

As the title of this section says, in most cases, you should not modify the default SQL Server configuration settings. This is because most of the default settings provided will provide the optimum performance for most SQL Servers. And most of all, if you are not exactly sure of what the implications are of changing a setting, it is possible to hurt your server’s performance instead of boosting it.

If this is the first time you have dealt with this particular SQL Server, one of your first steps should be to review the various configuration settings and then compare them to default settings in order to see which ones, if any, have been changed from the defaults. Once you have identified any of the changed settings, your next goal should be to find out why they were changed. If you can’t find out why, or if you do find out why, but the reasoning behind the change is flimsy, then you will want to change the settings back to the default values. Once you have done this, your next step is to review all of the other settings (those that were set to default when you started) and evaluate each one in order to see if there might be a benefit of changing the value from the default value to a more appropriate value.

The focus of this article will be SQL Server 2000, although most of the advice applies equally to SQL Server 7.0. Before trying any of these suggestions under SQL Server 7.0, you will want to review the configuration setting section in the SQL Server 7.0 Books Online just to be sure.

There are a total of 36 different SQL Server configuration settings in SQL Server 2000. We will only focus on 23 key performance-related ones here.

Getting Started

The easiest way to begin your audit of a SQL Server’s configuration settings is to run the following command, for each of your servers, in Query Analyzer:


This will produce a table similar to this one:

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          1            1
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)         & nbsp;  0           2147483647  5            5
remote proc trans                   0           1           0            0
remote query timeout (s)            0           2147483647  600          600
scan for startup procs              0           1           0            0
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

The first column, “name,” is the name of the SQL Server configuration setting. The second column, “minimum,” is the smallest legal value for the setting. The third column, “maximum,” is the largest legal value for the setting. The fourth column, “config_value,” is what the setting has been set to (but may or may not be what SQL Server is actually running now. Some settings don’t go into effect until SQL Server has been restarted, or until the RECONFIGURE WITH OVERRIDE option has been run, as appropriate.) And the last column, “run_value,” is the value of the setting currently in effect. If you have not changed any of these values since the last time you restarted SQL Server, then the values in the last two columns will always be the same.

Unfortunately, the default values for these settings are not listed when you run SP_CONFIGURE. For your convenience, this article lists the default values of those configuration settings we discuss here (see chart above).

How to Change SQL Server Configuration Settings

Most, but not all, of the SQL Server configuration settings can be changed using Enterprise Manager. But one of the easiest ways to change any of these settings is to use the SP_CONFIGURE command, like this:

SP_CONFIGURE [‘configuration name‘], [configuration setting value]


configuration name = The name of the configuration setting (see the name in the table above). Note that the name must be enclosed in single quote marks (or double quote marks, depending on Query Analyzer’s configuration).

configuration setting value = The numeric value of the setting (with no quote marks).

Once SP_CONFIGURE has run, you must perform one additional step. You must either run the RECONFIGURE option (normal settings) or the RECONFIGURE WITH OVERRIDE option (used for settings that can get you into trouble if you make a mistake), otherwise your setting change will not go into effect. Rather than trying to remember when to use each different version of the RECONFIGURE command, it is easier to just use RECONFIGURE WITH OVERRIDE all the time, as it works with all configuration settings. If you use Enterprise Manager to change a setting, it will execute RECONFIGURE WITH OVERRIDE automatically, so you don’t have to.

Once you do this, most, but not all, settings go into effect immediately. For those that don’t go into effect after RECONFIGURE, the SQL Server service has to be stopped and restarted.

Before we are finished with this topic, there is one more thing you need to know. Some of the configuration settings are considered “advanced” settings. Before you can change these options using the SP_CONFIGURE command, you must first change one of the SQL Server configuration settings to allow you to change them. The command to do this is:

SP_CONFIGURE ‘show advanced options’, 1

Only after you have run the above code may you now run SP_CONFIGURE to change an advanced SQL Server configuration setting.

Now that you know how to change the SQL Server configuration options, let’s take a look at those that are related to performance.


Leave a comment

Your email address will not be published.

SQL Server
Configuration Settings
Default Value Current Value
affinity mask  Yes Yes 0
awe enabled  Yes Yes 0
cost threshold for parallelism  Yes No 5
cursor threshold  Yes No -1
fill factor (%)  Yes Yes 0
index create memory (KB)  Yes No 0
lightweight pooling  Yes Yes 0
locks  Yes Yes 0
max degree of parallelism  Yes No 0
max server memory (MB) Yes No 2147483647
max text repl size (B) No No 65536
max worker threads Yes Yes 255
min memory per query (KB) Yes No 1024
min server memory (MB) Yes No 0
nested triggers No No 1
network packet size (B) Yes No 4096
open objects Yes Yes 0
priority boost Yes Yes 0
query governor cost limit Yes No 0
query wait (s)  Yes No -1
recovery interval (m) Yes No 0
scan for startup procs Yes No 0
set working set size Yes Yes 0
user connections Yes Yes 0