Home > Workload Solutions > SQL Server > Best Practices > AMD-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 the SQL Server to use 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 ability to define which processor cores the SQL Server engine will use to multitask. 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 vCPUs. For this reason, we implemented the following configuration changes in this best practice:
Recommendation
The use of CPU affinity did improve performance for the following metrics:
Reducing processor reloads and thread migration across processors was beneficial and resulted in a moderate database performance increase.
NOPM, TPM, Batch Requests per Second, and PowerMax IOPS all showed significant increases.
PowerMax average read and write response times did increase as the database system was operating at a greater efficiency level. Additionally, CPU utilization also increased with the additional workload.
Implementing CPU Affinity boosted database performance, therefore we recommend it as a Day 2 Moderate Recommendation best practice.
Implementation Steps
To configure SQL Server Processor Affinity to use processors 0 through 7 we executed the following command:
ALTER SERVER CONFIGURATION SET PROCESS AFFINITY CPU=0 TO 7
To configure SQL Server Processor Affinity to use NUMA NODE 0 we executed the following command:
ALTER SERVER CONFIGURATION SET PROCESS AFFINITY NUMANODE=0
Additional Resources