Explorar guías rápidas · SQL Server

Tecnología: SQL ServerCategoría: RendimientoNivel: IntermedioActualizado

Encontrar el uso de procedimientos almacenados

Encuentra los procedimientos almacenados ejecutados recientemente, compara su número de ejecuciones y su consumo de recursos, y entiende los límites de las estadísticas de la caché de procedimientos de SQL Server.

En esta página
  1. Problema
  2. Respuesta rápida
  3. Código / comandos
  4. 1. Encontrar los procedimientos ejecutados recientemente
  5. 2. Encontrar los procedimientos ejecutados con más frecuencia
  6. 3. Encontrar los procedimientos que más CPU total consumen
  7. 4. Encontrar los procedimientos con el CPU promedio más alto
  8. 5. Encontrar los procedimientos que hacen más lecturas lógicas
  9. 6. Encontrar los procedimientos con el tiempo transcurrido promedio más alto
  10. 7. Comprobar un procedimiento almacenado concreto
  11. 8. Encontrar los procedimientos que faltan actualmente en las estadísticas de procedimientos
  12. 9. Comprobar el tiempo de actividad de SQL Server / la ventana de observación
  13. 10. Comprobar cuándo entraron en la caché las estadísticas del procedimiento
  14. 11. Usar Query Store para obtener evidencia histórica duradera
  15. Qué significa el resultado
  16. Qué revisar
  17. Número de ejecuciones alto + costo promedio moderado
  18. Número de ejecuciones bajo + duración promedio muy alta
  19. Tiempo transcurrido alto + CPU bajo
  20. CPU alto + lecturas lógicas altas
  21. cached_time es reciente
  22. No hay fila en sys.dm_exec_procedure_stats
  23. Notas de producción
  24. Las estadísticas de procedimientos se basan en la caché
  25. El ciclo de vida de la caché de planes importa
  26. Los promedios pueden ocultar sesgos
  27. Eliminar un procedimiento requiere algo más que estadísticas de uso
  28. Los permisos varían según la versión
  29. Errores comunes
  30. 1. Tratar la ausencia de filas en la DMV como prueba de que no se usa
  31. 2. Ignorar cached_time
  32. 3. Clasificar solo por duración promedio
  33. 4. Eliminar un procedimiento almacenado porque «no se ha ejecutado»
  34. 5. Suponer que las estadísticas de procedimiento equivalen a las estadísticas de instrucción
  35. 6. Comparar estadísticas entre distintos ciclos de vida de la caché
  36. Siguiente paso
  37. Para profundizar

Problema

Necesitas saber si un procedimiento almacenado se sigue usando, qué procedimientos se ejecutan con más frecuencia o cuáles consumen más CPU, lecturas o tiempo transcurrido.

Las preguntas típicas incluyen:

  • ¿Se ha ejecutado este procedimiento almacenado recientemente?
  • ¿Cuántas veces se ha ejecutado?
  • ¿Qué procedimientos son los más costosos?
  • ¿Qué procedimientos no han aparecido en la carga de trabajo reciente?
  • ¿Puedo eliminar con seguridad un procedimiento que parece no usarse?

La limitación clave es que sys.dm_exec_procedure_stats es un historial basado en la caché, no un historial de uso permanente.

Respuesta rápida

Usa sys.dm_exec_procedure_stats para los procedimientos que tienen actualmente estadísticas de ejecución en caché.

SELECT TOP (50)
    DB_NAME(ps.database_id) AS database_name,
    OBJECT_SCHEMA_NAME(ps.object_id, ps.database_id) AS schema_name,
    OBJECT_NAME(ps.object_id, ps.database_id) AS procedure_name,

    ps.cached_time,
    ps.last_execution_time,
    ps.execution_count,

    CAST(ps.total_worker_time / 1000.0 AS decimal(18,2)) AS total_cpu_ms,
    CAST(
        ps.total_worker_time * 1.0
        / NULLIF(ps.execution_count, 0)
        / 1000.0
        AS decimal(18,2)
    ) AS avg_cpu_ms,

    ps.total_logical_reads,
    CAST(
        ps.total_logical_reads * 1.0
        / NULLIF(ps.execution_count, 0)
        AS decimal(18,2)
    ) AS avg_logical_reads,

    CAST(ps.total_elapsed_time / 1000.0 AS decimal(18,2)) AS total_elapsed_ms,
    CAST(
        ps.total_elapsed_time * 1.0
        / NULLIF(ps.execution_count, 0)
        / 1000.0
        AS decimal(18,2)
    ) AS avg_elapsed_ms
