Last Run Date on a Stored Procedure in SQL Server

Question

We starting to get a lot of stored procedures in our application  Many of them are for custom reports many of which are no longer used  Does anyone know of a query we could run on the system views in SQL Server 2005 that would tell us the last date a stored procedure was executed

User · Answer

The below code should do the trick   gt   2008  SELECT o name          ps last execution time  FROM   sys dm exec procedure stats ps  INNER JOIN         sys objects o         ON ps object id   o object id  WHERE  DB NAME ps database id        ORDER  BY         ps last execution time DESC    Edit 1   Please take note of Jeff Modens advice below  If you find a procedure here  you can be sure that it is accurate  If you do not then you just don t know - you cannot conclude it is not running

User · Answer

Oh  be careful now   All that glitters is NOT gold   All of the    stats    dm views and functions have a problem for this type of thing   They only work against what is in cache and the lifetime of what is in cache can be measure in minutes   If you were to use such a thing to determine which SPs are candidates for being dropped  you could be in for a world of hurt when you delete SPs that were used just minutes ago   The following excerpts are from Books Online for the given dm views     sys dm exec procedure stats Returns aggregate performance statistics for cached stored procedures  The view contains one row per stored procedure  and the lifetime of the row is as long as the stored procedure remains cached  When a stored procedure is removed from the cache  the corresponding row is eliminated from this view   sys dm exec query stats The view contains one row per query statement within the cached plan  and the lifetime of the rows are tied to the plan itself  When a plan is removed from the cache  the corresponding rows are eliminated from this view

User · Answer

I use this   use YourDB   SELECT      object name object id        last execution time       last elapsed time       execution count FROM         sys dm exec procedure stats ps  where        lower object name object id   like  Appl-Name   order by 1

User · Answer

This works fine on 2005  if the plan is in the cache   USE YourDb   SELECT qt  text           AS  SP Name          qs last execution time         qs execution count AS  Execution Count  FROM   sys dm exec query stats AS qs        CROSS APPLY sys dm exec sql text qs sql handle  AS qt WHERE  qt dbid   DB ID          AND objectid   OBJECT ID  YourProc

User · Answer

sys dm exec procedure stats contains the information about the execution functions  constraints and Procedures etc  But the life time of the row has a limit  The moment the execution plan is removed from the cache the entry will disappear    Use  yourDatabaseName  GO SELECT           SCHEMA NAME sysobject schema id           OBJECT NAME stats object id            stats last execution time     FROM            sys dm exec procedure stats stats         INNER JOIN sys objects sysobject ON sysobject object id   stats object id      WHERE           sysobject type    P      ORDER BY            stats last execution time DESC     This will give you the list of the procedures recently executed   If you want to check if a perticular stored procedure executed recently  SELECT       SCHEMA NAME sysobject schema id       OBJECT NAME stats object id        stats last execution time FROM        sys dm exec procedure stats stats     INNER JOIN sys objects sysobject ON sysobject object id   stats object id  WHERE       sysobject type    P      and  sysobject object id   object id  schemaname procedurename        OR sysobject name    procedurename   ORDER BY        stats last execution time DESC

User · Answer

If you enable Query Store on SQL Server 2016 or newer you can use the following query to get last SP execution  The history depends on the Query Store Configuration   SELECT        ObjectName         s name           o Name              LastModificationDate    MAX o modify date        LastExecutionTime       MAX q last execution time  FROM sys query store query q      INNER JOIN sys objects o         ON q object id   o object id     INNER JOIN sys schemas s         ON o schema id   s schema id WHERE o type IN   P   GROUP BY o name     s name

User · Answer

In a nutshell  no   However  there are  nice  things you can do    Run a profiler trace with  say  the stored proc name Add a line each proc  create a tabel of course     INSERT dbo SPCall  What  When  VALUES  OBJECT NAME   PROCID   GETDATE     Extend 2 with duration too   There are  fun  things you can do    Remove it  see who calls Remove rights  see who calls Add RAISERROR   Warning  pwn3d  call admin   16  1   see who calls Add WAITFOR DELAY  00 01 00   see who calls   You get the idea  The tried-and-tested  see who calls  method of IT support   If the reports are Reporting Services  then you can mine the RS database for the report runs if you can match code to report DataSet   You couldn t rely on DMVs anyway because they are reset om SQL Server restart  Query cache locks are transient and don t persist for any length of time

[sql-server] Last Run Date on a Stored Procedure in SQL Server

Examples related to sql-server