In SQL Server 2008 (in all editions except SQL Server Express),
processor affinity can be controlled dynamically.
When SQL Server starts up, all
scheduler tasks are started on server startup, so there is one scheduler per
CPU. If the affinity mask has been set, some of the schedulers are then marked
as offline and no tasks are assigned to them.
When the affinity mask is changed to include
additional CPUs, the new CPU is brought online. The Scheduler Monitor then
notices an imbalance in the workload and starts picking workers to move to the
new CPU. When a CPU is brought offline by changing the affinity mask, the
scheduler for that CPU continues to run active workers, but the scheduler
itself is moved to one of the other CPUs that are still online. No new workers
are given to this scheduler, which is now offline, and when all active workers
have finished their tasks, the scheduler stops.