FROM sys.dm_exec_procedure_stats AS ps
WHERE
    ps.database_id = DB_ID()
ORDER BY
    ps.last_execution_time DESC;

Usa esta consulta para entender la actividad reciente en caché.

No interpretes «no se devolvió ninguna fila» como prueba de que el procedimiento nunca se ha usado.

Código / comandos

1. Encontrar los procedimientos ejecutados recientemente

SELECT
    OBJECT_SCHEMA_NAME(ps.object_id, ps.database_id) AS schema_name,
    OBJECT_NAME(ps.object_id, ps.database_id) AS procedure_name,
    ps.last_execution_time,
    ps.execution_count,
    ps.cached_time
FROM sys.dm_exec_procedure_stats AS ps
WHERE
    ps.database_id = DB_ID()
ORDER BY
    ps.last_execution_time DESC;

Usa esta consulta cuando la pregunta sea:

¿Qué procedimientos almacenados se han ejecutado más recientemente?


2. Encontrar los procedimientos ejecutados con más frecuencia

SELECT TOP (50)
    OBJECT_SCHEMA_NAME(ps.object_id, ps.database_id) AS schema_name,
    OBJECT_NAME(ps.object_id, ps.database_id) AS procedure_name,
    ps.execution_count,
    ps.last_execution_time,
    ps.cached_time
FROM sys.dm_exec_procedure_stats AS ps
WHERE
    ps.database_id = DB_ID()
ORDER BY
    ps.execution_count DESC;

Un número de ejecuciones alto puede identificar:

  • procedimientos de API muy solicitados
  • patrones de aplicación demasiado conversacionales (chatty)
  • procedimientos utilitarios llamados con frecuencia
  • procedimientos en los que pequeñas ineficiencias se vuelven costosas a gran escala

3. Encontrar los procedimientos que más CPU total consumen

SELECT TOP (50)
    OBJECT_SCHEMA_NAME(ps.object_id, ps.database_id) AS schema_name,
    OBJECT_NAME(ps.object_id, ps.database_id) AS procedure_name,
    ps.execution_count,

    CAST(ps.total_worker_time / 1000.0 AS decimal(18,2)) AS total_cpu_ms,

    CAST(
        ps.total_worker_time * 1.0
        / NULLIF(ps.execution_count, 0)
        / 1000.0
        AS decimal(18,2)
    ) AS avg_cpu_ms,

    ps.last_execution_time
FROM sys.dm_exec_procedure_stats AS ps
WHERE
    ps.database_id = DB_ID()
ORDER BY
    ps.total_worker_time DESC;

Usa el CPU total cuando te importe el impacto global en el servidor.

Un procedimiento moderadamente costoso llamado miles de veces puede importar más que un procedimiento muy lento llamado una sola vez.


4. Encontrar los procedimientos con el CPU promedio más alto

SELECT TOP (50)
    OBJECT_SCHEMA_NAME(ps.object_id, ps.database_id) AS schema_name,
    OBJECT_NAME(ps.object_id, ps.database_id) AS procedure_name,
    ps.execution_count,

    CAST(
        ps.total_worker_time * 1.0
        / NULLIF(ps.execution_count, 0)
        / 1000.0
        AS decimal(18,2)
    ) AS avg_cpu_ms,

    CAST(ps.total_worker_time / 1000.0 AS decimal(18,2)) AS total_cpu_ms,
    ps.last_execution_time
FROM sys.dm_exec_procedure_stats AS ps
WHERE
    ps.database_id = DB_ID()
ORDER BY
    avg_cpu_ms DESC;

El costo promedio es útil para los procedimientos costosos por llamada.

Revisa siempre execution_count junto al promedio.

Los promedios basados en una o dos ejecuciones son evidencia débil.


5. Encontrar los procedimientos que hacen más lecturas lógicas

