Como ver quando os procedimentos armazenados foram executados pela última vez

Question

Eu quero ver uma listagem de todos os procs armazenados para cada banco de dados em um servidor junto com a última vez em que o processo de armazenamento foi executado. Eu sou muito bom com SQL, mas eu não sei sobre como observar estatísticas como essa que o sql mantém, então eu gostaria de receber uma pequena ajuda para encontrar essa informação.

EDITAR:

A partir das respostas que estou recebendo parece que isso não é possível do jeito que eu pensei que seria. Eu estava pensando que isso poderia ser feito de forma semelhante a como você pode ver quando uma tabela foi acessada pela última vez:

select  t.name, user_seeks, user_scans, user_lookups, user_updates, 
        last_user_seek, last_user_scan, last_user_lookup, last_user_update

from    sys.dm_db_index_usage_stats i JOIN
        sys.tables t ON (t.object_id = i.object_id)

where   database_id = db_id()

O script acima foi roubado de um comentário em link .

    

Answer 1

Aqui está um pequeno script que listará todos os procedimentos armazenados do usuário (ou seja, aqueles que não vieram com o SQL Server ou não são procedimentos armazenados no sistema) em todos os bancos de dados da instância do servidor:

CREATE TABLE #ProcTable (
    DbName sysname,
    ProcName sysname
)

DECLARE @command1 NVARCHAR(1000)

SET @command1 = 'USE [?];INSERT #ProcTable 
SELECT ''[?]'',Name
FROM sys.all_objects
WHERE TYPE=''p''
AND is_ms_shipped=0'

EXEC sp_msforeachdb @command1

SELECT * FROM #ProcTable 
ORDER BY DbName,ProcName

DROP TABLE #ProcTable 

Se você não estiver executando um rastreio que capturará o evento exec (e não terá um rastreio padrão em execução), será necessário ter um mecanismo de auditoria no nível do aplicativo para capturar a execução de procedimentos armazenados. Se você não tiver nenhum desses, você não poderá voltar no tempo para ver quando o processo foi executado.

    

Answer 2

Não há registros nativos que façam isso, portanto, você precisará executar um rastreamento do lado do servidor ou configurar algum tipo de auditoria para capturar essas informações.

    

Answer 3

Você pode obter uma resposta limitada para essa pergunta consultando a exibição de gerenciamento dinâmico sys.dm_exec_procedure_stats que foi introduzida no SQL Server 2008.

select
    DB_NAME(database_id) as "database_name" 
    , OBJECT_NAME(object_id, database_id) as "procedure_name"
    , last_execution_time
from 
    sys.dm_exec_procedure_stats

Para quaisquer procedimentos armazenados que estejam no cache de procedimentos , isso informará o último tempo de execução. (Existem muitas outras colunas interessantes no DMV também.)

Essas informações não são necessariamente completas: na melhor das hipóteses, elas terão informações somente desde o último início do mecanismo do banco de dados (não persistiu nas reinicializações do serviço). Se um procedimento armazenado não tiver sido executado desde que o servidor foi reiniciado, ele não estará presente aqui. Limpar o cache do plano fará com que esse DMV seja redefinido. Como as operações que limparão o cache do plano como um efeito colateral (como a alteração do MAXDOP, ou configurações de memória) ou invalidarão os planos em cache.

Mas dá a você uma estimativa, e se você está se sentindo realmente ambicioso, você poderia escrever uma rotina que pesquisa o DMV periodicamente e armazena os resultados em uma tabela para uma análise de longo prazo.