0

I've been using this Query in order to find the top 20 CPU usages of a day the last 2 hours. 

SELECT TOP 20
    qs.sql_handle,
    qs.execution_count,
    qs.total_worker_time AS Total_CPU,
    total_CPU_inSeconds = --Converted from microseconds
        qs.total_worker_time/1000000,
    average_CPU_inSeconds = --Converted from microseconds
        (qs.total_worker_time/1000000) / qs.execution_count,
    qs.total_elapsed_time,
    total_elapsed_time_inSeconds = --Converted from microseconds
        qs.total_elapsed_time/1000000,
    st.text,
    qp.query_plan,
    qs.last_execution_time
FROM
    sys.dm_exec_query_stats AS qs
CROSS APPLY 
    sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY
    sys.dm_exec_query_plan (qs.plan_handle) AS qp
where last_execution_time between dateadd(hour,-2,getdate()) and getdate()
ORDER BY 
    qs.total_worker_time DESC, qs.last_execution_time

Which gives me the output (this is cropped, there are many more columns and rows in reality)

total_elapsed_time_inSeconds    last_execution_time
494020                          2017-06-12 10:39:13.003

I calculated the seconds to amass more than 100 hours, yet the last_execution_time is from 8 minutes ago?

Have I misunderstood the code, or does the total_elapsed_time_inSeconds accumulate and the Query is ran often as a SQL job or so?

|improve this question
  • 2
    The total elapsed time is cumulative value which will keep adding whenever a query executes. So suppose this query executed 10 times and took 8 mins each time the value would be 80 mins or 4800 seconds – Shanky Jun 12 '17 at 9:23
  • @Shanky Thanks! Feel free to add your comment as an answer and I'll accept it. – Cenderze Jun 12 '17 at 11:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.