SELECT TOP (50)
    OBJECT_SCHEMA_NAME(ps.object_id, ps.database_id) AS schema_name,
    OBJECT_NAME(ps.object_id, ps.database_id) AS procedure_name,
    ps.execution_count,
    ps.total_logical_reads,

    CAST(
        ps.total_logical_reads * 1.0
        / NULLIF(ps.execution_count, 0)
        AS decimal(18,2)
    ) AS avg_logical_reads,

    ps.last_execution_time
FROM sys.dm_exec_procedure_stats AS ps
WHERE
    ps.database_id = DB_ID()
ORDER BY
    ps.total_logical_reads DESC;

Usa esta consulta para identificar los procedimientos que ejercen una fuerte presión sobre el buffer pool o recorren grandes cantidades de datos en caché.

Muchas lecturas no significan automáticamente «índice faltante».

Inspecciona los planes de consulta y la lógica del procedimiento.


6. Encontrar los procedimientos con el tiempo transcurrido promedio más alto

SELECT TOP (50)
    OBJECT_SCHEMA_NAME(ps.object_id, ps.database_id) AS schema_name,
    OBJECT_NAME(ps.object_id, ps.database_id) AS procedure_name,
    ps.execution_count,

    CAST(
        ps.total_elapsed_time * 1.0
        / NULLIF(ps.execution_count, 0)
        / 1000.0
        AS decimal(18,2)
    ) AS avg_elapsed_ms,

    CAST(
        ps.total_worker_time * 1.0
        / NULLIF(ps.execution_count, 0)
        / 1000.0
        AS decimal(18,2)
    ) AS avg_cpu_ms,

    ps.last_execution_time
FROM sys.dm_exec_procedure_stats AS ps
WHERE
    ps.database_id = DB_ID()
ORDER BY
    avg_elapsed_ms DESC;

Si el tiempo transcurrido es alto pero el CPU es bajo, investiga las esperas y las situaciones de bloqueo.


7. Comprobar un procedimiento almacenado concreto

DECLARE @schema_name sysname = N'dbo';
DECLARE @procedure_name sysname = N'YourProcedure';

SELECT
    OBJECT_SCHEMA_NAME(ps.object_id, ps.database_id) AS schema_name,
    OBJECT_NAME(ps.object_id, ps.database_id) AS procedure_name,

    ps.cached_time,
    ps.last_execution_time,
    ps.execution_count,

    CAST(ps.total_worker_time / 1000.0 AS decimal(18,2)) AS total_cpu_ms,
    CAST(ps.total_elapsed_time / 1000.0 AS decimal(18,2)) AS total_elapsed_ms,

    ps.total_logical_reads,
    ps.total_logical_writes,
    ps.total_physical_reads
FROM sys.dm_exec_procedure_stats AS ps
WHERE
    ps.database_id = DB_ID()
    AND ps.object_id = OBJECT_ID(
        QUOTENAME(@schema_name) + N'.' + QUOTENAME(@procedure_name)
    );

Si esta consulta no devuelve ninguna fila, no concluyas que el procedimiento no se usa.

Puede que simplemente no tenga en este momento un plan de procedimiento en caché.


8. Encontrar los procedimientos que faltan actualmente en las estadísticas de procedimientos

Esta consulta compara los procedimientos de usuario con la DMV de estadísticas basada en la caché.

SELECT
    s.name AS schema_name,
    p.name AS procedure_name,
    p.create_date,
    p.modify_date
FROM sys.procedures AS p
INNER JOIN sys.schemas AS s
    ON s.schema_id = p.schema_id
LEFT JOIN sys.dm_exec_procedure_stats AS ps
    ON ps.database_id = DB_ID()
   AND ps.object_id = p.object_id
WHERE
    p.is_ms_shipped = 0
    AND ps.object_id IS NULL
ORDER BY
    s.name,
    p.name;

Interpreta esto correctamente:

Estos procedimientos no tienen actualmente una fila en sys.dm_exec_procedure_stats.

No significa:

Estos procedimientos nunca se han ejecutado.


9. Comprobar el tiempo de actividad de SQL Server / la ventana de observación

SELECT
    sqlserver_start_time
FROM sys.dm_os_sys_info;

Las estadísticas de procedimientos están ligadas a los planes en caché.

