Puntos de control de base de datos (SQL Server)

2015-10-13

En este tema se proporciona información general sobre los puntos de control de base de datos de SQL Server. Un punto de control crea un buen punto conocido desde el que el motor de base de datos de SQL Server puede empezar a aplicar los cambios contenidos en el registro durante la recuperación después de un apagado inesperado o bloqueo.

Introducción a los puntos de control

Por motivos de rendimiento, el motor de base de datos realiza modificaciones en las páginas de base de datos en la memoria caché del búfer y no escribe estas páginas en el disco después de cada cambio. En su lugar, el motor de base de datos emite periódicamente un punto de control en cada base de datos. Un punto de control escribe las páginas modificadas en memoria actuales (conocidas como páginas desfasadas) y la información del registro de transacciones de la memoria en el disco y, además, registra información sobre el registro de transacciones.

El motor de base de datos admite varios tipos de puntos de comprobación: automático, indirecto, manual e interno. En la tabla siguiente se resumen los tipos de puntos de control.

Nombre	Interfaz de Transact-SQL	Descripción
Automático	EXEC sp_configure *'`recovery interval`','`seconds`'*	Emitido automáticamente en segundo plano para cumplir el límite de tiempo superior sugerido por la opción de configuración del `recovery interval` servidor. Los puntos de control automáticos se ejecutan hasta completarse. Los puntos de control automáticos se limitan en función del número de escrituras pendientes y de si el motor de base de datos detecta un aumento de la latencia de escritura por encima de 20 milisegundos. Para obtener más información, consulte Configurar la opción de configuración del servidor para el intervalo de recuperación.
Indirecto	ALTER DATABASE ... SET TARGET_RECOVERY_TIME =target_recovery_time { SECONDS \| MINUTES }	Realizado en segundo plano para satisfacer un tiempo de recuperación objetivo especificado por el usuario para una base de datos determinada. El tiempo de recuperación de destino predeterminado es 0, lo que hace que se utilicen heurísticas de puntos de comprobación automáticas en la base de datos. Si ha usado ALTER DATABASE para establecer TARGET_RECOVERY_TIME en >0, se usa este valor, en lugar del intervalo de recuperación especificado para la instancia del servidor. Para obtener más información, vea Cambiar el tiempo de recuperación de destino de una base de datos (SQL Server).
Manual de instrucciones	PUNTO DE CONTROL [ checkpoint_duration ]	Emitido al ejecutar un comando Transact-SQL CHECKPOINT. El punto de control manual se produce en la base de datos actual para tu conexión. De forma predeterminada, los puntos de control manuales se ejecutan hasta completarse. La limitación funciona de la misma manera que para los puntos de control automáticos. Opcionalmente, el parámetro checkpoint_duration especifica una cantidad de tiempo solicitada, en segundos, para que se complete el punto de control. Para obtener más información, vea CHECKPOINT (Transact-SQL).
Interno	Ninguno.	Emitido por varias operaciones de servidor, como la copia de seguridad y la creación de instantáneas de base de datos para garantizar que las imágenes de disco coincidan con el estado actual del registro.

Nota:

La -kopción de configuración avanzada de SQL Server permite a un administrador de bases de datos limitar el comportamiento de E/S del punto de comprobación en función del rendimiento del subsistema de E/S para algunos tipos de puntos de comprobación. La -k opción de configuración se aplica a los puntos de control automáticos y a los puntos de control manuales e internos sin restricciones.

En el caso de los puntos de control automáticos, manuales e internos, solo es necesario implementar las modificaciones realizadas después del último punto de control durante la recuperación de la base de datos. Esto reduce el tiempo necesario para recuperar una base de datos.

Importante

Las transacciones prolongadas no confirmadas aumentan la duración de la recuperación de todos los tipos de puntos de control.

Interacción de las opciones de TARGET_RECOVERY_TIME y "intervalo de recuperación"

