Este es el primer artículo (espero que no el último) de una serie dedicada a cuestiones que no se explican habitualmente en los cursos de SQL Server…
… o que has leído en la documentación y tu mente ha decidido olvidarlo…
… o es algo que prefieres ignorar porque tu vida es más fácil así…
… o que la gente repite y repite y nadie se plantea en revisar…
De cualquier forma, ¡bienvenido!
Missing indexes y el orden de las columnas clave en SQL Server
Missing indexes
La documentación oficial de Microsoft dice:
“Cuando el optimizador de consultas genera un plan, analiza cuáles son los mejores índices para una determinada condición de filtro. Si los mejores índices no existen, el optimizador sigue generando un plan usando los métodos de acceso menos costosos disponibles, pero también almacena información sobre esos índices. La función Missing Indexes permite acceder a esa información para decidir si deben implementarse.”
Para obtener la información de los missing indexes disponemos de varias opciones:
- Planes de ejecución: Se pueden obtener en SQL Server Management Studio (tanto gráfica como XML), Query Store o la DMV sys.dm_exec_text_query_plan.
- Combinando la información de DMVs como sys.dm_db_missing_index_details, sys.dm_db_missing_index_groups, sys.dm_db_missing_index_columns o sys.dm_db_missing_index_group_stats.
Y efectivamente, en la documentación oficial, se indican las limitaciones que tiene esta característica. Pero sea como sea, son tantas que convierten la información ofrecida más en una alerta por parte del optimizador de consultas que en una sugerencia de índice correcta que se puede aplicar directamente.
Entre las limitaciones más olvidadas/ignoradas están las siguientes:
- Las columnas clave se indican, pero el orden de las mismas no atiende a criterios de rendimiento.
- Las columnas en la sección INCLUDE también se sugieren, pero no se realiza un análisis coste/beneficio relativo al tamaño del índice frente al beneficio aportado.
- Sólo se muestra una sugerencia de Missing Index por plan aunque haya varias sugerencias posibles. Además el índice sugerido puede no ser el que aporte mayor beneficio para la consulta.
Es decir, podemos recibir una sugerencia de índice con un orden que no priorice las columnas más selectivas o con secciones INCLUDE kilométricas (y que aporten poco beneficio en relación al tamaño del índice) o que se nos proponga un índice que no sea el que mayor beneficio aporte a la consulta.
Lo dicho, es un grito de ayuda del optimizador de consultas, nuestro trabajo consiste en optimizarlo.
Orden de las columnas clave
¿De dónde se obtienen las columnas clave de un índice? Normalmente son aquellas por las que se filtra la consulta, como en las condiciones de:
– JOINS
– WHERE
Las columnas asociadas a la parte INCLUDE del índice vienen de los operadores:
– SELECT
– GROUP BY
– ORDER BY
En ocasiones, las columnas que vienen de ORDER BY o GROUP BY pueden dar mejores resultados como columnas clave (nos podemos ahorrar algún SORT de más con esta técnica).
La regla general para el orden de las columnas en un índice es:
1) Predicados de igualdad (equality) =.
2) A continuación, predicados de desigualdad (inequality) >, >=, <, <=, <>
3) Poner toda la lista de columnas de SELECT (y ORDER BY o GROUP BY) como columnas INCLUDE.
Ejemplo de orden de las columnas clave en SQL Server con Missing indexes
En la consulta:
SELECT column_a, column_b, column_c, column_d
FROM table_1
WHERE column_a = 10000
AND column_b < 20000
ORDER BY column_b, column_a
El índice recomendado (si existe), siguiendo las reglas anteriores sería:
CREATE NONCLUSTERED INDEX IX_index_1 ([column_a], [column_b]) on table_1
INCLUDE ([column_c], [column_d])
Es decir, primero columna EQUALITY ([columna_a]), luego INEQUALITY ([columna_b]) y el resto de valores requeridos en el SELECT pasan a INCLUDE ([column_c] y [columna_d]).
Si se cambiara la consulta modificando la condición equality por inequality:
SELECT column_a, column_b, column_c, column_d
FROM table_1
WHERE column_a > 10000
AND column_b = 20000
ORDER BY column_b, column_a
El índice recomendado cambiaría el orden de las columnas (a por b):
CREATE NONCLUSTERED INDEX IX_index_1 ([column_b], [column_a]) on table_1
INCLUDE ([column_c], [column_d])
¿Y si ambas condiciones fueran del mismo tipo (equality o inequality)? ¿Cuál sería el orden?
SELECT column_a, column_b, column_c, column_d
FROM table_1
WHERE column_a = 10000
AND column_b = 20000
ORDER BY column_b, column_a
Los índices propuestos podrían ser:
CREATE NONCLUSTERED INDEX IX_index_1 ([column_b], [column_a]) on table_1
INCLUDE ([column_c], [column_d])
-O-
CREATE NONCLUSTERED INDEX IX_index_1 ([column_a], [column_b]) on table_1
INCLUDE ([column_c], [column_d])
Dependiendo de la ordenación de las columnas en sys.columns. No se buscan los valores más selectivos, no se sigue el orden de los predicados en la consulta, sino que la columna que aparece en primer lugar en sys.columns será la primera en el índice.
Y efectivamente, esto significa que esa recomendación que ofrece el optimizador no tiene por qué ser eficiente ni tiene por qué usarse.
H8, agua.
Seleccionar el orden correcto de las columnas clave en SQL Server
La recomendación clásica es ordenar por las columnas más selectivas.
La manera más sencilla es contar cuántos valores distintos hay en cada columna. Cuanto más alto es el valor, más selectiva es la columna.
select count(distinct [column_a]) from table_1
select count(distinct [column_b]) from table_1
B6, tocado… pero no hundido. Esta recomendación es válida en muchos casos (en la mayoría) pero no en todos.
Si esa primera columna es un valor IDENTITY (que no define necesariamente valores de búsqueda utilizables) o si los datos en la columna no se distribuyen de forma uniforme o si esa primera columna no tiene el atributo UNIQUE o si hay otras operaciones en la consulta que aumentan su complejidad (los ejemplos anteriores son muy sencillos y no involucran operaciones complejas) el orden definido por esta recomendación puede no ser el más adecuado.
En resumen, para obtener el orden adecuado en las columnas clave hay que probar, volver a probar y por último, probar de nuevo. Hay pautas generales pero no son verdades absolutas.
Ahora sí C6, hundido.
Conclusiones
Las recomendaciones de Missing Indexes son alertas del optimizador indicando que falta un índice en la consulta y no deben ser aplicadas sin una revisión previa.
En este primer artículo se revisan los criterios de ordenación de las columnas en los índices sugeridos y se indican estrategias de evaluación de los mismos.
Agradecimientos
Todo el mérito de este artículo es de Aaron Bertrand (https://www.mssqltips.com/sqlservertip/5559/be-careful-with-key-order-in-sql-server-missing-index-recommendations/), Erik Darling (https://erikdarling.com/selectivity-for-what/) y Brent Ozar (https://www.brentozar.com/archive/2017/08/missing-index-recommendations-arent-perfect/).
