Consulta TSQL para encontrar la onu-utiliza procedimientos almacenados

Estoy tratando de rastrear a todos los procedimientos almacenados en una base de datos que nunca se han usado, o que no se han utilizado en muchos meses.

Me gustaría encontrar una consulta que muestre todos los procedimientos almacenados que no están en uso, de modo que los procedimientos almacenados pueden ser analizados para determinar si se pueden quitar.

Estoy familiarizado con sys.los procedimientos, pero no sé cómo determinar si un procedimiento está en uso o no.

SELECT *
FROM sys.procedures;

El uso de SQL Server 2008 R2.

ACTUALIZACIÓN ACTUALIZACIÓN ACTUALIZACIÓN

Mediante la consulta de Aaron Bertrand abajo, ligeramente modificado, esto es lo que terminé usando, y fue perfecto.

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;

Gracias por la ayuda.

  • Lo siento Siva, eso no es exactamente lo que yo estaba buscando. El formulario de respuesta Aarón abajo lo hizo.

3 Kommentare

  1. 28

    Dmv le registro de las estadísticas de los procedimientos, pero sólo posible ir tan lejos como el último reinicio (y, a menudo, no es que ahora, dependiendo de cuánto tiempo un plan de vida en caché):

    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;

    Entonces, si su sistema ha sido sólo por un corto tiempo, esta no es una medida fiable. El link @Siva señala es útil, así como para algunas otras ideas. Por desgracia, SQL Server no es realmente esto para usted en general de manera que a menos que se agregue el seguimiento o registro de que usted está atascado con la confianza que usted deposita en el DMV…

    EDITAR era un buen punto, yo era la solución para que los procedimientos que han ejecutar. En lugar usted puede querer esto:

    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;

    O puede que también desee incluir procedimientos que se han ejecutado, pero el fin de ellos cuando se ejecutó por última vez:

    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;

    Este será el fin de la primera los procedimientos que no se ejecute desde un reinicio, luego el resto cuando fueron ejecutadas última, la más antigua primero.

    • Aaron, gracias. Lo que me dio fue muy de cerca, con un pequeño cambio que hizo exactamente lo que yo estaba buscando. SELECCIONE p.* DE sys.procedimientos p LEFT JOIN sys.dm_exec_procedure_stats COMO s.[object_id] = p.[object_id] DONDE s.object_id ES nulo;
    • Sí, yo entiendo que esto es solo desde el último reinicio. Que es bastante bueno para mí. Gracias!
    • Me gustaría no decir «esto es solo desde el último reinicio». Este es caché después de todo y caché está siempre en movimiento (las cosas en y las cosas van a salir). Por favor, consulte @JeffModen la Respuesta aquí: stackoverflow.com/a/13506187/555798
    • Sí, a lo que me refería era que tan pronto ya que se puede ir. Yo no estaba tratando de venderlo como un completo y exhaustivo inventario en todos los casos. Nueva redacción te hacen más feliz?
  2. 2

    Aquí una variación en la aceptación de la respuesta que es el más útil para mí:

    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

    Las modificaciones mostrar el último tiempo de ejecución e incluir el procedimiento del esquema.

  3. 0

    Por alguna razón, mi dm_exec_procedure_stats vista borra a sí mismo a través del día. Así que me corrí con ella ahora mismo y los primeros tiempos de ejecución son de esta mañana, pero sé que no reinicie SQL esta mañana o nada.

    De todos modos, quería crear un proc que podría poner en un puesto de trabajo para que se ejecute cada hora y captura que procs había ejecutado recientemente. Aquí es lo que yo hice:

    Creado una tabla para registrar los procedimientos que se ejecutan y su primer y el último a los tiempos de ejecución.

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

    Creado un procedimiento en el que se inserta o actualiza el ProcsThatExecute tabla. Ejecutar esta cada hora en un trabajo y después de un par de días, o semanas o meses tiene un registro de que los procs de acostumbrarse:

    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

Kommentieren Sie den Artikel

Bitte geben Sie Ihren Kommentar ein!
Bitte geben Sie hier Ihren Namen ein

Pruebas en línea