Compartir a través de

SQL q & A: la historia de los registros

  • Artículo

Los registros de las transacciones son un componente fundamental de todas las instancias de SQL Server. Es imprescindible saber administrarlos correctamente y en forma eficiente.

Paul S. Randal

Rápido crecimiento

P. He sido correctamente utilizando la reconstrucción de índices en línea desde que se introdujo en SQL Server 2005. También he sido utilizando el modelo de recuperación de registro masivo para reducir la cantidad de crecimiento del registro de transacciones mientras la desfragmentación de índices. Hemos actualizado a SQL Server 2008 recientemente y ahora está creciendo el registro de transacciones más que nunca antes. ¿Puede explicar lo que está sucediendo?

**R.**Las operaciones de reconstrucción de índice anteriores a SQL Server 2005 siempre adquirir bloqueos para la duración de la operación de regeneración. Un índice agrupado significó un bloqueo exclusivo de tabla (concurrentes lectores ni escritores). Reconstrucción de índices no agrupados significó un bloqueo compartido (no hay editores simultáneas a la tabla).

Con la llegada de las operaciones de índice en la versión Enterprise Edition de SQL Server 2005, el bloqueo se cambió para operaciones en línea. Bloqueos sólo fueron retenidos por cortos períodos al principio y al final de la operación (consulte esta entrada de blog para obtener una explicación).

En SQL Server 2005, todas las operaciones de reconstrucción de índice utilizan un registro mínimo. Esto significa que se registran sólo las asignaciones de página, en lugar de todas las inserciones de fila en el nuevo índice. Esto reduce drásticamente la cantidad de registros de transacciones generados por la operación de regeneración de índice. También significa que la transacción propio archivo de registro no tiene que ser tan grande (porque no tiene que dar cabida a una operación de reconstrucción de índices registro completo). Este comportamiento sólo se produce al utilizar los modelos de recuperación de registro masivo o simple. Todas las operaciones se registran completamente en el modelo de recuperación completa, de ahí el nombre.

Desde SQL Server 2008, las operaciones de índice se cambiaron a se registran completamente en todos los modelos de recuperación. Esto garantiza la operación de restauración de la base de datos que abarca una operación de índice en línea en su registro de transacciones no tiene problemas. (Para obtener más información sobre esto, vea Microsoft artículo KB 2407439.)

Si limita el crecimiento del registro de transacciones es más importante que permite el acceso de la tabla concurrentes durante la reconstrucción de índices, lamentablemente debe volver a reconstrucciones de índices sin conexión para volver a ese comportamiento de un registro mínimo. Otra cosa a tener en cuenta es utilizando ALTER INDEX … REORGANIZAR para eliminar la fragmentación. Siempre funciona sin que el bloqueo y puede reducir la cantidad de registro de transacciones. (Hay una comparación de reconstrucción y reorganización de una de las siguientes respuestas.)

De forma masiva una copia de seguridad de su

P. Nos hemos intentado diseñar una estrategia de copia de seguridad que coincide con nuestro proceso de importación de datos cada noche para que nuestra copia de seguridad completa contiene todos los datos desde el proceso de importación. Nosotros no hemos podido averiguar de forma fiable para ello, a veces los datos están todo allí y a veces ninguno de los está ahí. ¿Podrían ayudarme?

**R.**Estado "a veces los datos están todos allí y a veces que ninguno de los está ahí". Que me hace pensar que está haciendo el proceso de importación como una transacción grande.

Si esto es lo que hace, considere la posibilidad de dividir en transacciones más pequeñas. Esto le dará SQL el bloqueo más potencial para borrar el registro de transacciones entre las transacciones de importación y menos a largo plazo. Un proceso de larga ejecución única transacción puede causar bloqueos en un bloqueo exclusivo de tabla. No puede cambiar el proceso de importación, pero debe hacerlo si es posible.

Una copia de seguridad tiene dos fases: leer los datos y leer el registro de transacciones. Cuando ha leído completamente los datos, a continuación, leerá el registro de transacciones desde que punto atrás como es necesario (consulte mi artículo de julio de 2009, "copias de seguridad de comprensión SQL Server," para obtener más detalles).

Al restaurar una copia de seguridad completa, el punto en que se restaura la base de datos es el tiempo en el que se completa la parte de lectura de datos de la copia de seguridad. La transacción incluida esencialmente en la copia de seguridad de registro ejecuta recuperación de fallos en la base de datos. Esto hace transaccionalmente coherente.

Tendrá que deshacer las transacciones durante el vuelo en ese momento, al igual que una recuperación normal. Si aún se está ejecutando el proceso de importación cuando la porción de datos de lectura completada, se deshacía durante la operación de restauración.

Es la única manera de garantizar una transacción en particular dentro de una copia de seguridad completa es serializar las operaciones para que la transacción en cuestión se completa antes de la copia de seguridad comienza. De lo contrario, no hay ninguna manera de saber si la transacción se completa antes de que finalice la lectura de datos.

Por supuesto, hay una alternativa a las operaciones de serialización. Realizar una copia de seguridad del registro de transacciones después de que finalice el proceso de importación. A continuación, restaure la copia de seguridad completa más la copia de seguridad del registro de transacciones. Contendrá la transacción del proceso de importación completa.

Para volver a generar o reorganizar

P. Estoy diseñar una estrategia de mantenimiento de índices. No entiendo por qué hay dos métodos para eliminar la fragmentación del índice. ¿Cómo puedo determinar si desea volver a generar nuestros índices o reorganizarlos? No se encuentra una lista de los equilibrios entre los dos métodos.

