unico - La mejor manera de implementar un nuevo índice en una tabla muy grande en SQL Server 2008
reconstruir indices sql server 2008 script (4)
Tengo una base de datos en producción con una tabla que se ha vuelto extremadamente grande (gran cantidad de datos acumulados).
Para mejorar el rendimiento de las consultas, utilicé el optimizador de servidor SQL que sugirió un nuevo índice.
Así que hice una copia de la base de datos de producción para probar y mejora el rendimiento, sin embargo, mi problema es que se tardaron aproximadamente 24 horas en crear el índice y mientras se crea el índice, la aplicación no se puede utilizar.
Para esta aplicación en particular, estar inactivo por unas pocas horas no es un problema, pero sí un tiempo de inactividad de 24 horas y estoy buscando una manera de crear este índice sin tener que hacerlo.
Solo tengo algunas ideas por el momento.
Una idea es copiar una copia de seguridad en otro servidor. Aplicar el nuevo índice y cualquier otro cambio. Copie la copia de seguridad de nuevo al servidor de producción. Baje la aplicación y fusione los datos nuevos desde que tomé la copia de seguridad.
Por supuesto, esto tiene su propio conjunto de problemas, como tener que volver a combinar los datos, por lo que no me gusta esta idea por esa razón.
Esta es la edición estándar de SQL Server 2008.
Normalmente implemento cambios en la base de datos por script.
ACTUALIZACIÓN: Otra idea sería mover los datos de archivo de la tabla principal durante varios días en bloques. Luego crea el índice cuando la tabla se vuelva lo suficientemente pequeña. Luego migran lentamente los datos de vuelta.
¿Por qué no particionar la tabla e indexar cada partición? De esta manera, solo está indexando en partes pequeñas y luego puede combinar las particiones más adelante.
Dada la falta de potencia de procesamiento disponible en la máquina VM, combinada con lo que sin duda es un rendimiento de IO bastante pobre, realmente consideraría calcular el tiempo para realizar una copia de seguridad, restaurar en un servidor medio decente, indexar y luego realizar una copia de seguridad / restaurar en la VM máquina.
Para evitar que la copia de seguridad inicial tarde mucho tiempo, puede hacer una copia de seguridad de un día y moverla durante el día, y luego, cuando se inicie la ventana de mantenimiento, haga una copia de seguridad del registro de transacciones y muévala de un lado a otro. movimiento. (Esto supone un modo de registro masivo / completo)
Otro enfoque puede ser no implementar los índices en todas las tablas sugeridas por el optimizador de servidor SQL, sino implementarlo primero en una tabla o grupo de tablas. Como mencionó, un par de horas de inactividad está bien, por lo que al usar estas pocas horas planifique las distintas tablas en las que se debe realizar la indexación. Ahora, seleccione diariamente aquellas tablas cuyos índices se pueden construir en el tiempo de inactividad dado. Trabajar inteligentemente puede resolver fácilmente este problema.
Se nos ocurrió el mismo escenario en el que pudimos obtener solo 1 hora de tiempo de inactividad al día e hicimos el mismo enfoque y en 9 días se crearon los nuevos índices y el tiempo de inactividad también se utilizó de manera efectiva.
Espero que esto ayude ...
Si estaba usando Enterprise, podría usar la opción ONLINE de CREATE INDEX que crea el índice sin mantener bloqueos a largo plazo en la mesa. Hay advertencias en torno a su uso; Consulte el artículo vinculado para obtener más información y es posible que el impacto en el rendimiento sea demasiado grande. Pero es académico como has dicho que estás usando estándar (perdón por faltar eso al principio).
El hecho de que sea una máquina virtual hace que uno piense inmediatamente en términos de "bombear" temporalmente la máquina virtual o incluso reubicarse temporalmente en una máquina virtual no virtual con un máximo de capacidad. Para reconstruir un índice en una tabla muy grande, creo que la velocidad de E / S y RAM serían los factores más importantes; ¿La VM está utilizando una unidad directamente o una unidad virtualizada? ¿Puede reubicar temporalmente los datos en una unidad física? Esa clase de cosas.
FWIW, su idea de "sacarlo fuera de línea" es exactamente lo que haría en una base de datos MySQL (nunca tuve que hacerlo en una base de datos de SQL Server): eliminar la base de datos principal, capturar una instantánea, eliminar los registros binacionales / habilita binlogging, y vuelve a activarlo. Hacer el índice en una máquina separada. Cuando esté listo, baje la base de datos, haga una copia de seguridad de la base de datos actualizada (por si acaso), vuelva a colocar la instantánea, aplique los binlogs y haga una copia de seguridad de la base de datos. Realmente es así de fácil; Espero que puedas hacer eso con SQL Server también. Por supuesto, ¡asume que puedes aplicar 24 horas de binlogs en la tabla (recientemente optimizada) dentro de una ventana de tiempo aceptable!