none
total wait for a query RRS feed

  • Question

  • is there a way to do correlate the exec stats and the wait times.

    More precisely, is it possible to cumulate the total waits encountered by a single query execution or a query hash (ideally the max (worse)  or the last execution, possibly average/total).


    • Edited by Antoine F Friday, August 26, 2016 2:39 PM semantic
    Friday, August 26, 2016 2:38 PM

Answers

All replies

  • This is just based on my assumption. See below query;

    select sql_handle, plan_handle, total_elapsed_time - total_worker_time AS Total_wait_Time 
    from sys.dm_exec_query_stats
    It gives you total worker time that the query spent on CPU and also it gives you, total elapsed time for the query. If you just subtract total CPU time from total elapsed time, I think you get the total wait time. 

    Friday, August 26, 2016 3:22 PM
  • Hi Antoine F,

    SELECT text,ST.total_elapsed_time - ST.total_worker_time FROM 
    
    sys.dm_exec_query_stats ST cross apply sys.dm_exec_sql_text(plan_handle)

    Friday, August 26, 2016 3:31 PM
  • Guys what you are doing is you are subtracting worker time which is cumulative of total time taken by query since it was recompiled. I suggest instead of total_elapsed_time-total_worker_time you should use total_elapsed_time-last_worker_time.

    Even recompiles is not going to affect last_worker_time. Also be aware sys.dm_exec_query_stats might miss some query

    Again if you are tracking time for procedure i strongly suggest use DMV sys.dm_exec_procedure_stats its recommended for proc


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Friday, August 26, 2016 5:32 PM
    Moderator
  • Thanks all of you.

    I can get it for a single session via this table sys.dm_exec_session_wait_stats.  Unfortunatly it applies to sql server 2016 which I do not use yet in my work environments.

    Are you aware of a method to output the cumulative details of a single session, query or the average, max, last of a query hash.

    The version is SQL server 2012.
    • Edited by Antoine F Friday, August 26, 2016 6:04 PM
    Friday, August 26, 2016 5:50 PM

  • Are you aware of a method to output the cumulative details of a single session, query or the average, max, last of a query hash.

    The version is SQL server 2012.

    I dont think so a DMV can give you that information. You have sys.dm_exec_requests which has column wait_time but it only gives time when the recent blocking started . If query is blocked 3 times this will not include previous information.

    This might be possible using XE trace but I have never tried it


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Friday, August 26, 2016 6:15 PM
    Moderator
  • >Are you aware of a method to output the cumulative details of a single session

    >The version is SQL server 2012.

    This information is available using XEvents.  See eg

    http://www.sqlskills.com/blogs/paul/capturing-wait-stats-for-a-single-operation/

    David


    David http://blogs.msdn.com/b/dbrowne/

    • Marked as answer by Antoine F Sunday, August 28, 2016 2:01 AM
    Friday, August 26, 2016 6:40 PM
  • You can have a look at sp_sqltrace written by Lee Tudor and hosted on my web site:
    http://www.sommarskog.se/sqlutil/sqltrace.html

    It sets up a trace for the current spid, runs a command and then abalyses and aggrgates the trace. Simultaneously, it also sets up an Xevent session to comllev wait stats and present the two together.

    • Marked as answer by Antoine F Sunday, August 28, 2016 2:01 AM
    Friday, August 26, 2016 9:44 PM
  • Thank you all,

    Would have loved to go with dmvs. Extended Events seems to be the solution here. The only down point is that I cannot investigate after the fact. Unless I leave the extended event session running.

    Love you blogs and sites!

    Sunday, August 28, 2016 2:11 AM