识别未使用的存储过程

明年，我正在帮助清理几个SQL Server环境。

我们大约有10,000个存储过程，并且估计只有大约1000个存储过程是定期使用的，而另外200个左右的存储过程很少使用，这意味着我们有很多工作要做。

由于我们有多个部门和团队可以访问这些数据库和过程，因此我们并不总是在调用过程，这意味着我们必须确定正在调用的过程。最重要的是，我们希望在几个月而不是几天内确定这一点（这消除了一些可能性）。

一种解决方法是使用SQL Server Profiler并跟踪正在执行的程序调用并将它们与我们拥有的程序列表进行比较，同时标记是否使用了这些程序。从那时起，我们可以将过程转移到其他模式，以防部门尖叫。

在这里使用Profiler是最有效的方法吗？和/或你们有没有做过类似的事情并找到了另一种方法/更好的方法呢？

#1 楼

您可以在测试或业务周期中使用服务器端跟踪（与使用Profiler GUI产生更多资源的情况不同），并且仅捕获与SP相关的内容。然后，您可以将其加载到表或excel中，以进行进一步的分析。第二种方法是使用DMV sys.dm_exec_procedure_stats（但有限制，如果重新启动sql server，则将刷新数据）。 br />
您甚至可以安排作业以将DMV数据捕获到表中以使其持久化。

 -- Get list of possibly unused SPs (SQL 2008 only)
    SELECT p.name AS 'SP Name'        -- Get list of all SPs in the current database
    FROM sys.procedures AS p
    WHERE p.is_ms_shipped = 0

    EXCEPT

    SELECT p.name AS 'SP Name'        -- Get list of all SPs from the current database 
    FROM sys.procedures AS p          -- that are in the procedure cache
    INNER JOIN sys.dm_exec_procedure_stats AS qs
    ON p.object_id = qs.object_id
    WHERE p.is_ms_shipped = 0;

参考：

如何在SQL Server 2005/2008中查找未使用的存储过程
识别数据库中未使用的对象

另请参见stackoverflow.com/questions/10421439/…和stackoverflow.com/questions/7150900/…（忽略了后者上与SQLServerPedia的链接现在已失效）。

–亚伦·伯特兰（Aaron Bertrand）
13年7月25日在21:58

确保您在数周甚至数月内定期检查DMV，因为可能有一些SP仅每月或每季度运行一次。实例重新启动时，将清除DMV，手动将其清除，甚至是在一段时间后清除。

–肯尼斯·费舍尔
13年7月25日在22:22

@KennethFisher这就是为什么我建议安排一个作业以将DMV数据捕获到表中的原因。感谢您的提及！

–金沙（Kin Shah）
13年7月25日在23:56

#2 楼

您会发现此问题很有用，它适用于表和列，但建议使用第三方工具ApexSQL Clean，该工具还可以查找未使用的存储过程以及数据库或外部数据库中任何其他对象未引用的所有对象

免责声明：我是ApexSQL的支持工程师

OP不想找到未引用的存储过程，而是OP想找到未使用的SP。您的答案不能用作该问题的答案。

–金沙（Kin Shah）
2014年1月23日下午13:09

亲爱的，我会更新。 ApexSQL Clean将未使用的对象标记为未引用，因此我知道这引起了混乱

–军医
2014年1月23日下午13:52

#3 楼

如果您使用的是SQL Server 2008+，则还可以将扩展事件与直方图目标一起使用。

AFAIK您可能需要为每个感兴趣的数据库创建一个不同的会话，尽管我看不到任何迹象表明可以对多列进行存储。下面的快速示例对database_id=10进行过滤，然后在该DB中运行了一些存储过程几次，然后使用

检索了数据。

CREATE EVENT SESSION [count_module_start_database_10]
ON SERVER
ADD EVENT sqlserver.module_start
(  
        WHERE (source_database_id=10) 
)
ADD TARGET package0.asynchronous_bucketizer
(     SET  filtering_event_name='sqlserver.module_start', 
            source_type=0, 
            source='object_id',
            slots = 10000
)
WITH (MAX_DISPATCH_LATENCY = 5 SECONDS)
GO
ALTER EVENT SESSION [count_module_start_database_10]
ON SERVER
STATE=START

输出为

 <HistogramTarget truncated="0" buckets="16384">
  <Slot count="36">
    <value>1287675635</value>
  </Slot>
  <Slot count="3">
    <value>1271675578</value>
  </Slot>
  <Slot count="2">
    <value>1255675521</value>
  </Slot>
</HistogramTarget>

显示该过程object_id的1287675635被执行了36次。 asynchronous_bucketizer仅是内存，因此最好设置一些经常轮询的东西并保存到持久性存储中。

的确，每个数据库需要一个会话。非常高兴地说WHERE（source_database_id IN（10,15,20）），但是las支持。

–亚伦·伯特兰（Aaron Bertrand）
13年7月26日在0:10

@AaronBertrand-即使支持，您仍然需要分别对不同数据库中具有相同object_id（或相同object_name）的对象的过程调用进行计数，我也不认为这是可能的。

–马丁·史密斯
13年7月26日在7:14

如果我是错的，但在2012年而不是2008年添加的扩展事件纠正了我吗？

–彼得
17年2月7日在8:48

@Peter是的，你错了。 :-) technet.microsoft.com/zh-CN/library/dd822788(v=sql.100).aspx

–马丁·史密斯
17年2月7日在20:27

扩展事件UI直到SSMS 2012才引入，我不认为它们使它向后兼容。在2008年，开箱即用地创建会话的唯一方法是通过TSQL，尽管有一个用于类似功能的社区项目extendedeventmanager.codeplex.com

–马丁·史密斯
17年2月8日在8:34

#4 楼

作为Kin脚本的后续内容。这是一个简单的脚本，用于创建一个表以跟踪一段时间内的使用情况，并提供一个脚本来定期对其进行更新。

#5 楼

这篇文章还提供了一个脚本来查找未使用的对象：在SQL Server中查找未使用的数据库表
下面是本文中的脚本，我将表类型“ U”更改为存储过程类型“ P”：

   USE DBName;
   SELECT 

       ao.[name] [Table],
       s.[name] [Schema],
       [create_date] [Created],
        [modify_date] [LastModified]
    FROM
         sys.all_objects ao JOIN sys.schemas s
           ON ao.schema_id = s.schema_id
    WHERE
         OBJECT_ID NOT IN (
              SELECT OBJECT_ID
              FROM sys.dm_db_index_usage_stats
        )
        AND [type] = 'P'
    ORDER BY
        [modify_date] DESC

这将始终返回所有过程，因为过程不会在索引使用情况统计DMV中获得输入。

–马丁·史密斯
2014年6月1日下午16:32

编程黑洞网