En la siguiente tabla se resume la interacción entre la configuración general del servidor 'sp_configure'recovery interval y el ajuste específico de la base de datos ALTER DATABASE ... TARGET_RECOVERY_TIME.

Tiempo de Recuperación Objetivo	"intervalo de recuperación"	Tipo de punto de control usado
0	0	puntos de control automáticos cuyo intervalo de recuperación objetivo es de 1 minuto.
0	>0	Puntos de control automáticos cuyo intervalo de recuperación de destino se especifica según la configuración definida por el usuario en la opción sp_configure recovery interval.
>0	No aplicable.	Puntos de control indirectos cuyo tiempo de recuperación de destino viene determinado por la configuración de TARGET_RECOVERY_TIME, expresada en segundos.

Puntos de control automáticos

Se produce un punto de comprobación automático cada vez que el número de registros alcanza el número que el motor de base de datos calcula que puede procesar durante el tiempo especificado en la opción de configuración del recovery interval servidor. En todas las bases de datos sin un tiempo de recuperación de destino definido por el usuario, el motor de base de datos genera puntos de control automáticos. La frecuencia de los puntos de control automáticos depende de la recovery interval opción de configuración avanzada del servidor, que especifica el tiempo máximo que una instancia de servidor determinada debe usar para recuperar una base de datos durante un reinicio del sistema. El motor de base de datos calcula el número máximo de registros que puede procesar dentro del intervalo de recuperación. Cuando una base de datos que usa puntos de control automáticos alcanza este número máximo de registros, el motor de base de datos emite un punto de control en la base de datos. El intervalo de tiempo entre los puntos de control automáticos puede ser muy variable. Una base de datos con una carga de trabajo de transacción sustancial tendrá puntos de comprobación más frecuentes que una base de datos que se usa principalmente para las operaciones de solo lectura.

Además, en el modelo de recuperación simple, también se pone en cola un punto de control automático si el registro de transacciones alcanza el 70 % de su capacidad.

En el modelo de recuperación simple, a menos que algún factor retrase el truncamiento del registro, un punto de control automático trunca la sección sin usar del registro de transacciones. Por el contrario, en los modelos de recuperación completos y optimizados para cargas masivas de registros, una vez establecida una cadena de copia de seguridad de registros, los puntos de control automáticos no provocan el truncamiento del registro. Para más información, consulte El registro de transacciones (SQL Server).

Después de un bloqueo del sistema, el período de tiempo necesario para recuperar una base de datos determinada depende en gran medida de la cantidad de E/S aleatoria necesaria para rehacer páginas que estaban sucias en el momento del bloqueo. Esto significa que la recovery interval configuración no es confiable. No puede determinar una duración de recuperación precisa. Además, cuando un punto de control automático está en curso, la actividad de E/S general para los datos aumenta significativamente de manera bastante impredecible.

Impacto del intervalo de recuperación en el rendimiento de la recuperación

Para un sistema de procesamiento de transacciones en línea (OLTP) mediante transacciones cortas, recovery interval es el factor principal que determina el tiempo de recuperación. Sin embargo, la recovery interval opción no afecta al tiempo necesario para deshacer una transacción de larga duración. La recuperación de una base de datos con una transacción de ejecución prolongada puede tardar mucho más tiempo que la especificada en la recovery interval opción . Por ejemplo, si una transacción de ejecución prolongada tardó dos horas en realizar actualizaciones antes de que la instancia del servidor se deshabilitara, la recuperación real tarda considerablemente más tiempo que el recovery interval valor para recuperar la transacción larga. Para obtener más información sobre el impacto de una transacción de larga duración en el tiempo de recuperación, vea El registro de transacciones (SQL Server).

Normalmente, los valores predeterminados proporcionan un rendimiento de recuperación óptimo. Sin embargo, cambiar el intervalo de recuperación podría mejorar el rendimiento en las siguientes circunstancias:

