Utilizing a single core to 100% doesn’t show in perf mon as single cpu being fully utilized [duplicate]

Ever since Windows 2008 I’ve noticed the following behavior:

I’m using a windows 2008R2 VM with two vCPUs but this also works on a physical server.
I’m using SQL server 2008 R2 for this example.

Make sure you have processor affinity set to AUTO (the default):

ALTER SERVER CONFIGURATION SET PROCESS AFFINITY NUMANODE = 0

(takes affect immediately)

Run a query that will utilize a single core at 100%:

DECLARE @c INTEGER

WHILE 1=1
BEGIN
    SET @c=@c+1
END

Now, open Windows Performance Monitor and add the counters:

• Processor: % Processor time _Total
• Processor: % Processor time 0
• Processor: % Processor time 1

In my pictures, _Total is highlighted (black) and both seperate CPUs are red and green.

What I would expect to see is, one of the cores at 100%, another near 0% and the _Total around 50%.

But instead I get this:

It seems as if the Windows OS Scheduler tries to divide the load across both cores by alternating the task between cores.

Set the processor affinity explicitly to use all cores. From a SQL cpu perpective everything is still the same. SQL Server still has goth both vCPUs are both usable.

ALTER SERVER CONFIGURATION SET PROCESS AFFINITY NUMANODE = 0

When I run the same query I get the graph that I was expecting:

For the most part of the time, the task is running on one core only. In this case the green one.

This last graph is basically how it was in pre windows 2008.

Questions:

1. What’s happening under the hood in the first scenario?
2. Why is this behaviour changed once I explicitly tell SQL Server that my processor affinity is to use all cores. Instead of leaving it on auto.
3. In the old days it was very easy to spot a query that was limited by a single core by opening up perf mon and look at all separate cores and find one that was utilized at 100%. Any alternatives to do that now?

1: The scheduler is moving the load between cores. This is a known behavior.

2: Because the processor affinity is actually a core affinity and you can not move a task away from a core if that is the only core allowed to run it.

3: Not really. Things get a lot more complex because of parallelization anyway. In general, SQL Server – unless you do very complex analysis – is a lot more memory / IO bound than CPU bound. Now, with SSD you can actually overcome this – but mostly I dont see that.

You can find that a query uses a lot of CPU – which is always something nice to optimize – but not necessarily whether it is single core bound.

If you need that, maybe an instance configured to use only one core may work. I mostly find that to be of limited use – because in large scenarios, as I said, you either are not CPU bound or with paralellism not single core bound.