Si la instancia se reinició recientemente, tu ventana de visibilidad puede ser corta.


10. Comprobar cuándo entraron en la caché las estadísticas del procedimiento

SELECT
    OBJECT_SCHEMA_NAME(ps.object_id, ps.database_id) AS schema_name,
    OBJECT_NAME(ps.object_id, ps.database_id) AS procedure_name,
    ps.cached_time,
    ps.last_execution_time,
    ps.execution_count
FROM sys.dm_exec_procedure_stats AS ps
WHERE
    ps.database_id = DB_ID()
ORDER BY
    ps.cached_time DESC;

cached_time es importante porque los contadores se aplican al ciclo de vida actual del plan y las estadísticas del procedimiento en caché.


11. Usar Query Store para obtener evidencia histórica duradera

Si Query Store está habilitado, suele ser una mejor fuente que las estadísticas de la caché de procedimientos para el historial de ejecución a largo plazo.

Empieza por localizar las consultas asociadas al contexto de objeto del procedimiento y a su historial de ejecución.

El análisis exacto en Query Store depende de la estructura de tus procedimientos y consultas, pero el principio general es:

  • estadísticas de procedimientos = vista rápida y reciente de la caché
  • Query Store = evidencia histórica persistida de las consultas y su ejecución

Usa ambas fuentes para decidir si un procedimiento realmente no se usa o es costoso.

Qué significa el resultado

Campo Por qué importa
execution_count Número de ejecuciones representadas por las estadísticas actuales en caché.
cached_time Momento en que las estadísticas o el plan del procedimiento entraron en la caché. Define la ventana de observación actual de esa fila.
last_execution_time Ejecución más reciente representada por la fila de estadísticas actual.
total_worker_time Tiempo total de CPU acumulado por las ejecuciones de la fila de estadísticas actual.
total_elapsed_time Tiempo transcurrido total acumulado.
total_logical_reads Total de lecturas lógicas acumuladas.
total_logical_writes Total de escrituras lógicas acumuladas.
total_physical_reads Total de lecturas físicas acumuladas.
avg_cpu_ms CPU por ejecución, calculado. Útil solo junto con el número de ejecuciones y el contexto.
avg_elapsed_ms Duración promedio calculada. Puede ocultar valores atípicos.

Qué revisar

Número de ejecuciones alto + costo promedio moderado

Aun así, puede ser un factor importante de la carga de trabajo.

Pequeñas ineficiencias multiplicadas por un volumen de llamadas muy alto pueden consumir una cantidad considerable de CPU y E/S.

Número de ejecuciones bajo + duración promedio muy alta

Posibles interpretaciones:

  • procedimiento de reporting o de procesamiento batch
  • carga de mantenimiento poco frecuente
  • procedimiento lento realmente problemático
  • problema puntual asociado a valores de parámetros concretos

No priorices solo por el promedio.

Tiempo transcurrido alto + CPU bajo

Investiga:

  • los bloqueos
  • las esperas de E/S
  • el consumo de red / de resultados
  • las esperas de memoria
  • la contención de recursos externos

CPU alto + lecturas lógicas altas

Probablemente merece un análisis más profundo del plan de ejecución.

Busca:

  • scans grandes
  • la estrategia de join
  • problemas de cardinalidad
  • instrucciones costosas repetidas
  • oportunidades de indexación

cached_time es reciente

Ventana de observación débil.

El procedimiento puede tener años de uso en producción, pero solo minutos de estadísticas en la caché actual.

No hay fila en sys.dm_exec_procedure_stats

Las posibles razones incluyen:

  • el procedimiento no se ha ejecutado desde los últimos eventos de caché o de reinicio de estadísticas
  • el plan fue expulsado
  • el procedimiento se recompiló
  • el servidor se reinició
  • cambió el contexto de base de datos
  • presión sobre la caché
  • cambios de metadatos o de esquema

No lo etiquetes como no usado solo por esto.

Notas de producción

Las estadísticas de procedimientos se basan en la caché

sys.dm_exec_procedure_stats no es telemetría histórica duradera.

Las filas pueden desaparecer cuando los planes se eliminan de la caché.

Esto significa que la DMV es excelente para:

  • el uso reciente
  • la clasificación reciente por CPU o lecturas
  • el triaje inmediato

