May 16, 2008 at 9:33 pm
Hi everyone,
I'm having some trouble to finding long queries on my database (sql server 2005). When I operated my application (AR System) with Oracle I was use the follow query to view the queries (on oracle)
select a.opname, trunc((100*(a.sofar/a.totalwork)),2), b.sql_text
from sys.GV_$SESSION_LONGOPS a, v$sql b
where a.sql_address = b.address
and a.sofar <> a.totalwork
select target Tabela,
start_time Início,
trunc((time_remaining/60),2) Estimativa
from sys.GV_$SESSION_LONGOPS
order by start_time desc
Anyone know that can I find long queries on SQL 2005?
Tks
Rodrigo
May 17, 2008 at 6:41 pm
Do you mean which queries have had the longest average run time since the server was started? Or do you mean which currently running queries are the oldest?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 17, 2008 at 9:39 pm
I want to know all tueh current querries running on my database.
May 17, 2008 at 10:02 pm
You could start with this:
select s.text,
P.*
from master..sysprocesses P
Cross apply fn_get_sql(sql_handle) s
though you will get better info from a profiler sessions.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 29, 2008 at 4:51 pm
Try this it will tell you the 100 slowest querys that have run in the last 24 hours. Note that it actually measure each query. If the query is part of an SP the Beginning text will tell you the sp. The statement_text will show the actual query.
select top 100
substring(st.text, 1, 100) as BeginningText
,qs.execution_count
,qs.total_elapsed_time / (1000 * qs.execution_count) as AvgTime
,qs.last_execution_time
,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 statement_text
from master.sys.dm_exec_query_stats as qs
outer apply master.sys.dm_exec_sql_text(qs.sql_handle) AS st
where datediff(hh, qs.last_execution_time, getdate()) <= 24
order by qs.total_elapsed_time / qs.execution_count desc
May 31, 2008 at 10:23 pm
What about the sql server 2005 Dashboard performance reports :
That will give you a lot of information about what is going on from the cache to the average duration
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply