Cómo comparar el optimizer entre versiones de SQL Server con rigor (y no fiarte de nadie)

Serie: Migraciones de SQL Server con criterio · Artículo 2 de 6

Cómo comparar el optimizer entre versiones de SQL Server con rigor

“Vale, ¿y cómo lo mido en MI caso antes de romper producción?”

Esa frase la escucho en cada proyecto de migración, casi con las mismas palabras. En el artículo anterior vimos que migrar de versión cambia los planes de ejecución, y que con datos reales el cambio es grande. Y la reacción natural de quien tiene que firmar la migración no es teórica: es esa.

Ahí está la habilidad que separa a un consultor de alguien que repite recomendaciones genéricas: tener un método reproducible para medir el impacto sobre tus datos y tus consultas. En vez de fiarte del benchmark que publicó un fabricante (con sus datos, su hardware y su interés comercial). Este artículo es ese método. Está pensado para que puedas reproducirlo, no para que confíes en nuestros números.

Los cuatro principios (por qué la mayoría de “comparativas” no valen)

Comparar el rendimiento de dos versiones de SQL Server parece trivial (“ejecuto la query en las dos y cronometro”) pero así se llega a conclusiones falsas. Cuatro principios marcan la diferencia.

1. Mismo binario, misma “forma”, recursos idénticos

Si comparas una instancia de producción con 128 GB contra un entorno de pruebas con 8 GB, estás midiendo el hardware, no el optimizer. Para aislar la variable “versión”, todo lo demás tiene que ser idéntico: mismo número de CPU, misma memoria, misma configuración. En nuestra comparativa, los dos SQL Server on-premises corren con 4 CPU y 4 GB y la misma memoria efectiva, y Azure SQL Database (el motor PaaS de Azure SQL - (RTM) - 12.0.2000.8) con una asignación de recursos razonablemente equivalente. Cualquier diferencia que quede es del optimizer, no de la máquina.

Detalle técnico que se suele pasar por alto: SQL Server, por defecto, reserva toda la memoria que puede. En un entorno con recursos acotados hay que fijar explícitamente max server memory por debajo del límite disponible, o el motor se muere por falta de memoria (OOM) al cargar datos grandes. Y si comparas dos motores con memoria distinta, los memory grant y los spill a disco no son comparables. La memoria idéntica no es un detalle: es un requisito.

2. Planes REALES, no estimados

SQL Server te da dos cosas: el plan estimado (lo que el optimizer cree que va a pasar) y el plan real (lo que pasó de verdad, con las filas reales por operador). Comparar planes estimados es cómodo pero engañoso: dos motores pueden mostrar el mismo plan estimado y comportarse distinto en ejecución. Nosotros capturamos el plan real de cada consulta (con SET STATISTICS XML ON), varias ejecuciones, descartando la primera (warm-up). Es más caro de capturar, pero es la única forma de ver qué pasa de verdad.

Cada plan real capturado se guarda como .sqlplan, el formato nativo que abre SSMS o Azure Data Studio con un doble clic, sin conversión. Así cualquiera puede auditar el árbol de operadores exacto que eligió cada motor. La query q21 de TPC-H entre SQL Server 2019 y 2025, por ejemplo, tiene el plan real de cada versión guardado por separado, listos para compararse lado a lado. Esa es la unidad de evidencia de toda la serie: no una cifra agregada, sino el plan que puedes abrir y comprobar.

Los planes reales de q21 en SQL Server 2019 y 2025 comparados operador a operador, con el Q-error y el grant de memoria de cada uno

Los dos planes reales de q21 (SQL 2019 vs 2025) lado a lado, distancia de árbol 13 sobre ~28 operadores. El borde azul marca dónde diverge la forma: 2025 encadena tres Adaptive Join (nodos 5, 7 y 9) donde 2019 usa dos y cierra con un Hash Match; el fondo rojo, dónde se dispara el Q-error de un operador. Y arriba se lee lo que arrastra cada plan en memoria: 2019 reserva 640 MB para usar 42 (×15), 2025 baja a 187 MB para usar 34 (×6). La versión nueva reserva mejor. Nada de esto lo delata el coste estimado: no es una unidad comparable entre motores.

