Identificando procedimentos armazenados não utilizados

Você pode usar o rastreamento do lado do servidor (diferente de usar a GUI do Profiler que incorre em mais recursos) durante o teste ou o ciclo de negócios e capturar apenas coisas relacionadas aos SPs. Em seguida, você pode carregá-lo em uma tabela ou excel para análise posterior.

A segunda abordagem é usar DMV sys.dm_exec_procedure_stats (com a limitação de que, se o servidor SQL for reiniciado, os dados serão liberados).

Você pode até agendar um trabalho para capturar dados DMV em uma tabela para mantê-los persistentes.

 -- 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;

Referir-se :

• Como encontrar procedimentos armazenados não utilizados no SQL Server 2005/2008
• Identificando objetos não utilizados em um banco de dados

Você pode achar esta questão útil, ela se aplica a tabelas e colunas, mas sugere o uso de uma ferramenta de terceiros ApexSQL Clean, que também pode encontrar procedimentos armazenados não utilizados, bem como todos os objetos que não são referenciados por nenhum outro objeto no banco de dados ou em bancos de dados externos

Isenção de responsabilidade: trabalho para ApexSQL como engenheiro de suporte

Se você estiver no SQL Server 2008+, também poderá usar eventos estendidos com um destino de histograma . Possivelmente isso seria mais leve do que um traço.

AFAIK, você precisaria criar uma sessão diferente para cada banco de dados de interesse, pois não consegui ver nenhuma indicação de que a segmentação em várias colunas fosse possível. O exemplo rápido abaixo filtra emdatabase_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

E então, depois de executar alguns procedimentos armazenados naquele banco de dados algumas vezes e recuperar os dados com

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'

A saída é

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

Mostrando que o procedimento com object_idof 1287675635foi executado 36 vezes por exemplo. A asynchronous_bucketizermemória é apenas para que seja melhor configurar algo que pesquise isso de vez em quando e salve no armazenamento persistente.

Como uma continuação do roteiro de Kin. Aqui está um script simples para criar uma tabela para rastrear os usos ao longo do tempo e um script para atualizá-lo periodicamente.

--  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
--  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

Esta postagem também fornece um script para localizar objetos não utilizados: Encontre as tabelas de banco de dados não utilizadas no SQL Server Abaixo está o script do artigo, alterei o tipo de tabela "U" para o tipo de procedimento armazenado "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