Descripción de los algoritmos de registro y almacenamiento de datos que amplían la confiabilidad de los datos en SQL Server

Versión original del producto: SQL Server 2014, SQL Server 2012, SQL Server 2008, SQL Server 2005
Número de KB original: 230785

Resumen

En este artículo se describe cómo los algoritmos de registro y datos de Microsoft SQL Server amplían la confiabilidad y la integridad de los datos.

Para obtener más información sobre los conceptos subyacentes de los motores y sobre el algoritmo para la semántica de recuperación y explotación de aislamiento (ARIES), consulte el siguiente documento Transacciones de ACM en sistemas de base de datos (en el volumen 17, número 1, marzo de 1992):

Vínculo externo: ARIES: un método de recuperación de transacciones que admite Fine-Granularity bloqueo y reversiones parciales mediante el registro de Write-Ahead

El documento aborda las técnicas de SQL Server para ampliar la confiabilidad y la integridad de los datos en relación con los errores.

Se recomienda leer los siguientes artículos en Microsoft Knowledge Base para obtener más información sobre el almacenamiento en caché y las discusiones alternativas sobre el modo de error:

• Descripción del almacenamiento en caché de controladores de disco en SQL Server

• Información sobre el uso de cachés de unidades de disco con SQL Server que todos los administradores de bases de datos deben conocer

Términos usados en este artículo

Antes de comenzar la discusión en profundidad, algunos de los términos que se usan a lo largo de este artículo se definen en la tabla siguiente.

Protocolo de registro de Write-Ahead (WAL)

El término protocolo es una excelente manera de describir WAL. Es un conjunto específico y definido de pasos de implementación necesarios para asegurarse de que los datos se almacenan e intercambian correctamente y se pueden recuperar a un estado conocido si se produce un error. Al igual que una red contiene un protocolo definido para intercambiar datos de forma coherente y protegida, el WAL también describe el protocolo para proteger los datos.

El documento de ARIES define el WAL de la siguiente manera:

El protocolo WAL afirma que los registros que representan cambios en algunos datos ya deben estar en un almacenamiento estable antes de que se permita que los datos modificados reemplacen la versión anterior de los datos en el almacenamiento no volátil. Es decir, el sistema no puede escribir una página actualizada en la versión de almacenamiento no volátil de la página hasta que al menos las partes de deshacer de los registros, que describen las actualizaciones de la página se han escrito en un almacenamiento estable.

Para obtener más información sobre el registro de escritura anticipada, consulte el tema Registro de transacciones de escritura anticipada en SQL Server Libros en pantalla.

SQL Server y wal

SQL Server usa el protocolo WAL. Para asegurarse de que una transacción se confirma correctamente, todos los registros asociados a la transacción deben protegerse en un almacenamiento estable.

Para aclarar esta situación, tenga en cuenta el siguiente ejemplo específico.

BEGIN TRANSACTION
 INSERT INTO tblTest VALUES (1)
COMMIT TRANSACTION

A continuación, divida la actividad en pasos de registro simplistas, como se describe en la tabla siguiente.

No se confunda con los términos "bloqueo" y "registro". Aunque importantes, el bloqueo y el registro son problemas independientes cuando se trata con WAL. En el ejemplo anterior, SQL Server suele contener el bloqueo temporal en la página 150 durante el tiempo necesario para realizar los cambios de inserción física en la página, no todo el tiempo de la transacción. Se establece el tipo de bloqueo adecuado para proteger la fila, el intervalo, la página o la tabla según sea necesario. Consulte las secciones de bloqueo de SQL Server Libros en pantalla para obtener más información sobre los tipos de bloqueo.

Si examina el ejemplo con más detalle, puede preguntar qué ocurre cuando se ejecutan los procesos LazyWriter o CheckPoint. SQL Server emite todos los vaciados adecuados para el almacenamiento estable de los registros transaccionales asociados a la página desfasada y anclada. Esto garantiza que la página de datos del protocolo WAL nunca se pueda escribir en un almacenamiento estable hasta que se hayan vaciado los registros transaccionales asociados.

SQL Server y almacenamiento estable

SQL Server mejora las operaciones de registro y página de datos al incluir el conocimiento de los tamaños del sector de disco (normalmente 4096 bytes o 512 bytes).