**R.**Ha habido dos métodos para quitar la fragmentación desde que escribí DBCC INDEXDEFRAG para SQL Server 2000. Los dos métodos son necesarios porque tienen características muy diferentes. Por desgracia, no hay ningún libro blanco que describe adecuadamente las diferencias entre los tres métodos en SQL Server 2005 adelante: ALTER INDEX … REORGANIZAR (el nuevo DBCC INDEXDEFRAG), ALTER INDEX … REGENERACIÓN (el nuevo DBCC DBREINDEX) y la versión en línea de ALTER INDEX … VOLVER A GENERAR.

Aquí es una comparación rápida de las opciones de ALTER INDEX RECONSTRUCCIÓN y REORGANIZACIÓN:

  • REGENERACIÓN requiere construir el nuevo índice antes de quitar el antiguo. Esto significa que debe haber suficiente espacio libre en la base de datos para alojar el nuevo índice; de lo contrario, la base de datos crecerá para proporcionar el espacio libre necesario. Esto puede ser problemático para los índices de gran tamaño. REORGANIZAR requiere solamente 8 KB de espacio adicional en la base de datos.
  • RECONSTRUCCIÓN puede utilizar varias CPU para que la operación se ejecute más rápido. REORGANIZAR es siempre único subproceso.
  • RECONSTRUCCIÓN puede requerir bloqueos a largo plazo en la tabla que puede limitar las operaciones simultáneas. REORGANIZAR no mantenga bloqueos (siempre es una operación en línea).
  • RECONSTRUCCIÓN puede utilizar un registro mínimo para reducir el crecimiento del registro de transacciones. REORGANIZAR siempre se registra, pero no impide que borrar el registro de transacciones.
  • RECONSTRUCCIÓN reconstruye automáticamente todas las estadísticas de columna de índice, mientras que REORGANIZAR no actualizan las estadísticas.

Por lo tanto, hay varias desventajas en términos de los requisitos de espacio de disco, de bloqueo, registro y paralelismo. Lo más importante a tener en cuenta es que las diferencias entre las dos operaciones algorítmicas.

La reconstrucción de índices siempre volverá a generar el índice completo, independientemente del grado de fragmentación. Esto significa una reconstrucción de índices para un índice fragmentado ligeramente realmente es excesiva. Reorganizar un índice sólo se ocupará de la fragmentación existente. Esto facilita una mejor opción para quitar la fragmentación de un índice fragmentado ligeramente, pero una mala elección para quitar la fragmentación de un índice muy fragmentado. En ese caso sería mejor simplemente volver a generar el índice.

Esto nos lleva a los diversos umbrales para elegir entre reconstrucción y reorganización. Existen umbrales de fragmentación ampliamente utilizado basados en la columna avg_fragmentation_in_percent en la salida de sys.dm_db_index_physical_stats:

  • porcentaje de 0 a 10 por ciento: no hacer nada
  • 10% al 30%: utilice ALTER INDEX … REORGANIZAR
  • 30 por ciento y superior: utilice ALTER INDEX … VOLVER A GENERAR

Estas directrices son generales y es posible que diferentes valores funcionan mejor en su entorno. También deberá tener en cuenta si se utiliza el reflejo de base de datos, lo que requiere el modelo de recuperación completa. Esto significa que las operaciones de reconstrucción de índices se registrarán completamente. Muchas personas consideran que esto produce demasiados registro de transacciones para enviar de forma eficaz entre la creación de reflejos principal y reflejado. En tales casos, puede ser mejor reorganizar índices para minimizar el registro de transacciones.

También puede considerar el uso de scripts de mantenimiento de índices de otra persona para ahorrar tiempo. Ola Hallengren tiene algunas secuencias de comandos completa y ampliamente utilizados.

Las subidas y bajadas de tamaño del registro

P. Sólo me he convertido en un DBA. Tengo problemas para configurar algunas nuevas bases de datos para nuestros desarrolladores. El problema que tengo es averiguar cómo debe ser el registro de transacciones. Tamaño en que elija, crece y permanezca de este modo. Si reducirlo, todavía crece con el mismo tamaño nuevo. ¿Hay alguna forma para establecer el tamaño correctamente al crear la base de datos?

**R.**En primer lugar, no reducir periódicamente el registro de transacciones. Cuando el registro de transacciones que se necesita para crecer, el nuevo espacio asignado al registro de transacciones debe inicializarse en cero. Esto significa que las transacciones que se espera para generar registros que tenga que esperar mientras esta inicialización tiene lugar.

Si estás en un shrink-grow, shrink crece ciclo con el registro de transacciones, está causando un rendimiento innecesario de SQL Server. Es mejor dejar el registro de transacciones en su tamaño necesario. Reducción debe ser un proceso poco frecuente en los registros de transacciones o de datos.

Puede estimar el tamaño de registro de transacciones basado en las operaciones de base de datos. Obtenga más información acerca de los que en mi columna de enero de 2011. Si el registro de transacciones crece a un tamaño constante después de cada shrink, probablemente es el tamaño debe ser. Dejarlo en ese tamaño, a menos que realmente es demasiado grande. Si es demasiado grande, determinar cuál es la causa del crecimiento y si puede dividir esa operación en varias partes para que el registro de transacciones tiene la posibilidad de borrar.

Paul S. Randal

Paul S. Randales el director de SQLskills.com, director regional de Microsoft y MVP de de SQL Server. Trabajó en el equipo de motor de almacenamiento de SQL Server de Microsoft de 1999 a 2007. Escribió DBCC CHECKDB/reparación para SQL Server 2005 y fue responsable del motor de almacenamiento de núcleo durante el desarrollo de SQL Server 2008. Randal es experto en recuperación ante desastres, alta disponibilidad y mantenimiento de bases de datos, y es moderador habitual en conferencias en todo el mundo. Blogs de él en SQLskills.com/blogs/paul, y puede encontrarlo en Twitter en Twitter.com/PaulRandal.

Contenido relacionado