3. La métrica correcta: Q-error, no “coste”

El coste estimado que muestra SQL Server (ese número tipo 3.4521) es una unidad relativa interna de cada versión del optimizer. Comparar "coste 8 en un motor vs coste 4 en otro" no significa nada: es como comparar precios en dos monedas distintas sin tipo de cambio. Es uno de los errores más comunes q veo en comparativas amateur.

La métrica correcta para la calidad de estimación es el Q-error (Moerkotte et al., VLDB 2009):

Q-error de un operador = max(estimado/real, real/estimado)

Un Q-error de 1.0 significa estimación perfecta (el optimizer acertó las filas). Un Q-error de 100 significa que se equivocó por un factor de 100 (estimó 100× de más o de menos). Se reporta la mediana, p90 y p95 sobre todos los operadores de todos los planes. Es la métrica estándar en la literatura académica precisamente porque es comparable entre motores: no depende de unidades internas, solo de "cuánto te equivocaste al estimar filas".

4. Datos que estresen el estimador (o no verás nada)

Como vimos en el artículo 1, con datos sintéticos uniformes los motores casi coinciden. Para que la comparación tenga poder de discriminación necesitas datos con correlaciones reales. Nosotros usamos dos datasets a propósito:

• TPC-H (sintético uniforme): como “grupo de control”. Si aquí divergen, es grave.
• JOB / IMDB (real correlacionado): el que de verdad estresa la estimación de cardinalidad.

Para tu propia migración, el equivalente ideal es una copia de tu base de datos de producción, o un subconjunto que preserve sus distribuciones. Un benchmark genérico te dirá menos q tus propios datos.

El pipeline, paso a paso

Así ejecuto la comparación (reproducible con cualquier par o trío de versiones):

1. Levantar los motores con recursos idénticos: mismos límites de CPU/memoria, misma configuración de Query Store en todos.
2. Cargar los mismos datos, de la misma forma, en todos. La carga tiene que ser idéntica byte a byte (misma ruta de importación) o introduces diferencias que no son del optimizer.
3. Capturar los planes reales: por cada consulta, en cada motor, en dos modos (con y sin paralelismo forzado, para separar “divergencia de plan lógico” de “divergencia por grado de paralelismo”), varias ejecuciones.
4. Parsear los planes (son XML) a tablas: una fila por operador, con filas estimadas, filas reales y Q-error calculado. Aquí hay un detalle que decide todo el análisis: la divergencia de plan se mide sobre la forma real del árbol que trae el XML (el árbol de operadores exacto), nunca sobre un número derivado del coste. El coste no es comparable entre motores, así que dos planes idénticos con estimaciones distintas te salen como “planes distintos” cuando no lo son. Si comparas planes, compara la forma, no el coste.
5. Analizar: por cada par de motores, cuántas consultas eligen un plan de forma distinta, y con qué Q-error estima cada uno. Esto produce una matriz de divergencia y una curva de Q-error por versión.
6. Sellar la evidencia: cada tanda registra la versión exacta de cada motor (ProductVersion), la memoria efectiva y el compat level. Dos tandas solo son comparables si esos sellos coinciden. Las imágenes se actualizan, y una comparación entre builds distintas no vale.

Clasificación por consulta de un par de motores: plan distinto, mismo plan con riesgo de memoria, o idéntico

Salida del paso 5 para un par (SQL 2019 vs 2025, TPC-H): cada consulta clasificada por su distancia de árbol y, cuando el plan coincide, por lo que arrastra en memoria (grant→uso). q21 sale como plan-distinto con distancia 13; más abajo, varias con el mismo plan pero grant excesivo en 2019 (q20 reserva ×33 lo que usa). No es una cifra agregada: es el desglose por query, cada uno trazable a su .sqlplan.

