• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 374
  • Last Modified:

How can I check when the last time a stored procedure was run?

How can I check when the last time a stored procedure was run?

I am using SQL Server 2005 .
0
Mr_Shaw
Asked:
Mr_Shaw
4 Solutions
 
chapmandewCommented:
Unless you explicitly track it, you really can't.  It might still be in the proc cache though.  If it is, you can use this (filter on object_name)


SELECT
      AverageLogicalReads = (total_logical_reads/execution_count),
      AverageLogicalWrites = (total_logical_writes/execution_count),
      AveragePhysicalReads = (total_physical_reads/execution_count),
      TotalLogicalReads = total_logical_reads,
      TotalLogicalWrites = total_logical_writes,
      AverageTimeSeconds = (total_elapsed_time/execution_count)/1000000.0,
      ExecutionCount = execution_count,
      TotalCPUSeconds = total_worker_time/1000000.0,
      MaxCPUSeconds = max_worker_time/1000000.0,
      AverageElapsedTimeSeconds = (total_elapsed_time/execution_count)/1000000.0,
      StatementText = SUBSTRING(t.text, (s.statement_start_offset/2)+1,
                  ((CASE s.statement_end_offset
                    WHEN -1 THEN DATALENGTH(t.text)
                   ELSE s.statement_end_offset
                   END - s.statement_start_offset)/2) + 1),
      ObjectName = object_name(objectid, dbid),
      LastExecutionTime = last_execution_time,
      AverageCPUSeconds = (max_worker_time/execution_count)/1000000.0
FROM
      sys.dm_exec_query_stats  s
      cross apply sys.dm_exec_sql_text(sql_handle) t
ORDER BY LastExecutionTime DESC


0
 
Om PrakashCommented:
0
 
chapmandewCommented:
That isn't a solution for the procs that have been previously written.  That is something to catch proc calls going forward.
0
 
halfbloodprinceCommented:
If a stored procedure is still in the procedure cache, you can find the last time it was executed by querying the sys.dm_exec_query_stats DMV. In this example, I also cross apply to the sys.dm_exec_query_plan DMF in order to qualify the object id:
METHOD 1:
declare @proc_nm sysname
 
– select the procedure name here
set @proc_nm = ‘usp_test’
 
select s.last_execution_time
from sys.dm_exec_query_stats s
cross apply sys.dm_exec_query_plan (s.plan_handle) p
where object_name(p.objectid, db_id(’AdventureWorks’)) = @proc_nm

OR ================================================
METHOD 2:
SELECT OBJECT_NAME(sys.dm_exec_sql_text.objectid),

sys.dm_exec_query_stats.*

FROM sys.dm_exec_query_stats

CROSS APPLY sys.dm_exec_sql_text (sys.dm_exec_query_stats.sql_handle)

WHERE sys.dm_exec_sql_text.dbid = db_id()

AND OBJECT_NAME(sys.dm_exec_sql_text.objectid) = 'yoursp'

0
 
Mr_ShawAuthor Commented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now