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:
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] GO RECONFIGURE WITH OVERRIDE GO
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 GO RECONFIGURE GO
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.