script - SQL Server: ¿Cuál es la diferencia entre la reconstrucción del índice y la reorganización del índice?
sys dm_db_index_physical_stats ejemplo (7)
"Reorganizar índice" es un proceso de limpieza, organización y desfragmentación del "nivel de hoja" del árbol B (en realidad, páginas de datos).
La reconstrucción del índice está cambiando todo el árbol B, recreando el índice.
Se recomienda que el índice se reorganice cuando la fragmentación del índice es del 10% al 40%; si la fragmentación del índice es superior al 40%, es mejor reconstruirlo.
La reconstrucción de un índice requiere más recursos, produce bloqueos y ralentiza el rendimiento (si elige mantener la tabla en línea). Por lo tanto, necesita encontrar el momento adecuado para ese proceso.
¿Cuál es la diferencia entre la reconstrucción del índice y la reorganización del índice?
Además de las diferencias anteriores (básicamente la reconstrucción creará el índice de nuevo y luego lo "intercambiará" por el existente, en lugar de tratar de corregir el existente), una consideración importante es que una reconstrucción, incluso una reconstrucción Enterprise ONLINE - interferirá con las transacciones de aislamiento de instantáneas.
TX1 inicia la transacción de instantáneas
TX1 lee desde T
TX2 reconstruye el índice en T
TX2 reconstrucción completa
TX1 vuelve a leer de T:
Error 3961, la transacción de aislamiento de instantánea falló en la base de datos porque el objeto al que accedió la declaración fue modificado por una declaración DDL en otra transacción concurrente desde el inicio de esta transacción. No está permitido porque los metadatos no están versionados. Una actualización simultánea a los metadatos puede llevar a una inconsistencia si se mezcla con el aislamiento de instantáneas.
Hay una serie de diferencias. Básicamente, la reconstrucción es una reconstrucción total de un índice: creará un nuevo índice, luego eliminará el existente, mientras que reorganizarlo simplemente, bueno ... lo reorganizará .
This entrada de blog que encontré hace un tiempo lo explicará mucho mejor de lo que puedo. :)
Piensa en cómo se implementa el índice. Generalmente es una especie de árbol, como un árbol B + o un árbol B-. El índice en sí se crea mirando las claves en los datos y construyendo el árbol para que la tabla pueda buscarse de manera eficiente.
Cuando reorganiza el índice, recorre el índice existente, limpiando los bloques para los registros eliminados, etc. Esto podría hacerse (y está en algunas bases de datos) cuando realiza una eliminación, pero eso impone una penalización de rendimiento. en su lugar, lo hace por separado para hacerlo más o menos en modo batch.
Cuando reconstruye el índice, borra el árbol existente y lee todos los registros, creando un nuevo árbol directamente a partir de los datos. Eso le da un nuevo árbol, y con suerte optimizado, que puede ser mejor que los resultados de reorganizar la tabla; también te permite regenerar el árbol si de alguna manera se ha corrompido.
Reconstrúyalo eliminando los índices actuales y recreando los nuevos.
Reorganizar es como poner la casa en orden con lo que ya tienes.
Es una buena práctica usar una fragmentación del 30% para determinar la reconstrucción frente a la reorganización.
<30% reorganizar vs.> 30% reconstruir
Reconstruir índice: reconstruye uno o más índices para una tabla en la base de datos especificada.
Reorganizar índice: desfragmenta los índices agrupados y secundarios de la tabla especificada
REBUILD bloquea la tabla durante todo el período de operación (que pueden ser horas y días si la tabla es grande).
REORGANIZE no bloquea la mesa.
Bien. en realidad, coloca algunos bloqueos temporales en las páginas con las que trabaja en este momento, pero se eliminan tan pronto como se completa la operación (que es una fracción de segundo para un bloqueo determinado).
Como señaló @Andomar , hay una opción para REBUILD un índice en línea, que crea el nuevo índice, y cuando se completa la operación, simplemente reemplaza el índice anterior por el nuevo.
Por supuesto, esto significa que debe tener suficiente espacio para conservar tanto la copia antigua como la nueva del índice.
REBUILD es también una operación DML que cambia las tablas del sistema, afecta las estadísticas, habilita los índices deshabilitados, etc.
REORGANIZE es una operación de limpieza pura que deja todo el estado del sistema como está.