En muchas ocasiones tendemos a simplificar nuestros esquemas mentales con la finalidad de aplicar estrategias generalistas para tratar de abordar un número potencialmente infinito de problemas. Esta simplificación es necesaria para ser más productivos y a la vez que nuestra mente sea capaz de tratar problemas desconocidos, pero a su vez puede traernos errores en casos concretos. Es lo que en general muchas veces ocurre cuando se aplican “buenas prácticas” o “reglas de oro”, etc. y no obtenemos el resultado esperado ya no son en realidad una “bala de plata” que mágicamente pueda solucionar todo lo que se nos ponga por delante.

En este post vamos a ver un caso concreto donde probablemente nuestro primer impulso para abordarlo no sea el correcto.

Imaginemos que tenemos el siguiente procedimiento en la base de datos AdventureWorks2017:

CREATE PROCEDURE sp_getDistinctProductsPerAccountPrefix
    @AccountPrefix varchar(20)
AS
BEGIN
    SET NOCOUNT ON;

    CREATE TABLE #Temp
    (
        SalesOrderID integer NOT NULL,
        AccountNumber varchar(20) NOT NULL
    );

    INSERT #Temp
        (SalesOrderID, AccountNumber)
    SELECT
        SalesOrderID,AccountNumber
    FROM Sales.SalesOrderHeader AS SOH
    WHERE
    AccountNumber like @AccountPrefix + '%'
  
    SELECT
        T.AccountNumber,
        COUNT_BIG(DISTINCT SOD.ProductID) TotalDistinctProducts
    FROM #Temp AS T
    JOIN Sales.SalesOrderDetail AS SOD
        ON SOD.SalesOrderID = T.SalesOrderID
    GROUP BY
        T.AccountNumber

    DROP TABLE #Temp;
END;

Básicamente lo que hace este procedimiento es extraer un conjunto de cabeceras de venta que están asociadas cuentas con un prefijo concreto. Una vez tenemos dichas cabeceras en una tabla temporal cruzamos con el detalle y contamos cuantos productos distintos tenemos en dichas ventas.

Si ejecutamos este procedimiento con un par de valores distintos observamos algo que llama la atención en los planes de ejecución:

EXEC sp_getDistinctProductsPerAccountPrefix '10-4020-00051'
EXEC sp_getDistinctProductsPerAccountPrefix '10-4030-0291'

Podemos ver que la tabla temporal, que acabamos de llenar con datos, muestra una estimación incorrecta en el segundo de los casos. En este caso el impacto en el plan no es grande, pero podría serlo en otros casos, necesitar pasar de un nested loop a un hash join, etc.

Normalmente cuando tenemos este tipo de problemas pensamos de forma rápida… parameter sniffing de nuevo… el coste de compilar será despreciable…  vamos a añadir un recompile a la consulta y “asunto solucionado”:

ALTER PROCEDURE sp_getDistinctProductsPerAccountPrefix
    @AccountPrefix varchar(20)
AS
BEGIN
    SET NOCOUNT ON;

    CREATE TABLE #Temp
    (
        SalesOrderID integer NOT NULL,
        AccountNumber varchar(20) NOT NULL
    );

    INSERT #Temp
        (SalesOrderID, AccountNumber)
    SELECT
        SalesOrderID,AccountNumber
    FROM Sales.SalesOrderHeader AS SOH
    WHERE
    AccountNumber like @AccountPrefix + '%'
  
    SELECT
        T.AccountNumber,
        COUNT_BIG(DISTINCT SOD.ProductID) TotalDistinctProducts
    FROM #Temp AS T
    JOIN Sales.SalesOrderDetail AS SOD
        ON SOD.SalesOrderID = T.SalesOrderID
    GROUP BY
        T.AccountNumber
  OPTION (RECOMPILE)

    DROP TABLE #Temp;
END;

Y efectivamente podemos comprobar que la estimación respecto al número de filas que tenemos en la tabla temporal, se soluciona con este cambio y en la segunda ejecución tenemos una estimación exacta de 108 filas que son las que realmente tenemos:

