Home > Workload Solutions > SQL Server > Best Practices > Intel-Based SQL Server Best Practices on Dell PowerEdge R740 and PowerMax 2000 > Database Best Practices > Performance Best Practices > SQL Server 2019: CPU Affinity
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.
Category | SQL Server 2019 Enterprise |
Product | SQL Server |
Type of best practice | Performance Optimization |
Day and value | Day 2, Moderate Recommendation |
Overview
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 8 allocated vCPUs. For this reason, we implemented the following configuration changes as part of this best practice:
Recommendation
The use of CPU affinity improved performance for the following metrics:
NOPM, TPM, BRPS, and PowerMax IOPS all significantly increases. Reducing processor reloads and thread migration across processors was beneficial and resulted in a moderate database performance increase.
PowerMax average read and write response times increased as the database system was operating at a greater efficiency level. CPU utilization also increased with the additional workload.
Overall, CPU Affinity provided a significant boost to database performance and we consider it as a Day 2 moderate recommendation best practice.
Implementation Steps
Use the following command to set the affinity to CPU 0 through 7:
ALTER SERVER CONFIGURATION SET PROCESS AFFINITY CPU = 0 TO 7
Additional Resources