Explorar guías rápidas · SQL Server
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
- Problema
- Respuesta rápida
- Código / comandos
- 1. Encontrar los procedimientos ejecutados recientemente
- 2. Encontrar los procedimientos ejecutados con más frecuencia
- 3. Encontrar los procedimientos que más CPU total consumen
- 4. Encontrar los procedimientos con el CPU promedio más alto
- 5. Encontrar los procedimientos que hacen más lecturas lógicas
- 6. Encontrar los procedimientos con el tiempo transcurrido promedio más alto
- 7. Comprobar un procedimiento almacenado concreto
- 8. Encontrar los procedimientos que faltan actualmente en las estadísticas de procedimientos
- 9. Comprobar el tiempo de actividad de SQL Server / la ventana de observación
- 10. Comprobar cuándo entraron en la caché las estadísticas del procedimiento
- 11. Usar Query Store para obtener evidencia histórica duradera
- Qué significa el resultado
- Qué revisar
- Número de ejecuciones alto + costo promedio moderado
- Número de ejecuciones bajo + duración promedio muy alta
- Tiempo transcurrido alto + CPU bajo
- CPU alto + lecturas lógicas altas
- cached_time es reciente
- No hay fila en sys.dm_exec_procedure_stats
- Notas de producción
- Las estadísticas de procedimientos se basan en la caché
- El ciclo de vida de la caché de planes importa
- Los promedios pueden ocultar sesgos
- Eliminar un procedimiento requiere algo más que estadísticas de uso
- Los permisos varían según la versión
- Errores comunes
- 1. Tratar la ausencia de filas en la DMV como prueba de que no se usa
- 2. Ignorar cached_time
- 3. Clasificar solo por duración promedio
- 4. Eliminar un procedimiento almacenado porque «no se ha ejecutado»
- 5. Suponer que las estadísticas de procedimiento equivalen a las estadísticas de instrucción
- 6. Comparar estadísticas entre distintos ciclos de vida de la caché
- Siguiente paso
- 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.
¿Te resultó útil este contenido?
¿Qué podríamos mejorar?
Gracias — esto me ayuda a mejorar Javi Builds.
Mantente al día
Recibe solo las novedades de Javi Builds que realmente te interesan.
Revisa tu bandeja de entrada
Te he enviado un enlace de confirmación. Tu suscripción se activará cuando la confirmes.