EXEC sp_getDistinctProductsPerAccountPrefix '10-4020-00051'
EXEC sp_getDistinctProductsPerAccountPrefix '10-4030-0291'

Pero no cantemos victoria tan rápidamente.  Imaginemos que lo que hacemos es algo ligeramente más complicado y aplicamos un filtrado sobre esas filas de la tabla temporal. En este caso vamos a realizar un filtrado un poco “tonto” , pero que muestra claramente el problema, consistente en añadir el mismo filtro que tenemos previamente (AccountNumber like @AccountPrefix + ‘%’).

Es decir, vamos a añadir el filtro que ya usamos para cargar la tabla en la consulta posterior, lo cual no debe afectar al número de registros ni al resultado ya que es a efectos prácticos redundante, debería devolver las mismas filas:

ALTER PROCEDURE sp_getDistinctProductsPerAccountPrefix
    @AccountPrefix varchar(20)
AS
BEGIN
    SET NOCOUNT ON;

    CREATE TABLE #Temp
    (
        SalesOrderID integer NOT NULL,
        AccountNumber varchar(20) NOT NULL
    );

    INSERT #Temp
        (SalesOrderID, AccountNumber)
    SELECT
        SalesOrderID,AccountNumber
    FROM Sales.SalesOrderHeader AS SOH
    WHERE
    AccountNumber like @AccountPrefix + '%'
  
    SELECT
        T.AccountNumber,
        COUNT_BIG(DISTINCT SOD.ProductID) TotalDistinctProducts
    FROM #Temp AS T
    JOIN Sales.SalesOrderDetail AS SOD
        ON SOD.SalesOrderID = T.SalesOrderID
WHERE
    T.AccountNumber like @AccountPrefix + '%'
    GROUP BY
        T.AccountNumber
  OPTION (RECOMPILE)

    DROP TABLE #Temp;
END;

Una vez hecho esto, volvemos a ejecutar los dos procedimientos y comparamos lo que obtenemos en la estimación de ese scan de la tabla temporal:

EXEC sp_getDistinctProductsPerAccountPrefix '10-4020-00051'
EXEC sp_getDistinctProductsPerAccountPrefix '10-4030-0291'

Como podemos ver ahora, la estimación en la segunda ejecución es de 1 fila, y no de las 108 que teníamos antes cuando en realidad el número de filas que tenemos y el número de filas que se han insertado en la tabla temporal es el mismo. Hemos mantenido también a “nuestro amigo” el RECOMPILE en su lugar, por lo que aparentemente algo extraño nos está pasando.

No sería raro que en este momento pensáramos… algo no está bien con el recompile, voy a borrar la caché de procedimientos y ejecutar el segundo caso, “asegurándome que compila” si o si a la fuerza para ver cómo se comporta para ese valor de parámetro:

DBCC FREEPROCCACHE 
EXEC sp_getDistinctProductsPerAccountPrefix '10-4030-0291'

Efectivamente el problema “desaparece”, tenemos nuestra estimación precisa de 108 como queríamos. Si a continuación ejecutamos el procedimiento con el primer parámetro nos encontramos con el problema pero en “sentido contrario”:

EXEC sp_getDistinctProductsPerAccountPrefix '10-4020-00051'

Convencidos que la recompilación no está “haciendo su trabajo como debe” decidimos subir el nivel de la recompilación a nivel de módulo, de procedimiento almacenado completo, modificando el procedimiento de la siguiente forma:

ALTER PROCEDURE sp_getDistinctProductsPerAccountPrefix
    @AccountPrefix varchar(20)
