CPU Affinity Mask & TF8002
January 13, 2023
Setting CPU affinity in SQL Server is not a task you do every day. Rarely are there use cases when you need to do that. I had a recent requirement to do it. We plan to replace a physical server with half of its current CPU. Primarily due to faster CPU and workload moved off of SQL Server to other cloud services. To test, we needed to set the CPU affinity mask in one of our non-production servers. In the research, I learned about the side effect of setting CPU affinity mask, which is nicely explained in this ( by Klaus Aschenbrenner) and this (by Adam Denby) blog post.
The goal of this Blog post is to document how to set CPU affinity and make sure that you did this correctly. Turning on Trace Flag 8802 and confirm the default behavior change.
I am using an 8-CPU test machine. Use the attached code to set up a test database with the necessary table and stored procedure. Open a new query window and run the following T-SQL statement. Note down the session id.
USE testcpuaffinity; GO WHILE (1=1) BEGIN EXEC dbo.p_StressTestTable_ins; END
For example, if your session id is 103, run the following T-SQL statement to check and confirm that the scheduler your session is bound to can run on any of the eight available CPUs.
SELECT r.session_id, t.affinity FROM sys.dm_exec_requests r JOIN sys.dm_os_workers w ON w.task_address = r.task_address JOIN sys.dm_os_threads t ON t.worker_address = w.worker_address WHERE r.session_id = 103
Results from the query above
The affinity of decimal 255 is equivalent to 11111111 in binary. This confirms that the scheduler your session is bound to can run on any of the eight available CPUs. Use this online calculator to convert decimals to binary or vice versa.
Run the following T-SQL to set the CPU affinity mask. We want the SQL engine to use only four of eight available CPUs.
USE master; GO ALTER SERVER CONFIGURATION SET PROCESS AFFINITY CPU = 4 TO 7; GO
Here is a visual representation of CPU affinity after running the above statement.
Rerun the while statement and check the CPU affinity. You will get a decimal number of 64, equivalent to 01000000 in binary. Meaning the scheduler session 103 is bound to can only run on CPU 6. This default behavior impacts performance, as explained in the two blog posts above.
Turn on Trace Flag 8002 as a startup parameter and restart the SQL Server engine. Note Trace Flag 8002 requires a restart. Suppose you turn it on with the DBCC TRACEON command; it does not take effect even though it shows on with DBCC TRACESTATUS output.
After turning on Trace Flag 8002 and restarting the SQL Server engine, running the same test will give a decimal number of 240, equivalent to 11110000 in binary. Meaning the scheduler session 103 is bound to can only run on any CPU between 4 and 7.