In this best practice, we used processor affinity to optimize SQL Server performance. The goal is to define a range of processors for SQL Server to reduce thread migration and improve performance.
CPU affinity is the capability to define which processor cores the SQL Server engine will use for multitasking. By assigning processor cores, SQL Server only uses those processors for multitasking database operations. This can improve performance by reducing processor reloads and thread migration across processors.
The virtual machine supporting SQL Server has 10 allocated vCPUs. For this reason, we implemented the following configuration changes as part of this best practice:
Use the following command to set the affinity to CPU 0 through 9:
ALTER SERVER CONFIGURATION SET PROCESS AFFINITY CPU = 0 TO 9