Para mantener las propiedades ACID de una transacción, el SQL Server debe tener en cuenta los puntos de error. Durante un error, muchas especificaciones de unidad de disco solo garantizan un número limitado de operaciones de escritura en el sector. La mayoría de las especificaciones garantizan la finalización de una escritura de un solo sector cuando se produce un error.

SQL Server usa páginas de datos de 8 KB y el registro (si se vacía) en múltiplos del tamaño del sector. (La mayoría de las unidades de disco usan 512 bytes como tamaño predeterminado del sector). Si se produce un error, SQL Server puede tener en cuenta las operaciones de escritura más grandes que un sector mediante el empleo de paridad de registros y técnicas de escritura desgarradas.

Detección de páginas rasgadas

Esta opción permite SQL Server detectar operaciones de E/S incompletas causadas por errores de energía u otras interrupciones del sistema. Cuando es true, hace que se voltee un poco para cada sector de 512 bytes en una página de base de datos de 8 kilobytes (KB) cada vez que la página se escribe en el disco. Si un bit está en el estado incorrecto cuando la página se lee más adelante por SQL Server, la página se escribió incorrectamente; se detecta una página desgarrada. Las páginas desgarradas se detectan durante la recuperación porque es probable que la recuperación lea cualquier página que se escribió incorrectamente.

Aunque SQL Server páginas de base de datos tienen 8 KB, los discos realizan operaciones de E/S mediante un sector de 512 bytes. Por lo tanto, se escriben 16 sectores por página de base de datos. Puede producirse una página desgarrada si el sistema produce un error (por ejemplo, debido a un error de alimentación) entre el momento en que el sistema operativo escribe el primer sector de 512 bytes en el disco y la finalización de la operación de E/S de 8 KB. Si el primer sector de una página de base de datos se escribe correctamente antes del error, la página de base de datos del disco aparecerá como actualizada, aunque es posible que no se haya realizado correctamente.

Mediante el uso de memorias caché de controladores de disco con respaldo de batería, puede asegurarse de que los datos se escriben correctamente en el disco o no se escriben en absoluto. En esta situación, no establezca la detección de páginas rasgadas en "true" porque esto no es necesario.

Paridad de registros

La comprobación de paridad de registros es similar a la detección de páginas rasgadas. Cada sector de 512 bytes contiene bits de paridad. Estos bits de paridad siempre se escriben con el registro de registro y se evalúan cuando se recupera el registro de registro. Al forzar las escrituras de registros en un límite de 512 bytes, SQL Server puede asegurarse de que las operaciones de confirmación se escriben en los sectores de disco físico.

Impactos en el rendimiento

Todas las versiones de SQL Server abrir los archivos de registro y datos mediante la función CreateFile win32. El miembro dwFlagsAndAttributes incluye la FILE_FLAG_WRITE_THROUGH opción cuando SQL Server los abre.

FILE_FLAG_WRITE_THROUGH indica al sistema que escriba a través de cualquier caché intermedia e vaya directamente al disco. El sistema todavía puede almacenar en caché las operaciones de escritura, pero no puede vaciarlas de forma diferida.

La FILE_FLAG_WRITE_THROUGH opción garantiza que, cuando una operación de escritura devuelve una finalización correcta, los datos se almacenan correctamente en un almacenamiento estable. Esto se alinea con el protocolo WAL que garantiza los datos.

Muchas unidades de disco (SCSI e IDE) contienen cachés incorporadas de 512 KB, 1 MB o más. Sin embargo, las memorias caché de la unidad suelen depender de un condensador y no de una solución respaldada por batería. Estos mecanismos de almacenamiento en caché no pueden garantizar escrituras en un ciclo de energía o un punto de error similar. Solo garantizan la finalización de las operaciones de escritura del sector. Esta es específicamente la razón por la que la detección de paridad de registro y escritura desgarrada se integraron en SQL Server 7.0 y versiones posteriores. A medida que las unidades siguen aumentando de tamaño, las memorias caché se hacen más grandes y pueden exponer grandes cantidades de datos durante un error.

Muchos proveedores de hardware proporcionan soluciones de controlador de disco con respaldo de batería. Estas cachés de controladores pueden mantener los datos en la memoria caché durante varios días e incluso permitir que el hardware de almacenamiento en caché se coloque en un segundo equipo. Cuando la alimentación se restaura correctamente, los datos no escritos se vacían antes de permitir el acceso a datos adicionales. Muchos de ellos permiten establecer un porcentaje de caché de lectura y escritura para un rendimiento óptimo. Algunas contienen áreas de almacenamiento de memoria grandes. De hecho, para un segmento específico del mercado, algunos proveedores de hardware proporcionan sistemas de controladores de almacenamiento en caché de disco con respaldo de batería de gama alta con 6 GB de caché. Esto puede mejorar significativamente el rendimiento de la base de datos.