WITH RECOMPILE
AS
BEGIN
    SET NOCOUNT ON;

    CREATE TABLE #Temp
    (
        SalesOrderID integer NOT NULL,
        AccountNumber varchar(20) NOT NULL
    );

    INSERT #Temp
        (SalesOrderID, AccountNumber)
    SELECT
        SalesOrderID,AccountNumber
    FROM Sales.SalesOrderHeader AS SOH
    WHERE
    AccountNumber like @AccountPrefix + '%'
  
    SELECT
        T.AccountNumber,
        COUNT_BIG(DISTINCT SOD.ProductID) TotalDistinctProducts
    FROM #Temp AS T
    JOIN Sales.SalesOrderDetail AS SOD
        ON SOD.SalesOrderID = T.SalesOrderID
  WHERE
    AccountNumber like @AccountPrefix + '%'
    GROUP BY
        T.AccountNumber
  --OPTION (RECOMPILE)

    DROP TABLE #Temp;
END;

Volvemos a ejecutar el procedimiento y nos encontramos que ya todo funciona según lo esperado: obtenemos una estimación correcta tanto si ejecutamos en un orden o en otro. En base a esto concluimos que lo que teníamos era un problema de parameter sniffing y de una recompilación algo tozuda/perezosa  a nivel de statement que hemos tenido que “subir” a nivel de procedimiento almacenado para que fuese efectiva:

EXEC sp_getDistinctProductsPerAccountPrefix '10-4020-00051'
EXEC sp_getDistinctProductsPerAccountPrefix '10-4030-0291'
EXEC sp_getDistinctProductsPerAccountPrefix '10-4020-00051'
EXEC sp_getDistinctProductsPerAccountPrefix '10-4030-0291'

Ahora bien, ¿qué es lo que está ocurriendo en realidad? ¿Estamos en lo cierto? Pues en realidad no, lo que ocurre es algo bastante distinto y que puede considerarse un efecto colateral de una optimización de SQL Server encargada de cachear tablas temporales.

Comenzaremos volviendo el procedimiento a su versión original, sin recompiles ni nada, y simplemente mostraremos el object_id asociado a la tabla temporal que estamos usando:

ALTER PROCEDURE sp_getDistinctProductsPerAccountPrefix
    @AccountPrefix varchar(20)
AS
BEGIN
    SET NOCOUNT ON;

    CREATE TABLE #Temp
    (
        SalesOrderID integer NOT NULL,
        AccountNumber varchar(20) NOT NULL
    );

  SELECT OBJECT_ID('tempdb.dbo.#Temp')

    INSERT #Temp
        (SalesOrderID, AccountNumber)
    SELECT
        SalesOrderID,AccountNumber
    FROM Sales.SalesOrderHeader AS SOH
    WHERE
    AccountNumber like @AccountPrefix + '%'
  
    SELECT
        T.AccountNumber,
        COUNT_BIG(DISTINCT SOD.ProductID) TotalDistinctProducts
    FROM #Temp AS T
    JOIN Sales.SalesOrderDetail AS SOD
        ON SOD.SalesOrderID = T.SalesOrderID
    GROUP BY
        T.AccountNumber

    DROP TABLE #Temp;
END;
EXEC sp_getDistinctProductsPerAccountPrefix '10-4020-00051'
EXEC sp_getDistinctProductsPerAccountPrefix '10-4030-0291'

Podemos ver que el object_id es el mismo lo cual implica que el objeto no fue desasignado y recreado desde cero entre ejecuciones, se está reutilizando exactamente el mismo objeto entre dos ejecuciones distintas de un mismo procedimiento. En este momento podríamos pensar que esto no es posible, qué ocurriría en caso de concurrencia, etc. Vamos a añadir un waitfor de 10 segundos antes del drop de la tabla temporal y ejecutaremos concurrentemente en dos sesiones las llamadas al procedimiento:

ALTER PROCEDURE sp_getDistinctProductsPerAccountPrefix
    @AccountPrefix varchar(20)
