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!
Best Answer
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.