¿Cómo reducir el costo de un LookUp de SQL Server?

Durante la exposición de nuestra charla de como optimizar consultas de SQL Server, hice bastante hincapié en la relación/dependencia de los índices no agrupados (no clustered) y el agrupado (clustered) de un tabla. En caso de que ésta no cuente con un índice agrupado, existe una relación/dependencia similar pero con una estructura interna de la tabla (RID).

En este post veremos qué es un LookUp (SQL 2005) o Bookmark LookUp (SQL 2000) y cómo eliminarlos o al menos mitigarlos cuando se pueda. Usaré SQL 20005, pero si tienes 2000, la manera de enfrentarlo es similar.

Antes de ir a la información del caso, haremos un pequeño repaso de lo revisado en la charlas de optimización de consultas.

Relación/dependencia entre índices en una tabla

Un índice no agrupado almacena, por cada elemento del índice, un “identificador” que dice donde encontrar en la tabla los datos asociados a este ítem del índice. De esta forma, cada vez que SQL Server utiliza el índice no agrupado y determina que debe ir a buscar los datos, utiliza ese “identificador” para ubicar los datos en la tabla.

Si la tabla no tiene un índice agrupado, el identificador utilizado es el identificador de la fila o row id (en inglés), el cual se representa con las siglas RID. Este RID está compuesto por el numero del archivo de la base de datos, el número de la página dentro de ese archivo y por último, el número de la fila en la página.

Si la tabla sí tiene un índice agrupado, el identificador utilizado es la llave del índice agrupado. La llave del índice agrupado son TODAS las columnas del índice agrupado. Por favor, no confundir la llave del índice con la llave primaria de una tabla, aunque muchas veces tienen el mismo “valor,” en especial cuando la tabla se creó sin darle mayor importancia a este tema.

Para más información, revisa el BOL (Books On Line) de SQL Server, en la instrucción Create Index.

Bookmark LookUp o LookUp a secas

En SQL Server 2000 se le conoce como Bookmark LookUp. En la versión 2005 se llama solamente LookUp. La idea es la misma para ambas versiones; El problema es el mismo, las soluciones similares.

LookUp, traducido al español, significa operación de búsqueda, y representa exactamente eso. Realiza una operación de búsqueda, con un “identificador” de una fila, para traer más información, de la fila específica, desde la tabla.

La búsqueda se hace, como ustedes se imaginan, utilizando el RID si la tabla no tiene un índice agrupado, o utilizando la llave del índice agrupado, si la tabla cuenta con uno de ese tipo. La siguiente imagen sirve para aclarar el concepto que se trata de explicar.

Utilizando el índice no agrupado, primero se identifica las filas que cumplen con la condición de filtrado. Resultado: Celdas amarillas.     Se usan los tres identificadores de filas (rojo, verde y azul) y se realiza la operación de búsqueda en el índice agrupado. Si la tabla no tiene índice agrupado, la operación es similar, pero usa el RID. Se retornan los datos faltantes.

Veamos entonces ahora información del caso. En esta oportunidad reproduciré el escenario en una base de datos de prueba, con unas tablas creadas para la ocasión.

LookUp muy costoso

Nota: Hace un tiempo atrás estaba en un curso de optimización de SQL Server y le hice la siguiente pregunta a la instructora: “Si tengo el plan de ejecución de una consulta, y el operador de lookup es responsable de casi el 100% del costo del plan, ¿cómo puedo mejorar el rendimiento de mi consulta?.”

Tristemente, no hubo una respuesta aclaratoria ante mi pregunta, como ocurrió en varias otras además. Hoy en día, retrospectivamente, pienso que la instructora era lo que nosotros acá en Chile llamamos “un Adán”, de Adán y Eva. ¿Por qué?. Porque si a Adán le sacas la hojita, queda desnudo. En este caso, la “hojita” es el “libro del curso.”

Si has estado tratando de optimizar consultas de SQL, es probable que ya te hayas topado con este problema. ¿Qué hacer cuando la operación LookUp es muy costosa?

Antes de comenzar, estructura de tablas

Para este ejemplo, utilizaremos dos tablas, t1 y t2, con la siguiente estructura cada una

La tabla t1 tiene 100.000 registros. La tabla t2 tiene 1.000.000 registros. La consulta que estamos realizando es la siguiente.

Con esta consulta, estamos trayendo un cantidad importante de columnas de la tabla t2, como también las dos columnas de la tabla t1. Existe un índice en t2.c2, por lo tanto, el filtro between 1000 and 2000 se realizará utilizando el índice para reducir las filas.

El siguiente es el plan de ejecución generado, en versión texto y gráfico.

Table ‘t2’. Scan count 1, logical reads 30682, physical reads 0, …

Table ‘t1’. Scan count 1, logical reads 6, physical reads 0, …

Rows    Executes  StmtText

——————————————————————————————-

