A lo largo de las pasadas 6 entradas hemos repasado todos los distintos tipos de índices en SQL Server. Sin embargo, hay aspectos que, aunque comentamos por encima en la introducción, no habíamos vuelto a profundizar y son los que vamos a ver. Durante estas líneas vamos a hablar sobre cuándo y cómo SQL Server hace uso de los índices. Además, veremos cómo se van llenando las páginas y cómo podemos influir nosotros en ello, un aspecto que afectará a la fragmentación. Por último aprenderemos cómo crear un plan de mantenimiento que solucione este problema.
Uso de índices y estadísticas
Sea cual sea el índice que creemos, SQL Server generará una estadística sobre él. De esta manera, podrá decidir si es recomendable buscar los registros a través de los niveles del árbol B, leer directamente el nivel de hoja en busca de los registros o recorrer enteramente la tabla y no usar el índice.
Cuando ejecutamos una consulta, SQL Server calculará distintos planes de ejecución con todas las opciones de recuperar los datos. En una tabla pequeña de menos de 10.000 páginas es fácil, se va a leer la tabla y no se usarán los índices nonclustered. Cuando tiene más, se usarán las estadísticas de los índices nonclustered para calcular el porcentaje de registros que se van a recuperar. En función de esos datos asignará un coste a cada uno de los planes de ejecución. Con todos los costes calculados se elegirá la mejor opción.
Llenado de índices y tablas
Hemos comentado en varias ocasiones que los datos en SQL se escriben en páginas. Vimos que en las tablas HEAP que los datos se escriben en el primer hueco libre disponible y, a la hora de editar, si el nuevo valor no cabe se mueve al final. Esto generaba punteros de reenvío y fragmentación.
En el caso de los índices clustered y nonclustered los datos se escriben en páginas igualmente. SQL Server intentará escribir el datos en su sitio pero, solo lo hará si hay sitio libre. Si no lo hay, lo escribirá en el espacio libre más próximo.
Fill Factor
Dado este comportamiento, para reducir al máximo la fragmentación y aprovechar el espacio de manera óptima podemos definir en los índices un nivel de llenado o Fill Factor. Este Fill Factor es un porcentaje que indicará al SQL cuánto espacio de las páginas de los índices se va a llenar de datos (no aplica para tablas HEAP). Por tanto, podremos controlar cuánto espacio se va a dejar libre al final de las páginas.
Podremos definir un nivel genérico en las propiedades de la instancia además de uno particular para cada índice si no queremos que use el genérico. Si no definimos otra cosa el fill factor será del 100%, es decir SQL tratará de llenar completamente las páginas. Esto puede ser una buena idea para índices en campos incrementales que nunca se editan. Sin embargo, si este no es el caso, llenar completamente las páginas nos generará tener que mover los datos o no escribirlos donde toca. En resumen, un mayor consumo de E/S y por tanto peor rendimiento.
Mantenimiento de índices
Acabamos de ver que muy probablemente nuestros índices se van a fragmentar con el uso, así que, como DBAs, es imprescindible que tengamos una buena política de mantenimiento que garantice que se encuentren en óptimas condiciones. Para ello tenemos varias alternativas, reorganizarlos o reconstruirlos. El método elegido dependerá en gran medida de la fragmentación que tenga el índice al momento de optimizarlo. Como norma general, reorganizaremos los índices con menos de un 30% de fragmentación y reconstruiremos los más fragmentados.
Además, tendremos que implementar también un mantenimiento de las estadísticas, como hemos visto son claves para que el motor de bases de datos calcule los costes de los planes de ejecución y decida por cuál de ellos decantarse. La reconstrucción de índices conlleva el mantenimiento de estadísticas, pero la reorganización no, por lo que tendremos que hacerlo por separado. Como os comenté en el artículo de las tareas diarias de un DBA, yo me decanto por la solución de olla hallengren.
Aunque un mantenimiento de las estadísticas es clave para el rendimiento de nuestra base de datos no tenemos que caer en el error de ejecutarlo con demasiada frecuencia.Cada vez que se actualizan estadísticas, todos los planes de ejecución que tenemos en la caché de SQL Server y que usan esas tablas se van a desechar. Esto provocará que la próxima ejecución de la consulta necesite recompilar el plan, calcular todas las opciones y elegir el de menos coste. Para esto, la solución de ola, tiene un parámetro para solo actualizar las estadísticas que hayan sufrido modificaciones desde la última actualización, lo que liberará a SQL Server de carga de trabajo innecesaria.
Conclusión
Dado que con este artículo cerramos la serie de índices, permitidme que por una vez me extienda un poco más en la conclusión y recapitulemos todo lo visto. Primero de todo quiero daros las gracias a vosotros, habéis llevado las páginas de esta serie a las primeras posiciones de las estadísticas de visitas del blog en muy pocos días.
Ahora ya sí, entremos en materia. Empezamos esta serie con una introducción donde vimos las estructura de las tablas en SQL Server y descubrimos la importancia de los índices.
Después analizamos los distintos tipos de índices que podemos usar en SQL Server, desde la ausencia de ellos en una tabla HEAP, índices clustered, nonclustered, columnares y, hasta los índices especiales (únicos, filtrados, espaciales, XML, hash y de texto completo). De todos ellos vimos cómo funcionan, para qué se usan y cuándo son recomendables.Todo este conocimiento adquirido durante los últimos días nos ha traído hasta esta última entrada donde hemos entendido mejor la fragmentación de los índices y cómo combatirla. Además hemos podido comprender la importancia de las estadísticas y su papel clave en el rendimiento de nuestro SQL Server.
Seguro que los más expertos echaréis en falta algún que otro aspecto relacionado con los índices o que entremos más en detalle sobre alguno de los temas tratados, os prometo que habrá más artículos sobre índices en un futuro aunque sea ya fuera de esta serie.
¡Espero que os haya gustado esta serie y hayáis aprendido cosas nuevas!
