Identyfikacja nieużywanych procedur przechowywanych

W przyszłym roku pomagam w czyszczeniu kilku środowisk SQL Server.

Mamy około 10 000 procedur przechowywanych i szacujemy, że tylko około 1000 z nich jest używanych regularnie, a kolejne 200 jest używanych w rzadkich przypadkach, co oznacza, że ​​mamy dużo pracy.

Ponieważ mamy wiele działów i zespołów, które mają dostęp do tych baz danych i procedur, nie zawsze to my wywołujemy procedury, co oznacza, że ​​musimy ustalić, jakie procedury są wywoływane. Ponadto chcemy to ustalić w ciągu kilku miesięcy, a nie kilku dni (co eliminuje pewne możliwości).

Jednym podejściem jest wykorzystanie SQL Server Profileri śledzenie wywoływanych procedur i porównanie ich z listą procedur, które posiadamy, przy jednoczesnym zaznaczeniu, czy procedury są używane, czy nie. Odtąd moglibyśmy przenieść procedury na inny schemat, na wypadek gdyby wyszedł jakiś dział.

Czy tutaj stosuje się Profilernajbardziej skuteczne podejście? I / lub czy ktoś z was zrobił coś podobnego i znalazł inny sposób / lepszy sposób na zrobienie tego?

Możesz używać śledzenia po stronie serwera (innego niż używanie interfejsu GUI Profiler, który wymaga więcej zasobów) podczas testowania lub cyklu biznesowego i przechwytywać tylko rzeczy związane z SP. Następnie możesz załadować to do tabeli lub programu Excel do dalszej analizy.

Drugim podejściem jest użycie DMV sys.dm_exec_procedure_stats (z tym ograniczeniem, że jeśli serwer SQL zostanie zrestartowany, to dane zostaną usunięte).

Możesz nawet zaplanować zadanie przechwytywania danych DMV do tabeli, aby zachować ją.

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

Odnosić się do :

• Jak znaleźć nieużywane Procedury składowane w SQL Server 2005/2008
• Identyfikacja nieużywanych obiektów w bazie danych

To pytanie może być przydatne, ma zastosowanie do tabel i kolumn, ale sugeruje użycie zewnętrznego narzędzia ApexSQL Clean, które może również znaleźć nieużywane procedury składowane, a także wszystkie obiekty, do których nie odwołuje się żaden inny obiekt w bazie danych lub w zewnętrznych bazach danych

Zastrzeżenie: Pracuję dla ApexSQL jako inżynier wsparcia

Jeśli korzystasz z SQL Server 2008+, możesz także używać zdarzeń rozszerzonych z celem histogramu . Być może byłby to mniejszy ciężar niż ślad.

AFAIK musiałbyś utworzyć inną sesję dla każdej interesującej bazy danych, ponieważ nie widziałem żadnych oznak, że możliwe jest tworzenie wiaderków na wielu kolumnach. Krótki przykład poniżej włącza się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

A następnie po kilkukrotnym uruchomieniu niektórych procedur przechowywanych w tym DB i pobraniu danych za pomocą

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'

Dane wyjściowe to

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

Pokazuje, że procedura z object_idod 1287675635wykonano 36 razy na przykład. asynchronous_bucketizerJest pamięć tylko dlatego najlepiej byłoby założyć coś, ankiety to tak często, i zapisuje do pamięci trwałej.

Jako kontynuacja skryptu Kin. Oto prosty skrypt do utworzenia tabeli do śledzenia zużycia w czasie oraz skrypt do okresowej aktualizacji.

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

Ten post zawiera również skrypt do wyszukiwania nieużywanych wyrzutów: Znajdź nieużywane tabele bazy danych w SQL Server Poniżej znajduje się skrypt z artykułu, zmieniłem typ tabeli „U” na typ procedury składowanej „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