AS
BEGIN
    SET NOCOUNT ON;

    CREATE TABLE #Temp
    (
        SalesOrderID integer NOT NULL,
        AccountNumber varchar(20) NOT NULL
    );

  SELECT OBJECT_ID('tempdb.dbo.#Temp')

    INSERT #Temp
        (SalesOrderID, AccountNumber)
    SELECT
        SalesOrderID,AccountNumber
    FROM Sales.SalesOrderHeader AS SOH
    WHERE
    AccountNumber like @AccountPrefix + '%'
  
    SELECT
        T.AccountNumber,
        COUNT_BIG(DISTINCT SOD.ProductID) TotalDistinctProducts
    FROM #Temp AS T
    JOIN Sales.SalesOrderDetail AS SOD
        ON SOD.SalesOrderID = T.SalesOrderID
    GROUP BY
        T.AccountNumber

    WAITFOR DELAY '00:00:10'

    DROP TABLE #Temp;
END;

Podemos ver que, al ejecutar, obtenemos dos object_id distintos en cada sesión:

EXEC sp_getDistinctProductsPerAccountPrefix '10-4030-0291'

EXEC sp_getDistinctProductsPerAccountPrefix '10-4020-00051'

Podemos pensar equivocadamente que es un efecto secundario de lanzar en la misma sesión el mismo procedimiento almacenado. Si volvemos a lanzar esta segunda ejecución sin concurrencia con la primera (desde la misma sesión donde obtuvimos el -1277356580) podemos ver como obtenemos ahora el ID -1261356523 (que era el que obtuvo la otra sesión previamente) por lo que el ámbito de reutilización no es la sesión, es global:

En realidad, lo que tenemos es una caché de objetos temporales y, dado un mismo “template” de una tabla, podemos tener N copias distintas disponibles que se reutilizarán por los procedimientos de forma indistinta según se soliciten. El objetivo de esta caché no es otro que reducir el coste de creación y destrucción de objetos temporales y aumentar el rendimiento con ello.

Ahora que sabemos que estamos compartiendo a veces un mismo objeto temporal entre ejecuciones, creado por otra ejecución previa, tenemos que considerar otras opciones. Es decir, ¿cuál es la razón real por la que teníamos la estimación incorrecta cuando indicábamos RECOMPILE a nivel de statement solo si añadíamos el filtro WHERE redundante a la consulta?

La razón de fondo la encontramos en las estadísticas asociadas a dicho objeto, que no son “limpiadas” entre ejecuciones de los procedimientos, únicamente se eliminan los contenidos de la tabla. Personalmente creo que podría haber sido adecuado que el proceso de limpieza del motor eliminara también las estadísticas para evitar que podamos encontrarnos con esta situación “extraña”.

Vamos a modificar nuestro procedimiento para que muestre las estadísticas de nuestra tabla temporal:

ALTER PROCEDURE sp_getDistinctProductsPerAccountPrefix
    @AccountPrefix varchar(20)
AS
BEGIN
    SET NOCOUNT ON;

    CREATE TABLE #Temp
    (
        SalesOrderID integer NOT NULL,
        AccountNumber varchar(20) NOT NULL
    );

  SELECT OBJECT_ID('tempdb.dbo.#Temp')

    INSERT #Temp
        (SalesOrderID, AccountNumber)
    SELECT
        SalesOrderID,AccountNumber
    FROM Sales.SalesOrderHeader AS SOH
    WHERE
    AccountNumber like @AccountPrefix + '%'
  
    SELECT
        T.AccountNumber,
        COUNT_BIG(DISTINCT SOD.ProductID) TotalDistinctProducts
    FROM #Temp AS T
    JOIN Sales.SalesOrderDetail AS SOD
        ON SOD.SalesOrderID = T.SalesOrderID
    GROUP BY
        T.AccountNumber

  DBCC SHOW_STATISTICS (N'tempdb.dbo.#Temp', AccountNumber) WITH HISTOGRAM;

    DROP TABLE #Temp;
END;

Si ejecutamos nuestras dos llamadas vemos que el histograma de las estadísticas es exactamente el mismo pese a que los datos insertados son distintos debido al filtrado basado en el prefijo:

EXEC sp_getDistinctProductsPerAccountPrefix '10-4020-00051'
EXEC sp_getDistinctProductsPerAccountPrefix '10-4030-0291'

Cuando añadimos el RECOMPILE lo que conseguimos fue que, cuando la consulta no tenía filtro, pudiéramos obtener una estimación real del número de filas. Sin embargo, cuando añadíamos el filtro no era suficiente con esta actualización del número total de filas, ya que al acceder el optimizador a la estadística para determinar cuántos valores tienen el prefijo “10-4030-0291” la respuesta eran 0, ya que cuando se creó dicha estadística únicamente teníamos valores del rango 10-4020 en la tabla temporal.

Cuando añadimos el WITH RECOMPILE al procedimiento almacenado lo que conseguimos fue no solamente recompilar sino deshabilitar la caché de tablas temporales para ese módulo, ya que la cláusula WITH RECOMPILE tiene ese efecto indirecto más allá de forzar la compilación. Otras razones típicas que inhabilitan la caché de tablas temporales son por ejemplo el realizar una operación DDL sobre dicha tabla tras su creación (añadir un índice, añadir una columna, etc.) o utilizar constraints con nombre explícito.

Una vez que hemos conseguido llegar al fondo del problema, a la causa raíz, podemos plantearnos como alternativa forzar una actualización de estadísticas para asegurarnos que no utilizamos una estadística “fantasma” heredada de una ejecución pasada y eliminar el RECOMPILE (mantendremos el object_id  y el show statistics). Para ello modificaremos la versión original a la que le añadimos el filtro where y añadiremos un UPDATE STATISTICS tras la carga de la tabla temporal:

ALTER PROCEDURE sp_getDistinctProductsPerAccountPrefix
    @AccountPrefix varchar(20)
AS
BEGIN
    SET NOCOUNT ON;

    CREATE TABLE #Temp
    (
        SalesOrderID integer NOT NULL,
        AccountNumber varchar(20) NOT NULL
    );

  SELECT OBJECT_ID('tempdb.dbo.#Temp')

    INSERT #Temp
        (SalesOrderID, AccountNumber)
    SELECT
        SalesOrderID,AccountNumber
    FROM Sales.SalesOrderHeader AS SOH
    WHERE
    AccountNumber like @AccountPrefix + '%'
  
  UPDATE STATISTICS #Temp 
    SELECT
        T.AccountNumber,
        COUNT_BIG(DISTINCT SOD.ProductID) TotalDistinctProducts
    FROM #Temp AS T
    JOIN Sales.SalesOrderDetail AS SOD
        ON SOD.SalesOrderID = T.SalesOrderID
    GROUP BY
        T.AccountNumber

  DBCC SHOW_STATISTICS (N'tempdb.dbo.#Temp', AccountNumber)
    WITH HISTOGRAM;

    DROP TABLE #Temp;
END;

Al realizar las ejecuciones vemos lo siguiente:

EXEC sp_getDistinctProductsPerAccountPrefix '10-4020-00051'
EXEC sp_getDistinctProductsPerAccountPrefix '10-4030-0291'

Es decir, se está reutilizando el mismo objeto temporal cacheado (mismo object_id) pero gracias el UPDATE STATISTICS tenemos la estadística actualizada en cada ejecución con los datos correctos. Si revisamos las estimaciones de los planes de ejecución vemos lo siguiente:

Es decir, aunque hemos actualizado las estadísticas y los datos son correctos en ellas, el plan no se ha recompilado y seguimos utilizando la estimación de filas de la ejecución anterior (40). Es decir, cuando pensábamos que ya lo teníamos claro, resulta que aún falta profundizar algo más.

La razón por la que seguimos teniendo esa mala estimación es que la actualización de estadísticas no es una condición suficiente para invalidar el plan de la caché, además debe ocurrir que se exceda un threshold que en este caso al tratarse de una tabla temporal cacheada depende de la cantidad de filas que tenga la tabla cambie lo suficiente. Por tanto, en este caso, no está ocurriendo la recompilación al no cambiar lo suficiente la cantidad de filas que introducimos entre ejecuciones.

