Solución:
A riesgo de ser demasiado general en mi respuesta, diré que debe ejecutar un proceso de mantenimiento de índices con regularidad. Sin embargo, su proceso de mantenimiento de índices solo debe reconstruir / reorganizar los índices que lo requieran específicamente.
Esto presenta la pregunta: ¿cuándo es necesario reconstruir o reorganizar un índice? Rolando tocó esto muy bien. Una vez más, corro el riesgo de ser extremadamente amplio. Un índice requiere mantenimiento cuando el nivel de fragmentación afecta negativamente al rendimiento. Este nivel de fragmentación podría variar según el tamaño y la composición del índice.
Hablando de SQL Server, tiendo a elegir un tamaño de índice y un nivel de fragmentación de índice en el que comienzo a realizar el mantenimiento del índice. Si un índice contiene menos de 100 páginas, no realizaré ningún mantenimiento.
Si un índice está fragmentado entre un 10% y un 30%, lo haré REORGANIZE el índice y UPDATE las estadísticas. Si un índice está fragmentado en más del 30%, REBUILD el índice – sin UPDATE STATISTICS, ya que de esto se encarga el REBUILD. Sin embargo, recuerde que una reconstrucción solo actualiza el objeto de estadísticas directamente asociado con el índice. Otras estadísticas de columna deberán mantenerse por separado.
Esta respuesta es realmente solo un largo camino para decir: Sí, debe realizar un mantenimiento de índice de rutina, pero solo en los índices que lo necesitan.
¿Cuándo debería reconstruir los índices en mi base de datos relacional (por ejemplo, SQL Server)?
Debería reconstruir los índices cuando se vuelvan muy fragmentados por eventos especiales. Por ejemplo, realiza una gran carga masiva de datos en una tabla indexada.
¿Existe un caso para reconstruir índices de forma regular?
Entonces, ¿qué pasa si sus índices se fragmentan de forma regular debido a la actividad regular? ¿Debería programar reconstrucciones regulares? ¿Con qué frecuencia deberían correr?
Tom Kyte, en este clásico hilo de Ask Tom, recomienda:
El lapso de tiempo entre reconstrucciones de índices debe ser aproximadamente PARA SIEMPRE.
…
No sé cómo decirlo mejor: el índice quiere ser grande y gordo con espacio adicional. Está en una columna que actualiza, moviendo la entrada del índice de un lugar a otro en el índice. Un día la fila tiene un código de “A”, al día siguiente el código es “G”, luego “Z”, luego “H” y así sucesivamente. Entonces, la entrada de índice para la fila se mueve de un lugar a otro en el índice. Mientras lo hace, necesita espacio; lo hará, si el espacio no está allí, dividimos el bloque en dos, y hacemos espacio. Ahora el índice está engordando. Con el tiempo, el índice es de 2 a 3 veces el tamaño que tenía cuando comenzó y está “la mitad o más vacío”. Pero eso está bien, ya que mueve filas. Ahora, cuando movemos las filas, ya no tenemos que dividir bloques para hacer espacio: el espacio ya está disponible.
Luego viene y reconstruye o suelta y recrea el índice (que tiene los mismos efectos, solo que la reconstrucción es “más segura”) no tiene ninguna posibilidad de perder el índice y puede ser más rápido ya que el índice puede ser reconstruido por escanear el índice existente en lugar de escanear la tabla y ordenar y construir un índice nuevo). Ahora, todo ese lindo espacio se ha ido. Comenzamos el proceso de dividir los bloques de nuevo, llevándonos de regreso a donde comenzamos.
No guardaste espacio.
El índice ha vuelto a ser el mismo que antes.
Estaría perdiendo el tiempo para reconstruirlo nuevamente, lo que provocaría que este círculo vicioso se repita.
La lógica aquí es sólida, pero está sesgada contra un perfil de carga de lectura pesada.
Un índice “gordo” (es decir, uno con muchos espacios) de hecho mantiene una buena cantidad de espacio para filas nuevas y movidas, lo que reduce las divisiones de página y mantiene sus escrituras rápidas. Sin embargo, cuando lea de ese índice gordo, tendrá que leer más páginas para obtener los mismos datos porque ahora está examinando más espacio vacío. Esto ralentiza sus lecturas.
Por lo tanto, en las bases de datos de gran lectura, desea reconstruir o reorganizar regularmente sus índices. (¿Con qué frecuencia y en qué condiciones? Matt M ya tiene una respuesta concreta a esta pregunta). En las bases de datos que experimentan una actividad de lectura y escritura aproximadamente equivalente, o en bases de datos con una gran cantidad de escritura, es probable que esté perjudicando el rendimiento de su base de datos al reconstruir índices regularmente.
La mayoría de las personas los reconstruyen de forma regular para que nunca se fragmenten. El momento en que necesita reconstruirlos se basa en la rapidez con que se fragmentan. Algunos índices deberán reconstruirse con frecuencia, otros básicamente nunca. Echa un vistazo a la secuencia de comandos que SQLFool armó y que se encarga de resolver muchas cosas por ti.