CODEWITHSUNDEEP
sql-serverstored-procedures
Jon Edmiston
Jon Edmiston

Reputation: 1000

Last Run Date on a Stored Procedure in SQL Server

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?

Upvotes: 88

Views: 194872

Answers (7)

Jeff Moden
Jeff Moden

Reputation: 3494

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 measured 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.

Upvotes: 35

Pixelated
Pixelated

Reputation: 1651

The below code should do the trick (>= 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.

Upvotes: 74

Alexander Sharovarov
Alexander Sharovarov

Reputation: 311

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

Upvotes: 9

user5175823
user5175823

Reputation: 21

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

Upvotes: 2

Naruto
Naruto

Reputation: 708

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

Upvotes: 9

Martin Smith
Martin Smith

Reputation: 452977

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')

Upvotes: 2

gbn
gbn

Reputation: 432190

In a nutshell, no.

However, there are "nice" things you can do.

  1. Run a profiler trace with, say, the stored proc name
  2. Add a line each proc (create a tabel of course)
    • "INSERT dbo.SPCall (What, When) VALUES (OBJECT_NAME(@@PROCID), GETDATE()"
  3. Extend 2 with duration too

There are "fun" things you can do:

  1. Remove it, see who calls
  2. Remove rights, see who calls
  3. Add RAISERROR ('Warning: pwn3d: call admin', 16, 1), see who calls
  4. 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.

Upvotes: 40

Related Questions