Si por ejemplo realizamos una llamada con un prefijo más amplio, obtendremos una recompilación y con ella cambiará la estimación y, en este caso, el plan en sí, pasando a ser paralelo, etc.:

EXEC sp_getDistinctProductsPerAccountPrefix '10-40'

Si después de ejecutar con ese parámetro, volvemos al anterior, el que manejaba 108 filas, nos encontramos que sí se recompila y se genera un nuevo plan con la estimación correcta, ya que el cambio en el número de filas (a la baja, pero un cambio al fin y al cabo) es suficientemente significativo como para forzar la recompilación sin tenerla que forzar con un OPTION RECOMPILE:

EXEC sp_getDistinctProductsPerAccountPrefix '10-4030-0291'

Si tras esta recopilación, ejecutamos el procedimiento con el parámetro que devolvía 40 filas, no es suficiente (viniendo del de 108) como para disparar esta recompilación de nuevo por lo que mantendríamos la estimación de 108:

EXEC sp_getDistinctProductsPerAccountPrefix '10-4020-00051'

El problema de este tipo de comportamientos es lo complicado que puede ser, en un sistema productivo en vivo, donde tenemos multitud de ejecuciones concurrentes del mismo procedimiento, con distintos parámetros, generándose recompilaciones aleatoriamente, etc.  Encontrar la razón por la que algunas ejecuciones funcionan mejor o peor y solo a veces. Al final necesitamos capturar qué plan se está usando en cada caso, lo cual es costoso, además de saber si hemos recompilado en esa ejecución o no y si las estadísticas que usamos están actualizadas o son “heredadas” de otra ejecución.

Por tanto, en este tipo de situaciones debemos plantear qué posible solución sería menos costosa para nosotros. Una opción sería sopesar el coste de actualizar la estadística cada vez y recompilar cada vez la consulta que utiliza dicha estadística ya que si es pequeño puede ser la opción más segura.

Otra opción sería invalidar la caché de tablas temporales para esa tabla por ejemplo añadiéndole una constraint (aunque no limite nada en realidad), o quizás plantear el uso de variables de tipo tabla en vez de tablas temporales. También podríamos intentar utilizar un procedimiento almacenado “proxy” que llame a N procedimientos distintos en función de los parámetros, de forma que aquellos que necesiten “planes distintos” basados en estimaciones distintas, etc. se ejecuten por procedimientos distintos, cada uno con su plan de ejecución diferenciado, etc.

También se le podría dar una vuelta de tuerca más y seguir un enfoque similar a los procedimientos de tipo “búsqueda dinámica” donde acabemos componiendo un “template dinámico” en forma de consulta parametrizada. Esta consulta tendría que tener algún “token” diferenciador en función no solo de los parámetros introducidos, sino también del valor de dichos parámetros para que se generara un plan específico para cada caso.

Por último, solo nos queda soñar con que algún día SQL Server implemente alguna funcionalidad que permita no solo protegernos de regresiones en planes de ejecución “peores” de forma global sino tener planes múltiples “por parámetro”. Es decir, abrir la posibilidad de tener N planes de ejecución distintos que se adapten a los distintos valores de los parámetros que podamos suministrar a nuestros procedimientos almacenados de forma que se utilice aquél que sea más eficiente para cada caso.

X Edición Executive Máster en BI & Advanced Analytics con Tecnologías Microsoft. Conviértete en un año en un experto en BI con un seguimiento personalizado de los mentores y MVPs de SolidQ y con el nuevo temario del máster en BI & Advanced Analytics , introduciendo Modern Data Warehouse, analítica y visualización avanzada.

¡Empezamos en octubre! Inscríbete ahora y aprovecha el descuento que hay disponible hasta finales de julio o completar inscripciones. Toda la información aquí.

Rubén Garrigós