TSQL 查询以查找未使用的存储过程 [英] TSQL query to find un-used stored procedures

问题描述

我正在尝试追踪数据库中从未使用过或数月未使用的所有存储过程.

I am trying to track down all stored procedures in a database that have never been used, or that have not been used in many months.

我想找到一个查询来显示所有未使用的存储过程，以便可以分析这些存储过程以确定是否可以删除它们.

I would like to find a query to show all the stored procedures that are not in use so that those stored procedures can be analyzed to determine if they can be removed.

我熟悉 sys.procedures，但不知道如何确定某个过程是否在使用中.

I am familiar with sys.procedures, but don't know how to determine if a procedure is in use or not.

SELECT *
FROM sys.procedures;

使用 SQL Server 2008 R2.

Using SQL Server 2008 R2.

更新更新更新

使用下面来自 Aaron Bertrand 的查询，稍作修改，这是我最终使用的，非常完美.

Using the query from Aaron Bertrand below, slightly modified, this is what I ended up using, and it was perfect.

SELECT p.*
  FROM sys.procedures AS p
  LEFT JOIN sys.dm_exec_procedure_stats AS s ON s.[object_id] = p.[object_id]
 WHERE s.object_id IS NULL;

感谢您的帮助.

推荐答案

DMV 将记录程序的统计信息，但它们只能追溯到上次重启时(通常不会那么远，取决于计划存在的时间长短)在缓存中):

DMVs will record stats for procedures, but they only possibly go as far back as the last restart (and often not that far, depending on how long a plan lives in cache):

SELECT * FROM sys.dm_exec_procedure_stats AS s
INNER JOIN sys.procedures AS p
ON s.[object_id] = p.[object_id]
ORDER BY p.name;

因此，如果您的系统只运行了很短的时间，这不是一个可靠的措施.@Siva 指出的链接对于其他一些想法也很有用.不幸的是，SQL Server 并没有真正为您全面跟踪这一点，因此除非您添加跟踪或日志记录，否则您会被 DMV 中的信任所困扰...

So if your system has only been up for a short time, this is not a reliable measure. The link @Siva points out is useful as well for some other ideas. Unfortunately SQL Server doesn't really track this for you overall so unless you add tracing or logging you are stuck with the trust you place in the DMV...

编辑这是一个好点，我正在解决已经运行的程序.相反，您可能想要这样:

EDIT it was a good point, I was solving for the procedures that have run. Instead you may want this:

SELECT sc.name, p.name 
FROM sys.procedures AS p
INNER JOIN sys.schemas AS sc
  ON p.[schema_id] = sc.[schema_id]
LEFT OUTER JOIN sys.dm_exec_procedure_stats AS st
  ON p.[object_id] = st.[object_id]
WHERE st.[object_id] IS NULL
ORDER BY p.name;

或者您可能还想包括已经运行过的过程，但是按照它们上次运行的时间排序:

Or you may want to also include procedures that have run as well, but order them by when they last ran:

SELECT sc.name, p.name 
FROM sys.procedures AS p
INNER JOIN sys.schemas AS sc
  ON p.[schema_id] = sc.[schema_id]
LEFT OUTER JOIN sys.dm_exec_procedure_stats AS st
ON p.[object_id] = st.[object_id]
ORDER BY st.last_execution_time, p.name;

这将首先对自重新启动后尚未运行的过程进行排序，然后按照最后执行的时间、最旧的最先执行其余过程.

This will order first the procedures that haven't run since a restart, then the rest by when they were executed last, oldest first.

这篇关于TSQL 查询以查找未使用的存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！