SQL q & R: Historias de transacciones

Artículo
08/24/2016

Registros de transacciones son críticos a los esfuerzos de copia de seguridad, pero debe asegurarse de que están configurados correctamente y de compensación cuando se supone para borrar.

Paul S. Randal

Borrar los registros

**Q.**He leído que en el modelo de recuperación completa, transacción registro borrado sólo ocurre cuando hay una copia de seguridad del registro de transacciones. Estoy viendo un comportamiento extraño, sin embargo. A veces las copias de seguridad de registro no borrar el registro. Parece que las copias de seguridad de datos que lo están haciendo. ¿Qué pasa?

**A.**Está correcto: en los modelos de recuperación completa y de registro masivo, sólo se puede desactivar el registro de transacciones (partes del registro se marcan como reutilizables) cuando hay una copia de seguridad del registro de transacciones. En el modelo de recuperación simple, es un punto de control que borra el registro de transacciones. Para más antecedentes sobre el registro y recuperación en general, consulte mi artículo de TechNet "registro de comprensión y recuperación de SQL Server."

Sin embargo, hay una vuelta de tuerca. Aunque la operación de limpieza de registro de transacciones se producirá al final de una copia de seguridad del registro de transacciones, no hay ninguna garantía de que realmente se borrará cualquier porción del registro de transacciones. El hecho de que la copia de seguridad de una parte del registro de transacciones no es suficiente para dejar que se borrará. SQL Server no debe necesitar esa parte del registro de transacciones para cualquier otro propósito en cualquier otro momento.

SQL Server puede aún requieren acceso a una parte del registro de transacciones porque se está ejecutando una copia de seguridad de datos (como una copia de seguridad completa). Una copia de seguridad de datos debe incluir parte del registro de transacciones. Al menos tendrá los datos de registro de transacciones generados mientras se copian los datos de la base de datos. Incluso puede necesitar más.

Esto significa que mientras se está ejecutando una copia de seguridad de datos, registro de compensación no puede ocurrir. Esto es cierto incluso si lleva a cabo una copia de seguridad del registro de transacciones concurrentes (concurrentes backups de datos y de registro han sido posibles desde SQL Server 2005).

En este caso especial, la parte de copia de seguridad de SQL Server "recuerda" que ha sido una copia de seguridad del registro de transacciones y recuerda las partes del registro de transacciones fueron respaldadas. Una vez finalizada la copia de seguridad de datos simultáneas, SQL Server puede borrar las partes del registro de transacciones que fueron respaldadas por la copia de seguridad del registro de transacciones (siempre y cuando algo no necesita ese registro de transacciones, como una transacción sin confirmar, de larga duración).

Este comportamiento se denomina registro diferido de truncamiento. Este es el comportamiento que experimenta. Parece que la copia de seguridad de datos es borrar el registro de transacciones, pero en realidad es un artefacto de las copias de seguridad del registro de transacciones concurrentes.

Espejo, espejo

**Q.**Estamos en el proceso de aplicación de base de datos espejado para proteger contra la pérdida de datos para una de nuestras bases de datos. ¿Me puede decir lo que nos debemos supervisar para garantizar que nuestro reflejo de base de datos funciona correctamente?

**A.**La aplicación database mirroring, absolutamente debe supervisar el tamaño de la cola de envío y la cola de REHACER. Estos directamente relacionados con la pérdida de datos y el tiempo de inactividad, respectivamente.

La cola de envío realiza un seguimiento de cuánto de las transacciones de la base de datos principal aún no se ha enviado al servidor reflejado. Esta parte del registro de transacciones describe los cambios en la base de datos principal que se perderían si un desastre representa no está disponible.

Muchas personas piensan que si configura espejado de base de datos para seguridad alta o alta disponibilidad (también conocido como sincrónico espejado), la cola de envío siempre será cero. No se puede confirmar una transacción en la base de datos principal hasta que todos los registros de transacciones de la transacción se han enviado al servidor reflejado. Sin embargo, esto no es cierto. Es posible, en determinadas circunstancias, para los servidores principal y espejo perder el contacto entre ellos y la base de datos principal a permanecen en línea. En este caso, la cola de envío empieza a crecer. Esto aumenta la exposición a la pérdida de datos. Pérdida de datos sólo está garantizada cuando se sincroniza el estado espejado.

