How to Change SQL Server Configuration Settings
Most, but not all of the SQL Server configuration settings can be changed using Management Studio or the Surface Area Configuration Tool. 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 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 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 Management Studio or the Surface Area Configuration Tool 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 WITH OVERRIDE, the SQL Server service has to be stopped and restarted. Be sure you don’t do this during the day on a production system with active users. The chart at the first of the article tells you which options require a SQL Server restart before they take effect.
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 view or change these options using the SP_CONFIGURE command, you must first change one of the SQL Server configuration settings to allow you to view and change advanced settings. For example, if you ran SP_CONFIGURE and only 14 options, not all 63 were listed, you need to run the following command. 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 view and to change any of the 63 SQL Server configuration setting.
Now that you know how to change SQL Server configuration options, let’s take a look at those that are related to performance.
Affinity I/O Mask
This is a new setting added in SQL Server 2005.
The affinity I/O mask configuration option binds SQL Server disk I/O to a specified subset of CPUs in a server. In busy SQL Server OLTP environments, this feature may enhance the performance of SQL Server threads issuing I/Os.
The value for the affinity I/O mask specifies which CPUs in a multiprocessor computer are eligible to process SQL Server disk I/O.
The values for affinity I/O mask are as follows:
- A 1-byte affinity I/O mask covers up to 8 CPUs in a multiprocessor computer.
- A 2-byte affinity I/O mask covers up to 16 CPUs in a multiprocessor computer.
- A 3-byte affinity I/O mask covers up to 24 CPUs in a multiprocessor computer.
- A 4-byte affinity I/O mask covers up to 32 CPUs in a multiprocessor computer.
A 1 bit in the affinity I/O pattern specifies that the corresponding CPU is eligible to perform SQL Server I/O operations; a 0 bit specifies that no SQL Server I/O operations should be scheduled for the corresponding CPU. When all bits are set to zero, or an affinity I/O mask is not specified, SQL Server I/O is scheduled to run on any of the CPUs eligible to process SQL Server threads.
When specifying the affinity I/O mask, you must use it with the affinity mask configuration option. Do not enable the same CPU in both the affinity I/O mask switch and the affinity mask option. The bits corresponding to each CPU should be in one of these three states:
- 0 in both the affinity I/O mask option and the affinity mask option.
- 1 in the affinity I/O mask option and 0 in the affinity mask option.
- 0 in the affinity I/O mask option and 1 in the affinity mask option.
Because setting the SQL Server affinity I/O mask option is very specialized, it should be used only when necessary. In most cases, the default affinity provides the best 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 on multiple CPUs, 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, and running either more than one instance of SQL server, or one instance of SQL Server and other applications, performance can often be boosted by specifying 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 from the server. For example, you can specify that SQL Server will only use some of the CPUs, not all of the CPUs that are 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 running only a single instance of SQL Server, and that are not overly busy, the default value is also generally the best overall setting for optimum performance.
But for servers with more than 4 CPUs, that are heavily loaded, and are running multiple instances of SQL Server, or one instance of SQL Server and other applications, 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, along with 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 has 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 an 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
255 0, 1, 2, 3, 4, 5, 6, and 7
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.