Menu
On this article, I am reading that:
| Data column | Column Number | Description |
|---|---|---|
| Duration | 13 | Amount of time (in milliseconds) taken by the event. |
| CPU | 18 | Amount of CPU time (in milliseconds) used by the event. |
I am troubleshooting a query that's using
2,650 ms
733 ms
Shouldn't these numbers be somewhat closer together? How can the query use more CPU time than the entire duration of the event?
Thanks
606
The SQL Server Profiler graphical user interface displays the Duration column in milliseconds by default, but when a trace is saved to either a file or a database table, the Duration column value is written in microseconds.
The cpu time is the total time spent by the cpu resources on a server. If a server has five cpus, and each cpu runs 3 milliseconds concurrently with the other cpus, then the total cpu time is 15 milliseconds. The elapsed time is the total time taken by SQL Server.
CPU time (or process time) is the amount of time for which a central processing unit (CPU) was used for processing instructions of a computer program or operating system, as opposed to elapsed time, which includes for example, waiting for input/output (I/O) operations or entering low-power (idle) mode.
SQL Server Management Studio Once you connect to your SQL Server or Azure SQL instance, you can select Reports > Performance Dashboard and see the current and historical values of CPU usage. Here you can find the query texts of the top resource consumers and identify the queries that are causing the CPU issues.
What is most likely happenings is that your SQL server is splitting your SELECT statement up to run on multiple threads on your Multi-core CPU. Judging by the times returned, I would venture to guess you have a Quad-Core CPU in your server.
For instance: If CPU1, Cpu2, and CPU3 all take 750ms and CPU4 takes 400ms, then you would have a combined total of 2650ms, but an overall duration of only 750ms.
52
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
