Optimización SQL para BI: Lecciones de Hugo Kornelis sobre planes de ejecución

En el ámbito del Business Intelligence, solemos obsesionarnos con las medidas DAX o la elegancia de un modelo en estrella. Sin embargo, la realidad de los proyectos de gran escala en sectores como el retail o la industria es que el rendimiento nace en el origen. Si la consulta SQL que alimenta tu partición de Power BI es ineficiente, ninguna cantidad de memoria en el servicio de Fabric podrá compensar esa latencia. Aquí es donde la figura de Hugo Kornelis y su exhaustivo trabajo sobre los planes de ejecución de SQL Server cobra una relevancia crítica para cualquier arquitecto de datos.

Entender un plan de ejecución no es una tarea exclusiva de los administradores de bases de datos (DBA). Para un consultor de BI, es la diferencia entre un refresco de datos que dura diez minutos y uno que falla tras dos horas de ejecución. Hugo Kornelis ha dedicado años a documentar cada rincón de estos planes, y su enfoque nos enseña que el motor de SQL no es una caja negra, sino un sistema lógico que nos dice exactamente dónde estamos fallando al pedir los datos.

En este artículo, analizamos qué implica para un entorno de producción actual el dominio de los planes de ejecución, qué elementos debemos revisar en nuestros modelos y cómo las enseñanzas de Kornelis pueden transformar nuestra forma de construir soluciones de datos escalables.

La importancia de la ‘Execution Plan Reference’ en proyectos BI

La documentación oficial de Microsoft sobre operadores de planes de ejecución es, en el mejor de los casos, esquemática. Hugo Kornelis ha llenado ese vacío con su Execution Plan Reference, un recurso que detalla el comportamiento interno de cada operador. Para quienes trabajamos con Power BI y SQL Server, esto es oro puro. ¿Por qué un Hash Match está consumiendo toda la memoria de nuestro servidor? ¿Por qué el motor decide usar un Nested Loop cuando esperamos un Merge Join?

Cuando diseñas un modelo de datos, la fase de extracción es el primer cuello de botella. Al enfrentarnos a tablas de hechos con cientos de millones de filas en sectores como el público o el industrial, no podemos permitirnos el lujo de ignorar cómo el motor de SQL lee esos datos. Si quieres profundizar en los fundamentos antes de avanzar, te recomiendo revisar nuestro artículo sobre Leer planes de ejecución en SQL Server: los 5 operadores que debes dominar.

Diferencia entre estimación y realidad: El primer paso del diagnóstico

Uno de los puntos clave que Kornelis enfatiza es la discrepancia entre el plan de ejecución estimado y el real. En BI, esto suele ocurrir por estadísticas desactualizadas o por el uso de parámetros que el optimizador no puede prever adecuadamente. Cuando lanzamos una consulta desde Power Query, SQL Server genera un plan basado en lo que cree que va a pasar.

Si el plan estimado dice que se procesarán 100 filas, pero en realidad se procesan 10 millones, el motor elegirá operadores ineficientes para ese volumen. Esto es especialmente sangrante en modelos con DirectQuery, donde la latencia visual del informe depende directamente de esta ejecución. En el siguiente ejemplo, vemos una consulta típica que podría fallar por una mala interpretación del optimizador al usar filtros complejos.

-- Consulta de ejemplo para extraer ventas por categoría y fecha
-- El uso de funciones en el WHERE suele impedir el uso de índices
SELECT 
 p.NombreProducto,
 SUM(v.Importe) AS TotalVentas
FROM FactVentas v
JOIN DimProducto p ON v.ProductoKey = p.ProductoKey
WHERE ISNULL(v.Estado, 'A') = 'A' -- Error común: ISNULL rompe la SARGability
GROUP BY p.NombreProducto;

En el caso anterior, el plan de ejecución nos mostraría un Index Scan en lugar de un Index Seek. Hugo Kornelis nos enseñaría que el operador Compute Scalar derivado del ISNULL está obligando al motor a evaluar cada fila, eliminando cualquier beneficio de nuestros índices. La solución pasa por reescribir la lógica para que sea amigable con el optimizador.

Operadores que un consultor BI debe vigilar

No todos los operadores tienen el mismo impacto. En entornos de Business Intelligence, donde realizamos grandes agregaciones y uniones de tablas, hay tres sospechosos habituales que debemos monitorizar constantemente en SQL Server Management Studio (SSMS) o Azure Data Studio.

Un error clásico que vemos en proyectos de media empresa es el uso indiscriminado del operador OR en las cláusulas de filtrado, lo que suele arruinar la ejecución paralela y forzar lecturas secuenciales. Para profundizar en cómo evitar esto, consulta nuestro análisis sobre OR en el WHERE: por qué penaliza y cómo usar UNION ALL para ganar rendimiento.

DirectQuery y el impacto de los planes de ejecución