pero débil para:

  • «¿Se ha usado en los últimos 6 meses?»
  • decisiones de retirada (deprecación)
  • informes de cumplimiento o de historial

El ciclo de vida de la caché de planes importa

Los eventos que pueden cambiar la ventana de observación incluyen:

  • reinicio
  • recompilación
  • expulsión de planes
  • cambios de esquema
  • presión de memoria
  • operaciones de vaciado de la caché

Inspecciona siempre cached_time.

Los promedios pueden ocultar sesgos

Un procedimiento puede ser:

  • rápido en el 99 % de las llamadas
  • muy lento en el 1 % de las llamadas

La duración promedio puede ocultar ese patrón.

Usa Query Store o telemetría detallada cuando la distribución importe.

Eliminar un procedimiento requiere algo más que estadísticas de uso

Antes de eliminar un procedimiento, comprueba:

  • las referencias en el código de la aplicación
  • los jobs de SQL Agent
  • los paquetes ETL
  • los sistemas de reporting
  • las integraciones externas
  • los scripts de despliegue
  • la documentación
  • Query Store / el historial
  • el control de versiones

Un procedimiento puede usarse rara vez y aun así ser crítico para la operación.

Los permisos varían según la versión

Una visibilidad amplia de las DMV suele requerir el permiso adecuado de rendimiento o de estado del servidor.

Para SQL Server 2022 y versiones posteriores, Microsoft usa VIEW SERVER PERFORMANCE STATE para muchas DMV de rendimiento.

Las versiones anteriores suelen usar VIEW SERVER STATE.

Errores comunes

1. Tratar la ausencia de filas en la DMV como prueba de que no se usa

Este es el error más grave.

Que no haya fila significa que no hay una fila de estadísticas actual en caché.

No significa que el procedimiento nunca se haya ejecutado.

2. Ignorar cached_time

Los números de ejecuciones sin una ventana de observación son engañosos.

10 ejecuciones en 5 minutos y 10 ejecuciones en 6 meses significan cosas muy distintas.

3. Clasificar solo por duración promedio

Los promedios pueden sobrepriorizar procedimientos puntuales e infrapriorizar la carga de trabajo que se ejecuta con frecuencia.

Fíjate en:

  • el uso total de recursos
  • el uso promedio de recursos
  • el número de ejecuciones
  • la importancia para el negocio

4. Eliminar un procedimiento almacenado porque «no se ha ejecutado»

Antes de eliminarlo, confirma las referencias fuera de las estadísticas de ejecución de SQL Server.

Los procedimientos operativos poco frecuentes pueden ser críticos durante incidentes, cierres de mes, cierres de año o flujos de recuperación.

5. Suponer que las estadísticas de procedimiento equivalen a las estadísticas de instrucción

Un procedimiento almacenado puede contener varias instrucciones con comportamientos muy distintos.

Usa Query Store, los planes de ejecución o las DMV a nivel de instrucción cuando necesites saber qué instrucción es costosa.

6. Comparar estadísticas entre distintos ciclos de vida de la caché

Si un procedimiento se recompiló o fue expulsado, los contadores pueden reiniciarse.

No compares los contadores actuales con instantáneas anteriores a menos que tengas en cuenta los reinicios.

Siguiente paso

Usa el patrón de uso para decidir qué investigar a continuación.

  • Número de ejecuciones alto + CPU total alto → inspecciona las instrucciones costosas del procedimiento y sus planes de ejecución.
  • Muchas lecturas lógicas → inspecciona scans, predicados, joins e indexación.
  • Tiempo transcurrido alto + CPU bajo → inspecciona los bloqueos y los tipos de espera.
  • No hay fila de estadísticas actual → revisa Query Store, las referencias en el código fuente, los jobs, el código de la aplicación y el monitoreo antes de considerarlo no usado.
  • cached_time reciente → recopila datos durante una ventana de observación más larga.
  • El procedimiento es candidato a eliminarse → verifica las dependencias fuera de la DMV antes de retirarlo.
  • El procedimiento combina ejecuciones rápidas y lentas → usa Query Store para un análisis histórico de planes y de ejecución.

← Todas las guías rápidas