561     1         |–Merge Join(Inner Join, MERGE:([test].[dbo].[t1].[c1])=([test].[dbo].[t

1001    1               |–Clustered Index Seek(OBJECT:([test].[dbo].[t1].[PK_t1]), SEEK:([

561     1               |–Filter(WHERE:([test].[dbo].[t2].[c3] like ‘2%’ AND [test].[dbo].

10010   1                    |–Nested Loops(Inner Join, OUTER REFERENCES:([test].[dbo].[t2

10010   1                         |–Index Seek(OBJECT:([test].[dbo].[t2].[idx_c2]), SEEK:(

10010   10010                     |–Clustered Index Seek(OBJE…]) LOOKUP ORDERED FORWARD)

Si miramos la versión gráfica, podremos observar que el 100% del costo del plan esta en la operación lookup. Notar que hemos marcado algunos textos en blanco arriba, los cuales revisaremos más adelante. Para ver el plan gráfico más grande, haz clic sobre él.

En el plan gráfico, podemos ver que la operación Key Lookup es responsable del 100% del costo de plan, con un costo de 22,06 (muy malo para un operador, y en consecuencia para el plan). Complementando con el plan de texto, en éste aparece la palabra LOOKUP, y está usando el índice agrupado (clustered) para recuperar las demás columnas que necesita.

Además, se realizan más de 30.000 lecturas sobre la tabla t2. 

¿Donde está el problema?¿Por qué tiene un costo tan elevado?

Una única operación de LookUp es de bajísimo costo, más aún si utiliza un índice para hacer un seek a una fila específica en la tabla. Sin embargo, si se ejecuta 10.010 veces, la sumatoria de estos costos pequeñísimos pasa a tener relevancia, como es nuestro caso.

La cantidad de veces que se ejecuta la operación LookUp se puede ver en 2 partes. Una de ellas es el texto marcado con blanco en el plan de ejecución de texto y la otra es en el Tooltip amarillo, en “Actual Number of Rows.”

Solución[es] al problema

Existen dos formas de enfrentar este problema, y por supuesto, dependen de cada escenario.

Una de ellas, y que no veremos ahora porque no tienen ninguna gracia, es agregar todas las columnas necesarias al índice, y con eso, evitar la operación LookUp. Si tengo toda la información en el índice, no necesito ir a buscar más columnas a la tabla. Esta no falla nunca, pero uno debe evitar incluir muchas columnas en los índices.

La segunda solución, y que es mucho más elegante, sólo es posible realizar en un escenario como el que tenemos ahora.

Si miran con detenimiento el plan de ejecución, el operador filter está filtrando en las columna c3 y c4, aplicando los criterios definidos en la consulta SQL. Esto es esperable. Más aún, como resultado del filtrado, la cantidad de filas retornadas disminuye (anchos de las flechas de entrada y de salida del filter). Esto también es esperable, ¿o no?

Si todo es esperable, ¿dónde está la gracia?

El costo del operador LookUp es muy alto porque se ejecuta muchas veces, ¿verdad?. ¿Qué sucedería si logro reducir la cantidad de veces que deba ejecutarse? Seguramente el costo de la consulta será menor y el rendimiento de ésta mucho mayor.

Para reducir la cantidad de veces que se ejecuta, debo reducir la cantidad de filas que son retornadas luego de que se filtre utilizando el índice idx_c2. Para reducir la cantidad de filas retornadas, agrego una columna más al índice, la que más me ayude a filtrar.

Debido a que la selectividad de la columna c3 es mejor que la de c4, incluiré sólo la columna c3 en el índice idx_c2. Con eso, reduciré la cantidad de filas que requerirán un lookup de más información. Recuerden que estamos tratando de minimizar el costo del LookUp. Si se quiere suprimir totalmente, se deberán agregar todas las columnas necesarias al índice, solución que deberá dejarse sólo para casos ultra-extremos.

Veamos el plan de ejecución de texto de la misma consulta, pero con el índice idx_c2 incluyendo a la columna c3 además de la c2 que ya tenía.

Table ‘t2’. Scan count 1, logical reads 3332, physical reads 0, …

Table ‘t1’. Scan count 1, logical reads 6, physical reads 0, …

Rows    Executes  StmtText

——————————————————————————————-

561     1         |–Merge Join(Inner Join, MERGE:([test].[dbo].[t1].[c1])=([test].[dbo].[t

1001    1               |–Clustered Index Seek(OBJECT:([test].[dbo].[t1].[PK_t1]), SEEK:([

561     1               |–Filter(WHERE:([test].[dbo].[t2].[c4] = (1)))

1075    1                    |–Nested Loops(Inner Join, OUTER REFERENCES:([test].[dbo].[t2

1075    1                         |–Index Seek(OBJECT:([test].[dbo].[t2].[idx_c2]), SEEK:(

1075    1075                      |–Clustered Index Seek(OBJE…]) LOOKUP ORDERED FORWARD)

Ahora sólo ejecutó 1.075 veces el LookUp. No nos hemos deshecho de él, pero al menos hemos reducido su impacto en el costo de la consulta.   Las lecturas lógicas fueron reducidas desde más de 30.000 a sólo 3.332. Una excelente optimización. El nuevo costo del operador LookUp es 3,58, como se puede observar a la derecha en el Tooltip. El costo antes de modificar el índice era de 22,06. Otra excelente reducción en el costo.

Conclusión

Hemos visto como minimizar el impacto de la operación LookUp en SQL Server. No vimos como eliminarlo ya que para lograr eso, el camino es directo y simple. A veces no es necesario eliminarlo para mejorar drásticamente el rendimiento de una consulta.

Hasta la próxima, desde Santiago de Chile
Patrick.