cpu and duration of SQL profiler and cpu_time and duration of Extended event

  • 892717952

    SSC-Addicted

    Points: 421

    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 ?

     

  • Eddie Wuerch

    SSChampion

    Points: 12275

    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

  • Eddie Wuerch

    SSChampion

    Points: 12275

    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 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply