performance monitors on the SQL 2005

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 VBScriptTake a look at various wait stats counters in the Dynamic Management Views
exposed in SQL 2005. See BOL. Microsoft has a nice document on performance
analysis using waits and queues. Profiler can provide information on
execution times, cpu and i/o usage of executed queries.
Honestly though, the best way for you to prove if the issues are with the
application code or the database is to hire a pro for a quick performance
review.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Daniel" <dan_c@.h.com> wrote in message
news:OBHJIVrNIHA.2208@.TK2MSFTNGP06.phx.gbl...
> 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
>|||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" <dan_c@.h.com> wrote in message
news:OBHJIVrNIHA.2208@.TK2MSFTNGP06.phx.gbl...
> 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
>|||Daniel
http://www.sql-server-performance.com/articles/per/sys_dm_os_performance_counters_p1.aspx
"Daniel" <dan_c@.h.com> wrote in message
news:OBHJIVrNIHA.2208@.TK2MSFTNGP06.phx.gbl...
> 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
>