Запрос TSQL для поиска неиспользуемых хранимых процедур

Я пытаюсь отслеживать все хранимые процедуры в базе данных, которые никогда не использовались или которые не использовались в течение многих месяцев.

Я хотел бы найти запрос, чтобы показать все хранимые процедуры, которые не используются, чтобы эти хранимые процедуры могли быть проанализированы, чтобы определить, можно ли их удалить.

Я знаком с sys.procedures, но не знаю, как определить, используется ли процедура или нет.

SELECT *
FROM sys.procedures;

Использование SQL Server 2008 R2.

UPDATE UPDATE UPDATE

Используя запрос от Aaron Bertrand ниже, слегка измененный, это то, что я использовал, и это было прекрасно.

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;

Спасибо за помощь.

Ответ 1

DMVs будут записывать статистику для процедур, но они могут идти только в качестве последнего перезапуска (и часто не так далеко, в зависимости от того, как долго живет план в кеше):

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

EDIT, это был хороший момент, я решал все запущенные процедуры. Вместо этого вам может понадобиться следующее:

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;

Или вы можете также включить также запущенные процедуры, но закажите их, когда они в последний раз побежали:

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;

Это сначала закажет процедуры, которые не выполнялись с момента перезапуска, а затем остальные, когда они были выполнены последними, наиболее старыми.

Ответ 2

Здесь вариация принятого ответа была наиболее полезной для меня:

SELECT sc.NAME + '.' + p.NAME [Procedure]
    ,s.last_execution_time
FROM sys.procedures AS p
LEFT JOIN sys.dm_exec_procedure_stats AS s ON p.[object_id] = s.[object_id]
INNER JOIN sys.schemas sc ON p.schema_id = sc.schema_id
ORDER BY s.last_execution_time
    ,sc.NAME
    ,p.NAME

Изменения отображают последнее время выполнения и включают схему процедуры.

Ответ 3

По какой-то причине мое представление dm_exec_procedure_stats очищается в течение дня. Поэтому я просто запустил его прямо сейчас, и самое раннее время выполнения с этого утра, но я знаю, что мы не перезапускали SQL этим утром или чем-то еще.

Во всяком случае, я хотел создать proc, который я мог бы запустить в работу, чтобы запускать каждый час и фиксировать, какие procs выполнялись в последнее время. Вот что я сделал:

Создал таблицу для регистрации обработанных процессов и их первого и последнего времени выполнения.

create table ProcsThatExecute (procName varchar(500), firstExecutionTime datetime, lastExecutionTime datetime)

Создал процедуру, которая вставляет или обновляет таблицу ProcsThatExecute. Выполняйте это каждый час на работе, и через несколько дней или недель или месяцев у вас есть журнал, который использует procs:

alter procedure LogProcsThatExecute as begin

    --If they don't already exist in this table, add them.
    insert into ProcsThatExecute(procName, firstExecutionTime, lastExecutionTime)
    SELECT p.NAME ProcName, s.last_execution_time, null
    FROM sys.procedures AS p
        LEFT OUTER JOIN sys.dm_exec_procedure_stats AS s ON p.[object_id] = s.[object_id]
    where not exists (
        select 1 from ProcsThatExecute pte where pte.procName = p.name
    ) and last_execution_time is not null


    --If they do exist in this table, update the last execution time.
    update ProcsThatExecute set lastExecutionTime = s.last_execution_time
    from ProcsThatExecute pte
    inner join sys.procedures AS p on pte.procName = p.name
        LEFT OUTER JOIN sys.dm_exec_procedure_stats AS s ON p.[object_id] = s.[object_id]
    where s.last_execution_time is not null

end