SQL Server Query Tuning: why CPU Time is higher than Elapsed Time ? Are they relevant to set operation?

Question

I have two query to filter some userid depend on question and its answers.

Scenario

Query A is (the original version):

SELECT userid
FROM mem..ProfileResult
WHERE ( ( QuestionID = 4
          AND QuestionLabelID = 0
          AND AnswerGroupID = 4
          AND ResultValue = 1
        )
        OR ( QuestionID = 14
             AND QuestionLabelID = 0
             AND AnswerGroupID = 19
             AND ResultValue = 3
           )
        OR ( QuestionID = 23
             AND QuestionLabelID = 0
             AND AnswerGroupID = 28
             AND ( ResultValue & 16384 > 0 )
           )
        OR ( QuestionID = 17
             AND QuestionLabelID = 0
             AND AnswerGroupID = 22
             AND ( ResultValue = 6
                   OR ResultValue = 19
                   OR ResultValue = 21
                 )
           )
        OR ( QuestionID = 50
             AND QuestionLabelID = 0
             AND AnswerGroupID = 51
             AND ( ResultValue = 10
                   OR ResultValue = 41
                 )
           )
      )
GROUP BY userid
HAVING COUNT(*) = 5

I use 'set statistics time on' and 'set statistic io on' to check the cpu time and io performance.

the result is:

CPU time = 47206 ms,  elapsed time = 20655 ms.

I rewrote Query A via using Set Operation, let me name it Query B:

SELECT userid
FROM ( SELECT userid
        FROM mem..ProfileResult
        WHERE QuestionID = 4
            AND QuestionLabelID = 0
            AND AnswerGroupID = 4
            AND ResultValue = 1
       INTERSECT
       SELECT userid
        FROM mem..ProfileResult
        WHERE QuestionID = 14
            AND QuestionLabelID = 0
            AND AnswerGroupID = 19
            AND ResultValue = 3
       INTERSECT
       SELECT userid
        FROM mem..ProfileResult
        WHERE QuestionID = 23
            AND QuestionLabelID = 0
            AND AnswerGroupID = 28
            AND ( ResultValue & 16384 > 0 )
       INTERSECT
       SELECT userid
        FROM mem..ProfileResult
        WHERE QuestionID = 17
            AND QuestionLabelID = 0
            AND AnswerGroupID = 22
            AND ( ResultValue = 6
                  OR ResultValue = 19
                  OR ResultValue = 21
                )
       INTERSECT
       SELECT userid
        FROM mem..ProfileResult
        WHERE QuestionID = 50
            AND QuestionLabelID = 0
            AND AnswerGroupID = 51
            AND ( ResultValue = 10
                  OR ResultValue = 41
                )
     ) vv;

the CPU Time and Elapsed Time is:

CPU time = 8480 ms,  elapsed time = 18509 ms

My Simple Analysis

As you can see from up result, Query A have CPU Time more than 2 times of Elapsed time

I search for this case, mostly people say CPU time should less than Elapsed time, because CPU time is how long the CPU running this task. And the Elapsed time include I/O time and other sort of time cost. But one special case is when the Server has multiple Core CPU. However, I just checked the development db server and it has one single core CPU.

Question 1

How to explain that CPU time more than Elapsed time in Query A in a single core CPU environment?

Question 2

After, using set operation, Is the performance really improved?

I have this question because logical reads of Query B is 280627 which is higher than Query A's 241885

Brad McGehee said in his article that 'The fewer the logical reads performed by a query, the more efficient it is, and the faster it will perform, assuming all other things are held equal.'

Than, does it correctly say that even Query B have higher logical reads than Query A, but CPU time is significantly less than Query A, Query B should have a better performance.

Hildahildagard

Your CPU is equipped with a flux capacitor and can run faster than time. Is there no room for humor here? It's obviously a bug in the CPU task scheduler or the database program. Does the 1-core CPU appear to be 2 virtual CPUs? – Fantastically

No, only 1 single core CPU, and no virtual CPU. – Hildahildagard

Then that shouldn't be possible. The server must think it has multiple CPUs. – Fantastically

Answer 1

If CPU is greater than elapsed, you do have a multi core or hyper-threaded CPU

The CPU time is where the SQL Server Engine is installed. It isn't for a local Management Studio install.

As for logical IO vs CPU, I'd go with lower CPU. If this runs often and overlapping, you'll run out of CPU resource first. I'd try a WHERE EXISTS (UNION ALL) construct and make sure I have good indexes.

Edit, after comments

• there are parallelism operators in the plan = more than one logical processor visible to the OS and SQL Server. So it's either multiple core or hyper-threaded

Try EXEC xp_msver

Pneumoencephalogram

Hi gbn, yes there are two Parallelism in the Execution plan. Is that mean the server have multiple core or any other info? – Hildahildagard

I just go back to check the Execution plan. Both of Query A and Query B have a Parallelism(Gather Streams). However, I go to MSDN to find word about Parallelism, it says Parallelism is for multiple processor. Then I really cannot figure out what is happen here... – Hildahildagard

But I ask out DBA remote login to that server, using windows task manager to check the CPU usage, there is only one CPU usage section shown in the task manager. Did I missed anything for checking this? – Hildahildagard

Try EXEC xp_msver. Answer updated too. – Pneumoencephalogram

thanks gbn, I just use this command to check the server, the command result indicates that ProcessorCount is 16. However, as I said above comment windows task manager shows only one CPU usage there and I talk about Server Virtualization with our Sys Admin before, he told me that we never setup virtual server. I will discussed this with our Sys Admin tomorrow. Thanks a lot for your patient. – Hildahildagard

Hi gbn, I go to discuss this with our sys admin and he use Dell server manage tools to check server, the server do have 4 processors and each of them has HT technology, basically means there 8 cores. This is now make sense for me. However, the windows task manager really just show 1 cpu usage, the windows version is windows server 2003R2, and this also surprise our sys admin, he believe the windows should be installed in wrong way. But anyway the question is got a answer, and thank you help again. – Hildahildagard

@JeffChen: the CPU usage meter on task manager always just shows one bar no matter how many CPUs you have. If one CPU is maxed out and the other is idle, it'll show 50%. If both CPUs are maxed out, then it'll show 100%. – Ioneionesco

@Pneumoencephalogram can you point to some official documentation or specs related to this behavior? – Ephemeris

Answer 2

In my case- SQL Server Execution Times: CPU time = 671 ms, elapsed time = 255 ms.

CPU time was nearly three times bigger than the elapsed time for query. Because the query was processed in parallel, the CPU burden was very high, and the CPU could become a bottleneck for this scenario.

SQL Server 2012 brings a solution to the CPU burden problem. It introduces iterators that process batches of rows at a time, not just row by row.

For query optimization you can Create columnstore index on your table-

CREATE COLUMNSTORE INDEX idx_cs_colname ON dbo.Tablename(feild1,feild2);

Delius