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?

我们开始在应用程序中获取大量存储过程。其中许多用于定制报告，其中许多已不再使用。有谁知道我们可以在SQL Server 2005中运行的系统视图上运行的查询，它会告诉我们最后一次执行存储过程的日期?

6 个解决方案

#1

In a nutshell, no.

简单地说,没有。

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

然而，你可以做一些“好”的事情。

Run a profiler trace with, say, the stored proc name
使用存储的proc名称运行分析器跟踪
Add a line each proc (create a tabel of course)
- "INSERT dbo.SPCall (What, When) VALUES (OBJECT_NAME(@@PROCID), GETDATE()"
- “插入dbo。SPCall(什么，什么，什么)值(OBJECT_NAME(@@PROCID)， GETDATE()”
为每个proc添加一行(当然要创建一个tabel)“插入dbo。SPCall(什么，什么，什么)值(OBJECT_NAME(@@PROCID)， GETDATE()”
Extend 2 with duration too
延长2和持续时间

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
添加RAISERROR ('Warning: pwn3d: call admin'， 16,1)，看看谁在调用
Add WAITFOR DELAY '00:01:00', see who calls
增加等待时间:00:01:00，看看是谁打来的

You get the idea. The tried-and-tested "see who calls" method of IT support.

你懂的。经过测试的“查看谁调用”方法支持IT。

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.

如果报表是报表服务，那么如果可以将代码与报表数据集匹配，就可以挖掘RS数据库，以便报表运行。

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.

您不能依赖DMVs，因为它们是重置om SQL服务器重启。查询缓存/锁是短暂的，不会持续任何时间。

#2

The below code should do the trick (>= 2008)

下面的代码应该很有用(>= 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.

编辑1:请注意下面Jeff Modens的建议。

#3

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.

哦,小心了!闪光的未必都是金子!所有的“stats”dm视图和函数都有这样的问题。它们只针对缓存中的内容，而缓存的生命周期可以在几分钟内进行度量。如果你用这样的东西来确定哪些SPs可能会被淘汰，那么当你删除几分钟前才使用的那些SPs时，你可能会受到伤害。

The following excerpts are from Books Online for the given dm views…

以下节选来自网络书籍，提供给dm的观点。

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_procedure dure_stats返回缓存存储过程的总体性能统计信息。视图每个存储过程包含一行，并且只要存储过程保持缓存，行的生命周期就为行。当从缓存中删除存储过程时，从这个视图中删除相应的行。

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.

sys。dm_exec_query_stats视图在缓存的计划中每个查询语句包含一行，并且行生命周期与计划本身绑定。当计划从缓存中删除时，相应的行将从这个视图中删除。

#4

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.

sys。dm_exec_procedure dure_stats包含关于执行函数、约束和过程等的信息。但是行的生命周期是有限制的，当执行计划从缓存中删除时，条目就会消失。

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

#5

This works fine on 2005 (if the plan is in the cache)

这在2005年可以正常工作(如果计划在缓存中)

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

#6

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

#1