En escenarios de DirectQuery, cada interacción del usuario en Power BI dispara una o varias consultas SQL. Aquí, el trabajo de Kornelis sobre la reutilización de planes es vital. SQL Server intenta cachear los planes, pero si nuestras consultas no están parametrizadas correctamente (algo común cuando Power Query genera SQL dinámico), llenaremos la Plan Cache con basura, obligando a recompilaciones constantes.

Esto se agrava si el modelo DAX es ineficiente. Una medida mal escrita puede generar un SQL monstruoso que el optimizador de SQL Server simplemente no sabe cómo gestionar en un tiempo razonable. Si trabajas con esta arquitectura, es obligatorio conocer las 50 Medidas y Patrones DAX que Destruyen el Rendimiento en DirectQuery.

Ajuste proactivo y automatización

A partir de SQL Server 2017 y en Azure SQL, contamos con herramientas de ajuste automático. Sin embargo, estas herramientas se basan en los mismos principios que explica Hugo Kornelis: identificar regresiones en los planes de ejecución. Cuando el motor detecta que un nuevo plan es peor que el anterior, puede forzar el último plan conocido que funcionó bien.

Como consultores, debemos saber cuándo confiar en el motor y cuándo intervenir manualmente. El Query Store es nuestro mejor aliado aquí, permitiéndonos ver la evolución del rendimiento de nuestras consultas de extracción a lo largo del tiempo. Si quieres ver cómo aplicar esto en un entorno real, lee sobre el Ajuste automático en SQL Server: FORCE_LAST_GOOD_PLAN en la práctica.

-- Forzar un plan específico desde el Query Store si detectamos regresión
-- Este es un último recurso cuando el optimizador falla sistemáticamente
EXEC sp_query_store_force_plan 
 @query_id = 452, 
 @plan_id = 12;
-- Siempre documentar por qué se toma esta decisión técnica

Decisiones de diseño: ¿Cuándo mirar el plan?

Mi recomendación como consultor es clara: no esperes a que el informe vaya lento. En la fase de desarrollo de tu ETL, ya sea con Power Query o procedimientos almacenados, debes examinar el plan de ejecución de tus consultas principales. Si ves un Spill to tempdb (un aviso amarillo en el operador), significa que SQL Server se ha quedado sin memoria RAM para esa operación y está escribiendo en disco. En un entorno de BI, esto es una señal inequívoca de que tu servidor sufrirá cuando el volumen de datos crezca.

Además, en procesos de integración de datos, la forma en que combinamos tablas es crítica. A menudo, la decisión entre unir y anexar consultas en Power Query tiene un reflejo directo en el plan de ejecución que se envía a la base de datos (Query Folding). Si el plegado de consultas se rompe, SQL Server enviará todos los datos y Power BI tendrá que procesarlos en memoria, lo cual es el escenario menos eficiente posible.

Checklist de revisión para planes de ejecución en BI

• Verificar alertas: Busca iconos de advertencia amarillos que indiquen conversiones implícitas o falta de memoria (Spills).
• Comparar filas estimadas vs reales: Si la diferencia es de órdenes de magnitud, actualiza las estadísticas de las tablas.
• Identificar operadores de alto coste: Prioriza la optimización de Sorts y Hash Matches que duren más del 20% del tiempo total.
• Validar el Query Folding: Asegúrate de que los filtros de fecha y categoría se están ejecutando en el servidor SQL y no en la máquina local.
• Revisar índices: Si el plan sugiere un índice faltante, analízalo, pero no lo crees sin considerar el impacto en las cargas de datos (INSERT/UPDATE).

Preguntas frecuentes

¿Es necesario ser un experto en SQL para optimizar Power BI?

No es necesario ser un DBA senior, pero sí entender cómo se recuperan los datos. Conocer los 4 o 5 operadores principales de un plan de ejecución te permite identificar cuellos de botella que el Performance Analyzer de Power BI no puede ver.

¿Qué herramientas recomienda Hugo Kornelis para ver planes?

Principalmente SQL Server Management Studio (SSMS), pero su enfoque es analítico. Lo importante no es la herramienta, sino saber leer las propiedades del XML del plan de ejecución, donde reside la información detallada sobre el uso de memoria y CPU.

¿Cómo afecta el nivel de compatibilidad de la base de datos?

Es vital. Muchas de las mejoras en el optimizador que permiten planes de ejecución más inteligentes solo están disponibles en niveles de compatibilidad altos (150 o 160). Si tu base de datos está en un nivel antiguo, el motor usará reglas de optimización obsoletas.

Conclusión

La maestría de Hugo Kornelis en los planes de ejecución nos recuerda que el rendimiento no es fruto del azar, sino de una ejecución lógica y predecible. Para los profesionales del Business Intelligence, bajar al nivel del motor SQL no es un retroceso, sino una ventaja competitiva. Un modelo de datos bien diseñado sobre una base SQL optimizada es la única forma de garantizar que nuestras soluciones de Fabric o Power BI escalen correctamente en entornos empresariales complejos.