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.
Cost Threshold for Parallelism
Using parallelism to execute a SQL Server query has its costs. This is because it takes a little additional overhead to run a query in parallel than to run it serially. But if the benefits of running a query using parallelism is higher than the costs, then using parallelism is a good thing.
As a rule of thumb, if a query can run serially very fast, there is no point in even considering parallelism for the query, as the extra time required to evaluate it for possible parallelism might be longer than the time it takes to run the query serially.
By default, if the Query Optimizer determines that a query will take less than 5 seconds to execute, parallelism is not considered by SQL Server. This 5 second figure can be modified using the “cost threshold for parallelism” SQL Server option. You can change this value anywhere from 0 to 32767 seconds. So if you set this value to 10, this means that the Query Optimizer won’t consider parallelism for any query that it thinks will take less than 10 seconds to run.
In most cases, you should not change this setting. But if you find that your SQL Server runs many queries with parallelism, and if the CPU rate is very high, raising this setting from 5 to a higher figure (you will have to experiment to find the ideal figure for your situation), will reduce the number of queries using parallelism, also reducing the overall usage of your server’s CPUs, which may help the overall performance of your server.
Another option to consider is to reduce the value from 5 seconds to a smaller number, although this could hurt, rather than help performance in many cases. One area where a smaller value might be useful is in cases where SQL Server is acting as a data warehouse and many very complex queries are being run. A lower value will allow the Query Optimizer to use parallelism more often, which can help in some situations.
You will want to test changes to the default value thoroughly before implementing it on your production servers.
If SQL Server only has access to a single CPU (either because there is only one CPU in the server, or because of an “affinity mask” setting, parallelism is not considered for a query.
If you find in your audit that the cost threshold for parallelism is being used, find out why. If you can’t get an answer, move it back to the default value.