Las implementaciones avanzadas de almacenamiento en caché controlarán la FILE_FLAG_WRITE_THROUGH solicitud al no deshabilitar la memoria caché del controlador, ya que pueden proporcionar verdaderas funcionalidades de reescritura en caso de un restablecimiento del sistema, un error de energía u otro punto de error.

Las transferencias de E/S sin el uso de una memoria caché pueden ser más largas debido al tiempo mecánico necesario para mover los cabezales de unidad, las velocidades de giro y otros factores de limitación.

Ordenación del sector

Una técnica común que se usa para aumentar el rendimiento de E/S es la ordenación del sector. Para evitar el movimiento mecánico de la cabeza, las solicitudes de lectura y escritura se ordenan, lo que permite un movimiento más coherente de la cabeza para recuperar o almacenar datos.

La memoria caché puede contener varias solicitudes de registro y escritura de datos al mismo tiempo. El protocolo WAL y la implementación SQL Server del protocolo WAL requieren el vaciado de las escrituras de registro en el almacenamiento estable antes de que se pueda emitir la escritura de página. Sin embargo, el uso de la memoria caché podría devolverse correctamente desde una solicitud de escritura de registro sin que los datos se escriban en la unidad real (es decir, escritos en un almacenamiento estable). Esto puede dar lugar a SQL Server la emisión de la solicitud de escritura de página de datos.

Con la implicación de la caché de escritura, los datos se siguen considerando que están en un almacenamiento volátil. Sin embargo, desde la llamada WriteFile de la API Win32, exactamente cómo SQL Server ve la actividad, se obtuvo un código de retorno correcto. SQL Server o cualquier proceso que use la llamada api WriteFile solo puede determinar que los datos han obtenido correctamente un almacenamiento estable.

Con fines de discusión, suponga que todos los sectores de la página de datos están ordenados para escribir antes que los sectores de los registros coincidentes. Esto infringe inmediatamente el protocolo WAL. La memoria caché está escribiendo una página de datos antes de los registros. A menos que la memoria caché esté totalmente respaldada por batería, un error puede provocar resultados catastróficos.

Al evaluar los factores de rendimiento óptimos para un servidor de base de datos, hay muchos factores que se deben tener en cuenta. La más importante de ellas es"¿ Mi sistema permite funcionalidades válidas FILE_FLAG_WRITE_THROUGH ?"

Las pruebas han demostrado que muchas configuraciones de unidades de disco pueden contener almacenamiento en caché de escritura sin la batería adecuada. Las unidades SCSI, IDE y EIDE aprovechan al máximo las cachés de escritura. Para obtener más información sobre cómo funcionan los SSD junto con SQL Server, consulte el siguiente artículo del blog de ingenieros de CSS SQL Server:

SQL Server y SSD: notas de aprendizaje de RDORR: parte 1

En muchas configuraciones, la única manera de deshabilitar correctamente el almacenamiento en caché de escritura de un IDE o una unidad EIDE es mediante una utilidad de fabricante específica o mediante jumpers ubicados en la propia unidad. Para asegurarse de que la memoria caché de escritura está deshabilitada para la propia unidad, póngase en contacto con el fabricante de la unidad.

Las unidades SCSI también tienen cachés de escritura. Sin embargo, el sistema operativo suele deshabilitar estas cachés. Si hay alguna pregunta, póngase en contacto con el fabricante de la unidad para obtener las utilidades adecuadas.

Apilamiento de caché de escritura

El apilamiento de caché de escritura es similar a la ordenación del sector. La siguiente definición se tomó directamente desde un sitio web del fabricante de unidades IDE líder:

Normalmente, este modo está activo. El modo de caché de escritura acepta los datos de escritura del host en el búfer hasta que el búfer está lleno o se completa la transferencia del host.

Una tarea de escritura de disco comienza a almacenar los datos del host en el disco. Los comandos de escritura de host se siguen aceptando y los datos se transfieren al búfer hasta que la pila de comandos de escritura está llena o el búfer de datos está lleno. La unidad puede reordenar los comandos de escritura para optimizar el rendimiento de la unidad.

