SQL Server Configuration Performance Checklist

Performance Audit Checklist

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.

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:

SP_CONFIGURE

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