Tuning SQL Server Configuration - Professional SQL Server 2005 Performance Tuning - page 181

Another way to get around this is to use a query hint called MAXDOP. For example, the following script
will only use two processors at the most, when and if the query processor decides multiple CPUs
are beneficial:
select * from orders option (maxdop 2)
WindowsFibers/LightweightPooling
You can put your SQL Server on a diet, that is, let it run in fiber mode, to see if it improves performance.
Similar to any popular dietary regimen, this may not necessarily work. So, you should test it to verify if
it improves or hurts performance, just like the way you deal with parallel processing.
Put simply, fibers are simple lighter execution units that run off of a thread. When fiber mode is enabled
in SQL Server 2005, a fiber runs just like a thread, but it is running under user mode, not kernel mode.
Therefore, it can be advantageous, especially when there are way too many context switches involved.
You control the number of fibers used by setting the proper worker threads. SQL Server 2005 also identi-
fies fiber usage as lightweight pooling. You can set it all up within the properties page, accessible via the
Server Properties page from Object Explorer within Management Studio, as shown earlier in Figure 7-5.
CPUAffinity
For a dedicated, single instance SQL Server box, you should leave this parameter at its default value. This
parameter comes into play when you have multiple instances or other applications running on the same
server as your SQL server box.
Another situation in which CPU affinity can be useful is when you have applications with different
network I/O. In that case, you can set network CPU affinity with SQL Server Configuration Manager.
See the section ''Network Connection Affinity'' later in this chapter for details.
Simply put, CPU affinity determines which processors SQL Server uses. The default value allows SQL
Server to use all available processors. However, if you have multiple instances on the box, you can use
this parameter to designate unique CPUs to different instances, thereby decreasing the CPU contention.
You can also use this parameter if you run other applications on the database box. Normally, you would
like your database box to be a dedicated single instance server; therefore, the default setting for it would
be ideal.
It is not difficult to set it using Management Studio, as seen earlier in Figure 7-5.
To determine CPU affinity, you can use this quick script:
select
scheduler_id,
CAST (cpu_id as varbinary) AS scheduler_affinity_mask
from
sys.dm_os_schedulers
I/OAffinity
As seen earlier in Figure 7-5, right next to the Processor Affinity check box in your properties page, there
is one additional column called I/O Affinity. This is one additional area that you can tweak to potentially
increase server performance.