Performance Tuning SQL Server's Configuration Settings

Unless you know exactly what you are doing and have already performed impartial experiments that prove that making SQL Server configuration changes helps you in your particular environment, do not change any of the SQL Server configuration settings that affect server performance.

SQL Server, in most cases, knows how to configure itself dynamically for optimum performance, and does a good job of it. In some cases, changing SQL Server configuration settings can end up causing more problems than they fix.

If you do decide that changing a specific setting is needed, be sure to test it out thoroughly on a test system before making the change on your production system. [7.0, 2000, 2005] Updated 3-6-2006

*****

The easiest way to view your SQL Server's configuration settings is to run the following command in Query Analyzer:

sp_configure

This will produce a table similar to this one:

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. You will have to check with the Books Online to see what all of them all. [7.0, 2000] Added 8-27-2002

*****

Most, but not all, of the SQL Server configuration settings can be changed using Enterprise Manager or Management Studio. 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

Where:

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 or Management Studio’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 run either the RECONFIGURE (normal settings) or the RECONFIGURE WITH OVERRIDE (used for settings that can get you into trouble if you make a mistake) option, 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 just to use RECONFIGURE WITH OVERRIDE all the time, as it works with all configuration settings. If you use Enterprise Manager or Management Studio to change a setting, it will execute RECONFIGURE WITH OVERRIDE automatically, so you do not have to.

Once you do this, most, but not all, settings go into effect immediately. For those that do not go into effect after RECONFIGURE, the SQL Server service has to be stopped and restarted. The table above tells you which of the performance-related configuration settings require that the service be restarted once they have been changed.

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. The table above tells you which of the performance-related configuration settings are "advanced" settings. [7.0, 2000, 2005] Updated 3-6-2006

If you are running SQL Server 6.5, don't make the most common mistake made by DBAs, and that is to accept the default Memory setting, which is either 8MB or 16MB, depending on how much RAM the physical server has. This number tells SQL Server how much RAM is can use out of the available RAM on NT Server. I have run across many 6.5 SQL Servers with the default memory settings, including one server that had 2GB of RAM.

This number needs to be bumped up manually, as SQL Server 6.5 does not have the ability to dynamically configure memory as does later versions of SQL Server. [6.5]