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
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 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.
Affinity Mask
When SQL Server is run under Windows Server, a SQL Server thread can move from one CPU to another. This feature allows SQL Server to run multiple threads at the same time, generally resulting in better load balancing among the CPUs in the server. The only downside to this process is that each time a thread moves from one CPU to another, the processor cache has to be reloaded, which can hurt performance in some cases.
In cases of heavily-loaded servers with more than 4 CPUs, performance can be boosted by specifying (to a limited degree) which processor(s) should run a specific thread. This reduces the number of times that the processor cache has to be reloaded, helping to eek out a little more performance of the server. For example, you can specify that SQL Server will only use some of the CPUs, not all of them available to it in a server.
The default value for the “affinity mask” setting, which is “0,” tells SQL Server to allow the Windows Scheduling algorithm to set a thread’s affinity. In other words, the operating system, not SQL Server, determines which threads run on which CPU, and when to move a thread from one CPU to another CPU. In any server with 4 or less CPUs, the default value is the best overall setting. And for servers with more than 4 CPUs, and that are not overly busy, the default value is also the best overall setting for optimum performance.
But for servers with more than 4 CPUs, and are heavily loaded because of one or more non-SQL Server applications are running on the same server as SQL Server, then you might want to consider changing the default value for the “affinity mask” option to a more appropriate value. Please note that if SQL Server is the only application running on the server, then using the “affinity mask” to limit CPU use could hurt performance, not help it.
For example, let’s say you have a server that is running SQL Server, multiple COM+ objects, and IIS. Let’s also assume that the server has 8 CPUS and is very busy. By reducing the number of CPUs that can run SQL Server from 8 to 4, what will happen is that SQL Server threads will now only run on 4 CPUs, not 8 CPUs. This will reduce the number of times that a SQL Server thread can jump CPUs, reducing how often the processor cache as to be reloaded, helping to reduce CPU overhead and potentially boosting performance somewhat. The remaining 4 CPUs will be used by the operating system to run the non-SQL Server applications, helping them also to reduce thread movement and boosting performance.
For example, if you have a 8 CPU system, the value you would use in the SP_CONFIGURE command to select which CPUs that SQL Server should only run on are listed below:
|
Decimal Value |
Allow SQL Server Threads on These Processors |
|---|---|
| 1 | 0 |
| 3 | 0 and 1 |
| 7 | 0, 1, and 2 |
| 15 | 0, 1, 2, and 3 |
| 31 | 0, 1, 2, 3, and 4 |
| 63 | 0, 1, 2, 3, 4, and 5 |
| 127 | 0, 1, 2, 3, 4, 5, and 6 |
Specifying the appropriate affinity mask is not an easy job, and you should consult the SQL Server Books Online before doing so for additional information. Also, you should test what happens to your SQL Server’s performance before and after you make any changes to see if the value you have selected hurts or helps performance. Other than trial and error, there is no easy way to determine the optimum affinity mask value for your particular server.
As part of your audit, if you find that an affinity mask is being used, try to find out why. If there are no good answers, remove it, and return to the default value.
Awe Enabled
If you are using SQL Server 2000 Standard Edition under Windows 2000 or 2003 (any version), or are running SQL Server 2000 Enterprise Edition under Windows 2000 or 2003 Server, or if your server has less than 4GB of RAM, the “awe enabled” option should always be left to the default value of 0, which means that AWE memory is not being used.
The AWE (Advanced Windowing Extensions) API allows applications (that are written to use the AWE API) to run under Windows 2000 or 2003 Advanced Server, or Windows 2000 or 2003 Datacenter Server, to access more than 4GB of RAM. SQL Server 2000 Enterprise Edition (not SQL Server 2000 Standard Edition) is AWE-enabled and can take advantage of RAM in a server over 4GB. If the operating system is Windows 2000 or 2003 Advanced Server, SQL Server 2000 Enterprise Edition can use up to 8GB of RAM. If the operating system is Windows 2000 or 2003 Datacenter Server, SQL Server 2000 Enterprise can use up to 64GB of RAM.
By default, if a physical server has more than 4GB of RAM, Windows 2000 and 2003 (Advanced and Datacenter), along with SQL Server 2000 Enterprise Edition, cannot access any RAM greater than 4GB. In order for the operating system and SQL Server 2000 Enterprise Edition to take advantage of the additional RAM, two steps have to be completed.
Exactly how you configure AWE memory support depends on how much RAM your server has. Essentially, to configure Windows 2000 or 2003 (Advanced or Datacenter), you must enter one of the following switches in the boot line of the boot.ini file, and reboot the server:
- 4GB RAM: /3GB (AWE support is not used)
- 8GB RAM: /3GB /PAE
- 16GB RAM: /3GB /PAE
- 16GB + RAM: /PAE
The /3GB switch is used to tell the OS to allow SQL Server to take advantage of 3GB out of the base 4GB of RAM that Windows 2000 and 2003 supports natively. If you don’t specify this option, then SQL Server will only take advantage of 2GB of the first 4GB of RAM in the server, essentially wasting 1GB of RAM.
AWE memory technology is used only for the RAM that exceeds the base 4GB of RAM, that’s why the /3GB switch is needed to use as much of the RAM in your server as possible. If your server has 16GB or less of RAM, then using the /3GB switch is important. But if your server has more than 16GB of RAM, then you must not use the /3GB switch. The reason for this is because the 1GB of additional RAM provided by adding the /3GB switch is needed by the operating system in order to take advantage of all of the extra AWE memory. In other words, the operating system needs 2GB of RAM itself to mange the AWE memory if your server has more than 16GB of RAM. If 16GB or less of RAM is in a server, then the operating system only needs 1GB of RAM, allowing the other 1GB of RAM for use by SQL Server.
Once this step is done, the next step is to set the “awe enabled” option to 1, and then restart the SQL Server service. Only at this point will SQL Server be able to use the additional RAM in the server.
One caution about using the “awe enabled” setting is that after turning it on, SQL Server no longer dynamically manages memory. Instead, it takes all of the available RAM (except about 128MB which is left for the operating system). If you want to prevent SQL Server from taking all of the RAM, you must set the “max server memory” option (described in more detail later in this article) to a figure that limits SQL Server to the amount of RAM you specify.
As part of your audit process, you will want to check what this setting is and then determine if the setting matches your server’s hardware and software configuration. If not, then change the setting appropriately.