Si el proceso de recuperación tarda significativamente más de 1 minuto sin revertir las transacciones de larga duración.
Si observa que los puntos de control frecuentes afectan al rendimiento en una base de datos.

Si decide aumentar la recovery interval configuración, se recomienda aumentarla gradualmente por pequeños incrementos y evaluar el efecto de cada aumento incremental en el rendimiento de la recuperación. Este enfoque es importante porque a medida que aumenta la configuración, la recovery interval recuperación de la base de datos tarda muchas veces más en completarse. Por ejemplo, si cambia recovery interval 10, la recuperación tarda aproximadamente 10 veces más en completarse que cuando recovery interval se establece en cero.

Puntos de control indirectos

Los puntos de control indirectos, nuevos en SQL Server 2012, proporcionan una alternativa configurable de nivel de base de datos a los puntos de control automáticos. En caso de bloqueo del sistema, los puntos de comprobación indirectos proporcionan un tiempo de recuperación potencialmente más rápido y predecible que los puntos de control automáticos. Los puntos de control indirectos ofrecen las siguientes ventajas:

Una carga de trabajo transaccional en línea en una base de datos que esté configurada para puntos de control indirectos podría experimentar una degradación del rendimiento. Los puntos de comprobación indirectos garantizan que el número de páginas desfasadas se encuentra por debajo de un umbral determinado para que la recuperación de la base de datos finalice en el tiempo de recuperación de destino. La opción de configuración de intervalo de recuperación usa el número de transacciones para determinar el tiempo de recuperación a diferencia de los puntos de comprobación indirectos, que usan el número de páginas desfasadas. Cuando se habilitan los puntos de comprobación indirectos en una base de datos que recibe un gran número de operaciones de DML, el escritor en segundo plano puede iniciar agresivamente el vaciado de búferes desfasados en el disco para asegurarse de que el tiempo necesario para realizar la recuperación se encuentra dentro del tiempo de recuperación de destino establecido de la base de datos. Esto puede provocar actividad de E/S adicional en determinados sistemas que pueden contribuir a un cuello de botella de rendimiento si el subsistema del disco está funcionando por encima del umbral de E/S o cerca de él.
Los puntos de comprobación indirectos permiten controlar de forma confiable el tiempo de recuperación de la base de datos considerando el costo de E/S aleatoria durante REDO. Esto permite que una instancia de servidor permanezca dentro de un límite superior en los tiempos de recuperación de una base de datos determinada (excepto cuando una transacción de larga duración provoca tiempos de UNDO excesivos).
Los puntos de control indirectos reducen la E/S relacionada con los puntos de control mediante la escritura continua de páginas sucias en el disco en segundo plano.

Sin embargo, una carga de trabajo transaccional en línea en una base de datos configurada para puntos de comprobación indirectos podría experimentar una degradación del rendimiento. Esto se debe a que el escritor en segundo plano usado por el punto de control indirecto a veces aumenta la carga total de escritura para una instancia de servidor.

Puntos de control internos

Varios componentes de servidor generan puntos de control internos para garantizar que las imágenes de disco coincidan con el estado actual del registro. El punto de control interno se genera en respuesta a los siguientes eventos:

Los archivos de base de datos se han agregado o quitado mediante ALTER DATABASE.
Se realiza una copia de seguridad de la base de datos.
Se crea una instantánea de base de datos, ya sea explícita o internamente para DBCC CHECK.
Se realiza una actividad que requiere el apagado de una base de datos. Por ejemplo, AUTO_CLOSE es ON y se cierra la última conexión de usuario a la base de datos o se realiza un cambio de opción de base de datos que requiere un reinicio de la base de datos.
Una instancia de SQL Server se apaga al detener el servicio SQL Server (MSSQLSERVER). Cualquiera de las acciones provoca un punto de control en cada base de datos de la instancia de SQL Server.
Desconectar una instancia de clúster de conmutación por error (FCI) de SQL Server.