0

In SQL Server Management Studio, I have two sessions. The workload for each session is:

Session 1: while loop to select top items from one table;

Session 2: while loop to update statistics of another table using full scan.

Then I used Get-Counter to get the processor counters of each session for a minute:

Get-Counter -Counter "\Process(sqlservr)\% Processor Time" -SampleInterval 1 -MaxSamples 60

When running session 1 itself, the average processor counter in a minute is about 34.6. When running session 2 itself, the average processor counter in a minute is 229.8. But when running session 1 and 2 together, the average processor counter is only 91.7. I tried it several times, sometimes session 1 and 2 add up together would be similar to session 2 itself. However, I was expecting something like the processor counter of session 1+2 would be similar to the one of session 1 + the one of session 2.

Session 1 only has logical reads (no physical reads) so I guess it's not a matter of IO block. The whole CPU usage of session 1+2 is only like 12% so it's not because CPU is fully utilized. Could someone explain why? What should I do to make the CPU usage of running session 1+2 together similar to the sum of running each itself? Thanks!

2
  • So we know you have more than 2 processors... but how many total? how many of those are physical cores vs logical cores? Jun 22, 2016 at 2:27
  • As task manager shows, I have 4 physical cores, 8 logical cores. Thanks! Jun 22, 2016 at 3:48

1 Answer 1

0

You are measuring CPU time, not real SQL Server query execution time or real CPU usage. Why do you expect these numbers to add up?
You have to use SQL Profiler or Extended events to capture CPU usage by those queries.

1
  • As I understand it, processor_counters divides by number_of_cores is the CPU busy time per second, which is then the CPU usage? Jun 22, 2016 at 3:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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