La cola de REHACER realiza un seguimiento de cuánto de las transacciones en el servidor espejo todavía no se hayan reproducido en la base de datos reflejada. Es un error común que, cuando el servidor principal envía el registro de transacciones para el servidor espejo, también inmediatamente es reproducido en la base de datos reflejada. Esto, además, no es cierto. Todo lo que se requiere es que el registro de transacciones se escribe en almacenamiento duradero en el servidor de réplica.

Esto significa que dependiendo el hardware del servidor espejo (incluyendo su subsistema de E/s, cualquier carga de trabajo simultáneo y otros factores que afectan el performance), puede haber una cola de registro de transacción importante que aún no ha sido reproducida en la base de datos reflejada.

Cuando hay un failover espejado, la base de datos reflejada no vienen en línea hasta que el registro de transacciones ha sido reproducido. Esto es proporcional a la cantidad de un-replayed de las transacciones. También representa el downtime de la aplicación y sus usuarios experimentará cuando se produce un failover espejado.

También debe ser cuidadoso con el tamaño de la cola de REHACER si vas a utilizar copias instantáneas de base de datos de la base de datos reflejada para generar informes. Cuando se crea una instantánea de base de datos, recuperación de base de datos debe procesar el registro de transacciones pendientes para que la instantánea de la base de datos es una vista transaccionalmente coherente de la base de datos subyacente. Cuanto mayor sea la cola de REHACER, más tiempo tardará en crear la instantánea de la base de datos. También afectará el rendimiento del servidor espejo, que puede hacer la cola de REHACER crecer.

Hay varias otras métricas que debe supervisar, tales como la latencia de la red entre los servidores principal y espejo. Esto se traduce en la sobrecarga por transacción de la aplicación de espejado sincrónico. Puede utilizar al Monitor de rendimiento (sys.dm_os_performance_counters vista de administración dinámica [DMV]) para realizar un seguimiento de estas métricas. También puede usar la herramienta Monitor de reflejo de base de datos integrada en Management Studio y se describe en Libros en pantalla de SQL Server. La herramienta le permite crear fácilmente alertas basadas en umbrales de tamaño de cola.

Mejoras en el rendimiento

**Q.**Me he informado que debo tengo varios archivos de datos para ayudar con el rendimiento, y entiendo por qué. Ahora estoy siendo dijo que también debo tener varios archivos de registro de transacciones para que SQL Server puede realizar operaciones de E/s más eficientes para el registro de transacciones. ¿Es correcto?

**A.**No, esto no es correcto. Por desgracia, me entero de esta recomendación cada cierto tiempo.

Varios archivos de datos pueden ayudar con la contención de subsistema de I/O. En algunos casos (comúnmente con tempdb), también pueden ayudar a contención en estructuras de asignación de base de datos en la memoria. Hay todo tipo de directrices sobre el número de archivos de datos para crear.

La recomendación de tener varios archivos de registro de transacciones es extrapolarse a partir de la misma recomendación para archivos de datos, pero no es correcta. Varios archivos de registro de transacciones no proporcionan ninguna ganancia en rendimiento, disponibilidad, escalabilidad o cualquier otra métrica mensurable.

El registro de transacciones es y debe ser secuencial en la naturaleza para que SQL Server no realizar E/s paralelo para el registro de transacciones si hay varios archivos de registro de transacción actuales. El primer archivo se utilizará en su totalidad, luego el segundo archivo, y luego el tercero y así sucesivamente. Esto será posible hasta que el registro de transacciones se ajusta y comienza de nuevo en el primer archivo de registro de transacciones. Así que los archivos de registro adicionales no proporcionan ninguna ganancia.

Hay una situación donde puede ser necesario un segundo archivo de registro de transacciones. Si el primer archivo de registro de transacciones está lleno, no se puede borrar el registro de transacciones (véase la respuesta a la primera pregunta para explicación adicional). El primer archivo de registro no puede crecer para dar cabida a más registros de transacción. En ese caso, añadiendo un segundo archivo de registro de transacciones temporalmente permitirá modificaciones de la base de datos continuar hasta el primer archivo de registro de transacciones puede borrar.

