Roy Ashbrook

let go or be dragged
posts - 198, comments - 29, trackbacks - 8

how can i find when a stored procedure was last executed in sql 2005?

select top 10
    t.text
    , s.last_execution_time
    , *
from
    sys.dm_exec_query_stats s
    cross apply sys.dm_exec_sql_text(s.sql_handle) t
where
    t.objectid is not null
    and text like '%procname%'
order by
    s.last_execution_time desc

 

obviously replace procname with the procname you want to find. you might get multiple results, and you can probably clean it up some, but it works fine for what i generally need to get the info for. while i was researching this, i also came accross this pretty cool query:

 


 

-- Get Top 200 executed SP's ordered by calls/minute
SELECT TOP 200 qt.text AS 'SP Name', qs.execution_count AS 'Execution Count',
qs.total_worker_time/ISNULL(qs.execution_count, 1) AS 'AvgWorkerTime',
qs.total_worker_time AS 'TotalWorkerTime',
qs.total_elapsed_time/ISNULL(qs.execution_count, 1) AS 'AvgElapsedTime',
qs.max_logical_reads, qs.max_logical_writes, qs.creation_time,
DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Age in Cache',
qs.execution_count/ISNULL(DATEDIFF(Minute, qs.creation_time, GetDate()), 1) AS 'Calls/Minute',
qs.execution_count/ISNULL(DATEDIFF(Second, qs.creation_time, GetDate()), 1) AS 'Calls/Second'
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE qt.dbid = 5 -- Change this for the database you are interested in
ORDER BY qs.execution_count/ISNULL(DATEDIFF(Minute, qs.creation_time, GetDate()), 1) DESC

quoted from: http://glennberrysqlperformance.spaces.live.com/blog/cns!45041418ECCAA960!248.entry 

Print | posted on Friday, March 21, 2008 6:22 PM | Filed Under [ DBA ]

Comments have been closed on this topic.

Powered by: