Cuando los planes de mantenimiento también necesitan mantenimiento

¿Utilizas los planes de mantenimiento de SQL Server? Si es así, déjame hacerte una pregunta más: ¿realizas algún tipo de limpieza o mantenimiento sobre su tabla de logs?

Si tu respuesta es no (o no lo sabes), no te preocupes: no eres el único. Yo mismo descubrí este detalle casi por casualidad… y las consecuencias. En este artículo te cuento qué ocurrió, por qué sucede y cómo lo solucioné.

El problema: picos periódicos de CPU

Hace unas semanas, mientras revisaba el estado general del sistema, empecé a notar picos de CPU que se repetían de forma periódica. No era algo puntual: el patrón se repetía desde hacía varios días.

Al profundizar en el análisis, observé que estos picos coincidían con lecturas intensivas sobre la tabla: msbd.dbo.sysmaintplan_logdetail

Esto ya llamó mi atención, ya que se trata de una tabla asociada a los planes de mantenimiento de SQL Server.

Investigando la causa

Buscando información, encontré un artículo muy esclarecedor de Steve Stedman donde se analizaba precisamente esta tabla y su crecimiento descontrolado https://stevestedman.com/2016/03/sql-server-sysmaintplan_logdetail

Tras ejecutar algunas de las consultas recomendadas…

…la situación quedó bastante clara:

• Total de registros: 4.325.471
• Registros con más de un año de antigüedad: 2.418.255
• Registros del último año: 1.907.510

Es decir, más de la mitad de la tabla contenía información antigua que ya no aportaba valor.

¿Por qué afecta al rendimiento?

Para entender los picos de CPU, analicé el comportamiento de una consulta ad hoc que accedía a esta tabla.

El resultado fue revelador: en determinados momentos, el consumo de CPU se disparaba de forma abrupta. El plan de ejecución antes y después no variaba, lo que indicaba que el problema no era el plan en sí, sino el volumen de datos que debía procesar.

Voy a intentar resumir qué es lo que está pasando para luego poder hablar de cómo lo voy a solucionar.

¿Qué es dbo.sysmaintplan_logdetail?

Es la tabla donde SQL Server registra todas las ejecuciones de los planes de mantenimiento (los que se configuran donde indica la imagen de abajo). Cada vez que se ejecuta un plan, su resultado queda almacenado aquí.

Si utilizas los planes de mantenimiento que ofrece SQL Server de forma nativa, el motor siempre registra información en esta tabla. No existe ninguna opción para desactivar este comportamiento.

Las opciones, por tanto, son básicamente dos:

1. Limpiar periódicamente esta tabla.
2. Crear planes de mantenimiento personalizados, sin utilizar los asistentes de SQL Server.

Ambas alternativas son válidas, pero en mi caso opté por la primera.

Un detalle curioso (y peligroso)

Esta tabla, al tratarse de un log, está definida como un HEAP (sin índice clustered).

Esto tiene sentido desde el punto de vista de escritura: las inserciones son rápidas. El problema aparece cuando el sistema necesita leerla, especialmente cuando el volumen de datos es elevado. Ahí es donde comienzan los escaneos costosos y, con ellos, los picos de CPU.

La solución: limpieza periódica

La solución fue sencilla en concepto, pero muy efectiva en resultados:

• Limpiar la tabla de registros antiguos.
• Programar un Job que realice esta limpieza de forma periódica.

Antes de implementar la solución, analizamos cuánto tiempo tenía realmente valor conservar esta información. Finalmente, decidimos mantener un histórico de 6 semanas, más que suficiente para nuestras necesidades.

El código utilizado para el borrado fue el siguiente:

declare @f datetime = '20241118'

WHILE @f < dateadd(WEEK,-6,GETUTCDATE() )
BEGIN
	print @f
	EXEC msdb.dbo.sp_maintplan_delete_log @oldest_time=@f

	set @f = DATEADD(day,1,@f)
END

Este proceso se programó como un Job para que se ejecute de manera automática.

El resultado

Tras aplicar estos cambios, los picos de CPU desaparecieron por completo. El sistema volvió a comportarse de forma estable y, además, se redujo notablemente el tamaño de la base de datos msdb.

Conclusión

Aunque muchas veces lo pasamos por alto, los propios planes de mantenimiento de SQL Server también necesitan mantenimiento. No gestionar adecuadamente el crecimiento de la tabla sysmaintplan_logdetail puede derivar en problemas de rendimiento difíciles de identificar.

Mi recomendación es clara: define una política de limpieza acorde a tus necesidades y automatízala. Te evitará más de una sorpresa desagradable 😊