Como desarrolladores nos toca asumir que cuando las cosas se complican, la última línea de soporte somos nosotros y claro, los problemas que nos llegan son los más extraños y complicados de resolver.

Recientemente hemos tenido que tratar con un caso curioso: una consulta a una base de datos SQL Server que normalmente funcionaba bien, pero en determinados casos tardaba mucho en ejecutarse. Utilizando la misma base de datos y los mismos parámetros de consulta, podía pasar de ejecutarse en menos de un segundo a tardar un par de minutos.

Después de descartar al sospechoso habital (el ORM), comprobar que la consulta se lanzaba directamente con una sentencia SQL generada «a mano» con cariño, asegurarnos de que no hacíamos demasiadas barbaridades en ella y regenerar índices y estadísticas, tocaba echar mano de la artillería pesada y empezar a investigar con el SQL Profiler para obtener más información.

Planes de ejecución

Cuando se envía una consulta a SQL Server el servidor se encarga de parsear el texto de la consulta, compilarla y generar un plan de ejecución que indicará la manera en que se ejecutará la consulta sobre la base de datos.

El plan de ejecución contiene intrucciones de «bajo nivel» con la forma en que se procesará la consulta, por ejemplo, decidiendo si una tabla se recorrerá entera o se accederá a partir de un índice, o si se realizarán bucles anidados o hash joins.

Como os podéis imaginar, elegir un plan de ejecución de consulta adecuado puede ser la diferencia entre que una consulta se ejecute en un tiempo razonable o no.

Por ejemplo, y simplificando mucho, si tenemos una tabla Order con pedidos y una tabla Customer con clientes, ambas con los índices adecuados, y queremos obtener los pedidos del último mes de clientes de Madrid, podríamos tener dos estrategias diferentes:

• Buscamos los clientes de Madrid usando un índice sobre la columna City de la tabla Customer, luego para cada cliente de Madrid buscamos sus pedidos y nos quedamos con los que son del último mes.
• Buscamos los pedidos del último mes usando un índice sobre la columna Date de la tabla Order, y luego buscamos el cliente asociado a ese pedido y nos quedamos sólo con los que son de Madrid.

Si tenemos pocos clientes de Madrid y muchos pedidos en el último mes, la primera opción será más eficiente porque estaremos reduciendo el conjunto de resultados antes. Obtendremos pocas filas de la tabla Customer al filtrar por City = 'Madrid' y por tanto haremos pocos accesos a la tabla Order para buscar pedidos asociados.

Por el contrario, si tenemos pocos pedidos en el último mes y muchos clientes de Madrid, será mejor utilizar la segunda opción para acotar antes el conjunto de resultados.

Para elegir entre una opción u otra, SQL Server se basa en las estadísticas que mantiene sobre los datos almacenados en las tablas, y que le permiten hacerse una idea de cuantos datos devolverá una operación concreta para así optimizar el plan de ejecución de la consulta.

Todo este proceso de generación del plan de ejecución de consulta puede llevar su tiempo, por lo que SQL Server mantiene una caché de planes de ejecución de consulta para poder reutilizarlos.

Parameter sniffing

Además, en su afán por mejorar el rendimiento, si estamos lanzando consultas con parámetros (ya sea mediante llamadas a procedimientos almacenados o con sp_executesql), SQL Server almacena el plan de ejecución que generó para el primer conjunto de parámetros y lo reutiliza en las siguientes ejecuciones de esa consulta, aunque los parámetros hayan cambiado.

Y ahí está el problema. Si volvemos al ejemplo anterior de los clientes y los pedidos, veíamos que dependiendo del número de pedidos que hubiera en el último mes y del número de clientes de la ciudad indicada, era mejor utilizar una estrategia u otra.

Imaginad que la primera vez que se lanza esa consulta, se filtra por clientes de Palencia, y resulta que nuestra empresa no vende nada en Palencia. SQL Server consulta sus estadísticas y decide que es mejor empezar por la tabla Customer, quedarse con 0 filas correspondientes a clientes de Palencia, y luego buscar los pedidos de esos 0 clientes. Perfecto.

Si a continuación lanzamos la consulta para clientes de Barcelona y tenemos 8000 clientes en Barcelona y sólo 50 pedidos en el último mes, SQL Server reutilizará el plan anterior, obtendrá 8000 filas de la tabla Customer y por cada una de ellas buscará sus pedidos asociados, cuando está claro que hubiese sido mucho mejo partir de los 50 pedidos del último mes y ver si el cliente era de Barcelona o no.

Este problema con el que ya me había tocado lidiar alguna vez se conoce como parameter sniffing o parameter spoofing y hay un montón de documentación en internet sobre él y sobre cómo podemos evitarlo, por ejemplo añadiendo un OPTION (RECOMPILE) a nuestra consulta.

¿Por qué los domingos?

Tras revisar con el SQL Profiler que, efectivamente, se estaba generando un plan de ejecución de consulta erróneo, hicimos la prueba evidente, vaciar la caché de planes de ejecución de consulta en la máquina de pruebas con un DBCC FREEPROCCACHE y ver qué pasaba en la aplicación.

Como era de esperar funcionó… un rato. Tras reiniciar la máquina para comprobar que todo seguía bien (estamos hablando de una aplicación con su propio SQL «privado» y local, no vayáis por ahí reiniciando servidores SQL, por favor), volvió a fallar y el plan de ejecución de consultas cacheado volvía a ser incorrecto pero, ¿cómo estaba llegando allí ese plan de ejecución de consultas?

La consulta que fallaba estaba en una pantalla que mostraba un listado de ventas durante un rango de fechas. Al cargar la pantalla por primera vez, se lanzaba esa consulta con la fecha de hoy para mostrar directamente al usuario las ventas del día.

Cuando se ejecutaba la aplicación un día normal, SQL Server sabía (por sus estadísticas) que había registros en la tabla de ventas y establecía un plan de ejecución basado en ese hecho, pero cuando se ejecutaba un domingo, SQL Server sabía que ese día no había ningún registro en la tabla de ventas, por lo que lo mejor era partir de ella para hacer los cruces.

Eso hacía que un plan preparado para obtener 0 filas de la tabla de ventas se viera procesando del orden de miles de filas si después de cargar la ventana se intentaban obtener las ventas de un mes.

La solución, como ya he dicho antes, pasaba por añadir un option (recompile) a la consulta, que aunque tiene sus pegas (obliga al SQL a hacer más trabajo), en este caso es perfectamente aceptable para una consulta que se lanza sólo una par de veces al día como mucho.

Tal vez estaría bien que SQL Server, después de lanzar una consulta usando un plan cacheado, comprobase si hay mucha diferencia entre los números de filas que había estimado para cada paso del plan y los números reales obtenidos al ejecutar la consulta.

Si la diferencia es muy grande, podría descartar el plan y dejar que se volviese a generar en la siguiente consulta, evitando así que todas las consultas queden condenadas a utilizar un plan generado para un caso extremo.

Este problema lleva mucho tiempo existiendo, y si Microsoft no lo ha corregido, supongo que la solución no será tan sencilla como la que planteo, pero estaría bien que buscasen alguna solución, porque este tipo de problemas son complicados de diagnosticar.