Calculate Total Query Response Time - help

Last Post 06 Nov 2010 09:39 AM by gunneyk. 6 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
SQL_Jr
New Member
New Member

--
04 Oct 2010 09:55 AM

Ok,this should be fairly simple, but I'm missing something.

Most of this could be derived out of the DMV sys.dm_os_wait_stats.

CPUTime + ResourceWaitTime + SignalWaitTime= Total Query Response Time

Where do I get the total CPUTime?  This is running time, so how to join historical and running data?  There must be another DMV  Can I get  script for this?  THx

 

rm
New Member
New Member

--
04 Oct 2010 11:01 AM
Did you check sys.dm_exec_query_stats ?
SQL_Jr
New Member
New Member

--
04 Oct 2010 12:31 PM
thanks. i'm a little confused though - any ideas how to put it together?
I can get cpu time from sys.dm_exec_query_stats, but probably should not use _wait_stats.
Script ideas? thx
rm
New Member
New Member

--
05 Oct 2010 06:29 AM
Per BOL:
sql_handle, together with statement_start_offset and statement_end_offset, can be used to retrieve the SQL text of the query by calling the sys.dm_exec_sql_text dynamic management function.
gunneyk
New Member
New Member

--
08 Oct 2010 02:04 PM
The total_elapsed_time column will give you the time for all the executions of that in the query stats dmv. You can get the average of each but if it was run more than once you cant get an exact time for a single itteration there.
SQL_Jr
New Member
New Member

--
05 Nov 2010 12:05 PM
gunneyk, would you say that this query below acurrately reflects the avg all query response time?. Is total query response time the equivalent of total elapsed time of a query?

SELECT avg(total_elapsed_time / execution_count)/1000 As avg_query_response_time --total_avg_elapsed_time (div by 1000 for ms, div by 1000000 for sec)
FROM sys.dm_exec_query_stats
gunneyk
New Member
New Member

--
06 Nov 2010 09:39 AM
Well it's as accurate as it gets . By that I mean you can have one itteration that had blocking going on and made it's duration much longer than normal. So the average may be skewed as a result. But hopefully it all averages out when you have enough executions to go by if you know what I mean. But the expression you have is correct.
You are not authorized to post a reply.

Acceptable Use Policy