This article explains all key points of CPU Affinity Masks in SQL Server.
To make the process of multi-tasking easier, Microsoft Windows often makes a shift in the process threads belonging to different processors. This can help when looked from a point of view of the operating system, but this can also lag the performance of the application when there is a heavy load on the system. As the processor cache for each process will be reloaded along with the data. The performance can be improved by reducing the load of processor reloads and by reducing the thread migration happening across processes. This association that exists between a given thread and a processor is referred to a processor affinity.
Affinity Masks for Supporting Processor Affinity in SQL Server
In SQL Server processor affinity is supported by two different options of Affinity masks.
- CPU Affinity Mask ( also called affinity mask)
- Affinity I/O Mask
We would be keeping our focus today on Affinity masks, i.e CPU Affinity Mask.
This was the option that was available in earlier editions of SQL Server by default and was responsible for dynamically controlling the CPU affinity. This option in SQL Server can still be configured even without restarting your SQL Server instance. If you are making use of sp_configure, you should either make use of RECONFIGURE or the alternative RECONFIGURE WITH OVERRIDE, once you have set the option for configuration. If you are making use of the Express edition of SQL Server then you will be required to restart your instance for changing the affinity mask.
Affinity masks also allow for dynamic changes which can allow for startup and shutdown of CPU schedulers on demand. These CPU schedulers are responsible for binding the process threads inside SQL Server. This happens when there is a change in the conditions of the application; this can include the addition of a new instance. The changes in the affinity masks are necessary also because it helps in redistributing the load on the processor.
Modifications in Affinity Bitmasks
For introducing modifications in the Affinity bitmasks you will need SQL Server to make use of an all-new CPU scheduler and put the existing one on halt. This new scheduler will be made use of only for the recent batches, and the existing batches will continue making use of the old scheduler. The workers will have to migrate to this newly created scheduler.
To shut down a scheduler you will first have to ensure that the batches that are currently placed on this scheduler have all their activities complete. Once a scheduler has been shut, it is marked as offline to ensure that none of the new batches are scheduled on it.
Whether you opt for adding or removing a new scheduler, permanent systems like the checkpoint, lockmonitor, system task thread, along with the signal process will continue running on the scheduler when server continues to be operational. These permanent system tasks will not migrate dynamically, to redistribute the processor load for all of these tasks; you will have to restart the instance.
While working with any complex operations or any data migration tasks, always keep a handy tool that can fix sql server database to deal with situations.
Victor Simon is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including access recovery and sql recovery software products. For more information visit www.datanumen.com