3

This question already has an answer here:

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:

enter image description here

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:

enter image description here

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?

marked as duplicate by Paul White sql-server Nov 29 '16 at 16:31

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

1

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.

  • Hi TomTom, thank you for taking the time to answer. Do you have more detailed information on why windows is dividing load across cores? Is there documentation about this? Regarding question number 2, I still don't understand why it's bound to a single core after I explicitly tell SQL server to use all cores. Configuring affinity explicitly does give me my solution. But I wonder if it has a negative side effects. Microsoft probably had a good reason to change the way the OS schedules. just overriding it by using ALTER SERVER CONFIGURATION SET PROCESS AFFINITY NUMANODE = 0 might not be smart. – Edward Dortland Dec 19 '13 at 14:06

Not the answer you're looking for? Browse other questions tagged or ask your own question.