I have been going through an article that explains the unwanted side effects of Setting a Processor Affinity in SQL Server.
What I have come to know is that in most of the cases the default setting of SQL Server works best as when we are setting processor affinity, we are making the scheduler CPU bound. I have a couple of questions here:
When the default setting of Processor affinity works best, then why does the need arise to set Processor affinity explicitly?
The article also states:
With the trace flag 8002 turned on, the scheduler is not bound to CPU anymore.
Is it justified to say that trace flag 8002 being
ONis just like the default
Processor affinityconfiguration with the exception of having less number of cores available for SQL Server?
For example, if we set the processor affinity to 16 cores for a 32 core processor (with trace flag 8002 as
ON), then would the schedulers that have SQL threads associated with them, will have only
16 coresto toggle?
Setting a Processor affinity restricts the SQL Server Scheduler to use some cores. Does this setting also restricts the core usage for other CPU intensive processes, such as a .NET application?
What I am trying to say is, if for a 32 core CPU, the processor affinity is set to 16 core, then SQL Server scheduler has only 16 cores to toggle. If yes, then what about another CPU intensive application such as a .NET application? Does the .NET app have the liberty of switching between all 32 cores, since it will be dependent on Windows OS and not SQLOS?
It is just a hypothetical scenario I had imagined. Given a production scenario, I highly doubt people would go for SSMS and a CPU intensive .NET application on the same server. That will greatly hamper performance and scarce the CPU availability for any other process. Correct me if I am wrong here.