Reasignación automática de escritura (AWR)

Otra técnica común que se usa para proteger los datos es detectar sectores incorrectos durante la manipulación de datos. La siguiente explicación procede de un sitio web del fabricante de unidades IDE líder:

Esta característica forma parte de la memoria caché de escritura y reduce el riesgo de pérdida de datos durante las operaciones de escritura diferida. Si se produce un error de disco durante el proceso de escritura del disco, la tarea de disco se detiene y el sector sospechoso se reasigna a un grupo de sectores alternativos que se encuentran al final de la unidad. Después de la reasignación, la tarea de escritura en disco continúa hasta que se completa.

Esta puede ser una característica eficaz si se proporciona la copia de seguridad de la batería para la memoria caché. Esto proporciona la modificación adecuada al reiniciar. Es preferible detectar los errores de disco, pero la seguridad de los datos del protocolo WAL requeriría de nuevo que esto se realizara en tiempo real y no de forma diferida. Dentro de los parámetros wal, la técnica de AWR no puede tener en cuenta una situación en la que se produce un error en una escritura de registro debido a un error de sector, pero la unidad está llena. El motor de base de datos debe conocer inmediatamente el error para que la transacción se pueda anular correctamente, se pueda alertar al administrador y se realicen los pasos correctos para proteger los datos y corregir la situación de error de los medios.

Seguridad de datos

Hay varias precauciones que un administrador de base de datos debe tomar para garantizar la seguridad de los datos.

• Siempre es una buena idea asegurarse de que la estrategia de copia de seguridad es suficiente para recuperarse de un error catastrófico. El almacenamiento fuera del sitio y otras precauciones son adecuadas.
• Pruebe la operación de restauración de la base de datos en una base de datos secundaria o de prueba con frecuencia.
• Asegúrese de que los dispositivos de almacenamiento en caché puedan controlar todas las situaciones de error (interrupción de energía, sectores incorrectos, unidades incorrectas, interrupción del sistema, bloqueos, pico de energía, etc.).
• Asegúrese de que el dispositivo de almacenamiento en caché:
  • Ha integrado la copia de seguridad de la batería
  • Puede volver a emitir escrituras al encender
  • Se puede deshabilitar completamente si es necesario
  • Controla la reasignación de sector incorrecta en tiempo real
• Habilite la detección de páginas desgarradas. (Esto tiene poco efecto en el rendimiento).
• Configure las unidades RAID que permiten un intercambio frecuente de una unidad de disco incorrecta, si es posible.
• Use controladores de almacenamiento en caché más recientes que le permitan agregar más espacio en disco sin reiniciar el sistema operativo. Esta puede ser una solución ideal.

Pruebas de unidades

Para proteger completamente los datos, debe asegurarse de que todo el almacenamiento en caché de datos se controla correctamente. En muchas situaciones, debe deshabilitar el almacenamiento en caché de escritura de la unidad de disco.

Microsoft ha realizado pruebas en varias unidades SCSI e IDE mediante la SQLIOSim utilidad . Esta utilidad simula una intensa actividad asincrónica de lectura y escritura en un dispositivo de datos simulado y un dispositivo de registro. Las estadísticas de rendimiento de prueba muestran el promedio de operaciones de escritura por segundo entre 50 y 70 para una unidad con almacenamiento en caché de escritura deshabilitada y un intervalo de RPM entre 5.200 y 7.200.

Para obtener más información sobre la SQLIOSim utilidad, consulte el siguiente artículo en Microsoft Knowledge Base:

Cómo usar la utilidad SQLIOSim para simular SQL Server actividad en un subsistema de disco

Muchos fabricantes de equipos ordenan las unidades con la memoria caché de escritura deshabilitada. Sin embargo, las pruebas muestran que puede que no siempre sea así. Por lo tanto, pruebe siempre por completo.

Dispositivos de datos

En todas las situaciones, pero no registradas, SQL Server solo necesitarán vaciar los registros. Al realizar operaciones no registradas, las páginas de datos también deben vaciarse en un almacenamiento estable; no hay registros individuales para volver a generar las acciones en caso de error.

Las páginas de datos pueden permanecer en caché hasta que el proceso LazyWriter o CheckPoint las vacíe a un almacenamiento estable. El uso del protocolo WAL para asegurarse de que los registros se almacenan correctamente garantiza que la recuperación pueda recuperar una página de datos en un estado conocido.

