RE: SQL Server 2017 not setting affinity for all processors in system

SSC Veteran

Points: 237

December 29, 2017 at 2:23 pm

#1973898

Yeah I thought it was licensing too. I've been scouring a number of MSFT tech pages about the affinity mask but nothing really explaining why auto stops at 64 procs. Also when I try setting manually, I still can't enable the others.

https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-server-configuration-transact-sql#Affinity

I also looked into processor groups to see if the sql server process is stuck to one group that has 64 procs and not accessing the other.

https://technet.microsoft.com/en-us/library/ee210547(v=sql.105).aspx

select * from sys.dm_os_schedulers
With regard to sys.dm_os_schedulers, the cpu_id column list values 0-47 and 64-111. The scheduler_id column lists values 0-95 then jumps to a few high values for some hidden online schedulers (1048576-1048593)

Also looking at my perfmon data, the overall CPU usage caps at around 56% and never increases no matter how much more load I add with my test. I even did some spot checking with my data files and looked at specific cpus within both NUMA nodes. They varied between 45%-57% usage. When I look at task manager during an active test, it seems like all the procs are under load.

I looked into these things before I posted to the forum. I was hoping someone with a high core count system had seen this before. Let me know if you can think of anything else to look into. Thank you.