Ajuste automático en SQL Server: FORCE_LAST_GOOD_PLAN en la práctica

En el mantenimiento de almacenes de datos para Business Intelligence, uno de los incidentes más frustrantes es la degradación súbita del rendimiento. Una consulta que ayer tardaba dos segundos en alimentar un informe de Power BI, hoy tarda dos minutos sin que haya cambiado el volumen de datos ni el código T-SQL. Este fenómeno se conoce como regresión del plan de consulta (Query Plan Regression) y suele estar provocado por cambios en las estadísticas, recompilaciones o el temido parameter sniffing.

SQL Server introdujo el Ajuste Automático (Automatic Tuning) para mitigar este riesgo. Su función estrella, FORCE_LAST_GOOD_PLAN, no es una fórmula mágica que optimiza código mal escrito, sino un mecanismo de seguridad que garantiza la estabilidad. Como consultores, nuestra prioridad no es solo que el sistema sea rápido, sino que sea predecible. En este artículo analizamos cómo implementar esta característica, qué requisitos exige y por qué el modo observación es tu mejor aliado antes de automatizar por completo.

Requisitos técnicos: El papel crítico de Query Store

Para que SQL Server pueda aplicar el ajuste automático, es obligatorio tener activo Query Store. Sin esta característica, el motor de base de datos no tiene memoria; no puede comparar el rendimiento actual con el pasado porque no guarda un histórico de planes ni de estadísticas de ejecución.

Query Store actúa como una grabadora de datos de vuelo. Registra cada consulta, sus diferentes planes de ejecución y los recursos consumidos (CPU, memoria, E/O). Para habilitar el ajuste automático, primero debemos asegurarnos de que Query Store esté en modo de lectura y escritura y que el ajuste automático esté configurado a nivel de base de datos. Es vital entender que esta característica funciona a nivel de base de datos individual, no a nivel de instancia.

-- Paso 1: Habilitar Query Store si no lo está
ALTER DATABASE [Ventas_DWH] SET QUERY_STORE = ON;
GO

-- Paso 2: Configurar el ajuste automático en modo observación
ALTER DATABASE [Ventas_DWH] SET AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = ON );
GO

Es importante recalcar que en entornos de Fragmentación en Microsoft Fabric Warehouse o bases de datos tradicionales, la estabilidad de los metadatos es lo que permite que estas herramientas tomen decisiones coherentes. Si la base de datos está bajo una presión extrema de recursos, el propio proceso de captura de Query Store podría pausarse, invalidando temporalmente el ajuste automático.

¿Cómo detecta SQL Server una regresión de plan?

El motor de SQL Server no fuerza un plan antiguo por cualquier pequeña variación. Utiliza un algoritmo interno que evalúa la ganancia estimada frente al riesgo. El proceso sigue estos pasos:

• Monitorización: El motor analiza las estadísticas de ejecución de las consultas almacenadas en Query Store.
• Detección de regresión: Se identifica una consulta cuyo plan actual es significativamente peor (normalmente en términos de tiempo de CPU o duración) que un plan anterior.
• Criterio de umbral: La regresión debe ser superior a un umbral de error predefinido. SQL Server busca una mejora de rendimiento de al menos 10 unidades de medida (CPU o duración) y que el nuevo plan sea al menos un 20% peor que el anterior.
• Acción: Si el ajuste automático está activo, el motor fuerza el «último plan bueno» conocido de forma temporal.

Este mecanismo es especialmente útil cuando trabajamos con Seleccionar sobre resultados de consulta: CTEs y Subconsultas en el mundo real, donde la complejidad de la consulta puede confundir al optimizador tras una actualización de estadísticas. El sistema monitoriza el plan forzado y, si detecta que no mejora el rendimiento, lo deshace automáticamente para evitar un bloqueo en un plan subóptimo.

Auditoría de decisiones: Consultando sys.dm_db_tuning_recommendations

Como expertos en datos, no debemos confiar ciegamente en procesos automatizados sin supervisión. SQL Server expone todas las decisiones de ajuste a través de la vista de sistema sys.dm_db_tuning_recommendations. Esta vista es fundamental para entender por qué se ha forzado un plan o qué sugerencias tiene el motor pendientes de aplicar.

El contenido de esta vista es un JSON que detalla el ID de la consulta, el ID del plan regresado, el ID del plan recomendado y la ganancia estimada de CPU. Es recomendable integrar esta consulta en nuestros cuadros de mando de salud de la base de datos.

