SqlServer, plan de consulta y optimización (Tuning) (3)

SqlServer, plan de consulta y optimización (Tuning) (3)

El problema

En la entrada pasada vimos al sql server meter la pata groseramente al intentar estimar la cantidad de registros a recibir en una consulta con datos correlacionados.
Quedo en evidencia el problema y se dijo que dado lo grosero del error dificilmente el motor pudiera escoger un plan optimo de consulta.
El problema radica en que el sql server no entiende que existe relación entre dos columnas, hay múltiples ejemplos de datos correlacionados, edad y altura, peso y tamaño, calidad y precio etc.
La situación es que el sql server calcula la probabilidad de que se cumpla una condición múltiple basicamente como el producto de ambas, esto es correcto y es lo que aprendimos en probabilidad y estadista, la probabilidad de que ocurran dos eventos es el producto de la probabilidad de que ocurra cada uno de ellos.
Lo que también nos enseñaron es que los experimentos deben ser independiente y no deben afectarse mutuamente cosa que aquí no pasa. Difícilmente encontremos una persona con cintura estrecha, que mida menos de 1.6 metros y que pese mas de 100 kg, las tres variables están correlacionadas.
Podemos encontrar ejemplo donde el motor descarta un indice por errores de estimación en columnas correlacionadas, no es tan fácil encontrar ejemplos donde el motor escoja una estrategia de join equivocada.

Hagamos un experimento con la siguiente consulta :



declare @marca char(20)
declare @Modelo char(20)
declare @Motor char(20)
declare @Origen char(20)
declare @Ranking int


set @marca='VW'
set @Modelo='GOL'
set @Motor='VW 1.6'
set @Origen='BRASIL'
set @Ranking=1


select *
from Vehiculo as v1 inner JOIN Vehiculo as v2
On v1.IdVehiculo=v2.IdVehiculo  inner  JOIN Vehiculo as v3
On v2.IdVehiculo=v3.IdVehiculo inner join Vehiculo as v4
On v3.IdVehiculo=v4.IdVehiculo
Where v1.Marca=@marca and
v1.Modelo=@Modelo and
v1.Motor=@Motor and
v1.Origen='BRASIL' and
v1.Ranking=1

La ejecución de la consulta otorga la siguiente información,

La estrategia de join del motor es "nested loop", el motor piensa que va a recibir pocos registros y por tanto escoge esta estrategia, seguramente la mas conveniente con conjuntos de 1500 registros (Los que estima recibir).
La realidad demuestra que se reciben 200000, y el join es por IdVehiculo, indice cluster, por tanto los conjuntos están ordenados.
Se ve marcado en rojo la enorme diferencia que existe entre lo que el sql espera recibir y lo que verdaderamente recibe


La solución ( MERGE JOIN )

Existe un mecanismo de join llamado MERGE_JOIN es un mecanismo que es eficiente solo con conjuntos ordenados de determinado tamaño. Si forzáramos MERGE JOIN en conjuntos no ordenados el sql los tendría que ordenar previamente y generalmente esto no da buenos resultados.
No es este el caso donde ex profeso hemos hecho el join por el indice cluster.
Vemos también que no es la opción elegida por nuestro Sql Server, a pesar de que los conjuntos están ordenados la cantidad de registros que espera obtener no a merita el uso de Merge join.
Forzamos entonces al sql a utilizar MERGE_JOIN a sabiendas de que el motor tiene enormes problemas para predecir la cantidad de registros que cumplen la condición.


declare @marca char(20)
declare @Modelo char(20)
declare @Motor char(20)
declare @Origen char(20)
declare @Ranking int


set @marca='VW'
set @Modelo='GOL'
set @Motor='VW 1.6'
set @Origen='BRASIL'
set @Ranking=1


select *
from Vehiculo as v1 inner MERGE JOIN Vehiculo as v2
On v1.IdVehiculo=v2.IdVehiculo  inner MERGE  JOIN Vehiculo as v3
On v2.IdVehiculo=v3.IdVehiculo inner MERGE join Vehiculo as v4
On v3.IdVehiculo=v4.IdVehiculo
Where v1.Marca=@marca and
v1.Modelo=@Modelo and
v1.Motor=@Motor and
v1.Origen='BRASIL' and
v1.Ranking=1

Observemos y comparemos el rendimiento en uno y otro caso

Mas allá de esta medición puntual en varias pruebas observe que el tiempo de ejecución se reduce a la mitad, en fin los datos correlacionados hicieron desastres con las estimaciones y destruyeron los intentos del motor por obtener un buen plan de consulta.

Consideraciones finales

Para cerrar, mucho cuidado, el problema se da bajo las condiciones actuales de la tabla, pero atención  mas registros, un cambio en la distribución y correlación de los datos, o un cambio en la estructura invalidaría lo expuesto, por eso precaución al forzar el plan de consulta