June 19, 2014 at 9:32 am
Hey all,
I am in the process of migrating a 2005 db to 2012 and I need to find out which of my procs run the longest and last execution start and finish. Not sure how to do this in 2005. I was going to post on that forum, but the last post was answered 2 days ago. Seems like SQL 2005 is old as dirt :).
Thanks
SQL-TG
Everyone has a plan until they get punched in the mouth. --Mike Tyson
June 19, 2014 at 10:22 am
You can query the plan cache.
You can run a server side trace.
or you could try something like this
SELECT TOP 10
[Object_Name] = object_name(st.objectid),
creation_time,
last_execution_time,
total_cpu_time = total_worker_time / 1000,
avg_cpu_time = (total_worker_time / execution_count) / 1000,
min_cpu_time = min_worker_time / 1000,
max_cpu_time = max_worker_time / 1000,
last_cpu_time = last_worker_time / 1000,
total_time_elapsed = total_elapsed_time / 1000 ,
avg_time_elapsed = (total_elapsed_time / execution_count) / 1000,
min_time_elapsed = min_elapsed_time / 1000,
max_time_elapsed = max_elapsed_time / 1000,
avg_physical_reads = total_physical_reads / execution_count,
avg_logical_reads = total_logical_reads / execution_count,
execution_count,
SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
(
(
CASE 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
sys.dm_exec_query_stats qs
CROSS APPLY
sys.dm_exec_sql_text(qs.sql_handle) st
WHERE
Object_Name(st.objectid) IS NOT NULL
AND st.dbid = DB_ID()
ORDER BY
db_name(st.dbid),
total_worker_time / execution_count DESC
The problem with plan cache and the provided query is that you may not have all of the queries that get executed in cache at the time of execution.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply