主页 / dba / 问题 / 47025
Accepted
Question3CPO
Asked: 2013-07-26 13:15:29 +0800 CST

识别未使用的存储过程

  • 772

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

我们有大约 10,000 个存储过程，估计其中只有大约 1000 个被定期使用，另外 200 个左右在极少数情况下使用，这意味着我们有很多工作要做。

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

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

在这里使用Profiler最有效的方法吗？和/或你们中的任何人有没有做过类似的事情并找到另一种方式/更好的方式来做到这一点？

sql-server profiler

5 个回答

  1. Best Answer

    您可以在测试或业务周期期间使用服务器端跟踪 （与使用会产生更多资源的 Profiler GUI 不同）并仅捕获与 SP 相关的内容。然后，您可以将其加载到表格或 Excel 中以进行进一步分析。

    第二种方法，是使用 DMV sys.dm_exec_procedure_stats（有限制，如果 sql server 重新启动，则数据被刷新）。

    您甚至可以安排一项作业以将 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;

    参考：

    • 33

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

    免责声明：我作为支持工程师在 ApexSQL 工作

    • 11

  3. 如果您使用的是 SQL Server 2008+，您还可以使用带有histogram target的扩展事件。可能这会比痕迹更轻。

    AFAIK 您需要为每个感兴趣的数据库创建一个不同的会话，尽管我看不到任何迹象表明可以对多个列进行分桶。下面的快速示例过滤database_id=10

    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

    然后在该数据库中运行一些存储过程几次并使用

    SELECT CAST(target_data as XML) target_data
FROM sys.dm_xe_sessions AS s 
JOIN sys.dm_xe_session_targets t
    ON s.address = t.event_session_address
WHERE s.name = 'count_module_start_database_10'

    输出是

    <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_idof的过程1287675635执行了 36 次。这asynchronous_bucketizer只是内存，所以最好设置一些经常轮询它并保存到持久存储的东西。

    • 10

  4. 作为Kin的剧本的后续。这是一个简单的脚本，用于创建一个表来跟踪一段时间内的使用情况以及一个定期更新它的脚本。

    --  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
--  Create the use table 
--  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
CREATE TABLE [dbo].[_ProcedureUseLog](
    [ObjectName] [nvarchar](255) NOT NULL,
    [UseCount] [int] NULL,
    [LastUse] [datetime] NULL,
    [LastCache] [datetime] NULL,
 CONSTRAINT [PK___PROCEDURE_USE] PRIMARY KEY CLUSTERED 
(
    [ObjectName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[_ProcedureUseLog] ADD  CONSTRAINT [DF_Table_1_References]  DEFAULT ((0)) FOR [UseCount]
GO

--  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
--  Run this periodically to update the usage stats
--  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DECLARE @UsesTable TABLE
(
    ObjectName nvarchar(255),
    Executions int,
    LastUse datetime,
    LastCache datetime
)

INSERT INTO @UsesTable       
SELECT p.name, qs.execution_count, qs.last_execution_time, qs.cached_time
FROM    sys.procedures AS p LEFT OUTER JOIN
        sys.dm_exec_procedure_stats AS qs ON p.object_id = qs.object_id
WHERE        (p.is_ms_shipped = 0)

MERGE [dbo].[_ProcedureUseLog]      AS [Target]
USING @UsesTable                    AS [Source]
    ON Target.ObjectName = Source.ObjectName
WHEN MATCHED AND 
        ( Target.LastCache <> Source.LastCache)
    THEN UPDATE SET
        Target.UseCount = Target.UseCount + Source.Executions,
        Target.LastCache = Source.LastCache,
        Target.LastUse = Source.LastUse
WHEN NOT MATCHED
    THEN INSERT (ObjectName, UseCount, LastUse, LastCache) 
    VALUES      (ObjectName, Executions, LastUse, LastCache);

--  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
--  This just shows what you've logged so far
--  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SELECT * FROM [_ProcedureUseLog] ORDER BY UseCount DESC
    • 4

  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
    • 0

相关问题