-- Consultar recomendaciones de ajuste activo o pendiente
SELECT 
 name,
 type,
 reason,
 state,
 JSON_VALUE(details, '$.estimatedGain') AS GananciaEstimada,
 JSON_VALUE(details, '$.implementationDetails.script') AS ScriptSugerido
FROM sys.dm_db_tuning_recommendations
WHERE state != 'Ignored'; -- Filtrar las que no hemos descartado manualmente

Esta capacidad de auditoría es similar a la que buscamos al realizar una Seguridad en SQL Server: Objetos Temporales, Metadatos y Auditoría Crítica, donde la trazabilidad de los cambios en el motor es lo que nos permite responder ante una caída de rendimiento frente al cliente.

Modo observación vs. Aplicación automática

Una de las decisiones de diseño más importantes es si dejar que SQL Server actúe solo o simplemente nos avise. En proyectos de retail o energía con cargas de trabajo críticas, mi recomendación es empezar siempre en modo observación. Esto nos permite validar si las recomendaciones del motor tienen sentido o si la regresión es un falso positivo causado por una carga de datos excepcional (por ejemplo, un proceso de carga masiva nocturno).

Si optamos por el modo automático, debemos complementar la estrategia con otras funciones de optimización adaptativa como el DOP Feedback en SQL Server 2022. Juntas, estas características crean un entorno de base de datos auto-reparable que reduce drásticamente los tickets de soporte por «informes lentos».

Errores frecuentes al implementar Automatic Tuning

A pesar de su potencia, hay errores de concepto que pueden llevarnos a resultados mediocres:

• No configurar correctamente el tamaño de Query Store: Si el espacio asignado a Query Store es muy pequeño, el motor borrará datos históricos con frecuencia. Sin historial, no hay comparación posible y FORCE_LAST_GOOD_PLAN nunca se activará.
• Ignorar el impacto del plan forzado: Forzar un plan soluciona el síntoma, pero no la causa. Si una consulta regresa constantemente, quizás el problema sea una falta de índices o un diseño de tabla que no sigue el modelo Kimball.
• Confundir ajuste automático con optimización de índices: El ajuste automático de planes no crea índices. Existe otra característica en Azure SQL llamada Automatic Index Management, pero en SQL Server on-premise, FORCE_LAST_GOOD_PLAN solo gestiona la elección del plan de ejecución.
• Dependencia excesiva en parches: Usar esta característica para compensar consultas de Power Query ineficientes. A veces, el problema nace en la transformación de datos inicial y no en el motor SQL.

Checklist final para la implementación

1. Verificar versión: Asegurar que estamos en SQL Server 2017 o superior (o Azure SQL).
2. Activar Query Store: Configurar el modo READ_WRITE y un tamaño de almacenamiento adecuado (mínimo 1GB para DWH medianos).
3. Habilitar monitorización: Activar FORCE_LAST_GOOD_PLAN y monitorizar sys.dm_db_tuning_recommendations durante al menos dos semanas.
4. Establecer alertas: Crear alertas de SQL Agent que nos notifiquen cuando el estado de una recomendación pase a ‘Success’.
5. Revisión periódica: No olvidar las recomendaciones; si un plan se fuerza constantemente, es hora de refactorizar la consulta.

Preguntas frecuentes

¿Qué pasa si el último plan bueno ya no es válido?

SQL Server monitoriza el rendimiento del plan forzado. Si detecta que el rendimiento no mejora o que el plan ya no es eficiente debido a cambios estructurales (como borrar un índice), el motor deja de forzarlo y vuelve al proceso de optimización normal.

¿Afecta el ajuste automático al rendimiento general del servidor?

El impacto es despreciable. La lógica de comparación se ejecuta en segundo plano utilizando los datos que Query Store ya está recolectando. Es mucho más costoso para el servidor ejecutar una consulta con un plan regresado (con escaneos de tabla innecesarios) que realizar la comprobación de ajuste.

¿Puedo forzar planes manualmente si el ajuste automático no lo hace?

Sí, a través de la interfaz de Query Store en SQL Server Management Studio (SSMS). Puedes ver las consultas con mayor variación y pulsar el botón «Force Plan». El ajuste automático es simplemente la automatización de esta tarea manual basada en umbrales de confianza.

¿Funciona con consultas parametrizadas?

Es precisamente donde mejor funciona. El ajuste automático es la solución más robusta contra el parameter sniffing, ya que es capaz de detectar cuándo un plan generado para un valor específico está perjudicando al resto de ejecuciones y volver a una versión más estable.