Reducir la E/s, aumento de la Performance

**Q.**Nosotros hemos estado investigando el uso de discos de estado sólidos (SSD) para tratar de reducir algunos de nuestros problemas de performance de I/O, pero estamos confundidos sobre qué bases de datos en ellos. ¿Nos puede dar alguna orientación sobre la mejor manera de usar SSD?

**A.**Esto es una pregunta interesante. Hay una variedad de respuestas "definitivas" verá en foros de ayuda de Internet, tales como, "siempre poner tempdb en su unidad de estado sólido" o "siempre poner los registros de transacciones en el SSD." Ninguno de estos es apropiado.

Hay algunos factores a tener en cuenta al considerar la mejor manera de usar SSD con SQL Server:

SSDs son caros, por lo que desee asegurarse de que obtiene el mejor rendimiento de la inversión de ellos.
SSDs proporcionan la mayoría mejor performance para cargas de I/O aleatorios, cargas de trabajo de I/O no secuenciales.
Para cualquier parte de un subsistema de E/s sobrecargado, un SSD ofrecerá un aumento de rendimiento — independientemente del patrón de I/O — debido a su naturaleza de reducir enormemente la latencia de lectura y escritura.
Conexión directa SSDs deberían proporcionar una mejora de rendimiento más profunda que el acceso a través de cualquier tipo de tejido de comunicaciones.
Registros de transacciones son de escritura secuencial, con principalmente lecturas secuenciales (y algunas lecturas aleatorias si hay mucho potencial para las transacciones de deshacer).
Tempdb puede utilizarse muy ligeramente en SQL Server. Incluso si se usan moderadamente, no pueden experimentar una alta cantidad de actividad de escritura de un archivo de datos.

Teniendo en cuenta estos factores, te das cuenta de que poner registros tempdb o transacción en su SSD no sea la mejor manera de utilizarlas. Identificar las partes del subsistema de E/s que son el mayor cuello de botella de rendimiento para su carga de trabajo. Bien pueden ser archivos de datos de una base de datos de volátiles transacciones en línea (OLTP) de procesamiento o un registro de transacciones de una base de datos con una carga de trabajo pesada insertar, o bien pueden ser tempdb. Estos son los candidatos para la puesta en el SSD, en lugar de elegir sólo una parte del almacenamiento de información de SQL Server sin realizar cualquier investigación.

Otra pieza de malos consejos acerca de SSDs es que puede dejar de estar preocupados acerca de fragmentación de índice cuando se usa SSDs para almacenar archivos de datos. Esto absolutamente no es el caso. Es cierto que se mitigar los efectos de lectura menos eficiente por delante de fragmentación un poco por el reducido enormemente Lee latencia, pero aún es el costo de E/s más del necesario. Se puede reducir por abordar la fragmentación.

Lo que no considera la mayoría de la gente es que fragmentación no es sólo de lectura por delante. Uno de los efectos secundarios de lo que provoca la fragmentación (operaciones llamadas "página divisiones") es que el porcentaje de espacio no utilizado en las páginas del archivo de datos puede aumentar considerablemente, de 40 por ciento a 50 por ciento (esto se denomina la "densidad de página").

Si una gran parte de los índices de bases de datos está fragmentada, entonces sin mantenimiento de índices regulares las páginas del archivo de datos va a contener mucho espacio vacío. Además de reducir la eficiencia de I/o y uso de la memoria, que también medios utiliza SSD caro para almacenar el espacio vacío. No es un buen retorno de la inversión en cualquier libro.

Paul Randal

**Paul S. Randal**es el director general de SQLskills.com, un director regional de Microsoft y un MVP de SQL Server. Trabajó en el equipo de motor de almacenamiento de SQL Server en Microsoft desde 1999 a 2007. Escribió DBCC CHECKDB y reparación para SQL Server 2005 y fue responsable por el motor principal durante el desarrollo de SQL Server 2008. Randal es un experto en recuperación ante desastres, alta disponibilidad y mantenimiento de base de datos y es una presentadora habitual en conferencias en todo el mundo. Blogs en SQLskills.com/blogs/paul y se le puede encontrar en Twitter en Twitter.com/PaulRandal.