Por qué importa el “sellado”

Este último punto es el que le da credibilidad a una comparativa de consultor. Las builds de cada motor cambian con el tiempo (parches, actualizaciones, nuevas versiones). Si publicas “SQL 2025 estima X” sin decir la build exacta, tu conclusión no es reproducible ni auditable. Cada resultado de nuestra comparativa lleva su meta.json con la versión sellada. Cuando presentas esto a un cliente, la diferencia entre "según mi experiencia" y "según esta medición reproducible con estas builds exactas, q puedes repetir" es toda la diferencia.

Panel de tandas con su sello: build de cada motor, compat level y validez, junto al Q-error mediano por versión

Cada tanda muestra su sello: la build de cada motor (v12/v15/v17), su compat level (CL 150/170) y si es válida o le falta sello. Una tanda sin sello no entra en las conclusiones. El Q-error mediano de arriba (8.21 / 12.00 / 15.54) solo significa algo porque debajo está la build exacta que lo produjo.

La lección para una migración

No aceptes una comparativa de rendimiento (ni la nuestra, ni la del fabricante) sin saber: qué datos se usaron, si eran sintéticos o reales, si los recursos eran idénticos, si son planes reales o estimados, y qué build exacta se midió. Si falta alguno de esos cinco, la conclusión no es fiable.

Y fíjate en lo que este método NO produce: una nota de “mejor o peor”. Lo que deja al final es lo que has visto en las capturas: la clasificación query a query de cada par de motores (cuáles cambian de forma, cuáles conservan el plan pero arrastran memoria, cuáles son idénticas), el Q-error de cada operador, y detrás de cada cifra un .sqlplan que se abre en SSMS. Es decir: la lista corta de dónde mirar, con la evidencia para defenderla delante de quien firma la migración.

Qué viene en la serie

El método ya está montado, sellado y auditable. Lo que queda de la serie es aplicarlo y contarte lo que salió:

• Artículo 3: el hallazgo más contraintuitivo. Con datos reales, la versión más antigua estimó mejor en la mediana. Las cifras exactas y el porqué.
• Artículo 4: la trampa del compat level. El mismo nivel dio resultados distintos en cada motor; un experimento de control lo demuestra.
• Artículo 5: el procedimiento completo del laboratorio, para auditar o reproducir cada cifra de la serie.
• Artículo 6: el checklist del consultor: este método convertido en pasos concretos para validar tu migración.

Este método es reproducible con tus datos, pero es trabajo de días. Montar las versiones, cargar una copia de tu base de datos, capturar los planes reales de tus consultas críticas y comparar el Q-error es exactamente lo que hacemos en una validación de migración.

Referencias

La métrica: Q-error

• Moerkotte, G., Neumann, T., Steidl, G. (2009). Preventing Bad Plans by Bounding the Impact of Cardinality Estimation Errors. Proceedings of the VLDB Endowment, 2(1), 982–993. https://doi.org/10.14778/1687627.1687738 · PDF

El método: comparar optimizers con datos reales

• Leis, V., Gubichev, A., Mirchev, A., Boncz, P., Kemper, A., Neumann, T. (2015). How Good Are Query Optimizers, Really? Proceedings of the VLDB Endowment, 9(3), 204–215. https://doi.org/10.14778/2850583.2850594 · PDF

Datasets (para reproducir el método con tus propios datos)

• JOB / IMDB (real correlacionado): consultas, schema.sql y fkindexes.sql en github.com/gregrahn/join-order-benchmark · snapshot de datos IMDB (imdb.tgz): event.cwi.nl/da/job/imdb.tgz
• TPC-H (sintético “de libro”): especificación y programas oficiales (TPC) en tpc.org/tpch · generador dbgen/qgen: github.com/electrum/tpch-dbgen