25 July,2011 by Jack Vamvas
As of SQL Server 2008 R2 , ALTER SERVER CONFIGURATION SET PROCESS AFFINITY , CPU affinity can be configured on up to 256 logical processors.
The options are:
AUTO (Default setting; no threas assigned to a CPU)
Distribute SQL Server Worker thread to specifc CPU range
Applying an affinity mask , partitions the SQL processes on specific cores.
1) Do not enable affinity masking , without careful analysis.
2) Check sys.dm_os_schedulers (DMV) . Returns a recordset where one scheduler is mapped to a processor. The DMV gives you a good picture of the schedulers
3) What is affinity masking aka CPU affinity masking in SQL Server ? Affinity masking is an option where a processor is assigned a specific thread.
4) Prove that excessive thread migration between processors is causing excessive context switching , causing performance decrease.
5) Servers with 33 – 64 processors support affinity masking only on 64 bit architecture
6) A Microsoft recommendation is to not enable Operating system affinity masking and SQL Server affinity masking simultanenously.