Systems that use multiprocessors can take advantage of the enhancements provided by SQL Server for parallel and symmetric multiprocessing. You can control how and when processors are used by SQL Server as well as when queries are processed in parallel.
Multitasking is an important part of the operating system. Often the operating system will need to move threads of execution among different processors. On a system with a light load, this allows the server to improve performance by balancing the workload. On a system with a heavy load, however, this shuffling of threads can reduce performance because processor cache has to be reloaded repeatedly.
SQL Server 2005 supports processor affinity and I/O affinity to optimize how processors are used. Processor affinity assigns processors to specific threads of execution to eliminate processor reloads and reduce thread migration across processors. I/O affinity specifies which processors are eligible to process SQL Server–related disk I/O operations. If you decide to manage affinity manually, you will want some processors to have priority for threading and some processors to have priority for disk I/O, with no overlap between the two. For example, on a 32-processor system running SQL Server 2005 Enterprise Edition, you might want processors 0 to 15 to have processor affinity (which means they will manage threads of execution) and processors 16 to 31 to have I/O affinity (which means they will manage disk I/O operations).
Note
There is no specific formula for allocation. You do not need to allocate half of the CPUs to processor affinity and half to I/O affinity. The actual configuration will depend on server usage and load.
Affinity settings are automatically configured and optimized when you install SQL Server. If you are trying to optimize performance for a server under a heavy load, you might want to try to optimize the affinity settings. Keep the following guidelines in mind before reconfiguring affinity settings:
Do not change these settings without careful forethought. You can reduce performance by incorrectly managing affinity settings.
Do not configure CPU affinity in both the operating system and in SQL Server. Both techniques have the same goal. Use one technique or the other.
Do not enable the same CPU for both processor and I/O affinity. Each processor can have only one affinity. This means that there are three possible affinity states: processor affinity enabled, I/O affinity enabled, or no affinity enabled.
You can manually configure processor usage by completing the following steps:
Start SQL Server Management Studio, and then connect to the server you want to configure.
Right-click the server name in the SQL Server Management Studio Object Explorer view, and then choose Properties from the shortcut menu.
From the Server Properties dialog box, go to the Processors page, as shown in Figure 6-4.
Use the Processor list to determine which processors SQL Server uses. Select the check box for processors you want to use, and clear the check box for processors you do not want to use. The first CPU on the system is identified as CPU 0, the second as CPU 1, and so on.
Best Practices
If the system has more processors than SQL Server supports, SQL Server does not use all of them. For example, on an eight-way symmetric multiprocessing (SMP) system, SQL Server Standard can use only four processors. This leaves four processors for other applications and system-level tasks.
Best Practices
You may want to assign SQL Server to the higher numbered processors (5, 6, 7, and 8), but this is not a good idea. Windows assigns deferred process calls associated with network interface cards (NICs) to the highest numbered processors. So, if the system described in the example had two NICs, these calls would be directed to CPU 8 and CPU 7. Be sure to consult the equipment documentation before changing these values.
Click OK. The new settings will apply when the server has been stopped and restarted.
You can also use the stored procedure sp_configure to set the affinity mask. The related command is:
exec sp_configure "affinity mask", <integer value> exec sp_configure "affinity i/o mask", <integer value>
SQL Server interprets the integer value as a bit mask representing the processors you want to use. In this bit mask, CPU 0 is represented by bit 0, CPU 1 with bit 1, and so on. A bit value of 1 tells SQL Server to use the CPU. A bit value of 0 tells SQL Server not to use the CPU. For example, if you wanted to turn on support for processors 1, 2, and 5, you would have a binary value of
000100110
The corresponding integer value is 38:
32 + 4 + 2 = 38
A lot of calculations are required to determine if parallel processing should be used or not. Generally, SQL Server processes queries in parallel in these cases:
When the number of CPUs is greater than the number of active connections
When the estimated cost for the serial execution of a query is higher than the query plan threshold (The estimated cost refers to the elapsed time in seconds required to execute the query serially.)
Certain types of statements cannot be processed in parallel unless they contain clauses, however. For example, UPDATE, INSERT, and DELETE are not normally processed in parallel even if the related query meets the criteria. But if the UPDATE or DELETE statements contain a WHERE clause, or an INSERT statement contains a SELECT clause, the WHERE and SELECT can be executed in parallel. Changes are applied serially to the database in these cases.
You can configure parallel processing by completing the following steps:
From the Server Properties dialog box, go to the Advanced page.
By default, the Max Degree Of Parallelism setting has a value of 0, which means that the maximum number of processors used for parallel processing is controlled automatically. Essentially, SQL Server uses the actual number of available processors, depending on the workload. To limit the number of processors used for parallel processing to a set amount (up to the maximum supported by SQL Server), change the Max Degree Of Parallelism setting to a value greater than 1. A value of 1 tells SQL Server not to use parallel processing.
Large, complex queries usually can benefit from parallel execution. However, SQL Server performs parallel processing only when the estimated number of seconds required to run a serial plan for the same query is higher than the value set in the cost threshold for parallelism. Set the cost estimate threshold using the Cost Threshold For Parallelism box on the Advanced page of the Server Properties dialog box. You can use any value from 0 to 32,767. On a single CPU, the cost threshold is ignored.
Click OK. These changes are applied immediately. You do not need to restart the server.
You can use the stored procedure sp_configure to configure parallel processing. The Transact-SQL commands are:
exec sp_configure "max degree of parallelism", <integer value> exec sp_configure "cost threshold for parallelism", <integer value>