¿Qué columnas añado a un índice y en qué orden?

Ya hicimos en este blog una serie de 7 artículos sobre índices que podéis consultar en la página índices dentro del menú de rendimiento. Sin embargo, nos faltaba algo muy importante y por lo que muchos me preguntáis: ¿Qué columnas añado a un índice y en qué orden?. Eso es lo que vamos a tratar de resolver hoy. Vaya por delante, antes de seguir leyendo, que esto de los índices no es algo exacto, seguramente la mayoría de las ocasiones deberéis hacer varias pruebas hasta dar con el resultado adecuado. ¿Os he hablado ya de la importancia de un entorno de pruebas? Pues eso, probad, fallad varias veces, pero no os resignéis que la solución está ahí y esto es así.

Índices faltantes

En los últimos post sobre problemas de rendimiento por alto consumo de RAM y CPU vimos que los índices jugaban un papel fundamental en esto del rendimiento. Si no tenemos los índices correctos nuestro SQL Server va a tener que hacer más lecturas de las necesarias provocando sobrecarga de E/S de disco además de sobrecargar RAM y CPU. Así lo hablamos, también, en los comentarios a uno de estos post en LinkedIn, donde mi buen amigo Óscar nos señalaba este hecho como el causante de nuestros problemas. Por suerte, SQL Server es capaz de detectar este problema y, aunque no siempre acierta, es un buen punto de partida.

Característica índice faltante de SQL Server

Cuando estamos ejecutando una consulta que podría mejorar con la creación de un índice, SQL Server nos mostrará un aviso (warning) de que nos falta un índice (missing index) en el componente raíz del plan de ejecución. Además, nos sugerirá el script de creación del mismo. Estos datos, además, se persisten y podemos ver todas estas sugerencias de creación de índices juntas en unas vistas de administración dinámica (DMV). El problema de estas sugerencias es que son para consultas individuales, es decir SQL dice que le falta un índice para optimizar el rendimiento de la consulta que está ejecutando sin medir el impacto en el resto de consultas.

Además tampoco sugiere de forma correcta el orden de las columnas ni analiza el coste/beneficio de las columnas que recomienda como incluidas. Tiene más limitaciones, como que solo propone índices nonclustered básicos, ni columnstore ni índices filtrados ni únicos, solo muestra un índice faltante por consulta y sobre todo, una de las mayores carencias para mi es que no nos muestra ni la consulta ni el plan de ejecución que ha generado la alerta. Esto último lo podemos compensar si tenemos activado el almacén de consultas (Query Store), en ese caso sí que podremos verlo. Pero bueno, ahora entendéis lo que os decía de que es un buen punto de partida, simplemente eso, no es una solución mágica que seguir al pie de la letra.

Mi guía definitiva para la creación de un buen índice

Disclaimer: Igual me he venido un poco arriba con el título y es simplemente una lista de recomendaciones, pero espero que seáis benévolos y me lo sepáis perdonar. 

Como decía, partiremos de la base de las sugerencias de creación de índices para detectar las debilidades de nuestra base de datos en este sentido y a partir de ahí empezaremos a trabajar.

Paso 1 Triaje

Es importante conocer las consultas que hacen un mayor uso de los recursos para poder empezar a optimizar por ahí y valorar si es o no necesario ese índice. Por lo general yo reviso juntas todas las sugerencias de índices de una misma tabla para con el mínimo número de índices cubrir la mayoría de escenarios. Recordad aquí las recomendaciones generales, una tabla no debería tener más de 5 o 10 índices.

Paso 2 Filtros de equidad y no equidad

Existen dos tipos de filtros en SQL Server, los filtros de equidad (un valor igual a otro con el que se quiere comparar) y los de inequidad (no igual, mayor que, menor que, etc…). En nuestro caso buscaremos primero los filtros (en el where o joins) de equidad. Esas columnas serán las primeras claves de nuestros índices. Las columnas sobre las que se realicen filtros de inequidad irán después.

Paso 3 Agrupados y ordenaciones

Otra de las cosas que tenemos que tener muy en cuenta son las columnas por las que se ordena o agrupa. En el primer caso, si siempre ordenamos por la misma columna igual es interesante que sea el índice clustered de la tabla y que nuestra PK sea nonclustered. Si realizamos muchas agrupaciones por la misma consulta valoraremos el uso de índices columnares, que nos darán mejor rendimiento en estas situaciones. Si no cumple con los requisitos para crear un índice de otro tipo propio pero aún así supone un coste alto en nuestras consultas valoraremos incluir estas columnas en el INCLUDE de nuestro índice.

Paso 4 Lecturas

Como ya vimos en la serie de artículos sobre los índices, SQL Server usará nuestro índice para filtrar y después se irá a la tabla a buscar el resto de campos que necesite para cubrir lo que le hemos pedido en el SELECT. Esto, por supuesto, no es lo más eficiente y un índice que cubra completamente la consulta es lo ideal. En una situación ideal los usuarios de la base de datos no usarían SELECT * y podríamos plantearnos crear índices que cubran completamente la consulta entre las columnas clave y las incluidas pero si no es el caso cuidado. Muchos datos en las columnas include de nuestro índice puede hacer que tenga un coste tan elevado de lectura que termine no usándose.

Paso 5 Orden

Llegamos a uno de los puntos clave a la hora del rendimiento de nuestros índices. SQL Server empezará a usar la estructura de árbol B de nuestro índice desde la primera columna en orden hasta la última. Si no filtramos por una de las columnas anteriores del índice, entonces ya no podremos hacer uso de esa SARGabilidad y nos encontraremos con un escaneo del nivel hoja del índice. Es decir, si nuestro índice incluye las columnas A, B y C en este orden y nuestra consulta no usa, por ejemplo la A siempre nos vamos a encontrar con un scan, lo mismo si filtramos por la A y la C pero no la B. No pasaría esto si filtramos por la columna A, la A y B o todas ellas.

Teniendo en mente esta capacidad de lectura a través de la estructura de árbol invertido podemos concluir que para un mejor rendimiento primero pondremos las columnas con mayor cantidad de datos distintos (que serán las más selectivas). 

Paso 6 Unicidad y filtrado

Es el momento de decidir aspectos extra de nuestro índice como si es además una restricción de unicidad o si es recomendable que esté filtrado. Este último caso es muy común en entornos donde en vez de borrarse los registros se usa un check de registro activo. En este caso, normalmente se filtra por registros activos y añadir este filtro al índice escribiendo solo los registros vivos puede reducir su tamaño sin afectar al rendimiento.

Conclusión

Las recomendaciones de missing indexes son una petición de socorro de nuestro SQL Server que nos dice que algo no va bien pero no debemos tomarlas al pie de la letra. Es nuestra responsabilidad analizar, validar y modificar estas sugerencias para adaptarlos a nuestro escenario. No tenemos que olvidar tampoco las vistas indexadas para tener índices que nos cubran consultas más complejas con uniones entre tablas. 

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 al que te puedes unir. ¡Hasta la próxima!