Vamos a pasar el mal trago de ayer con los índices de SQL Server que nos perjudicaban el rendimiento y vamos a ver un aspecto que sí podemos controlar nosotros directamente. Mucho hemos hablado de los índices ya en el blog, y también de que SQL Server hace uso de las estadísticas de las tablas para decidir el plan de ejecución más óptimo. Sin embargo no habíamos profundizado en cómo funciona esto y no, no es por arte de magia. SQL Server va a hacer lo que se conoce como parameter sniffing.
El motor de bases de datos de SQL Server utiliza el parameter sniffing para hacerse una idea del volumen de datos de cada una de las tablas que intervienen en nuestras consultas (con los filtros ya aplicados) y así tomar las mejores decisiones. Como habrás adivinado, el parameter sniffing, más allá del tecnicismo (que quedará muy bien si lo soltamos en mitad de una conversación técnica), es una de las claves para el rendimiento de las consultas en SQL Server.
Planes de ejecución de consulta
Lo primero que tenemos que entender es como SQL calcula los planes de ejecución y para ellos, vamos a intentar llevarlo a algo que todos podamos imaginar, así será fácil de entender. Imagina que trabajas de encargado de almacén y tienes que gestionar el trabajo de los mozos del almacén. Te han encargado la tarea de mover todo el stock de unos determinados productos a otro almacén de la compañía. Entre los distintos productos que tienes que mover hay desde tornillos hasta maquinaria industrial. Para poder asignar recursos a las distintas tareas, tendrás que saber cuántos artículos de cada cosa debes mover y cuáles son sus pesos, ¿verdad?. Pues en SQL Server pasa lo mismo, cuando nosotros le pedimos que nos de unos datos, necesita conocer cuántos registros van a ser y cuanto ocupan para poder hacer una asignación correcta de recursos.
Parameter Sniffing en acción
Como podéis imaginar, calcular el plan tiene un coste y es por esto que SQL cachea esos planes para poder reutilizarlos. Esto es realmente útil cuando las consultas se repiten siempre igual pero, ¿qué pasa si cambian? Almacenaremos gran cantidad de planes y muchos serán iguales. Para eso existen los procedimientos almacenados, fragmentos de código que SQL almacena como un objeto, donde las variables están parametrizadas para que nosotros podamos definirlas en cada ejecución. En estas situaciones el plan de ejecución es siempre el mismo porque la consulta es siempre la misma sea cual sea el valor que le pongamos en los filtros. Esto podemos forzarlo para las consultas ad hoc con la opinión parametrización forzada de SQL Server de la que ya hablamos en profundidad aquí.
En estas situaciones SQL Server analizará el volumen de datos que coinciden con ese parámetro y lo usará para generar un plan de ejecución que se utilizará en todas las consultas siguientes. A este procedimiento interno de SQL Server se le conoce como Parameter Sniffing y reduce mucho los tiempos de ejecución cuando tenemos una distribución equitativa de los datos. Es decir, los registros pesan más o menos lo mismo sea cual sea el valor.
Los problemas de Parameter Sniffing
Como hemos visto el parameter sniffing reduce mucha carga de compilación de los planes de ejecución en consultas con una distribución de datos similar pero, ¿Qué pasa si no es así? Volviendo a nuestro ejemplo anterior, ¿asignaremos los mismos recursos para mover un clavo que para mover una máquina de varias toneladas? Obviamente no, y esto es un problema (o lo era). SQL Server asignará los recursos a la consulta en función del plan de ejecución que tiene almacenado y este se habrá calculado en base a los resultados de la primera ejecución de la consulta. Al menos, históricamente siempre ha sido así. En Microsoft conocedores de este problema, implementaron en SQL 2019 una optimización en el procedimiento de parameter sniffing y en los planes de ejecución añadiendo los adaptative joins.
Parameter Sniffing a partir de SQL 2019
En las últimas versiones de SQL Server (2019 y 2022), lo que se hace es compilar el plan de ejecución de varias ejecuciones de la consulta. Si el resultado de estas primeras compilaciones es siempre el mismo plan, se cacheará ese y el comportamiento será el de siempre, pero, si los planes cambian, se almacenará un plan con un join adaptativo que permitirá aplicar un plan u otro en función de los parámetros de la consulta.
Parameter Sniffing caso práctico
Vamos a ver esto en la práctica que con ejemplos es como mejor se quedan las cosas. Para ello vamos a usar la base de datos de demo AdventureWorks.
Si os fijáis, en esta primera imagen, la consulta es la misma y solo cambia el valor que le pasamos al filtro. Solo con eso, dada la cardinalidad de la consulta, vemos como el motor de base de datos calcula un plan de ejecución distinto para cada una de ellas.
Vamos a probar ahora a crear el siguiente procedimiento almacenado, como veis es la misma consulta de la imagen de antes pero hecha procedimiento almacenado.
CREATE PROCEDURE GET_OrderDetail
@id INT
AS
SELECT *
FROM Sales.SalesOrderDetail
WHERE ProductID = @idCon el procedimiento creado mirad lo que pasa. Calcula el plan de ejecución en base al primero de los parámetros y ese es el plan de ejecución que se queda para siempre.
Conclusión
Hoy hemos podido aproximarnos más a cómo SQL usa las estadísticas de las tablas para calcular los planes de ejecución y cómo se comporta cuando usamos consultas parametrizadas. Hemos entendido las ventajas y los inconvenientes del parameter sniffing y lo hemos podido ver en un ejemplo práctico. Dejamos para mañana ver cómo, nosotros como DBAs, podemos influir sobre ello y sacarle todo el partido.
Espero que este artículo te haya sido útil y que te ayude a optimizar el rendimiento de tus consultas en SQL Server. Si tenéis alguna duda o sugerencia, podéis dejarla en Twitter, por mail o dejarnos un mensaje en los comentarios. Y recuerda que también tenemos un grupo de LinkedIn y un canal de YouTube a los que te puede unir. ¡Hasta la próxima!
