October 22, 2019 at 4:16 am
Sorry for troubling you for the following questions, thanks!
1. when I use SQL profiler to trace the SQL statement, there is a column of cpu and duration. what does the column of cpu mean ? and what is the relationship between cpu and duration ?
and what are the measurement units of them?
2. When I use extended event to capture the sql statement, there is a column of cpu_time and duration. what does the column of cpu_time mean ? and what is the relationship between cpu_time and duration ?
3. is there any better way to save the view target data (extended event log record) into a table or file ?
October 23, 2019 at 1:15 am
You can look up the description for the columns in an XE trace in the sys.dm_xe_object_columns view. For example, to view the column defs for the sp_statement_completed event:
SELECT name, type_name, column_type, capabilities_desc, description
FROM sys.dm_xe_object_columns
WHERE object_name = 'sp_statement_completed'
ORDER BY column_type, column_id
duration - "The time (in microseconds) that it took to execute the statement."
Duration is the wall clock amount of time between the start time and end time of execution.
cpu_time - "Indicates the CPU time (in microseconds) that is consumed by the statement."
cpu_time is the amount of time was 'on CPU', meaning 'actually executing' per CPU.
A blocked statement will continue to accrue Duration time but no cpu_time.
There is no direct correlation between duration and cpu_time; they measure different operational aspects of a running query.
For example:
A statement that took 4 milliseconds to complete, during which it was actively executing in parallel on 8 cores for 3 milliseconds of it, would show 4000 as the duration and 24000 for the cpu_time.
A statement that took 4 milliseconds to complete, during which it was blocked* for 3.5 milliseconds before completing on one core in 0.5ms would have 4000 for the duration and 500 for the cpu_time.
* "Blocked" in this context refers to any wait condition, not just normal SQL Server lock-based blocking.
Eddie Wuerch
MCM: SQL
October 23, 2019 at 1:20 am
Adding:
While there is no normal correlation between duration and cpu_time, the ratio between them measured and compared over time for a given query may establish a correlation if a query is expected to always behave a certain way (affecting the approximate same number of rows using the same indexes, etc.). That specific correlation may be relevant for determining when plans start going bad or when table size and data distribution renders existing indexes sub-optimal.
Eddie Wuerch
MCM: SQL
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply