performance monitors on the SQL 2005

You can gleen this info from sys.dm_exec_query_stats with some limitations.

select total_worker_time/execution_count as AvgCPU
, total_elapsed_time/execution_count as AvgDuration
, (total_logical_reads+total_physical_reads)/execution_count as AvgReads
, execution_count
, substring(st.text, (qs.statement_start_offset/2)+1 , ((case
qs.statement_end_offset when -1 then datalength(st.text) else
qs.statement_end_offset end - qs.statement_start_offset)/2) + 1) as txt
, query_plan
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
order by 2 desc


--
Jason Massie
www: http://statisticsio.com
rss: http://statisticsio.com/Home/tabid/36/rssid/1/Default.aspx


"Daniel" wrote in message

> Looking to see if there is anyway to enable or capture or if already
> available query performance stats on SQL2005
>
> we are having issues with one of our databases.. and the programmers say
> everything is ok.. ( they are outside verndors)
>
> So is there anything we can get from SQL2005 to show us the times the
> performance was low or high.. and such...
>
> --
> ASP, SQL2005, DW8 VBScript
>
 >> Stay informed about: performance monitors on the SQL 2005 

Daniel
http://www.sql-server-performance.com/articles/per/sys_dm_os_performan...counter


"Daniel" wrote in message

> Looking to see if there is anyway to enable or capture or if already
> available query performance stats on SQL2005
>
> we are having issues with one of our databases.. and the programmers say
> everything is ok.. ( they are outside verndors)
>
> So is there anything we can get from SQL2005 to show us the times the
> performance was low or high.. and such...
>
> --
> ASP, SQL2005, DW8 VBScript
>
 >> Stay informed about: performance monitors on the SQL 2005