Descubrimiento de la opción de sugerencia NOEXPAND en consultas

Hace una semana estaba yo realizando un análisis de rendimiento a un cliente que está utilizando Microsoft Dynamics 365 Business Central (o lo que es lo mismo, el Navision de toda la vida) y revisando las querys que se estaban ejecutando por debajo desde las pantallas de Navision, me encuentro con que muchas de ellas utilizan la opción de sugerencia en la consulta: NOEXPAND. Por lo que me puse a investigar el por qué el grupo de desarrollo de navision utiliza esta sugerencia en sus consultas.

Si vamos a la documentación de Microsoft te dice lo siguiente:

Usar NOEXPAND
NOEXPAND solo se aplica a las vistas indizadas. Una vista indizada es una vista con un único índice clúster creado en ella. Si una consulta tiene referencias a columnas que están presentes en una vista indizada y en tablas base, y el optimizador de consultas determina que el uso de vistas indizadas proporciona el mejor método para ejecutar la consulta, el optimizador de consultas utiliza el índice en la vista. Esta funcionalidad se denomina coincidencia de vista indizada. Antes de SQL Server 2016 (13.x) SP1, el uso automático de una vista indizada por parte del optimizador de consultas solo se admite en determinadas ediciones de SQL Server

Para exigir que el optimizador utilice un índice para una vista indizada, especifique la opción NOEXPAND. Esta sugerencia solo se puede usar si la vista también aparece en la consulta. SQL Server no proporciona ninguna sugerencia que obligue a usar una vista indizada determinada en una consulta que no mencione la vista directamente en la cláusula FROM. Sin embargo, el optimizador de consultas considera el uso de vistas indizadas, incluso aunque no se haga referencia directa a ellas en la consulta. SQL Server solo creará automáticamente las estadísticas en una vista indexada cuando se usa una sugerencia de tabla NOEXPAND. La omisión de esta sugerencia puede provocar advertencias del plan de ejecución sobre estadísticas que faltan que no se pueden resolver mediante la creación manual de estadísticas. Durante la optimización de consultas, SQL Server usará las estadísticas de vista creadas de forma automática o manual cuando la consulta hace referencia directamente a la vista y se usa la sugerencia NOEXPAND.

Como se comportan nuestras consultas con NOEXPAND

Vamos a ver exactamente qué es lo que quiere decir esta opción y el por qué Navision la utiliza tanto:

Dependiendo de la versión de SQL

• Lo primero que hay que dejar claro, desde el principio, es que la forma de trabajar de SQL con las vistas indizadas no es la misma en versiones anteriores a SQL 2016 Service Pack 1:
  • Si estas en una versión anterior, dependiendo de si tienes versión estándar o empresarial , el optimizador de consultas de SQL se comporta de una manera diferente, si esta en empresarial podrá elegir la mejor opción que crea el si las columnas por las que estas consultando están también en índices de la vista indizada como en la tabla base.
  • A partir de SQL 2016 SP1 es indiferente la versión, siempre lo hará.

Explicación

•  Si estamos en una versión empresarial o en SQL 2016 Sp1 o mayor, si SQL se encuentra este tipo de consulta con una vista indexada puede ocurrir lo siguiente:
  • Lo primero que vamos a hacer es generar una tabla base y luego la vista indexada:

CREATE VIEW dbo.CustomerOrders
WITH SCHEMABINDING AS
SELECT
SOH.CustomerID,
SOD.ProductID,
OrderQty = SUM(SOD.OrderQty),
NumRows = COUNT_BIG(*)
FROM Sales.SalesOrderDetail AS SOD
JOIN Sales.SalesOrderHeader AS SOH
ON SOH.SalesOrderID = SOD.SalesOrderID
GROUP BY
SOH.CustomerID,
SOD.ProductID;

CREATE UNIQUE CLUSTERED INDEX cuq
ON dbo.CustomerOrders (ProductID, CustomerID);

• • Una vez creado el índice sobre la vista, esta se convierte en una vista indizada y podemos comprobar como SQL ha generado para ese índice unas estadísticas:

• • Ahora si probamos a lanzar la siguiente consulta:

SELECT
CO.CustomerID,
SUM(CO.OrderQty)
FROM dbo.CustomerOrders AS CO --WITH (NOEXPAND)
WHERE
CO.ProductID BETWEEN 711 AND 718
GROUP BY
CO.CustomerID

  Es una Select sobre la vista por el campo ProductID, el plan de ejecución que genera el optimizador de consultas es el siguiente:

Donde podemos observar cómo ha elegido utilizar el índice creado sobre la vista para realizar el filtro por ProductID que es lo más óptimo.

• • Pero, si utilizamos la opción NOEXPAND ¿qué es lo que pasaría? En teoría debería de ser lo mismo porque le estamos diciendo al motor que no intente utilizar otra alternativa que no sea la vista indizada para resolver la consulta, cosa que ya ha hecho por defecto en la anterior, al ver que es lo más óptimo, y estar en una versión de SQL 2016 SP1 o superior. Vamos a comprobarlo:

Como pensábamos el plan de ejecución es el mismo, pero hay dos diferencias muy importantes:

A) Como dice la documentación de Microsoft , arriba en el artículo, con la opción de sugerencia  de NOEXPAND aparte de especificar que solo utilice la vista indizada para la resolución de la consulta también genera estadísticas para la columna que ha tenido que utilizar en el filtrado , que en este caso es Product_ID :

B) Y ahora como SQL ha creado unas estadísticas sobre los valores de esta columna, la estimación es más realista como podemos observar entre los dos pantallazos de arriba en la parte de la derecha, en la venta de propiedades:

1. 1. 1. En la primera consulta, sin la opción, estimo 10871 y realmente los registros leídos fueron 7301.
      2. En la segunda consulta los registros estimados y los reales coinciden.

Esto en esta consulta no genera gran diferencia al ser muy simple, pero en consultas más complejas donde existan JOINS y acumulados, el que los valores de estimación y reales sean tan dispares generan que el plan de ejecución reserva más memoria para la resolución de la consulta que la necesaria o que los operadores lógicos (NESTED LOOP, HASH MATCH,…) no sean los más óptimos para la realidad, este tema daría para más que un blog, por lo que no entro en más detalle.

CONCLUSIÓN los desarrolladores de Navision en previsión de este comportamiento de SQL han decidió siempre poner esta opción en las consultas para utilizar los índices de las vista y las estadísticas más actualizadas. Por tanto, si nosotros utilizamos mucho en nuestras consultas las vistas indizadas, debemos de tener en cuenta este comportamiento a la hora de analizar porque las consultas no rinden.

Como se comporta SQL con las vistas indexadas dependiendo de la versión

Por último no quería dejar sin explicar el comportamiento de SQL en versiones anteriores a SQL 2016 Sp1 y en versión estándar, donde SQL no utiliza una vista indizada cuando ejecutas una consulta donde las tablas referenciadas están contenidas en una vista indizada que podría ser más óptimo para su resolución, por ejemplo, el siguiente SELECT:

SELECT
SOH.CustomerID,
SUM(OrderQty)
FROM Sales.SalesOrderHeader AS SOH
JOIN Sales.SalesOrderDetail AS SOD
ON SOD.SalesOrderID = SOH.SalesOrderID
WHERE
SOD.ProductID BETWEEN 711 AND 718
GROUP BY
SOH.CustomerID

Si lanzamos esto en estándar:

Podemos observar como SQL utiliza las dos tablas para resolver el JOIN realizando un recorrido en ambas, aparte de recomendarte un índice para mejorar el rendimiento de la consulta.

Si esta misma consulta la ejecutamos en la versión empresarial o SQL 2016 SP1 superior:

SQL detecta que existe una vista indizada que permite resolver la consulta de manera más óptima y por tanto automáticamente el la elige.

Puede ser que en algún momento este comportamiento no sea el más óptimo, para ello siempre tenemos otra opción OPTION (EXPAND VIEWS) que nos permite indicarle a SQL que a la hora de resolver la consulta no mire si hay algún índices de una vista indizada que permita optimizar la consulta y solo utilice los objetos referenciados en la consulta.