Esto no significa que sea aconsejable colocar archivos de datos en una unidad almacenada en caché. Cuando el SQL Server vacía las páginas de datos en un almacenamiento estable, los registros de registro se pueden truncar del registro de transacciones. Si las páginas de datos se almacenan en la caché volátil, es posible truncar los registros que se usarían para recuperar una página en caso de error. Asegúrese de que los dispositivos de datos y de registro se adapten correctamente al almacenamiento estable.

Aumento del rendimiento

La primera pregunta que podría surgirle es: "Tengo una unidad IDE que estaba almacenando en caché. Pero cuando lo deshabilitó, mi rendimiento fue menor de lo esperado. ¿Por qué?"

Muchas de las unidades ide que microsoft prueba se ejecutan a 5200 RPM y las unidades SCSI a 7200 RPM. Al deshabilitar el almacenamiento en caché de escritura de la unidad IDE, el rendimiento mecánico puede convertirse en un factor.

Para solucionar la diferencia de rendimiento, el método que se debe seguir es claro: "Abordar la tasa de transacciones".

Muchos sistemas de procesamiento de transacciones en línea (OLTP) requieren una alta tasa de transacciones. Para estos sistemas, considere la posibilidad de usar un controlador de almacenamiento en caché que pueda admitir adecuadamente una caché de escritura y proporcionar el aumento de rendimiento deseado a la vez que garantiza la integridad de los datos.

Para observar cambios significativos en el rendimiento que se producen en SQL Server en una unidad de almacenamiento en caché, la tasa de transacciones se incrementó mediante transacciones pequeñas.

Las pruebas muestran que una alta actividad de escritura de búferes que tiene menos de 512 KB o más de 2 MB puede provocar un rendimiento lento.

Veamos el ejemplo siguiente:

CREATE TABLE tblTest ( iID int IDENTITY(1,1), strData char(10))
GO

SET NOCOUNT ON
GO

INSERT INTO tblTest VALUES ('Test')
WHILE @@IDENTITY < 10000
INSERT INTO tblTest VALUES ('Test')

Los siguientes son resultados de prueba de ejemplo para SQL Server:

SCSI(7200 RPM) 84 seconds
SCSI(7200 RPM) 15 seconds (Caching controller)

IDE(5200 RPM) 14 seconds (Drive cache enabled)
IDE(5200 RPM) 160 seconds

El proceso de encapsular toda la serie de INSERT operaciones en una sola transacción se ejecuta en aproximadamente cuatro segundos en todas las configuraciones. Esto se debe al número de vaciados de registros necesarios. Si no crea una sola transacción, cada INSERT se procesa como una transacción independiente. Por lo tanto, se deben vaciar todos los registros de la transacción. Cada vaciado tiene un tamaño de 512 bytes. Esto requiere una intervención de accionamiento mecánica significativa.

Cuando se usa una sola transacción, se pueden agrupar los registros de la transacción y se puede usar una sola escritura más grande para vaciar los registros recopilados. Esto reduce significativamente la intervención mecánica.

Examine las instrucciones asociadas al vaciado del registro para determinar si se puede reducir el valor de Bytes de registro vaciados/s. En el ejemplo anterior, se usó una sola transacción. Sin embargo, en muchos escenarios, esto puede provocar un comportamiento de bloqueo no deseado. Examine el diseño de la transacción. Puede usar código similar al siguiente para ejecutar lotes con el fin de reducir la actividad de vaciado de registros frecuente y pequeña:

BEGIN TRAN
GO

INSERT INTO tblTest VALUES ('Test')
WHILE @@IDENTITY < 50
    BEGIN
        INSERT INTO tblTest VALUES ('Test')
  
        if(0 = cast(@@IDENTITY as int) % 10)
        BEGIN
            PRINT 'Commit tran batch'
            COMMIT TRAN
            BEGIN TRAN
        END
    END
GO

COMMIT TRAN
GO

SQL Server requiere que los sistemas admitan la entrega garantizada a medios estables, como se describe en el documento de descarga de requisitos de revisión del programa de confiabilidad de E/S de SQL Server. Para obtener más información sobre los requisitos de entrada y salida para el motor de base de datos de SQL Server, vea Motor de base de datos de Microsoft SQL Server Requisitos de entrada y salida.