Optimizador de Consultas SQL Server

En este ejemplo, queremos extraer los datos de las líneas de pedido que más dinero han proporcionado al negocio. Extrapolando este ejemplo a la base de datos AdventureWorks, podríamos escribirlo así:

select od.*
from
(
select SalesOrderID,
MAX(LineTotal)
as max_Total
from Sales.SalesOrderDetail
group by SalesOrderID
)
mx INNER JOIN
Sales.SalesOrderDetail od on
mx.SalesOrderID=od.SalesOrderID and
mx.max_Total=od.LineTotal

Quisiera hacer un inciso en este punto para comentar que en muchas ocasiones hemos visto en clientes que para resolver esta consulta primeramente creaban una tabla temporal (variable o no, eso da igual), insertaban los datos en dicha tabla y posteriormente hacían el join con la tabla final para obtener los datos. Como podréis imaginar, esa opción es muy ineficiente, implicando la necesidad de unos recursos de más que pueden ser perfectamente evitables reescribiendo la instrucción al estilo que el ejemplo muestra.
Una vez hecha la puntualización, si queremos que la consulta se resuelva de forma eficiente, nos hace falta un índice sobre las columnas SalesOrderId y LineTotal, algo que hace la siguiente instrucción:

CREATE
NONCLUSTERED
INDEX nci_Total ON [Sales].[SalesOrderDetail](SalesOrderID, LineTotal)

Si mostramos el plan de ejecución, veremos que usa el índice que acabamos de crear:

Hasta aquí nada extraño ni fuera de lo común: SQL Server ha creído oportuno usar el índice nci_Total para agrupar los resultados en base a una columna y extraer el valor máximo de la otra columna. Luego tan sólo tiene que hacer un hash match para mostrar los datos solicitados (en este caso, todas las columnas de la tabla).
Sin embargo, ahora viene uno de los jefes que nos pide una serie de modificaciones en la consulta en un momento dado, ya que ahora nos pide sacar los datos de las últimas líneas de pedido de cada uno de los pedidos (campo ModifiedDate). Y como nos lo pide para ayer, nosotros, para cumplir con las exigencias, dejamos la instrucción así:

SELECT od.* 

FROM
( 

    SELECT SalesOrderID,
MAX(LineTotal)
AS max_Total, MAX(ModifiedDate)
AS max_Date 

    FROM [Sales].[SalesOrderDetail] 

    GROUP
BY SalesOrderID 

    ) mx INNER
JOIN [Sales].[SalesOrderDetail] od ON mx.SalesOrderID=od.SalesOrderID AND mx.max_Date=od.ModifiedDate

Si vemos el plan de ejecución de esta consulta, vemos que es muy pesada porque no hay un índice útil para agrupar los datos de la forma en la que se solicitan, por lo que se tiene que recorrer dos veces la tabla (Clustered Index Scan) para resolverla. Pero como era una petición puntual, no creemos conveniente (con toda la razón del mundo) crear un índice.
Una vez pasado el momento de crisis, volvemos a modificar la consulta, pero con las prisas del día a día la dejamos así:

SELECT od.* 

FROM
( 

    SELECT SalesOrderID,
MAX(LineTotal)
AS max_Total, MAX(ModifiedDate)
AS max_Date 

    FROM [Sales].[SalesOrderDetail] 

    GROUP
BY SalesOrderID 

    ) mx INNER
JOIN [Sales].[SalesOrderDetail] od ON mx.SalesOrderID=od.SalesOrderID AND mx.max_Total=od.LineTotal

El problema está en que se nos olvidó quitar el campo max_Date de la consulta interna, con lo que el índice nci_Total no va a ser suficiente para resolver eficientemente la instrucción… ¿Seguro? Analicemos el plan de ejecución:

No, no he vuelto a copiar el plan de ejecución anterior, la instrucción es diferente (aparece MAX(ModifiedDate)). Sin embargo, como SQL Server sabe que ese campo no va a ser accedido en ninguna otra parte de la consulta, directamente lo obvia: ha conseguido que un olvido por culpa del día a día no tenga implicaciones en el rendimiento del servidor.
Con lo cual volvemos al párrafo inicial: cuando me encontré con la situación parecida a la que muestro en el ejemplo, por un lado me sorprendió al ver que usaba el índice para resolver la consulta y que no tenía en cuenta el resto de las columnas sobre las que se aplicaban funciones de agregado, pero por otro lado no me sorprendió porque realmente es lo mismo que hace cuando diariamente combinamos dos tablas: al procesar la instrucción, una de las fases es ver qué es lo que necesita y qué no para resolver la consulta, no cogiendo nada más que lo imprescindiblemente necesario.
Además, de este modo podemos comprobar lo importante que es pedir sólo lo que se va a usar. En muchas ocasiones hemos escuchado decir también que solicitan todos los campos de la tabla “por si acaso más adelante se necesitan”. Como si eso no implicara nada, ¿verdad?